11g New Feature: Statistic Gathering and Publishing Decoupled/Statistics Maintenance Changes

Posted at Wednesday, October 31, 2007
In addition to the extended statistics functionality introduced by 11g, you can now gather statistics without having to publish them right away.

This allows one to gather the statistics and test the efficacy thereof before effecting a live production environment; in other words, existing explain plans will not be invalidated/regenerated.

Reference this 'Oracle by Example' article for a great demonstration: Gathering and Publishing Statistics Independently.

Additionally, Oracle has made some significant changes which makes using the out-of-the-box statistics collection program much more feasible. Namely, DBMS_STATS, has been changed so that you can set statistics collection preferences - think arguments passed to DBMS_STATS.GATHER_*_STATS procedures - at both the global and table levels. In turn, the automatic statistics gathering job will respect these preferences when it runs (during the applicable maintenance window).

In the past, I have always disabled the aforementioned 'AutoTask' job - for this very lack of granular control - in favor of a custom statistics gathering job. This was particularly necessary in Data Warehouse environments due to heavy use of partitioning as well as concomitant data volumes. In such environments, sampling only the partitions that have changed is critical to the conservation of system resources.

With the abovementioned scenario in mind, if you want to employ the out-of-the-box 'AutoTask' job then simply set table preferences (as appropriate) using DBMS_STATS. For instance, in order to gather large partitioned tables' statistics properly, you may wish to alter the following: INCREMENTAL, DEGREE, METHOD_OPT, STALE_PERCENT, and ESTIMATE_PERCENT.

For each, verify the current value for the table:

SQL> select dbms_stats.get_prefs('INCREMENTAL','SH','COSTS') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
--------------------------------------------------------------------------------
FALSE

The current setting for the table looks like the system default.

Alter the value, just for the table in question:

SQL> exec dbms_stats.set_table_prefs('SH','COSTS','INCREMENTAL' ,'TRUE') ;

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('INCREMENTAL','SH','COSTS') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
--------------------------------------------------------------------------------
TRUE

Verify that the global setting and table-specific setting co-exist and are, in fact, different:

SQL> select dbms_stats.get_prefs('INCREMENTAL') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL')
--------------------------------------------------------------------------------
FALSE

Here is another example, where we change METHOD_OPT for a table, analyze it using the DBMS_STATS package and then verify that the number of buckets we specify are created. It is important to note that the DBMS_STATS.SET_TABLE_PREFS doesn't seem to support the full syntax that METHOD_OPT allows:

Set the preference:

SQL> exec dbms_stats.set_table_prefs(ownname=>'SH',tabname=>'SALES',pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE 10');


Gather stats - this should pick up the tables preference:

SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES');
PL/SQL procedure successfully completed.


Verify that the number of histograms are created per the specificed METHOD_OPT:

SQL> select table_name, column_name, count(*) from user_histograms
2 where table_name='SALES'
3 group by table_name, column_name;

TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ ------------------------------ ----------
SALES PROD_ID 11
SALES CHANNEL_ID 4
SALES AMOUNT_SOLD 11
SALES CUST_ID 11
SALES PROMO_ID 4
SALES QUANTITY_SOLD 1
SALES TIME_ID 11

11g, also introduces the ability to easily revert to previous statistics' settings. They have extended the DBMS_STATS package as well as provided new *_STATS_HISTORY views.

Building on our prior example, let's say I analyze the aforementioned table again (with different options), but then I want to revert to the previous statistics above:

Analyze the table:

SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed.

Verify that the number of buckets, are in fact, different:

SQL> select table_name, column_name, count(*) from user_histograms
2 where table_name='SALES'
3 group by table_name, column_name;

TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ -------------------- ----------
SALES PROD_ID 72
SALES CHANNEL_ID 4
SALES AMOUNT_SOLD 250
SALES CUST_ID 255
SALES PROMO_ID 4
SALES QUANTITY_SOLD 1
SALES TIME_ID 2

Query the timestamp for the prior collected stats:

SQL>  select count(*), stats_update_time
2 from user_tab_stats_history
3 where table_name='SALES'
4 group by stats_update_time;

COUNT(*) STATS_UPDATE_TIME
---------- ----------------------------------------
29 25-JAN-08 11.18.09.693351 AM -07:00
29 25-JAN-08 11.18.57.432585 AM -07:00

Restore the old stats for the table:

SQL> exec dbms_stats.restore_table_stats(ownname=>'SH',tabname=>'SALES',AS_OF_TIMESTAMP=>'25-JAN-08 11.18.57.432585 AM -07:00');

PL/SQL procedure successfully completed.

Validate that the old stats have been put back:

SQL> select table_name, column_name, count(*) from user_histograms
2 where table_name='SALES'
3 group by table_name, column_name;

TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ -------------------- ----------
SALES PROD_ID 11
SALES CHANNEL_ID 4
SALES AMOUNT_SOLD 11
SALES CUST_ID 11
SALES PROMO_ID 4
SALES QUANTITY_SOLD 1
SALES TIME_ID 11

Otherwise, Oracle has not changed a lot in terms of Statistics Maintenance. The same instances in which you have had to gather statistics manually still apply: System Statistics (those found in aux_stats$), Fixed Statistics, Volatile Tables, Tables post Direct-load, etc.

Reference the following Oracle docs:

Managing Optimizer Statistics

DBMS_STATS package reference

Labels: , ,