dbms_xplan.display_* 能按照 plan_hash_value 只有 display_awr 方法，如果这个SQL PLAN 刚刚生成，没有写入到AWR怎么办呢？
DELETE FROM plan_table;
insert into plan_table(
statement_id
,PLAN_ID
,timestamp
,REMARKS
,OPERATION
,OPTIONS
,OBJECT_NODE
,OBJECT_OWNER
,OBJECT_NAME
,OBJECT_ALIAS
,OBJECT_INSTANCE
,OBJECT_TYPE
,OPTIMIZER
,SEARCH_COLUMNS
,ID
,PARENT_ID
,DEPTH
,POSITION
,COST
,CARDINALITY
,BYTES
,OTHER_TAG
,PARTITION_START
,PARTITION_STOP
,PARTITION_ID
,OTHER
,OTHER_XML
,DISTRIBUTION
,CPU_COST
,IO_COST
,TEMP_SPACE
,ACCESS_PREDICATES
,FILTER_PREDICATES
,PROJECTION
,TIME
,QBLOCK_NAME
)
SELECT
'PLAN_'||PLAN_HASH_VALUE AS statement_id
, 0 PLAN_ID
,SYSDATE timestamp
,REMARKS
,OPERATION
,OPTIONS
,OBJECT_NODE
,OBJECT_OWNER
,OBJECT_NAME
,OBJECT_ALIAS
,0 OBJECT_INSTANCE
,OBJECT_TYPE
,OPTIMIZER
,SEARCH_COLUMNS
,ID
,PARENT_ID
,DEPTH
,POSITION
,COST
,CARDINALITY
,BYTES
,OTHER_TAG
,PARTITION_START
,PARTITION_STOP
,PARTITION_ID
,OTHER
,OTHER_XML
,DISTRIBUTION
,CPU_COST
,IO_COST
,TEMP_SPACE
,ACCESS_PREDICATES
,FILTER_PREDICATES
,PROJECTION
,TIME
,QBLOCK_NAME
FROM v$sql_plan
WHERE PLAN_HASH_VALUE='&plan_hash_value'
AND ADDRESS=(SELECT MAX(ADDRESS) FROM V$SQL_PLAN WHERE PLAN_HASH_VALUE='&plan_hash_value');
SELECT * FROM TABLE(dbms_xplan.display('plan_table', 'PLAN_'||'&plan_hash_value'));
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。