使用 dbms_xplan.display 按照 plan_hash_value 查執(zhí)行計(jì)劃的方法

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'));