Notes on the Oracle webinar about How to prevent impact of statistics gathering on application queries in 11G? (Access to Oracle Support required).
The gist of the webinar seemed to be the use of deferred statistics collection to avoid unexpected changes in plans for SQL running in production.
The agenda is:
- New enhancements in 11G
- Deferred Statistics
- Other features
New Enhancements in 11G
The enhancements for Oracle 11G are (Slide #14):
- Automatic optimizer statistics collection become fine grained
AUTO_SAMPLE_SIZEmore efficient for estimate_percent
- GRANULARITY to AUTO for partition statistics
- INCREMENTAL statistics for partitioned tables (11.1+)
- Using pending statistics
Deferred Statistics Publishing
This feature prevents the impact of statistics gathering on application queries in 11G using this mechanism. (Slide #15)
- Publish statistics automatically (default)
- Save as pending
- Allow DBA to validate
Preference set to
PUBLISH=FALSE to save collected statistics as pending into
OPTIMIZER_USE_PENDING_STATISTICS=TRUE to test the effect of pending statistics on queries in a session. (Slide #16).
PUBLISH_PENDING_STATS to publish the pending statistics.
Statistics can be exported to test system via
EXPORT_PENDING_STATS into a staging table. This staging table is sent to the test environment via expdp/impdp. The procedure
IMPORT_TABLE_STATS copies the statistics from the staging table into the Data Dictionary. The production and test systems should have the same system statistics.
exec dbms_stats.set_table_prefs('SH','CUSTOMERS','PUBLISH',FALSE'); exec dbms_stats.gather_table_stats('SH','CUSTOMERS'); alter session set optimizer_use_pending_statistics = true; -- execute workload and measure performance exec dbms_stats.publish_pending_stats('SH','CUSTOMERS');
Other New Features related to Statistics
- Statistics Preferences
- Incremental Statistics Collection for Partitions
- Multicolumn Statistics
- Deferred Statistics Publishing
New preferences in 11G are:
The preferences can be set at:
- Statement level (
- Table level via
- Schema Level via
- Database level via
- Global level via
SET_GLOBAL_PREFS(includes SYS and SYSTEM)
DBA_TAB_STAT_PREFS has all of the table level preferences for statistics gathering.
Partitioned Tables and Incremental Statistics
For 11G onwards, incremental statistics gathering is available. Allows for efficient statistics gathering.
GRANULARITY=GLOBAL% & INCREMENTAL=TRUE
Hash-based Sampling for Column Statistics
New approximate counting techique based on hash sampling when
ESTIMATE_PERCENT is set to
AUTO_SAMPLE_SIZE. (Slide #21)
New feature in 11G.
The CBO assumes that columns are independent, and so calculates the selectivity for a conjunction of two (2) columns is done as follows:
Selectivity(MAKE and MODEL) = Selectivity(MAKE) * Selectivity(MODEL)
If the columns, MAKE and MODEL, are not independent in the AUTO table, use:
select dbms_stats.create_extended_stats(USER, 'AUTO','(MAKE,MODEL)') from dual; exec dbms_stats.gather_table_stats(USER, 'AUTO', method_opt='for all columns size 1 for columns(make,model) size 3')
Can also do this for column expressions, e.g.
Can use function based indices. An alternative is to use expression statistics as follows:
select dbms_stats.create_extended_stats(USER, 'AUTO','(UPPER(model))') from dual; exec dbms_stats.gather_table_stats(USER, 'AUTO', method_opt='for all columns size 1 for columns(UPPER(model)) size 3')
Oracle Support Documents
- How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g [ID 465787.1]
- How to Use Pending Statistics [ID 1456776.1]
- MultiColumn/Column Group Statistics Examples [ID 452883.1]
- MultiColumn/Column Group Statistics – Additional Examples [ID 872406.1]
- Overview of 11g Statistics Preferences [ID 1185043.1]