PT - Managing Automatic Optimizer Statistics Scheduler window

Managing Automatic Optimizer Statistics Collection

Scenario:

We will check and change the DB automatic Optimizer Statistics status

• Information about the automatic maintenance task that gathers the optimizer statistics. 

QUERY :

SQL> set linesize 180

SQL> col "Auto Optimizer Stats Info." format a100

SQL> SELECT  'STATUS: '|| STATUS || chr(10) || 'WINDOW_GROUP: '||WINDOW_GROUP || chr(10)|| 'MEAN_JOB_DURATION: '||MEAN_JOB_DURATION || chr(10)|| 'MEAN_JOB_CPU: ' || MEAN_JOB_CPU ||chr(10)|| 'MAX_DURATION_LAST_7_DAYS: '|| MAX_DURATION_LAST_7_DAYS || chr(10)|| 'MAX_DURATION_LAST_30_DAYS: '||MAX_DURATION_LAST_30_DAYS|| chr(10) as "Auto Optimizer Stats Info." FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME='auto optimizer stats collection';

Auto Optimizer Stats Info.

----------------------------------------------------------------------------------------------------

STATUS: ENABLED

WINDOW_GROUP: ORA$AT_WGRP_OS

MEAN_JOB_DURATION: +000000000 00:00:05.566037736

MEAN_JOB_CPU: +000000000 00:00:04.089622642

MAX_DURATION_LAST_7_DAYS: +000 00:02:38

MAX_DURATION_LAST_30_DAYS: +000 00:02:38

• Status information of the Scheduler Windows that belong to the maintenance task Window Group. 

QUERY:

SQL> col WINDOW_NAME format a20

SQL> col REPEAT_INTERVAL format a55

SQL> col DURATION format a15

SQL> SELECT WINDOW_NAME, REPEAT_INTERVAL, DURATION, ENABLED  FROM DBA_SCHEDULER_WINDOWS WHERE  '"SYS"."' || WINDOW_NAME ||'"' IN (SELECT MEMBER_NAME FROM DBA_SCHEDULER_GROUP_MEMBERS WHERE GROUP_NAME='ORA$AT_WGRP_OS') ORDER BY NEXT_START_DATE;

WINDOW_NAME          REPEAT_INTERVAL                                         DURATION        ENABL

-------------------- ------------------------------------------------------- --------------- -----

FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0    +000 20:00:00   TRUE

SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0    +000 20:00:00   TRUE

MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

7 rows selected.

SQL>

• Modify the Sunday window so that it opens at 10pm and lasts for 4 hours. Observe that you need to disable the window, before you can make any modification on it

QUERY:

SQL>

SQL> BEGIN

  2  DBMS_SCHEDULER.DISABLE( NAME  => 'SUNDAY_WINDOW');

  3  DBMS_SCHEDULER.SET_ATTRIBUTE(     NAME      => 'SUNDAY_WINDOW',     ATTRIBUTE => 'DURATION',     VALUE     => numtodsinterval(4, 'hour'));

  4  DBMS_SCHEDULER.SET_ATTRIBUTE(     NAME      => 'SUNDAY_WINDOW',     ATTRIBUTE => 'REPEAT_INTERVAL',     VALUE     => 'freq=daily;byday=SUN;byhour=22;byminute=0; bysecond=0');

  5  DBMS_SCHEDULER.ENABLE( name => 'SUNDAY_WINDOW');

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

• Modify the Friday window so that it opens at 6 am and lasts for 20 hours. 

QUERY :

SQL> BEGIN

  2  DBMS_SCHEDULER.DISABLE( NAME  => 'FRIDAY_WINDOW');

  3  DBMS_SCHEDULER.SET_ATTRIBUTE(     NAME      => 'FRIDAY_WINDOW',     ATTRIBUTE => 'DURATION',     VALUE     => numtodsinterval(20, 'hour'));

  4  DBMS_SCHEDULER.SET_ATTRIBUTE(     NAME      => 'FRIDAY_WINDOW',     ATTRIBUTE => 'REPEAT_INTERVAL',     VALUE     => 'freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0');

  5  DBMS_SCHEDULER.ENABLE( NAME => 'FRIDAY_WINDOW');

  6  END;

  7  /

