Explain plan for last executed statement

来源：互联网发布：ttc提取ttf mac 编辑：程序博客网时间：2024/04/30 23:19

define plan_table=PLAN_TABLE
set linesize 132 pagesize 1000 feedback off verify off
column statement_id new_value statement_id
column prev_sql_addr new_value sql_addr
select prev_sql_addr||'/'||prev_child_number statement_id,prev_sql_addr,prev_hash_value,prev_sql_id
from v$session join v$mystat using(sid) where rownum=1;
delete from &PLAN_TABLE where plan_id=-1;
insert into &PLAN_TABLE select
'&statement_id' STATEMENT_ID,
-1 PLAN_ID,
TIMESTAMP,REMARKS,OPERATION,OPTIONS,OBJECT_NODE,OBJECT_OWNER,OBJECT_NAME,OBJECT_ALIAS
,null 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 address||'/'||child_number='&statement_id';
select sql_text from v$sqltext_with_newlines where address='&sql_addr' order by piece;
select * from table(dbms_xplan.display('&PLAN_TABLE','&statement_id'));
set feedback on verify on