 |
|
Oracle 11g
dbms_stats enhancements
Oracle11g Tips by Burleson Consulting
October 13, 2007 |
These are work in
progress excerpts from
the book "Oracle
11g New Features" authored by John Garmany, with Oracle ACE's Steve
Karam, Lutz Hartmann, V.J. Jain and Brian Carr.
Oracle 11g has
the challenge of making their wonderful cost-based optimizer (CBO)
always generate the "best" execution plan for any SQL, a formidable
challenge.
To achieve this
goal, Oracle understands that the quality of their metadata is
critical. Only by knowing the distribution of values with the
tables can the CBO make the "best" decision regarding execution
plans.
Prior to Oracle
10g, adjusting powerful optimizer parameters (optimizer_index_cost_adj) was the only way to compensate for
sample size issues with dbms_stats.
But as of
Oracle 10g, improvements in system statistics collection using dbms_stats.gather_system_stats
(to measure sequential vs. scattered disk I/O speed) plus improved sampling within
dbms_stats had made adjustments to the optimizer parameters a
"worst
practice" exercise in most cases.
Ceteris Parabus, always adjust CBO statistics before adjusting optimizer
parms. For more details on optimizer parameters, see my latest book "Oracle
Tuning: The Definitive Reference".
Improving CBO statistics the smart
way
Oracle
performance guru
Greg Rahn performed a
representative test of the
Oracle11g enhancements to the dbms_stats package, correctly noting that
"skewed" data distributions were problematic in 10g because the
dbms_stats package did not manage skew and out of range values
in an optimal fashion. Rahn also demonstrated several exciting
improvements to dbms_stats:
-
Faster
collection - The dbms_stats package is ~= 2x faster
when collecting statistics. Of course, the recommended
procedure with dbms_stats is to collect a single, deep
sample and save your statistics, but this performance feature is
welcome, especially for very large shops.
-
Refined
automatic sample size - Rahn notes that the 11g
dbms_stats auto_sample_size now collects a statistically
significant sample, even for highly skewed data distributions.
Overall, Oracle
11g has taken great strides into improving the automated collection
of CBO statistics, ensuring that SQL will be optimized with much
less manual intervention.
Traditionally, a skewed
distribution within column values required manual
intervention by adding column histograms.
In Oracle 11g we now see
11g extended
optimizer statistics, an
alternative to dynamic_sampling for estimating result set
sizes.
Oracle guru
Guy Harrison also
offers this advice for 11g statistics collection.
In 11g, I think there are two other ways to get
statistics collected for indexed expressions:
1)
Collect extended statistics directly on the expression.
So for instance, if we had a function SALES_CATEGORY, we might do
this:
DBMS_STATS.gather_table_stats
(ownname => USER,
tabname => ?SALES?,
method_opt => ?FOR ALL
COLUMNS FOR COLUMNS
(sale_category(amount_sold))?
);
2) Create a virtual column on the
expression, then index that column. So for the same
example as above we might create the following virtual column,
then index the column and collect stats as usual:
ALTER TABLE
SALES
ADD
sales_category
GENERATED
ALWAYS AS
(sale_category(amount_sold));
Arup Nanda has a great article on extended statistics with dbms_stats,
specialty histogram analysis using function-based columnar data:
Next, re-gather statistics on the table and collect the
extended statistics on the expression upper(cust_name).
begin
dbms_stats.gather_table_stats (
ownname => 'ARUP',
tabname => 'CUSTOMERS',
method_opt => 'for all columns size skewonly for columns (upper(cust_name))'
);
end;
Alternatively you can define
the column group as part of the gather statistics command.
You do that by placing
these columns in the method_opt parameter of the gather_table_stats procedure in
dbms_stats as shown below:
begin
dbms_stats.gather_table_stats (
ownname => 'ARUP',
tabname => 'BOOKINGS',
estimate_percent=> 100,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS(HOTEL_ID,RATE_CATEGORY)',
cascade => true
See my notes here on
tuning with histograms, which may be obsolete as of Oracle 11g with
their improvements to the dbms_stats collection mechanism:
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
and include the URL for the page.
