Notes on #oracle webinar “How to prevent impact of statistics gathering on application queries in 11G?”


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
  • Demo

New Enhancements in 11G

The enhancements for Oracle 11G are (Slide #14):

  • Automatic optimizer statistics collection become fine grained
  • AUTO_SAMPLE_SIZE more 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)

Options are:

  • Publish statistics automatically (default)
  • Save as pending
    • Allow DBA to validate

Preference set to PUBLISH=FALSE to save collected statistics as pending into DBA_TAB_PENDING_STATS.

Use OPTIMIZER_USE_PENDING_STATISTICS=TRUE to test the effect of pending statistics on queries in a session. (Slide #16).

Use 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.

Slide #17:

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

Statistics Perferences

New preferences in 11G are:

  • PUBLISH
  • INCREMENTAL
  • STALE_PERCENT

The preferences can be set at:

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)

Multicolumn Statistics

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. UPPER(model).

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s