[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;

--//其他不再使用,全部删除.