RE: stats in 11g

From: Jorgensen, Finn <Finn.Jorgensen_at_constellation.com>
Date: Mon, 18 Jul 2011 09:25:48 -0400
Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF026940B32B1B_at_EXM-OMF-04.Ceg.Corp.Net>

In the dbmsstat.sql file you can see the definition of gather_database_stats as :

  procedure gather_database_stats
    (estimate_percent number default DEFAULT_ESTIMATE_PERCENT,

     block_sample boolean default FALSE,
     method_opt varchar2 default DEFAULT_METHOD_OPT,
     degree number default to_degree_type(get_param('DEGREE')),
     granularity varchar2 default DEFAULT_GRANULARITY,
     cascade boolean default DEFAULT_CASCADE,
     stattab varchar2 default null, statid varchar2 default null,
     options varchar2 default 'GATHER', statown varchar2 default null,
     gather_sys boolean default TRUE,
     no_invalidate boolean default
       to_no_invalidate_type(get_param('NO_INVALIDATE')),
     gather_temp boolean default FALSE,
     gather_fixed boolean default FALSE,
     stattype varchar2 default 'DATA',
     obj_filter_list ObjectTab default null);

Notice the OPTIONS parameter which default to GATHER. Options can take the following values (also from dbmsstat.sql) :

• options - further specification of which objects to gather statistics for
• 'GATHER' - gather statistics on all objects in the schema
• 'GATHER AUTO' - gather all necessary statistics automatically. Oracle
• implicitly determines which objects need new statistics, and
• determines how to gather those statistics. When 'GATHER AUTO' is
• specified, the only additional valid parameters are stattab,
• statid, objlist and statown; all other parameter settings will be
• ignored. Also, return a list of objects processed.
• 'GATHER STALE' - gather statistics on stale objects as determined
• by looking at the *_tab_modifications views. Also, return
• a list of objects found to be stale.
• 'GATHER EMPTY' - gather statistics on objects which currently
• have no statistics. also, return a list of objects found
• to have no statistics.
• 'LIST AUTO' - return list of objects to be processed with 'GATHER AUTO'
• 'LIST STALE' - return list of stale objects as determined
• by looking at the *_tab_modifications views
• 'LIST EMPTY' - return list of objects which currently
• have no statistics

Looks like you will want to change that to GATHER STALE to get where you want to be.

Thanks,
Finn

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of K R Sent: Friday, July 15, 2011 9:43 PM
To: Oracle-L_at_freelists.org
Subject: stats in 11g

All,

on 11.2.0.2 I only run this to gather statistics DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>dbms_stats.auto_sample_size);

But I see that dbms_stats.auto_sample_size is touching all the tables in the db instead of only the tables those were changed . is this normal?

and I see few of the user table and SYS tables stats getting locked

ORA-20005: object statistics are locked (stattype = ALL)

what will be a recommended approach to gather stats in 11gR2.

appreciate any suggestions

TIA
Kart
>>> This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 18 2011 - 08:25:48 CDT