11 House Keeping in Oracle

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)