3 Replies Latest reply: Feb 20, 2014 6:47 PM by rp0428 RSS

    Concurrent Statistics Gathering

    Solomon Yakobson

      I am testing Concurrent Statistics Gathering using directions from About Concurrent Statistics Gathering. However, I don't see any DBMS_SCHEDULER jobs. I tried it with PDB, CDB and finally with non-CDB. This one it with non-CDB.Session 1:

       

      SQL*Plus: Release 12.1.0.1.0 Production on Wed Feb 19 13:35:56 2014

      Copyright (c) 1982, 2013, Oracle.  All rights reserved.

      Enter user-name: / as sysdba

      Connected to:
      Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
      ions

      SYS@SOL12 > select banner from v$version;

      BANNER
      --------------------------------------------------------------------------------

      Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
      PL/SQL Release 12.1.0.1.0 - Production
      CORE    12.1.0.1.0      Production
      TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
      NLSRTL Version 12.1.0.1.0 - Production

      SYS@SOL12 > ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';

      System altered.

      SYS@SOL12 > SHOW PARAMETER PROCESSES;

      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      aq_tm_processes                      integer     1
      db_writer_processes                  integer     1
      gcs_server_processes                 integer     0
      global_txn_processes                 integer     1
      job_queue_processes                  integer     1000
      log_archive_max_processes            integer     4
      processes                            integer     300
      SYS@SOL12 > BEGIN
        2    DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','ALL');
        3  END;
        4  /

      PL/SQL procedure successfully completed.

      SYS@SOL12 > EXEC DBMS_STATS.GATHER_DATABASE_STATS

      PL/SQL procedure successfully completed.

      SYS@SOL12 >

       

      While stats is collected I am checking DBA_OPTSTAT_OPERATION_TASKS in Session 2:

       

      SYS@SOL12 > SELECT OPID, TARGET, JOB_NAME,
        2         (SYSTIMESTAMP - START_TIME) AS elapsed_time
        3  FROM   DBA_OPTSTAT_OPERATION_TASKS
        4  WHERE  STATUS = 'IN PROGRESS'
        5  /

      no rows selected


      SYS@SOL12 > /

      no rows selected

      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------------------------
             602 SYS.AQ$_ALERT_QT_                +000000000 00:00:00.152000
                 S


      SYS@SOL12 > set linesize 132
      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------------------------
             602 SYS.AQ$_SCHEDULER                +000000000 00:00:00.382000
                 $_EVENT_QTAB_S


      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------
             602 SYS.CDB_RESOURCE_                +000000000 00:00:00.071000
                 PLAN_DIRECTIVE$


      SYS@SOL12 > /

      no rows selected

      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------
             602 CTXSYS.DR$INDEX_O                +000000000 00:00:00.014000
                 BJECT


      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------
             602 CTXSYS.DR$USER_EX                +000000000 00:00:00.023000
                 TRACT_TKDICT

             602 CTXSYS.SYS_C00507                +000000000 00:00:00.001000
                 3


      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------
             602 SYS.ERROR$                       +000000000 00:00:00.199000

      SYS@SOL12 > /

      no rows selected

      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------
             602 SYS.FGACOL$                      +000000000 00:00:00.006000

      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------
             602 SYS.FILE$                        +000000000 00:00:00.181000
             602 SYS.I_FILE2                      +000000000 00:00:00.012000

      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------
             602 SYS.HISTGRM$                     +000000000 00:00:00.545000
             602 SYS.I_H_OBJ#_COL#                +000000000 00:00:00.149000

      SYS@SOL12 > /

            OPID TARGET            JOB_NAME       ELAPSED_TIME
      ---------- ----------------- -------------- ---------------------------------------------------------
             602 SYS.HIST_HEAD$                   +000000000 00:00:00.879000
             602 SYS.I_HH_OBJ#_COL                +000000000 00:00:00.336000
                 #


      SYS@SOL12 > select distinct job_name from DBA_OPTSTAT_OPERATION_TASKS
        2  /

      JOB_NAME
      --------------


      SYS@SOL12 > select job_name from dba_scheduler_running_jobs;

      no rows selected

      SYS@SOL12 > /

      no rows selected

      SYS@SOL12 > /

      no rows selected

      SYS@SOL12 > /

      no rows selected

      SYS@SOL12 >

       

      As you can see, there are no DBMS_SCHEDULER jobs. Same works in 11.2.0.3.0 even withour resource manager. Session 1:

       

      SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 20 08:27:36 2014

      Copyright (c) 1982, 2011, Oracle.  All rights reserved.

      Enter user-name: / as sysdba

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      SYS@orcl > ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

      System altered.

      SYS@orcl > SHOW PARAMETER PROCESSES

      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      aq_tm_processes                      integer     1
      db_writer_processes                  integer     1
      gcs_server_processes                 integer     0
      global_txn_processes                 integer     1
      job_queue_processes                  integer     1000
      log_archive_max_processes            integer     4
      processes                            integer     150

      SYS@orcl > BEGIN
        2    DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
        3  END;
        4  /

      PL/SQL procedure successfully completed.

      SYS@orcl > EXEC DBMS_STATS.GATHER_DATABASE_STATS

       

      Session 2:

       

      SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 20 08:28:11 2014

      Copyright (c) 1982, 2011, Oracle.  All rights reserved.

      Enter user-name: / as sysdba

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      SYS@orcl > select job_name from dba_scheduler_running_jobs;

      no rows selected

      SYS@orcl > /

      JOB_NAME
      --------------
      ST$SD2077_134

      SYS@orcl > /

      JOB_NAME
      --------------
      ST$SD2077_288
      ST$SD2077_289
      ST$SD2077_287
      ST$SD2077_134
      ST$SD2077_290

      SYS@orcl > /

      JOB_NAME
      --------------
      ST$SD2077_134

      SYS@orcl > /

      JOB_NAME
      --------------
      ST$SD2077_134

      SYS@orcl > /

      JOB_NAME
      --------------
      ST$SD2077_134

      SYS@orcl >

       

      I used DBA_SCHEDULER_RUNNING_JOBS since there is no

       

      DBA_OPTSTAT_OPERATION_TASKS in Oracle 11G.

       

      SY.

        • 1. Re: Concurrent Statistics Gathering
          rp0428

          Not at a DB right now but a couple of questions/observations:

           

          1. Your steps are similar to the doc but do NOT seem to include step #5

          Confirm that the statistics were enabled.

          2. ALL of the elapsed times you show are less than 1/2 second.

           

          3. You didn't post any info showing that any stats were actually collected.

           

           

          Were any stats really collected? Did you check the actual stat collection time both before and after the test to confirm t

           

          The query you posted would only show the 'in progress':

          > SELECT OPID, TARGET, JOB_NAME,

            2         (SYSTIMESTAMP - START_TIME) AS elapsed_time

            3  FROM   DBA_OPTSTAT_OPERATION_TASKS

            4  WHERE  STATUS = 'IN PROGRESS'

            5  /

          What does that view show for the history? Does it show anything actually being done?

           

          Also, not for this question but FYI - there is a MULTITENANT thread for questions particular to 12c. Not sure many people know about it yet.

          https://community.oracle.com/community/developer/english/oracle_database/multitenant

          • 2. Re: Concurrent Statistics Gathering
            Solomon Yakobson

            Thanks for replying:

             

            1. Confirming it is enabled:

             

            SYS@SOL12 > SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;

            DBMS_STATS.GET_PREFS('CONCURRENT')
            -----------------------------------------------------------------

            ALL

            SYS@SOL12 >

             

            2. And what's wrong with "elapsed times you show are less than 1/2 second"? In any case:

            SYS@SOL12 > select distinct job_name from DBA_OPTSTAT_OPERATION_TASKS

              2  /

            JOB_NAME
            --------------


            SYS@SOL12 >

            shows non of the tasks have job name.

             

            3. Yes, stats were collected, just not concurrently since job name is blank.

             

            Reason I didn't post to MULTITENANT is simple. I started with PDB and CDB - no luck. I can't make it work for non-CDB either. So my first goal is making it work for non-CDB - less moving parts.

             

            SY.

            • 3. Re: Concurrent Statistics Gathering
              rp0428

              I wasn't suggesting there was necessarily anything wrong with sub-second elapsed times just wondering if some of the stats were collected because of the 'staleness' issue that arises now and the.

               

              Re MULTITENANT - I just mentioned it because there is a thread over there right up your alley that we can't figure out if you care to take a look

              How to recompile invalid PKG in PDB refering to CDB as METADATA LINK<:nav id=context-exp-title-actions-1803643836795007 class="j-act-actions j-act-actions-init font-color-meta clearfix" data-objectType="1" data-objectID="3519243">