Analyzing usage and data

Oracle has various ways of analyzing tables and table objects such as indexes and partitions.  How these statistics are maintained and gathered is very important in terms of performance.  Additional information can help the optimizer in creating the optimal execution plan for a query.  Too much information can slow down parsing since the optimizer has more options to choose from.  As a general rule, if there are any, do not gather table statistics if performance is adequate.  New statistics can result in worse query optimization in certain cases.  If it ain’t broken – don’t fix it.

On the other hand, if you have a badly performing query that “should” execute faster, gathering, or at least examining, the statistics may be the first resort.

Restoring statistics

As of Oracle 10g (and perhaps earlier, I haven’t checked) Oracle maintains a history of statistics meaning that if you analyze a table (either by dbms_stats or analyze table)  and then find out that as a result all your queries run slower since something in the statistics has thrown the optimizer off (there may be other reasons, such as bind variable peeking),  you can restore the previous statistics.  By default Oracle keeps old statistics for  31 days before dropping them.

You restore old statistics with the RESTORE procedures of DBMS_STATS.  You can restore table, schema and database stats and some more.

Copying statistics

Another interesting procedure of the DBMS_STATS package is i.e.:

copy_table_stats(’OWN’, ’T1’ ,’PREVIOUS_PARTITION’,’CURRENT_PARTITION’);

This will copy all relevant statistics for table T1 from a partition named PREVIOUS_PARTITION to a partition named CURRENT_PARTITION.  If the data is close in enough, this will save you a lot of time otherwise spent on analyzing.

 This procedure has a SCALE parameter in 11g that allows you to scale your data according to size.  The default is 1 meaning that no scaling takes place (cardinality changes).  This procedure also takes care of high and low values for partition columns etc. so they make sense in the new partition statistics.  Note that this is unsupported in 10g but so far I’ve found no problems with it.

Monitoring index usage

It can be useful to know if a table/index is being used or not.  Monitoring index usage is very straight forward, you simply issue the command:

ALTER INDEX index_name MONITORING USAGE;

To stop monitoring issue the command:

ALTER INDEX index_name NOMONITORING USAGE;

To see the results, look in the view V$OBJECT_USAGE and it will tell you whether an index has been used or not after monitoring was turned on.  This sometimes shows surprising results.  I recently dropped 13 indexes of a 100 000 000 row table since they wera not being referred to by queries.  Considerable space was freed and considerable redo and cpu was saved as well.  Just be careful when doing this since some indexes are created for a specific purpose (such as end of month runs) and it may be normal not to use and index for some time.