Automating histogram sampling with dbms_stats (zt)

SQL> exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'sxitismg',method_opt=>'for all indexed columns',options=>'GATHER',cascade=>TRUE);

SQL>execute dbms_stats.gather_schema_stats(ownname => ‘scott’,estimate_percent => 20,method_opt=>‘for all indexed columns size 10’, cascade=>true);

[@more@]

exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'scott',method_opt=>'for all indexed columns',options=>'GATHER',cascade=>TRUE);

GATHER_SCHEMA_STATS 下有個引數： cascade

,設定此值為true 時，oracle才對index進行分析；

dbms_stats.gather_table_stats

dbms_stats.gather_index_stats

最好還是用dbms_stats.gather_index_stats來進行分析，不要一次對一個使用者下的物件進行分析，不好。寫個指令碼就行了

SQL> analyze table t3 compute statistics

for table

for all indexes

for all indexed columns size 254;

Automating histogram sampling with dbms_stats
May 14, 2003
Don Burleson



One exciting feature of dbms_stats is the ability to automatically look for columns that should have histograms, and create the histograms. Multi-bucket histograms add a huge parsing overhead to SQL statements, and histograms should ONLY be used when the SQL will choose a different execution plan based upon the column value.

To aid in intelligent histogram generation, Oracle uses the method_opt parameter of dbms_stats. There are also important new options within the method_opt clause, namely skewonly, repeat and auto.

method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
In practice, there is a specific order to use the different options of dbms_stats. See this article for details. Let’s take a close look at each method option.



The method_opt=’SKEWONLY’ dbms_stats Option

The first is the “skewonly” option which very time-intensive because it examines the distribution of values for every column within every index. If dbms_stats discovers an index whose columns are unevenly distributed, it will create histograms for that index to aid the cost-based SQL optimizer in making a decision about index vs. full-table scan access. For example, if an index has one column that is in 50% of the rows, a full-table scan is faster than and index scan to retrieve these rows.

Histograms are also used with SQL that has bind variables and SQL with cursor_sharing enabled. In these cases, the CBO determines if the column value could affect the execution plan, and if so, replaced the bind variable with a literal and performs a hard parse.



--*************************************************************
-- SKEWONLY option – Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************

begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
/


The method_opt=’REPEAT’ dbms_stats Option

Following the one-time detailed analysis, the re-analyze task will be less resource intensive with the REPEAT option. Using the repeat option will only re-analyze indexes with existing histograms, and will not search for other histograms opportunities. This is the way that you will re-analyze you statistics on a regular basis.



--**************************************************************
-- REPEAT OPTION - Only re-analyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only re-analyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;
/


The method_opt=’AUTO’ dbms_stats Option

The auto option is used when monitoring is implemented and creates histograms based upon data distribution and the manner in which the column is accessed by the application (e.g. the workload on the column as determined by monitoring, especially foreign keys to determine the cardinality of table join result sets). Using method_opt=>’auto’ is similar to using the gather auto in the option parameter of dbms_stats.

begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;
/

Remember, analyzing for histograms is time-consuming, and histograms are used under two conditions:
Table join order – The CBO must know the size of the intermediate result sets (cardinality) to properly determine the correct join order the multi-table joins.

Table access method – The CBO needs to know about columns in SQL where clauses, where the column value is skewed such that a full-table scan might be faster than an index range scan. Oracle uses this skew information in conjunction with the clustering_factor columns of the dba_indexes view.

Hence, this is the proper order for using the dbms_stats package to locate proper columns for histograms:

1. Skewonly option - You want to use skewonly to do histograms for skewed columns, for cases where the value will make a difference between a full-table scan and an index scan.

2. Monitor - Next, turn-on monitoring. Issue an “alter table xx monitoring” and “alter index yyy monitoring” command for all segments in your schema. This will monitor workload against

3. Auto option - Once monitoring is in-place, you need to re-analyze with the "auto" option to create histograms for join columns within tables. This is critical for the CBO to determine the proper join order for finding the driving table in multi-table joins.

4. Repeat option - Finally, use the "repeat" option to re-analyze only the existing histograms.

Periodically you will want to re-run the skewonly and auto option to identify any new columns that require histograms. Once located, the repeat option will ensure that they are refreshed with current values.


--------------------------------------------------------------------------------

If you like DBA internal tricks, check-out my new book Creating a Self-tuning Oracle Database by Rampant TechPress. This book is now available at this link: