3 Replies Latest reply: Jan 17, 2013 10:48 PM by Allen RSS

    11g and automatic stats gathering

    rahulras
      Hi All,

      I am on 11.2 on Linux. Need some help on automatic ( I mean Oracle's default ) stats gathering.
      Let me confess that, I have read the Performance Tuning Manual of 11.2, but could not find answers for my questions.

      What is the job name for (Oracle's default) stats gathering job in 11g?
      Where can I see the time this job runs? how can I change it?
      Where can I see the parameters with which stats is running? i.e. I want to check degree of parallelism, estimate percentage of the automatic stats collection job.
      Also I want the automatic stats collection job to not create any histograms, is that possible? with normal dbms_stats.gather_table_stats, I an set the method_opt and change the histogram setting, how to do it with the automatic stats job?

      Any help is much appriciated.

      Thanks in advance.
        • 1. Re: 11g and automatic stats gathering
          sb92075
          rahulras wrote:
          Hi All,

          I am on 11.2 on Linux. Need some help on automatic ( I mean Oracle's default ) stats gathering.
          Let me confess that, I have read the Performance Tuning Manual of 11.2, but could not find answers for my questions.

          What is the job name for (Oracle's default) stats gathering job in 11g?
          let me guess the answer.
           1  select job_name from dba_scheduler_jobs
            2  where job_name like '%STAT%'
            3* order by 1
          SQL> /
          
          JOB_NAME
          ------------------------------
          BSLN_MAINTAIN_STATS_JOB
          MGMT_STATS_CONFIG_JOB
          Where can I see the time this job runs? how can I change it?
          Where can I see the parameters with which stats is running? i.e. I want to check degree of parallelism, estimate percentage of the automatic stats collection job.
          Also I want the automatic stats collection job to not create any histograms, is that possible? with normal dbms_stats.gather_table_stats, I an set the method_opt and change the histogram setting, how to do it with the automatic stats job?

          Any help is much appriciated.

          Thanks in advance.
          • 2. Re: 11g and automatic stats gathering
            SSR
            What is the job name for (Oracle's default) stats gathering job in 11g? 
            select * from DBA_AUTOTASK_OPERATION;
            auto optimizer stats collection

            Where can I see the time this job runs? how can I change it?
            select * from DBA_AUTOTASK_SCHEDULE;
            EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE( 'WEEKNIGHT_WINDOW', 'repeat_interval', 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');

            Ref:
            How to Change The Default WINDOW_START_TIME of Automatic Statistics Collection On 11g [ID 1450173.1]
            11g: Scheduler Maintenance Tasks or Autotasks [ID 756734.1]
            FAQ: Automatic Statistics Collection Jobs (10g and 11g) [ID 1233203.1]
            Where can I see the parameters with which stats is running? i.e. I want to check degree of parallelism, estimate percentage of the automatic stats collection job.
            How to Change Default Parameters for Gathering Statistics in Oracle 11g [ID 1493227.1] -> provides the default value for the parameter
            Also I want the automatic stats collection job to not create any histograms, is that possible? with normal dbms_stats.gather_table_stats, I an set the method_opt and change the histogram setting, how to do it with the automatic stats job?
            You can specify a particular non-default parameter value for an individual DBMS_STATS.GATHER_STATS command. You can override the default parameter values for DBMSSTATS.GATHER_STATS procedures using the DBMSSTATS.SET__PREFS procedures.
            CASCADE DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARITY PUBLISH INCREMENTAL STALE_PERCENT

            SET_TABLE_PREFS
            SET_SCHEMA_PREFS
            SET_DATABASE_PREFS

            exec dbms_stats.set_table_prefs('SCOTT','EMP','15');
            Here 15 is the sample size and so on with other attributes.
            • 3. Re: 11g and automatic stats gathering
              Allen
              If your auto task processes stopped working after an upgrade to 11.2.0.3, double check the following MOS article to see if it applies: Scheduled Maintenance Auto Tasks Not Working After 11.2.0.3 Upgrade [ID 1452836.1]