PL/SQL procedure successfully completed.

• Check the change status

QUERY:

SQL> SELECT WINDOW_NAME, REPEAT_INTERVAL, DURATION, ENABLED  FROM DBA_SCHEDULER_WINDOWs  WHERE  '"SYS"."' || WINDOW_NAME ||'"' IN (SELECT MEMBER_NAME FROM DBA_SCHEDULER_GROUP_MEMBERS WHERE GROUP_NAME='ORA$AT_WGRP_OS') ORDER BY NEXT_START_DATE;

WINDOW_NAME          REPEAT_INTERVAL                                         DURATION        ENABL

-------------------- ------------------------------------------------------- --------------- -----

SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0    +000 20:00:00   TRUE

FRIDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0    +000 20:00:00   TRUE

SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

7 rows selected.

SQL>

• Check  information about the program that gets executed by the maintenance task.  

QUERY:

SQL> col PROGRAM_INFO  format a90

SQL> SELECT 'Action: ' || PROGRAM_ACTION ||chr(10)|| 'Comments: ' || COMMENTS PROGRAM_INFO FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME='GATHER_STATS_PROG';

PROGRAM_INFO

------------------------------------------------------------------------------------------

Action: dbms_stats.gather_database_stats_job_proc

Comments: Oracle defined automatic optimizer statistics collection program

SQL>

• Check the status of history log of the optimizer statistics gathering task.

QUERY:

SQL> col JOB_NAME format a22

SQL> col OPERATION format a12

SQL> col STATUS format a10

SQL> SELECT TO_CHAR(LOG_DATE,'DD-MM-YYYY HH24:MI:SS') AS LOG_DATE, JOB_NAME, OPERATION, STATUS FROM   DBA_SCHEDULER_JOB_LOG WHERE  JOB_NAME LIKE 'ORA$AT%OPT%' ORDER  BY LOG_DATE DESC fetch first 10 rows only;

LOG_DATE            JOB_NAME               OPERATION    STATUS

------------------- ---------------------- ------------ ----------

08-05-2020 01:36:34 ORA$AT_OS_OPT_SY_134   RUN          FAILED

08-05-2020 01:36:34 ORA$AT_OS_OPT_SY_134   DROP

08-05-2020 01:36:34 ORA$AT_OS_OPT_SY_134   COMPLETED

08-05-2020 01:36:31 ORA$AT_OS_OPT_SY_134   UPDATE

08-05-2020 01:36:31 ORA$AT_OS_OPT_SY_134   ENABLE

08-05-2020 01:36:31 ORA$AT_OS_OPT_SY_134   UPDATE

08-05-2020 01:36:31 ORA$AT_OS_OPT_SY_134   UPDATE

08-05-2020 01:26:31 ORA$AT_OS_OPT_SY_132   DROP

08-05-2020 01:26:31 ORA$AT_OS_OPT_SY_132   RUN          FAILED

08-05-2020 01:26:31 ORA$AT_OS_OPT_SY_132   COMPLETED

10 rows selected.

SQL>

• Check the list of statistics gathering operations performed at the schema and database level.

QUERY:

SQL> SELECT 'Operation: ' || OPERATION  ||chr(10)||  'Target: ' || TARGET    ||chr(10)||  'Start Time: ' || START_TIME  ||chr(10)|| 'End Time: ' || END_TIME   ||chr(10)||  'Status: ' || STATUS    ||chr(10)|| 'Job Name: ' || JOB_NAME   ||chr(10)||  'Notes: ' || NOTES "Stats Operations"  FROM DBA_OPTSTAT_OPERATIONS WHERE TARGET  NOT LIKE 'SYS%' ORDER BY ID DESC;

Stats Operations

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Operation: gather_database_stats (auto)

Target: AUTO

Start Time: 08-MAY-20 01.36.31.642000 AM +05:30

End Time: 08-MAY-20 01.36.34.563000 AM +05:30

Status: COMPLETED

Job Name: ORA$AT_OS_OPT_SY_134

Notes: <params><param name="block_sample" val="FALSE"/><param name="cascade" val="NULL"/><param name="concurrent" val="FALSE"/><param name="degree" val="DEFAULT_DEGREE_VALUE"/><par

am name="estimate_percent" val="DEFAULT_ESTIMATE_PERCENT"/><param name="granularity" val="DEFAULT_GRANULARITY"/><param name="method_opt" val="DEFAULT_METHOD_OPT"/><param name="no_i

nvalidate" val="DBMS_STATS.AUTO_INVALIDATE"/><param name="reporting_mode" val="FALSE"/><param name="stattype" val="DATA"/></params>

Operation: gather_database_stats (auto)

Stats Operations

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Target: AUTO

Start Time: 08-MAY-20 01.26.30.468000 AM +05:30

End Time: 08-MAY-20 01.26.31.186000 AM +05:30

Status: COMPLETED

Job Name: ORA$AT_OS_OPT_SY_132

Notes: <params><param name="block_sample" val="FALSE"/><param name="cascade" val="NULL"/><param name="concurrent" val="FALSE"/><param name="degree" val="DEFAULT_DEGREE_VALUE"/><par

am name="estimate_percent" val="DEFAULT_ESTIMATE_PERCENT"/><param name="granularity" val="DEFAULT_GRANULARITY"/><param name="method_opt" val="DEFAULT_METHOD_OPT"/><param name="no_i

nvalidate" val="DBMS_STATS.AUTO_INVALIDATE"/><param name="reporting_mode" val="FALSE"/><param name="stattype" val="DATA"/></params>

Operation: gather_database_stats (auto)

Target: AUTO

Stats Operations

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Start Time: 08-MAY-20 01.16.29.684000 AM +05:30

End Time: 08-MAY-20 01.16.30.887000 AM +05:30

Status: COMPLETED

Job Name: ORA$AT_OS_OPT_SY_130

Notes: <params><param name="block_sample" val="FALSE"/><param name="cascade" val="NULL"/><param name="concurrent" val="FALSE"/><param name="degree" val="DEFAULT_DEGREE_VALUE"/><par

am name="estimate_percent" val="DEFAULT_ESTIMATE_PERCENT"/><param name="granularity" val="DEFAULT_GRANULARITY"/><param name="method_opt" val="DEFAULT_METHOD_OPT"/><param name="no_i

nvalidate" val="DBMS_STATS.AUTO_INVALIDATE"/><param name="reporting_mode" val="FALSE"/><param name="stattype" val="DATA"/></params>

Operation: gather_database_stats (auto)

Target: AUTO

Start Time: 08-MAY-20 01.06.28.883000 AM +05:30

Stats Operations

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

End Time: 08-MAY-20 01.06.30.117000 AM +05:30

Status: COMPLETED

Job Name: ORA$AT_OS_OPT_SY_128

Notes: <params><param name="block_sample" val="FALSE"/><param name="cascade" val="NULL"/><param name="concurrent" val="FALSE"/><param name="degree" val="DEFAULT_DEGREE_VALUE"/><par

am name="estimate_percent" val="DEFAULT_ESTIMATE_PERCENT"/><param name="granularity" val="DEFAULT_GRANULARITY"/><param name="method_opt" val="DEFAULT_METHOD_OPT"/><param name="no_i

nvalidate" val="DBMS_STATS.AUTO_INVALIDATE"/><param name="reporting_mode" val="FALSE"/><param name="stattype" val="DATA"/></params>

Operation: gather_database_stats (auto)

Target: AUTO

Start Time: 08-MAY-20 12.56.27.815000 AM +05:30

End Time: 08-MAY-20 12.56.28.815000 AM +05:30

Stats Operations

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Status: COMPLETED

Job Name: ORA$AT_OS_OPT_SY_126

Notes: <params><param name="block_sample" val="FALSE"/><param name="cascade" val="NULL"/><param name="concurrent" val="FALSE"/><param name="degree" val="DEFAULT_DEGREE_VALUE"/><par

am name="estimate_percent" val="DEFAULT_ESTIMATE_PERCENT"/><param name="granularity" val="DEFAULT_GRANULARITY"/><param name="method_opt" val="DEFAULT_METHOD_OPT"/><param name="no_i

nvalidate" val="DBMS_STATS.AUTO_INVALIDATE"/><param name="reporting_mode" val="FALSE"/><param name="stattype" val="DATA"/></params>

Operation: gather_database_stats (auto)

Target: AUTO

Start Time: 08-MAY-20 12.46.26.824000 AM +05:30

End Time: 08-MAY-20 12.46.28.043000 AM +05:30

Status: COMPLETED

• Retrieve the objects that were processed by the statistics gathering operations. 

QUERY:

SQL> col TARGET format a30

SQL> col TARGET_TYPE format a11

SQL> col START_TIME format a17

SQL> col END_TIME format a17

SQL> col DURATION format a11

SQL> SELECT TARGET, TARGET_TYPE, TO_CHAR(START_TIME,'Mon-dd HH24:MI:SS') START_TIME, TO_CHAR(END_TIME,'Mon-dd HH24:MI:SS') END_TIME, SUBSTR(TO_CHAR(END_TIME-START_TIME,'HH:MI:SS'),12,8) DURATION, STATUS FROM DBA_OPTSTAT_OPERATION_TASKS WHERE TARGET NOT LIKE 'SYS%' -- AND END_TIME-START_TIME >= INTERVAL '10' SECOND ORDER BY OPID DESC, START_TIME DESC fetch first 10 rows only;

TARGET                         TARGET_TYPE START_TIME        END_TIME          DURATION    STATUS

------------------------------ ----------- ----------------- ----------------- ----------- -------------------------------------------------

CTXSYS.DR$FEATURE_USED         TABLE       May-06 00:21:47   May-06 00:21:47   00:00:00    COMPLETED

CTXSYS.DR$FEAT_KEY             INDEX       May-06 00:21:47   May-06 00:21:47   00:00:00    COMPLETED

DBSNMP.BSLN_BASELINES          TABLE       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

DBSNMP.BSLN_BASELINES_PK2      INDEX       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

DBSNMP.BSLN_BASELINES_UK2      INDEX       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

CTXSYS.DR$INDEX                TABLE       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

CTXSYS.DRC$IDX_COLUMN          INDEX       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

CTXSYS.DRC$IDX_KEY             INDEX       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

CTXSYS.DRC$IDX_COLSPEC         INDEX       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

CTXSYS.DR$INDEX_VALUE          TABLE       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

CTXSYS.DRX$IXV_KEY             INDEX       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

TARGET                         TARGET_TYPE START_TIME        END_TIME          DURATION    STATUS

------------------------------ ----------- ----------------- ----------------- ----------- -------------------------------------------------

MDSYS.SDO_FEATURE_USAGE        TABLE       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

MDSYS.SYS_C006872              INDEX       May-06 00:21:49   May-06 00:21:49   00:00:00    COMPLETED

SOE.EMP3                       TABLE       May-05 20:46:33   May-05 20:46:33   00:00:00    COMPLETED

SOE.EMP2_EMPNO_UQ              INDEX       May-05 20:46:33   May-05 20:46:33   00:00:00    COMPLETED

MDSYS.SDO_XSD_TABLE            TABLE       May-05 22:00:22   May-05 22:00:22   00:00:00    COMPLETED

MDSYS.SDO_COORD_OP_PARAM_VALS  TABLE       May-05 22:00:22   May-05 22:00:22   00:00:00    COMPLETED

MDSYS.COORD_OP_PARA_VAL_PRIM   INDEX       May-05 22:00:22   May-05 22:00:22   00:00:00    COMPLETED

WMSYS.WM$ENV_VARS$             TABLE       May-05 22:00:26   May-05 22:00:26   00:00:00    COMPLETED

