[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 ( )
--------------------------------------------------------------------------------
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才能正常執行,估計與它定義指令碼里面函式存在分號有關,
--//不再探究它。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2845324/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20170628]完善ooerr指令碼.txt指令碼
- [20210506]完善tix指令碼.txt指令碼
- [20210407]完善ti.sql指令碼.txtSQL指令碼
- [20210623]完善清除aud指令碼.txt指令碼
- [20201202]完善sosi指令碼.txt指令碼
- [20220510]完善tpt expandz.sql指令碼.txtSQL指令碼
- [20211230]完善sql_id指令碼.txtSQL指令碼
- [20221010]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善gts.sql指令碼.txtSQL指令碼
- [20211130]完善tpt t.sql指令碼.txtSQL指令碼
- [20211122]完善descx.sql指令碼.txtSQL指令碼
- [20230414]完善seg2.sql指令碼.txtSQL指令碼
- [20231117]完善ashtt.sql指令碼.txtSQL指令碼
- [20230203]完善awr.sql指令碼.txtSQL指令碼
- [20221012]完善spsw.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap.sql指令碼.txtSQL指令碼
- [20211129]完善tpt tablist.sql指令碼.txtSQL指令碼
- [20211129]完善tpt killi.sql指令碼.txtSQL指令碼
- [20220323]完善tpt get_trace.sql指令碼.txtSQL指令碼
- [20220309]完善shp4.sql指令碼.txtSQL指令碼
- [20220217]完善tpt gts.sql指令碼.txtSQL指令碼
- [20230210]建立完善swcnm.sql指令碼.txtSQL指令碼
- [20230123]完善curheapz.sql指令碼.txtSQL指令碼
- [20210125]完善hide.sql指令碼.txtIDESQL指令碼
- [20190416]完善shared latch測試指令碼2.txt指令碼
- [20220823]完善tpt的ashtop.sql指令碼.txtSQL指令碼
- [20231025]完善tpt的trans.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap_awr.sql指令碼.txtSQL指令碼
- [20211126]完善tpt pr.sql指令碼.txtSQL指令碼
- [20191111]完善bind_cap.sql指令碼.txtSQL指令碼
- [20220311]完善ash_wait_chains指令碼.txtAI指令碼
- [20230302]建立完善tpt o2.sql指令碼.txtSQL指令碼
- [20230203]建立完善sp1x.sql指令碼.txtSQL指令碼
- [20220422]完善tpt ash ash_index_helperx指令碼2.txtIndex指令碼
- [20220111]完善tpt ashash_index_helper指令碼.txtIndex指令碼
- [20220317]補充完善TPT 顯示欄位列的指令碼.txt指令碼
- [20220129]完善tpt ash ash_index_helperx指令碼.txtIndex指令碼