Options for managing
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
• How many DBA and Developers are
comfortable letting go of all control in this
Useful sites and authors
• Structured Data by Greg Rahn
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
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
• Richard Foote
• Randolf Geist
• Christian Antognini
Setting the AUTOSTATS_TARGET
• A compromise solution that will let the Oracle
database software make decisions about
system schemas but leave your application
• 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?
• NO_INVALIDATE parameter in DBMS_STATS.
• 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 ).
• 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
• 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?
• Specifically for GATHER_TABLE_STATS (or GATHER_SCHEMA_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
ESTIMATE_PERCENT defaults to DBMS_STATS.AUTO_SAMPLE_SIZE.
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.
NO_INVALIDATE defaults to DBMS_STATS.AUTO_INVALIDATE.
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;
/* CASCADE, DEGREE, NO_INVALIDATE, METHOD_OPT can all be checked */
• 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
COLUMNS SIZE 1’);
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
/* run representative workload or phony workload that performs both indexed lookups and full table scans
/* 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 */
/* Possibly use NO_INVALIDATE=>FALSE */