DBA Sensation

December 19, 2008

how to use dbms_stats package in a efficient way

Filed under: [System Performance tuning] — zhefeng @ 4:31 pm

i found a very good doc talking about oracle dbms_stats package from dba-oracle website: here is the link (http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm)

The old fashioned “analyze table” and dbms_utility methods for generating CBO statistics 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. The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats results in faster SQL execution plans.

Let’s see how dbms_stats works. It’s easy! Here 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 –

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 that we need to know about:

*gather – re-analyzes the whole schema.

*gather empty – Only analyze tables that have no existing statistics.

*gather stale – Only re-analyze tables with more than 10% modifications (inserts, updates, deletes).

*gather auto – This will re-analyze objects which currently have no statistics and objects with stale statistics. (like “gather empty”+”gather stale”)

Note that both gather stale and gather auto require monitoring. 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

The most interesting of these options is the 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).

Hence, 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.

The 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 –

The DEGREE Option

Note that you can also parallelize the collection of statistics because the CBO does full-table and full-index scans. 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).
Automating sample size with dbms_stats

Now that we see how the dbms_stats options works, get see how to specify the sample size for dbms_stats. The following estimate_percent argument is a new way to 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

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

In our next installment we will look at automatics the collection of histogram data from dbms_stats.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: