A Quick Guide to improve SQL performance!!

 Author: Don Burleson

Generating CBO statistics:

Many database administrators use the old ‘ analyze table' and ‘ dbms_utility' methods in order to generate CBO statistics. However these methods are obsolete and somewhat dangerous to SQL performance.  This is because the cost-based SQL Optimizer (CBO) relies on the quality of the statistics to choose the best execution plan for all SQL statements. 

dbms_stats:

The dbms_stats utility does a far better job in estimating the CBO statistics. Especially it works good for large partitioned tables, and the better stats results in faster SQL execution plans.

How does dbms_stats work?

Below is a sample execution of dbms_stats with the options clause

exec dbms_stats.gather_schema_stats( -

     ownname          => 'SCOTT', -

     estimate_percent => dbms_stats.auto_sample_size, -

     method_opt       => 'for all columns size repeat', -

     degree           => 34 -

   )

options clause:

When the options clause is specified you may specify GATHER options.  When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored.

exec dbms_stats.gather_schema_stats( -

     ownname          => 'SCOTT', -

     options          => 'GATHER AUTO'

   )

There are several values for the options parameter

alter table xxx monitoring command:

If you issue the alter table xxx monitoring command, Oracle tracks changed tables with the dba_tab_modifications view.  Below we see that the exact number of inserts, updates and deletes are tracked since the last analysis of statistics.

SQL> desc dba_tab_modifications;

 Name                               Type
 ------------------------------------------------------
 TABLE_OWNER              VARCHAR2(30)
 TABLE_NAME                  VARCHAR2(30)
 PARTITION_NAME          VARCHAR2(30)
 SUBPARTITION_NAME  VARCHAR2(30)
 INSERTS                           NUMBER
 UPDATES                          NUMBER
 DELETES                          NUMBER
 TIMESTAMP                      DATE
 TRUNCATED                    VARCHAR2(3)

gather stale option:

Because all statistics will become stale quickly in a robust OLTP database, we must remember the rule for gather stale is > 10% row change (based on num_rows at statistics collection time). 

Almost every table except read-only tables will be re-analyzed with the gather stale option. Hence, the gather stale option is best for systems that are largely read-only.  For example, if only 5% of the database tables get significant updates, then only 5% of the tables will be re-analyzed with the “gather stale” option.

CASCADE option:

When analyzing specific tables, the cascade option can be used to analyze all related objects based on foreign-key constraints.  For example, stats$snapshot has foreign key referential integrity into all subordinate tables ( stats$sysstat , etc.), so a single analyze can invoke an analyze of all subordinate tables:

exec  dbms_stats.gather_table_stats( -

      ownname          => 'PERFSTAT', -

      tabname          => 'STATS$SNAPSHOT' -

      estimate_percent => dbms_stats.auto_sample_size, -

      method_opt       => 'for all columns size skewonly', -

      cascade          => true, -

      degree           => 7 -

   )

DEGREE Option:

CBO does full-table and full-index scans and hence you can also parallelize the collection of statistics.  When you set degree=x , Oracle will invoke parallel query slave processes to speed up table access.  Degree is usually about equal to the number of CPUs, minus 1 (for the OPQ query coordinator).

estimate_percent argument:

You can specify the sample size for dbms_stats .  The estimate_percent argument allow Oracle's dbms_stats to automatically estimate the best percentage of a segment to sample when gathering statistics:

      estimate_percent => dbms_stats. auto_sample_size

dba_tables sample_size column:

You can verify the accuracy of the automatic statistics sampling by looking at the dba_tables sample_size column.  Oracle chooses between 5% to 20% for a sample_size when using automatic sampling.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   A Guide to Oracle Bitmap Index Techniques!!
   Efficient Tips for Finding Unused Oracle Segments!!
   Expert Tips on Drop Temporary Tablespace Hangs!!
   A Few Things To Know Regarding Databases