Explain plan for last executed statement

最新推荐文章于 2024-10-31 18:10:53 发布

jiawancai

最新推荐文章于 2024-10-31 18:10:53 发布

阅读量680

点赞数

分类专栏： oracle调优文章标签： object table sql insert filter delete

oracle调优专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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