RelationalDBDesign RelationalDBDesign 


Query Optimizer  «Prev 

DBMS_STATS create Statistics Table in Oracle

Prior to Oracle 10g, adjusting optimizer parameters was the only way to compensate for sample size issues with dbms_stats. As of Oracle 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats have made adjustments to these parameters less important. With other things the same, always adjust CBO statistics before adjusting optimizer parameters.
The CBO gathers information from many sources, and he has the goal of using DBA-provided metadata to make the most desireable execution plan decision:
Let us examine the following areas of CBO statistics and see how to gather quality statistics for the CBO and how to create an appropriate CBO environment for your database.

Obtaining statistics for the CBO

The choices of executions plans made by the CBO are only as good as the statistics available to it. The old-fashioned analyze table and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance. As we may know, the CBO uses object 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 statistics result in faster SQL execution plans. Here is a sample execution of dbms_stats with the OPTIONS clause:

 
exec dbms_stats.gather_schema_stats( - 
 ownname          => 'SCOTT', - 
 options          => 'GATHER AUTO', - 
 estimate_percent => dbms_stats.auto_sample_size, - 
 method_opt       => 'for all columns size repeat', - 
 degree           => 34 - 
 )

Here is another dbms_stats example that creates histograms on all indexes columns:
BEGIN
dbms_stats.gather_schema_stats(
ownname=>'TPCC',
METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',
CASCADE=>TRUE,
ESTIMATE_PERCENT=>100);
END;
/

DBMS_STATS.CREATE_STAT_TABLE
('scott','savestats');
The CREATE_STAT_TABLE procedure creates a table to hold statistics of the data dictionary

The GATHER_TABLE_STATS collects new statistics on the emp table, and also exports the existing statistics to the stattab table before collecting the new stats

You would test optimizations with the new statistics at this point

DBMS_STATS.DELETE_TABLE_STATS
('scott', 'temp');

If the new statistics result in poor execution plans, you begin the process of restoring the old statistics by dropping the existing statistics

The final restoration step is to import the saved statistics