Note:
See "Introduction to Histograms", "
skewonly", "all
about histograms" and "Oracle
Cardinality and histograms" for more details on using Oracle
histograms to improve execution plans.
Please note that the Oracle SQL optimizer
has been dramatically changed in Oracle9i release 2 and the single
most important task for the DBA is collecting good CBO statistics.
To aid in this, there is a new method_opt
in dbms_stats called skewonly.
Some DBA's recommend that all Oracle DBA's use
the method_opt=skewonly option to
automatically identify skewed column values and generate histograms,
where others recommend a detailed analysis of you existing data and
execution plans.
Oracle column histograms are important for several reasons:
- The CBO needs information about the result set size from
multi-table joins to determine the optimal join order for tables.
- Oracle needs to know about heavily skewed column values when
deciding about whether to use an index vs. a full-table scan.
Here are details about the new automatic histogram generation
features in 9ir2:
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;
/
