PT - Gathering Optimizer Statistics Manually Table examples with stats and explain plan

Gathering Optimizer Statistics Manually Table examples with stats and explain plan

• Create TEST1 table

SQL> CREATE TABLE test.test1 (  ORDER_ID   NUMBER(12),  ORDER_DATE  TIMESTAMP(6) WITH LOCAL TIME ZONE,  ORDER_MODE  VARCHAR2(8),  CUSTOMER_ID   NUMBER(12),  ORDER_STATUS  NUMBER(2),  ORDER_TOTAL  NUMBER(8,2),  SALES_REP_ID  NUMBER(6),  NOTES   VARCHAR2(15));

Table created.

• Check table analyzed status of table (dba_table)

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

SQL>

• Insert values on table for testing purpose

SQL> SET AUTOT OFF

SQL>

SQL> INSERT INTO TEST1 SELECT ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, DBMS_RANDOM.STRING('a',ROUND(DBMS_RANDOM.VALUE(5,15))) FROM ORDERS fetch first 30000 rows only;

40000 rows created.

SQL>  COMMIT;

Commit complete.

• Check the status of analyzed table

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

SQL>

• Stats gathering of table table TEST1

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST1');

PL/SQL procedure successfully completed.

• Check the status of stats gather

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED  FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

     30000        370 11-MAY-20

SQL>

• Delete TEST1 table values 

SQL> DELETE TEST1 WHERE MOD(ORDER_ID,2)=0;

19999 rows deleted.

SQL> COMMIT;

Commit complete.

• Check the status of stats gather

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

     30000        370 11-MAY-20

SQL>

• Check the execution plan and statistics of query

set autot on 

SELECT * FROM TEST1 WHERE ORDER_ID BETWEEN 30000 AND 50000; 

3753 rows selected.

Execution Plan

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

Plan hash value: 4122059633

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

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |       | 12409 |   545K|   103   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST1 | 12409 |   545K|   103   (1)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ORDER_ID"<=30000 AND "ORDER_ID">=50000)

Statistics

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

         15  recursive calls

         36  db block gets

        573  consistent gets

          0  physical reads

       7116  redo size

     199509  bytes sent via SQL*Net to client

       3358  bytes received via SQL*Net from client

        252  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       3753  rows processed

• STATS gather of table TEST1 again

SQL> set autot off

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST1');

PL/SQL procedure successfully completed.

• Check analyzed status of table TEST1

SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST1';

  NUM_ROWS     BLOCKS LAST_ANAL

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

     20001        370 11-MAY-20

SQL>

• Execute Query Again and explain plan and statistics status

set autot on 

SELECT * FROM ORDERS2 WHERE ORDER_ID BETWEEN 30000 AND 50000;

3753 rows selected.

Execution Plan

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

Plan hash value: 4122059633

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

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |       |  3720 |   163K|   102   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST1 |  3720 |   163K|   102   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ORDER_ID"<=30000 AND "ORDER_ID">=50000)

Statistics

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

          0  recursive calls

          0  db block gets

        566  consistent gets

          0  physical reads

          0  redo size

     199509  bytes sent via SQL*Net to client

       3358  bytes received via SQL*Net from client

        252  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       3753  rows processed

SQL>

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

PT - Optimizer Statistics Collection Automatic and Manual examples

Automatic Optimizer Statistics Collection 

To check internal task details :

QUERY:

SELECT * FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME='auto optimizer stats collection';

OUTPUT:

auto optimizer stats collection ENABLED ORA$AUTOTASK OS ON BY DEFAULT, VOLATILE, SAFE TO KILL ORA$AT_WGRP_OS FALSE 0 0:0:7.166666667 0 0:0:4.966388889 36 18 18 0 0:2:58.79 0 0:2:58.79 0 0:2:38.0 0 0:2:38.0 0 7:59:59.972 0 7:59:59.972

 Checking maintenance window opens and closes , We can modifies accordingly with date and time:

QUERY:

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;

OUTPUT:

THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 0 20:0:0.0 TRUE

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 0 20:0:0.0 TRUE

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE

 Scheduler job automatically created, runs, and dropped 

QUERY:

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;

OUTPUT:

07-05-2020 01:44:15 ORA$AT_OS_OPT_SY_90 RUN FAILED

07-05-2020 01:44:15 ORA$AT_OS_OPT_SY_90 COMPLETED

07-05-2020 01:44:15 ORA$AT_OS_OPT_SY_90 DROP

07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 ENABLE

07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 UPDATE

07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 UPDATE

07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 UPDATE

07-05-2020 01:34:15 ORA$AT_OS_OPT_SY_88 DROP

07-05-2020 01:34:15 ORA$AT_OS_OPT_SY_88 COMPLETED

07-05-2020 01:34:15 ORA$AT_OS_OPT_SY_88 RUN FAILED

07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 UPDATE

07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 UPDATE

07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 ENABLE

07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 UPDATE

07-05-2020 01:24:11 ORA$AT_OS_OPT_SY_86 RUN FAILED

07-05-2020 01:24:11 ORA$AT_OS_OPT_SY_86 COMPLETED

07-05-2020 01:24:11 ORA$AT_OS_OPT_SY_86 ENABLE

Objects affected by the job can be obtained

QUERY:

SELECT TARGET, TARGET_TYPE, START_TIME, END_TIME, SUBSTR(TO_CHAR(END_TIME-START_TIME,'HH:MI:SS'),12,8) DURATION, STATUS FROM DBA_OPTSTAT_OPERATION_TASKS ORDER BY OPID DESC, START_TIME DESC;

OUTPUT:

SOE.WAREHOUSES_PK INDEX 07-05-20 04:26:42.183000000 PM +05:30 07-05-20 04:26:42.183000000 PM +05:30 00:00:00 COMPLETED

SOE.WAREHOUSES TABLE 07-05-20 04:26:42.167000000 PM +05:30 07-05-20 04:26:42.183000000 PM +05:30 00:00:00 COMPLETED

SOE.WHS_LOCATION_IX INDEX 07-05-20 04:26:42.167000000 PM +05:30 07-05-20 04:26:42.183000000 PM +05:30 00:00:00 COMPLETED

SOE.V TABLE 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.167000000 PM +05:30 00:00:00 COMPLETED

SOE.PRODUCT_INFORMATION_PK INDEX 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED

SOE.PROD_CATEGORY_IX INDEX 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED

SOE.PROD_SUPPLIER_IX INDEX 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED

SOE.PROD_NAME_IX INDEX 07-05-20 04:26:42.136000000 PM +05:30 07-05-20 04:26:42.136000000 PM +05:30 00:00:00 COMPLETED

SOE.PRODUCT_INFORMATION TABLE 07-05-20 04:26:42.136000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED

SOE.PRD_DESC_PK INDEX 07-05-20 04:26:42.121000000 PM +05:30 07-05-20 04:26:42.136000000 PM +05:30 00:00:00 COMPLETED

SOE.PRODUCT_DESCRIPTIONS TABLE 07-05-20 04:26:42.105000000 PM +05:30 07-05-20 04:26:42.136000000 PM +05:30 00:00:00 COMPLETED

SOE.ITEM_PRODUCT_IX INDEX 07-05-20 04:26:41.652000000 PM +05:30 07-05-20 04:26:42.105000000 PM +05:30 00:00:00 COMPLETED

SOE.ITEM_ORDER_IX INDEX 07-05-20 04:26:41.605000000 PM +05:30 07-05-20 04:26:41.652000000 PM +05:30 00:00:00 COMPLETED

SOE.ORDER_ITEMS_PK INDEX 07-05-20 04:26:39.371000000 PM +05:30 07-05-20 04:26:41.605000000 PM +05:30 00:00:02 COMPLETED

Gathering Optimizer Statistics Manually 

 Examples

Gather statistics of all the objects in the database:

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS;

PL/SQL procedure successfully completed.

Gather statistics of all the schema objects :

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SOE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SOE', CASCADE => TRUE);

PL/SQL procedure successfully completed.

 Gather statistics of specific table:

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SOE', 'ADDRESSES');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SOE', 'ADDRESSES',CASCADE=> TRUE);

PL/SQL procedure successfully completed.

Gather statistics for schemas dictionary and RDBMS components:

SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.