WMSYS.WM$ENV_VARS_PK           INDEX       May-05 22:00:26   May-05 22:00:26   00:00:00    COMPLETED

DBSNMP.BSLN_METRIC_DEFAULTS    TABLE       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

DBSNMP.BSLN_METRIC_DEFAULTS_PK INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

TARGET                         TARGET_TYPE START_TIME        END_TIME          DURATION    STATUS

------------------------------ ----------- ----------------- ----------------- ----------- -------------------------------------------------

XDB.XDB$TTSET                  TABLE       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

XDB.SYS_C005492                INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

XDB.XDB$ALL_MODEL              TABLE       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

XDB.SYS_C005497                INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

XDB.XDB$ANY                    TABLE       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

XDB.SYS_C005503                INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

XDB.X$NM6A0WVGOTVN9KD9IOC5OF8P TABLE       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

6HD7

XDB.X$NN6A0WVGOTVN9KD9IOC5OF8P INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

6HD7

TARGET                         TARGET_TYPE START_TIME        END_TIME          DURATION    STATUS

------------------------------ ----------- ----------------- ----------------- ----------- -------------------------------------------------

XDB.X$NI6A0WVGOTVN9KD9IOC5OF8P INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

6HD7

XDB.X$QN6A0WVGOTVN9KD9IOC5OF8P TABLE       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

6HD7

XDB.X$QS6A0WVGOTVN9KD9IOC5OF8P INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

6HD7

XDB.X$QQ6A0WVGOTVN9KD9IOC5OF8P INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

TARGET                         TARGET_TYPE START_TIME        END_TIME          DURATION    STATUS

------------------------------ ----------- ----------------- ----------------- ----------- -------------------------------------------------

6HD7

XDB.X$QI6A0WVGOTVN9KD9IOC5OF8P INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

6HD7

XDB.APP_USERS_AND_ROLES        TABLE       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

XDB.SYS_C005512                INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

WMSYS.WM$SYSPARAM_ALL_VALUES$  TABLE       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

WMSYS.WM$ENV_SYS_PK            INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

OJVMSYS.OJDS$INODE$            TABLE       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

OJVMSYS.OJDS$NODE_INDEX        INDEX       May-05 22:00:45   May-05 22:00:45   00:00:00    COMPLETED

TARGET                         TARGET_TYPE START_TIME        END_TIME          DURATION    STATUS

------------------------------ ----------- ----------------- ----------------- ----------- -------------------------------------------------

OJVMSYS.OJDS$PERMISSIONS$      TABLE       May-05 22:00:45   May-05 22:00:46   00:00:00    COMPLETED

OJVMSYS.OJDS$PERM_INDEX        INDEX       May-05 22:00:45   May-05 22:00:46   00:00:00    COMPLETED

CTXSYS.DR$PREFERENCE           TABLE       May-05 22:00:46   May-05 22:00:46   00:00:00    COMPLETED

CTXSYS.DRC$PRE_KEY             INDEX       May-05 22:00:46   May-05 22:00:46   00:00:00    COMPLETED

CTXSYS.DRC$PRE_NAME            INDEX       May-05 22:00:46   May-05 22:00:46   00:00:00    COMPLETED

CTXSYS.DR$INDEX                TABLE       May-05 22:00:46   May-05 22:00:46   00:00:00    COMPLETED

CTXSYS.DRC$IDX_COLUMN          INDEX       May-05 22:00:46   May-05 22:00:46   00:00:00    COMPLETED

CTXSYS.DRC$IDX_KEY             INDEX       May-05 22:00:46   May-05 22:00:46   00:00:00    COMPLETED

CTXSYS.DRC$IDX_COLSPEC         INDEX       May-05 22:00:46   May-05 22:00:46   00:00:00    COMPLETED

CTXSYS.DR$SECTION_GROUP        TABLE       May-05 22:00:46   May-05 22:00:46   00:00:00    COMPLETED

CTXSYS.DRC$SGP_KEY             INDEX       May-05 22:00:46   May-05 22:00:46   00:00:00    COMPLETED