2017/04/11

SQL Monitoring Report History does contain predicate information

It can be extracted pretty easily using the following queries:

insert into plan_table
  (statement_id, plan_id, timestamp, remarks, operation, options, 
   object_node, object_name, object_alias, id, depth, position, 
   cost, cardinality, bytes, partition_start, partition_stop, 
   partition_id, distribution, cpu_cost, io_cost, temp_space, 
   access_predicates, filter_predicates, qblock_name)
  select
   r.key1 statement_id, 1 plan_id, sysdate timestamp, 
   'sql monitor history report' remarks,
   OPERATION, OPTIONS, OBJECT_NODE, OBJECT_NAME, OBJECT_ALIAS, 
   ID, DEPTH, POSITION, COST, CARDINALITY, BYTES, PARTITION_START,
   PARTITION_STOP, PARTITION_ID, DISTRIBUTION, CPU_COST, IO_COST, 
   TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, QBLOCK_NAME
    from dba_hist_reports r,
         dba_hist_reports_details d,
         xmltable('/report/sql_monitor_report/plan/operation' 
                  PASSING xmlparse(document d.report) COLUMNS 
                  OPERATION path '@name'
                 ,options path '@options'
                 ,OBJECT_NODE path 'node'
                 ,OBJECT_NAME path 'object'
                 ,object_alias path 'object_alias'
                 ,id path '@id'
                 ,depth path '@depth'
                 ,POSITION path '@pos'
                 ,cost path 'cost'
                 ,CARDINALITY path 'card'
                 ,bytes path 'bytes'
                 ,PARTITION_START PATH 'partition/@start'
                 ,PARTITION_STOP path 'partition/@stop'
                 ,PARTITION_ID path 'partition/@id'
                 ,DISTRIBUTION path 'distrib'
                 ,cpu_cost path 'cpu_cost'
                 ,io_cost path 'io_cost'
                 ,TEMP_SPACE path 'TEMP_SPACE'
                 ,ACCESS_PREDICATES path 'predicates[@type="access"]'
                 ,FILTER_PREDICATES path 'predicates[@type="filter"]'
                 ,QBLOCK_NAME path 'qblock'
                 ) x1
   WHERE r.component_name = 'sqlmonitor'
     and r.REPORT_ID = d.REPORT_ID
     and r.key1 = 'dhpn35zupm8ck'
     and r.report_id = 70954;
select * from table(dbms_xplan.display());


Just put relevant SQL_ID and REPORT_ID.


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |       |       |   233 (100)|
|   1 |  SORT ORDER BY                         |         |     2 |   146 |   233   (1)|
|   2 |   NESTED LOOPS                         |         |     2 |   146 |   232   (0)|
|   3 |    NESTED LOOPS                        |         |    46 |   146 |   232   (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| TYPE$   |    46 |  1012 |   230   (0)|
|   5 |      INDEX FULL SCAN                   | I_TYPE2 |  4565 |       |     2   (0)|
|*  6 |     INDEX RANGE SCAN                   | I_OBJ3  |     1 |       |     1   (0)|
|*  7 |    TABLE ACCESS BY INDEX ROWID         | OBJ$    |     1 |    51 |     1   (0)|
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(BITAND("T"."PROPERTIES",8388608)=8388608)
   6 - access("O"."OID$"="T"."TVOID")
       filter("O"."OID$" IS NOT NULL)
   7 - filter(SYSDATE@!-"O"."CTIME">.0007)




BTW, active SQL Monitor History Report also shows predicates on "Plan" tab with "Tabular" view turned on.

No comments: