Donald K. Burleson Oracle Tips

New Oracle9i statistics gathering

The Oracle9i cost-based SQL optimizer has been enhanced to take advantage I/O and CPU utilization statistics when determining the optimal execution plan for SQL statements. This information is gathered using the new Oracle9i DBMS_STATS package. We discussed the serious limitations of this earlier in this paper, and since most systems have multiple modes of operation, careful consideration must be given to the times when system stats are gathered.

The DBMS_STATS package is most commonly used with the dynamic SGA feature of Oracle9i.  Because the SGA memory regions can all be changed with alter system commands, the Oracle DBA now has the ability to  adjust the configuration of the SGA depending upon the processing load characteristics.

Hence, Oracle professionals who choose to use DBMS_STATS will sample statistics during each type of processing experienced by their database.  For example, assume that you have a database that runs in OLTP mode during the data and in Decision Support System (DSS) mode at night.

In the example below the stats are gathered during the day when the system runs in OLTP mode and at night when batch operations are running:

-- Create the table to hold the stats
dbms_Stats.Create_Stat_Table (
  ownname => 'PROD',
  stattab => 'dbastats',
  tblspace => 'perfstat');

-- Run during the day gather stats for when the system is in OLTP mode
BEGIN
  dbms_Stats.Gather_System_Stats(
    interval => 300,
    stattab => 'dbatats',
    statid => 'OLTP');
END;
/

-- Start at night to gather stats during batch operations
BEGIN
  Dbms_Stats.Gather_System_Stats(
    interval => 300,
    stattab => 'mystats',
    statid => 'DSS');
END;
/

Once we have gathered samples of the system statistics, the system stats can be switched dynamically as the system changes processing modes:

exec dbms_stats.import_system_stats('dbastats','OLTP');
exec dbms_stats.import_system_stats('dbastats','BATCH');

Of course, this feature is primary for bi-modal Oracle databases where the processing characteristics vary widely.  For homogenous database with consistent query patterns, this feature will make little difference in the execution plan for your queries.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm