Possible approaches to managing optimizer statistics

Document Sample
Possible approaches to managing optimizer statistics Powered By Docstoc
					Options for managing
 Optimizer Statistics

       John Hurley
    Senior Oracle DBA
 Summit Racing Equipment
      Old and New Strategies and Tactics
• Never make any changes to Optimizer Stats
   … just say no …
• Re-assign any DBA who starts thinking about Stats changes
   … hello from the remote office in Saskatchewan …
• Generate new statistics periodically
   ( often monthly or quarterly or yearly … not usually every week ).
• Generate new statistics only when a specific problem makes
  the old set(s) no longer viable.
• Never create any histograms on columns.
• Allow the Oracle database software to make all decisions
  about managing statistics for the entire database.
• Allow Oracle to only manage statistics for Oracle system
  type of schemas. ( User schemas versus system schemas ).
     Oracle makes all the decisions?

• Clearly this is the direction that Oracle
  corporation is promoting.
• Eventually these types of decisions and
  choices may be removed from even being an
  option ( version 15? … dynamic sampling for
  everything? ).
• How many DBA and Developers are
  comfortable letting go of all control in this
               Useful sites and authors
• Structured Data by Greg Rahn
   – http://structureddata.org

    One particularly good starting point here might be “Choosing An Optimal Stats Gathering Strategy”
       which can be found from the Most Visited Posts list on the right side.

    Many of the links on the presentation tab are also quite good.

• Optimizer Magic by members of Oracle Optimizer team
   – http://optimizermagic.blogspot.com

    Both of these sites tend to be extremely well done but maybe a little too
      strong on the “this is the way Oracle recommends doing stats”.
     Sites and authors (continued )
• Jonathan Lewis
   – http://jonathanlewis.wordpress.com aka Oracle Scratchpad
• Tom Kyte
   – http://asktom.oracle.com
• Richard Foote
   – http://richardfoote.wordpress.com
• Randolf Geist
   – http://oracle-randolf.blogspot.com
• Christian Antognini
• A compromise solution that will let the Oracle
  database software make decisions about
  system schemas but leave your application
  schemas alone.
• Put into effect via executing
• User schemas are left alone … so you could fall
  back into the never generate new stats!
When do new statistics take effect?
• In the old days before 10g ( might have been before 10.2 ? )
  when new statistics were generated all existing SQL that
  referenced the tables that were changed was invalidated.
• How does this work in more recent releases?
• Anyone familiar with ROLLING INVALIDATION?
• If set to FALSE it works just like it used to.
• Default value for NO_INVALIDATE is
• Behavior works in conjunction of value of
  _optimizer_invalidation_period which has default value of
  18000 ( 5 hours ).
            Dynamic sampling
• First became available in Oracle 9.2.
• The ability of the cost based optimizer to sample the tables a query
  references during a hard parse to determine better default statistics for
  unanalzed segments and to verify its guesses.
• Levels of sampling available between 1 and 10 ( 9.2 defaults to 1 10.1 and
  above defaults to 2 ).
• A technique to consider … perhaps very useful handling empty tables.
• Uses instance parameter ( OPTIMIZER_DYNAMIC_SAMPLING = 2 for
  example ).
• Impact would be immediate for subsequent queries if a call to
  DATE=>FALSE) was done after table was emptied ( truncated hopefully ).
                    What are (some of the)
               current defaults for DBMS_STATS?
procedure gather_table_stats(ownname varchar2, tabname varchar2, partname varchar2 default null,
estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
method_opt varchar2 default DEFAULT_METHOD_OPT,
degree number default to_degree_type(get_param('DEGREE')),
cascade boolean default DEFAULT_CASCADE,
no_invalidate boolean default

METHOD_OPT defaults to ‘FOR ALL COLUMNS SIZE AUTO’ – the optimizer
   may generate histograms for some of the columns in the table.
DEGREE defaults to NULL so statistics will not be gathered in parallel.
CASCADE defaults to DBMS_STATS.AUTO_CASCADE which lets the Oracle
   optimizer decide whether or not index statistics will be re-gathered.
      Useful DBMS_STATS things to remember
• You can export out statistics before changes are made to them.
    – This can be done at a schema level or at a table level.
    – Of course you can import statistics back in from an export.
    – There are always anomalies that must be tested. If you export out schema stats when a
      table had no statistics then import those statistics … the current stats will remain.

    Probably best to delete schema or table statistics before importing and test test test!

• You can lock statistics on many levels
    – This can be used to ensure stability ( prevent statistics changes from influencing
      execution plans ).

• DBMS_STATS does not look at migrated/chained rows
    – The analyze command is still used when looking at this kind of information.
              Adjusting preferences and changing
                   defaults for DBMS_STATS
• 10g had GET_PARAM and SET_PARAM
    Select dbms_stats.get_param(‘ESTIMATE_PERCENT’) from dual;


•     Exec dbms_stats.set_param(‘METHOD_OPT’,’FOR ALL COLUMNS SIZE 1’);

This would disable histogram collection!

•     Exec dbms_stats.set_param(‘CASCADE’,’TRUE’);

This would set CASCADE=>TRUE for all gather stats routines.
             11g added more control
• DBMS_STATS GET_PARAM and SET_PARAM now deprecated ( already! ).
• Procedures in DBMS_STATS SET_*_PREFS offer a much finer granularity of
• Preferences can be set GLOBALLY, at the DATABASE level, at the SCHEMA
  level, or at the TABLE level.
• The DATABASE level means all user defined schemas in the database.
Exec dbms_stats.set_database_prefs(‘METHOD_OPT’,’FOR ALL INDEXED COLUMNS’);
Exec dbms_stats.set_schema_prefs(‘MY_SCHEMA’,’METHOD_OPT’,’FOR ALL COLUMNS SIZE
Exec dbms_stats.set_table_prefs(‘MY_SCHEMA’,’MY_TABLE’,’METHOD_OPT’,’FOR ALL
     My approach for an 11g system
• This follows some of Oracle’s current best practices.
• Gather system workload statistics.
• Gather fixed statistics, data dictionary stats, stats for SYS.
• Set AUTOSTATS_TARGET to ORACLE leave automatic stats jobs running.
• Manage application schema statistics manually.
• Gather SCHEMA stats or all TABLE stats with current DBMS_STATS default.
• Delete statistics for empty tables and use dynamic sampling set to level 2.
• A custom procedure that monthly looks at each table and checks the
  number of rows. If significant changes to the table then gather new
  statistics for the table.
• Monitor tables that are being truncated and each time they are truncated
  delete the statistics and fall back to dynamic sampling.
• Technical details
exec dbms_stats.gather_system_stats(‘START’);

/* run representative workload or phony workload that performs both indexed lookups and full table scans

exec dbms_stats.gather_system_stats(‘STOP’);

exec dbms_stats.gather_fixed_object_stats;
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_schema_stats(‘SYS’);

exec dbms_stats.set_param(‘AUTOSTATS_TARGET’,’ORACLE’);

/* For each loaded table in all user schemas … */

exec dbms_stats.gather_table_stats(ownname=>’SOME SCHEMA’,tabname=>’SOME_TABLE’,CASCADE=>TRUE);

/* For each empty table that has optimizer statistics */

exec dbms_stats.delete_table_stats(ownname=>’SOME_SCHEMA’,tabname=>’SOME_TABLE’);

/* Possibly use NO_INVALIDATE=>FALSE */

Shared By: