Starting in Oracle9i
release 2 we have several super-exciting ways to automate the tedious
task of providing the cost-based SQL optimizer with index histograms:
Details on new method_opt features:
These automate the
detection of columns that require histograms, and automatically create
them:
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
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. This is normally performed for foreign key
constraints, and non-constraint, non-unique columns that are used in
table joins. This is a critical task for the CBO to determine
the optimal sequence for joining many tables together as efficiently
as possible.
-
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.
 |
|
Need Oracle training?
- Get Oracle training from a practicing Oracle
expert
- Get custom training designed to
fit your needs
- Conveniently offered at your
workplace, anywhere in the USA
BC Oracle training offers some of the
USA's most respected Oracle experts and authors. Why spend
thousands on cookie cutter Oracle classes when you can have the
personalized attention of a real Oracle
guru? Just call now: |
|
