[20211202]完善d_buffer.sql脚本.txt

--//最近一直在看tpt脚本,发现一些取样脚本很有意思.自己根据需求改写我以前写的d_buffer.sql脚本.
--//如果要有什么缺点就是要建立一个函数。

1.建立tptsleep函数.
create or replace function tptsleep (sec in number default 1) return number as
--------------------------------------------------------------------------------
-- tptsleep by Tanel Poder ( http://www.tanelpoder.com )
--------------------------------------------------------------------------------
begin
     dbms_lock.sleep(sec);
     return 1;
end;
/

grant execute on tptsleep to public;

begin
    execute immediate 'drop public synonym tptsleep';
exception
    when others then null;
end;
/

create public synonym tptsleep for tptsleep;

2.建立脚本:
$ cat d_bufferx.sql
SET NUMW 15
WITH s1
     AS (SELECT /*+ NO_MERGE MATERIALIZE */
               sql_id
               ,executions
               ,cpu_time
               ,buffer_gets
               ,elapsed_time
               ,rows_processed
               ,inst_id
           FROM gv$sqlarea
          WHERE sql_id = '&&1' AND inst_id IN (&&3))
    ,sleep AS (SELECT /*+ NO_MERGE MATERIALIZE */
                     tptsleep (&2) x FROM DUAL)
    ,s2
     AS (SELECT /*+ NO_MERGE MATERIALIZE */
               sql_id
               ,executions
               ,cpu_time
               ,buffer_gets
               ,elapsed_time
               ,rows_processed
               ,inst_id
           FROM gv$sqlarea
          WHERE sql_id = '&&1' AND inst_id IN (&&3))
SELECT *
  FROM (SELECT /*+ ORDERED */
              s2.sql_id
              ,s2.inst_id
              ,s2.executions - s1.executions executions
              ,s2.cpu_time - s1.cpu_time cpu_time
              ,s2.elapsed_time - s1.elapsed_time elapsed_time
              ,s2.buffer_gets - s1.buffer_gets buffer_gets
              ,s2.rows_processed - s1.rows_processed rows_processed
              ,  (s2.cpu_time - s1.cpu_time)
               / NULLIF (s2.executions - s1.executions, 0)
                  cpu_per_exec
              ,  (s2.elapsed_time - s1.elapsed_time)
               / NULLIF (s2.executions - s1.executions, 0)
                  elapsed_time_exec
              ,  (s2.buffer_gets - s1.buffer_gets)
               / NULLIF (s2.executions - s1.executions, 0)
                  buffer_gets_exec
              ,  (s2.rows_processed - s1.rows_processed)
               / NULLIF (s2.executions - s1.executions, 0)
                  rows_processed_exec
          FROM s1, sleep, s2
         WHERE s1.sql_id = s2.sql_id AND s1.inst_id = s2.inst_id);


3.测试看看：

> select distinct sql_text from gv$sqlarea where sql_id='f8733rs2f3bng';
SQL_TEXT
------------------------------------------------------------
SELECT sysdate FROM Dual

> @ d_bufferx.sql f8733rs2f3bng 10 1,2
SQL_ID                INST_ID      EXECUTIONS        CPU_TIME    ELAPSED_TIME     BUFFER_GETS  ROWS_PROCESSED    CPU_PER_EXEC ELAPSED_TIME_EXEC BUFFER_GETS_EXEC ROWS_PROCESSED_EXEC
------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ----------------- ---------------- -------------------
f8733rs2f3bng               1            9241           69418          294944               0            9241 7.5119575803484   31.916892111243                0                   1
f8733rs2f3bng               2              12             989             465               0              12 82.416666666667             38.75                0                   1

--//再换一个sql_id.

> @ d_bufferx.sql g7ytdh9mxt1s0 1 1,2
SQL_ID                INST_ID      EXECUTIONS        CPU_TIME    ELAPSED_TIME     BUFFER_GETS  ROWS_PROCESSED    CPU_PER_EXEC ELAPSED_TIME_EXEC BUFFER_GETS_EXEC ROWS_PROCESSED_EXEC
------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ----------------- ---------------- -------------------
g7ytdh9mxt1s0               1            1504           21191           70616               0            1504 14.089760638298   46.952127659574                0                   1
g7ytdh9mxt1s0               2              31            1210            2005               0              31 39.032258064516   64.677419354839                0                   1

> @ d_bufferx.sql g7ytdh9mxt1s0 1 1,2
SQL_ID                INST_ID      EXECUTIONS        CPU_TIME    ELAPSED_TIME     BUFFER_GETS  ROWS_PROCESSED    CPU_PER_EXEC ELAPSED_TIME_EXEC BUFFER_GETS_EXEC ROWS_PROCESSED_EXEC
------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ----------------- ---------------- -------------------
g7ytdh9mxt1s0               2              52            2294            3272               0              52 44.115384615385   62.923076923077                0                   1
g7ytdh9mxt1s0               1            1410           18353           62663               0            1410 13.016312056738   44.441843971631                0                   1

--//注12c执行在sql语句里面定义函数，不过我发现自能在12c版本的sqlplus才能正常执行，估计与它定义脚本里面函数存在分号有关，
--//不再探究它。