8 Replies Latest reply: Dec 8, 2011 1:27 PM by user12182279 RSS

    stats collection issue on 11.2

    694473
      Hello all,

      we have a oracle 11.2.0.3 enterprise edition database has auto optimizer stats collection is enabled
      but does not collect any stats, I tried to disable and enable the task with same result,

      database is been up for a month now,
      no rows listed in DBA_AUTOTASK_CLIENT_HISTORY, DBA_AUTOTASK_schedule,DBA_AUTOTASK_TASK

      statistics_level is set to typical,

      is there anything else I could check,

      Any help is appreciated,

      Thanks,

      Red
        • 1. Re: stats collection issue on 11.2
          sb92075
          rred wrote:
          Hello all,

          we have a oracle 11.2.0.3 enterprise edition database has auto optimizer stats collection is enabled
          but does not collect any stats, I tried to disable and enable the task with same result,

          database is been up for a month now,
          no rows listed in DBA_AUTOTASK_CLIENT_HISTORY, DBA_AUTOTASK_schedule,DBA_AUTOTASK_TASK

          statistics_level is set to typical,

          is there anything else I could check,

          Any help is appreciated,

          Thanks,

          Red
          SQL> show parameter queue
          
          NAME                         TYPE      VALUE
          ------------------------------------ ----------- ------------------------------
          job_queue_processes               integer      1000
          
          SQL> select job_name from dba_scheduler_jobs;
          
          JOB_NAME
          ------------------------------
          XMLDB_NFS_CLEANUP_JOB
          SM$CLEAN_AUTO_SPLIT_MERGE
          RSE$CLEAN_RECOVERABLE_SCRIPT
          FGR$AUTOPURGE_JOB
          BSLN_MAINTAIN_STATS_JOB
          DRA_REEVALUATE_OPEN_FAILURES
          HM_CREATE_OFFLINE_DICTIONARY
          ORA$AUTOTASK_CLEAN
          FILE_WATCHER
          PURGE_LOG
          MGMT_STATS_CONFIG_JOB
          
          JOB_NAME
          ------------------------------
          MGMT_CONFIG_JOB
          RLM$SCHDNEGACTION
          RLM$EVTCLEANUP
          POSPAY_CHECK_JOB
          
          15 rows selected.
          
          SQL> 
          • 2. Re: stats collection issue on 11.2
            694473
            SQL>show parameter queue

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            job_queue_processes integer 1000


            SQL>select job_name from dba_scheduler_jobs;

            JOB_NAME
            ------------------------------
            XMLDB_NFS_CLEANUP_JOB
            SM$CLEAN_AUTO_SPLIT_MERGE
            RSE$CLEAN_RECOVERABLE_SCRIPT
            FGR$AUTOPURGE_JOB
            BSLN_MAINTAIN_STATS_JOB
            DRA_REEVALUATE_OPEN_FAILURES
            HM_CREATE_OFFLINE_DICTIONARY
            ORA$AUTOTASK_CLEAN
            FILE_WATCHER
            PURGE_LOG
            MGMT_STATS_CONFIG_JOB

            JOB_NAME
            ------------------------------
            MGMT_CONFIG_JOB
            RLM$SCHDNEGACTION
            RLM$EVTCLEANUP

            14 rows selected.


            MGMT_STATS_CONFIG_JOB has not run in a while since database was created looks like,

            Thanks,
            • 3. Re: stats collection issue on 11.2
              753231
              I have experienced with 11G that statistic jobs run as weekly basis.
              Just collect manually once using schema. Then check ifor any not pending not published.

              1-
              execute dbms_stats.gather_schema_stats( 'schema_name', method_opt => 'FOR ALL COLUMNS SIZE AUTO');

              2-
              select 'EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('''||owner||''','''||table_name||''');'
              from dba_tab_PENDING_STATs

              That should fix your issue.
              • 4. Re: stats collection issue on 11.2
                Vishalp-Oracle
                what does status in DBA_AUTOTASK_CLIENT show for stats collection job client?
                • 5. Re: stats collection issue on 11.2
                  Vishalp-Oracle
                  Also provide output of:

                  select job_name, job_status, job_start_time, job_duration, job_error from DBA_AUTOTASK_JOB_HISTORY where client_name like 'auto optimizer stats collection';
                  • 6. Re: stats collection issue on 11.2
                    user12182279
                    like I said in the initial post DBA_AUTOTASK_CLIENT_HISTORY, DBA_AUTOTASK_schedule,DBA_AUTOTASK_TASK are all empty,
                    for some reason all the maintenance windows got disabled so DBA_AUTOTASK_schedule is empty but I have enabled them and DBA_AUTOTASK_schedule has schedules,
                    I am thinking that might have been the issue , will know tomorrow.
                    • 7. Re: stats collection issue on 11.2
                      Vishalp-Oracle
                      ok. sorry for missing out the part of your initial post which says those views have no rows.
                      • 8. Re: stats collection issue on 11.2
                        user12182279
                        this is fixed after enabling the maintenance windows.
                        thanks for helpful suggestions.