Bi-modal system configuration

It is not uncommon for databases to be bi-modal, operating OLTP during the day (CPU-intensive) and doing aggregations and rollups (I/O-intensive) at night.

The CPU and I/O statistics can now be captured using dbms_stats  and then swapping them in as the processing mode changes.  Most shops do this with the dbms_scheduler  (dbms_job) package so that the statistics are swapped at the proper time.

With that introduction to the influence of parameters, it is time to examine other Oracle features that influence SQL execution.  The most important system level factor is the schema statistics which have a huge influence on SQL execution.

Statistics and SQL Optimization

The new features in Oracle10g that indicate when statistics are old and need to be recalculated are extremely helpful. Gone are the days when statistics were calculated weekly, or on whatever schedule, just in case the data changed.  It is now possible to know, for certain, one way or the other.  Of course, some will still believe that new statistics should only be calculated if there is a problem, and once a decent access path exists, it should be left alone.

Some believe in the practice of running statistics by schedule such as weekly.  Some believe in just calculating statistics when the data changes.  Still others believe that statistics are only needed to fix a poor access path, and once things are good; they should not be touched.  It is difficult to say who is correct. 

Oracle10g automatically reanalyzes schema statistics based on the number of changes to row in the table, but it may be sub-optimal, and many senior Oracle DBAs use more sophisticated methods for determining when to re-analyze CBO statistics.

Although the Oracle CBO is one of the world’s most sophisticated software achievements, it is still the job of the Oracle professional to provide valid statistics for the schema and understand how Oracle parameters affect the overall performance of the SQL optimizer.

Keep in mind, suboptimal SQL execution plans are a major reason for poorly performing Oracle databases, and because the CBO determines the execution plans, it is a critical component in Oracle optimization.

The dbms_stats  utility is a great way to improve SQL execution speed. By using dbms_stats to collect top quality statistics, the CBO will usually make an intelligent decision about the fastest way to execute any SQL query. The dbms_stats utility continues to improve and the exciting new features of automatic sample size and automatic histogram generation greatly simplify the job of the Oracle professional.

SEE CODE DEPOT FOR FULL SCRIPTS

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts: