dbms_の使用xplan.ディスプレイはplan_に従いますhash_value実行計画の方法を調べる
1989 ワード
dbms_xplan.display_* plan_に従うことができますhash_valueはdisplayのみawrメソッド、もしこのSQL PLANが生成されたばかりで、AWRに書き込まれなかったらどうしますか?
V$SQL_PLANのデータLOAD入力PLAN_TABLE後に表示:
V$SQL_PLANのデータLOAD入力PLAN_TABLE後に表示:
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'));