Invalid Optimizer Statistics
- Statistics are used by the optimizer to come up with a good execution plan
- Sometimes optimizer statistics might be stale or missing
The statistics may not always be accurate because the size of the table might have changed from a few 100k rows to a few million rows. There fore statistics should be periodically collected and be made as part of the database maintenance routines.
Always do the following
- Check Last_analyzed date
- Ensure tables, indexes, columns and partitions have statistics
/* Table Statistics for schema Inventory*/ SELECT * from DBA_TAB_STATISTICS where owner='sales_history'; /* Index Statistics for schema Inventory*/ SELECT * FROM DBA_IND_STATISTICS where owner='sales_history'; /* table Column Statistics for schema Inventory */ SELECT * FROM DBA_TAB_COL_STATISTICS where owner='sales_history';
- Re-gather Statistics via DBMS_STATS
exec dbms_stats.gather_table_stats ( user, 'sales_history', cascade=>true; ); /* cascade= true makes sure that table, index, table column & partition statistics are collected */
The last_analyzed column will give the date of the last analyzed date for the tables in the schema.
How do I know which all statements are performing badly?
Buffer Gets and Disk Reads
- Oracle storage is organized into blocks of a given size (e.g. 8 k) . Tables and indexes are formed of a series of blocks on disk. When these blocks are in memory they occupy a buffer.
- When Oracle requires a block, first it checks if the block it needs is already in memory. If so, in memory version is used. If it does not have the block in memory, then it will read if from disk into memory.
- So a buffer get represents the number of times Oracle had to access a block.
When ever the database has to go to the hard disk to get the data it is called a disk read, where as when the data is obtained from the RAM or primary memory it is called a buffer get.
All the SQL statements executed are stored in the V$SQLAREA view. By querying this view we can get the queries where the buffer get < 80%. That means these SQL statements are going into the physical disk to read the data.
For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)
Alternatively click through the link if you found this article interesting. (This will help elevate my companies Search engine rankings)