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());
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:
Post a Comment