[20230519]整理dpc系列查看执行计划脚本.txt

[20230519]整理dpc系列查看执行计划脚本.txt --//我自己写的dpc系列查看执行计划的脚本如下. > ls -l dp*.* -rw-rw-rw-   1 user     group          62 Mar  5  2015 dp.sql -rw-rw-rw-   1 user     group         885 Oct 28  2022 dpc.sql -rw-rw-rw-   1 user     group         158 Apr 13  2015 dpcawr.sql -rw-rw-rw-   1 user     group         133 Mar 31  2022 dpcawr1.sql -rw-rw-rw-   1 user     group         133 Nov 29  2021 dpcawrx.sql -rw-rw-rw-   1 user     group         117 Jul 23  2014 dpcc.sql -rw-rw-rw-   1 user     group        1795 Jan 22  2016 dpccpu.sql -rw-rw-rw-   1 user     group         870 Dec  2  2021 dpcx.sql -rw-rw-rw-   1 user     group         365 Apr 29  2015 dpcy.sql -rw-rw-rw-   1 user     group         414 Dec 10  2015 dpcz.sql --//实际上许多我基本不用,而且非常混乱,包括命名风格,自己花一点点时间整理一下.而且我自己非常不习惯使用tpt对应脚本. --//顺便也总结一下tpt对应脚本(放弃!!x开头系列以及xb开头系列). > cat dpcformat.sql prompt prompt argment : basic typical serial all adaptive advanced peeked_binds outline prompt argment : rows bytes cost partition predicate projection alias remote note iostats memstats allstats last prompt argment : adaptive (12c) hint_report (19c) prompt --//单独收集dbms_xplan.display_cursor相关显示提示. > cat dp.sql set verify off select * from table(dbms_xplan.display(format=>'advanced &&1')); @dpcformat.sql --//tpt 下x2.sql x9.sql x9a.sql x9all.sql 脚本对应.对方的系列非常特别,先运行sql语句再执行xN.sql脚本. --//由于这样看到的执行计划可能不真实,现在估计很少使用了.作者里面的9我估计指的是9i版本. --//dbms_xplan.display系列.原来dpc原来查询全部共享池的执行计划,dpcc查询特定child_number的执行计划,两者合并在一起. --//参数3指定为child_number. --//dbms_xplan.display_cursor系列. > cat dpc.sql set verify off -- argument1=sql_id argument2=format argument3=child_number -- select * from table(dbms_xplan.display_cursor(nvl('&1',null),null,'all allstats last peeked_binds cost partition note -projection -outline &2')); select * from table(dbms_xplan.display_cursor(nvl('&1',null),nvl('&3',null),'all allstats last peeked_binds cost partition note -projection -outline &2')); def 3='' @ dpcformat.sql --//注意参数2必须放在最后.不然可能不起作用.因为前面加入-projection -outline. --//如果在前面会导致projection,outline不起作用. > cat dpcc.sql -- argument1=sql_id argument2=format argument3=child_number select * from table(dbms_xplan.display_cursor(nvl('&1',null),nvl('&3',null),'all allstats last peeked_binds cost partition note -projection -outline &2')); set verify off @ dpcformat.sql --//可以指定argument3= child_number,干脆做一个合并,以后仅仅需要一个dpc.sql脚本就可以了. --//dbms_xplan.display_awr系列. --//整理dpcawr.sql 和 dpcawrh.sql,两者合并在一起,实际上dpcawr.sql已经包括dpcawrh.sql的功能. > cat dpcawr.sql -- argument1=sql_id argument2=format argument3=plan_hash_value set verify off --select * from table(dbms_xplan.display_awr('&1',null,null,'all allstats last peeked_binds cost partition note -projection -outline &2')); select * from table(dbms_xplan.display_awr('&1',nvl('&3',null),null,'all allstats last peeked_binds cost partition note -projection -outline &2')); def 3='' @ dpcformat.sql > cat dpcawrh.sql -- argument1=sql_id argument2=format argument3=plan_hash_value set verify off select * from table(dbms_xplan.display_awr('&1',nvl('&3',null),null,'all allstats last peeked_binds cost partition note -projection -outline &2')); @ dpcformat.sql --//dbms_xplan.display_sql_plan_baseline系列,我自己很少使用spm管理执行计划. > cat dpcb.sql -- argument1 = sql_handle set verify off select * from table(dbms_xplan.display_sql_plan_baseline(nvl('&1',null),null,'advanced')); @ dpcformat.sql > cat dpcbs.sql -- argument1 = sql_text set verify off select t.* from (select distinct sql_handle from dba_sql_plan_baselines where sql_text like '%&&1%') pb,      table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle, null, 'advanced')) t; @ dpcformat.sql --//使用查询包含特定字符串的sql语句. --//dpccpu.sql 来源已经无从查起,估计来源Franck Pachot的blog. > cat dpccpu.sql with  "sql" as (select SQL_ID,CHILD_NUMBER,PLAN_HASH_VALUE,'' FORMAT from v$sql where sql_id='&1'),  "ash" as (           select sql_id,sql_plan_line_id,child_number,sql_plan_hash_value           ,round(count(*)/"samples",2) load           ,nvl(round(sum(case when session_state='ON CPU' then 1 end)/"samples",2),0) load_cpu           ,nvl(round(sum(case when session_state='WAITING' and wait_class='User I/O' then 1 end)/"samples",2),0) load_io           from "sql" join           (             select sql_id,sql_plan_line_id,sql_child_number child_number,sql_plan_hash_value,session_state,wait_class,count(*) over (partition by sql_id,sql_plan_hash_value) "samples"             FROM V$ACTIVE_SESSION_HISTORY           ) using(sql_id,child_number) group by sql_id,sql_plan_line_id,child_number,sql_plan_hash_value,"samples"  ),  "plan" as (         -- get dbms_xplan result         select          sql_id,child_number,n,plan_table_output          -- get plan line id from plan_table output          ,case when regexp_like (plan_table_output,'^[|][*]? *([0-9]+) *[|].*[|]$') then           regexp_replace(plan_table_output,'^[|][*]? *([0-9]+) *[|].*[|]$','\1')           END SQL_PLAN_LINE_ID          from (select rownum n,plan_table_output,SQL_ID,CHILD_NUMBER from "sql", table(dbms_xplan.display_cursor("sql".SQL_ID,"sql".CHILD_NUMBER,"sql".FORMAT)))  ) select PLAN_TABLE_OUTPUT||CASE        -- ASH load to be displayed        WHEN LOAD >0 THEN TO_CHAR(100*LOAD,'999')||'% (' || TO_CHAR(100*LOAD_CPU,'999')||'% CPU'|| TO_CHAR(100*LOAD_IO,'999')||'% I/O)'        -- header        WHEN REGEXP_LIKE (PLAN_TABLE_OUTPUT,'^[|] *Id *[|]')  THEN ' %ASH SAMPLES'        end plan_table_output from "plan" left outer join "ash" using(sql_id,child_number,sql_plan_line_id) order by sql_id,child_number,n; --//其他不再使用,全部删除.