Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: February 25, 2013 - 4:09 pm UTC

Last updated: July 31, 2013 - 6:37 pm UTC

Version: 11.1.0.7

Viewed 10K+ times! This question is

You Asked

Hello Tom

I want to ask what should be the correct approach for a enterprise standard on statstics gathering (for Oracle DBs). The point is we want to keep it as standard as possible for new builds, upgrades and migrations as possible, but in the meantime are flexible enough to go "on-demand" changes in statistics gathring (like addressing special needs on skipping stats, being more frequent, building histograms etc.).

Right now, what we are doing to our 10g and 11g DBs is:

spool analyze_no_stat.list
select 'exec dbms_stats.gather_table_stats(ownname=>'||chr(39)||owner||chr(39)||',tabname=>'||chr(39)||table_name||chr(39)||',estimate_percent=>$epct,degree=>$dgr,cascade=>TRUE);'
  from (select owner,table_name from dba_tables
          where owner not in ('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','PUBLIC','SCOTT','OPS\$ORACLE','CTXSYS','WKSYS','WMSYS','ORDSYS','MDSYS','TSMSYS','APPLSYS')
            and owner not in ('PERFSTAT')
            and table_name not like '%IOT_OVER%'
            and temporary = 'N'
            and table_name = upper(table_name)
            and (owner,table_name) not in (select owner,table_name from dba_external_tables)
            and table_name not in (select distinct table_name from dba_tab_partitions)
            and last_analyzed is null)
/
spool off


Also we change the above query to order by last_analyzed or do a last_analyzed < (sysdate - 5) etc and call it analyze_oldest.list or analyze_stale.list etc and run it alternately.

The problem is, in some cases, we've come across scenarios where we gather stats on a bunch of tables with method_opt=>'for all columns' and building histograms helps us a lot. I understand we can't be doing one or the other thing without much analysis, but want to know if there's a preferred approach?

Thanks!

and Tom said...

I would suggest in 11g to not specify estimate percent (let it default) at all and avoid method_opt if at all possible.


for estimate percent, see:

https://blogs.oracle.com/optimizer/entry/improvement_of_auto_sampling_statistics_gathering_feature_in_oracle_database_11g

https://blogs.oracle.com/optimizer/entry/i_thought_the_new_auto


for method_opt, see:

"Why Does My Plan Change?"
http://www.oracle.com/technetwork/issue-archive/2006/06-may/o36asktom-099107.html

that would answer your "The problem is, in some cases, we've come across scenarios where we gather stats on a bunch of tables with method_opt=>'for all columns' and building histograms helps us a lot. I understand we can't be doing one or the other thing without much analysis, but want to know if there's a preferred approach? " question.


so you would specify the segment and optionally the degree.

you might maintain a table of "anti defaults" to be used just for specific tables - if you encounter an exception that doesn't work well with the default method_opt (but the sample size should just default in 11g and above)

Bear in mind that any index you've just created or rebuilt would already have statistics on it and would not need cascade=>true, in fact that would just be extra busy work.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 05, 2013 - 11:01 am UTC

Going back to the original question:

I don't think writing and scheduling a single custom gather_stats job is a "correct approach for a enterprise standard on statistics gathering."

Should the starting answer for this question to let the database do it automatically? From the 11.2 docs:

13.2 Managing Automatic Optimizer Statistics Collection

Oracle recommends that you enable automatic optimizer statistics collection. In this case, the database automatically collects optimizer statistics for tables with absent or stale statistics.

http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41282


When you hit a "bad" plan, you address it specifically. Unless every database (parameters, memory setup, etc.), data, schema, server specs, etc. are exactly the same, there is no correct "enterprise standard on statistics gathering."

Automatic statistics

raju, July 23, 2013 - 11:18 am UTC

Tom, thanks for the answer.

is it good use oracle's automatic statistics gathering option or to manually gather statistics with dbms_stats package with auto sampling.
Tom Kyte
July 31, 2013 - 6:37 pm UTC

in general, for many situations, yes.

for every one? no.

so in the end "it depends"
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.