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

[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才能正常执行，估计与它定义脚本里面函数存在分号有关， --//不再探究它。