Friday 15 May 2020

PT - Lock | Unlock Optimizer Statistics Examples ORA-20005: object statistics are locked (stattype = ALL)

Scenario : 
 We Lock the Optimizer Statistics of tables  and then unlock the Optimizer Statistics of same table 

  • Create table test2
SQL> CREATE TABLE TEST.TEST2 ( ORDER_ID  NUMBER(12), ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE, ORDER_TOTAL NUMBER(8,2));

Table created.

  • Insert values on test2 table
SQL> INSERT INTO TEST.TEST2 SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL FROM TEST.ORDERS WHERE ORDER_TOTAL >100;

1304324 rows created.

SQL> COMMIT;

Commit complete.

  • Create index on table test2
SQL> CREATE INDEX TEST.TEST2_TOTAL_IX ON TEST.TEST2(ORDER_TOTAL);

Index created.

  • Stats gathering of table test2
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.


  • Check the locked staus of  table
SQL> SELECT STATTYPE_LOCKED, NUM_ROWS FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

STATT   NUM_ROWS
----- ----------
         1304324

  • Lock table using   DBMS_STATS.LOCK_TABLE_STATS

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('TEST','TEST2') ;

PL/SQL procedure successfully completed.

SQL>


  • Check lock status using query

SQL> SELECT STATTYPE_LOCKED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

STATT
-----
ALL

  • Try to gather stats using DBMS_STATS.GATHER_TABLE_STATS it will through error

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);
BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36507
ORA-06512: at "SYS.DBMS_STATS", line 8582
ORA-06512: at "SYS.DBMS_STATS", line 9461
ORA-06512: at "SYS.DBMS_STATS", line 35836
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1


SQL>

  • We can perform DML operations on table only affect on stats gathering

SQL> DELETE TEST.TEST2 WHERE ORDER_TOTAL>115;

1304048 rows deleted.

SQL> COMMIT;

Commit complete.

  • Unlock the stats gathering
SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('TEST', 'TEST2');

PL/SQL procedure successfully completed.

  • Execute stats gathering again to check status 
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.




Monday 11 May 2020

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>

Saturday 9 May 2020

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