 |
|
Oracle 11g changes to dbms_stats
Oracle Tips by Burleson Consulting
|
In Oracle 11g we now see
11g
extended optimizer statistics, an
alternative to dynamic_sampling for estimating result set
sizes.
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
Richard Foote notes some important changes to the method_opt
argument in dbms_stats in 10g.
Richard Foote is an experienced real-world working DBA, working
for the Australian Government as a DBA specialist, supporting
mission critical, large scale Oracle databases. Richard shares these
important insights on changes to dbms_stats default behavior in 10g:
“The new default
value of METHOD_OPT with 10g is ‘FOR ALL COLUMNS SIZE AUTO’. This
basically means that Oracle will automatically decide for us which
columns need histograms and which columns don’t based on what it
considers to be the distribution of data within a column and based
on the “workload” associated with the table (basically are there any
SQL statements running in the database referencing columns which
might need histograms for those statements to be costed correctly).
. .
However in
environments with many tables and columns (potentially many
thousands) with many users executing many different SQL statements,
the ramifications of potentially suddenly having thousands of
additional histograms can be disastrous.”
It's a perpetual trade-off between sub-optimal plans and unneeded
overhead.
An exploration of "intelligent histogram creation", is needed, a
method that uses AWR to correlate the SQL to the objects, avoiding
histograms that are never used, and develop a method to only create
histograms that "make a difference".
|
