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
——————————–
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)

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.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: