create or replace view stats as select 'STAT...' || a.name name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# union all select 'LATCH.' || name, gets from v$latch union all select 'STAT...Elapsed Time', hsecs from v$timer;
create global temporary table RUN_STATS ( runid VARCHAR2(15), name VARCHAR2(80), value INTEGER ) on commit delete rows;
CREATE OR REPLACE PACKAGE runstats_pkg AS PROCEDURE print_cputime(p_time_type VARCHAR2 DEFAULT 'start'); PROCEDURE rs_start; PROCEDURE rs_middle; PROCEDURE rs_stop(p_difference_threshold IN NUMBER DEFAULT 0); END; /
CREATE OR REPLACE PACKAGE BODY runstats_pkg AS g_start INTEGER; g_run1 INTEGER; g_run2 INTEGER; g_startt INTEGER; g_runt1 INTEGER; g_runt2 INTEGER; --获取包处理过程中的cpu时间 PROCEDURE print_cputime(p_time_type VARCHAR2) IS BEGIN CASE lower(p_time_type) WHEN 'start' THEN dbms_output.put_line(g_start); WHEN '1' THEN dbms_output.put_line(g_run1); WHEN '2' THEN dbms_output.put_line(g_run2); ELSE dbms_output.put_line('parameter is wrong'); END CASE; END; --设置比较性能开始比较 PROCEDURE rs_start IS BEGIN DELETE FROM run_stats; INSERT INTO run_stats SELECT 'before', stats.* FROM stats; g_start := dbms_utility.get_cpu_time; g_startt := dbms_utility.get_time; END; --设置比较性能第二个过程 PROCEDURE rs_middle IS BEGIN g_run1 := (dbms_utility.get_cpu_time - g_start); g_runt1 := (dbms_utility.get_time - g_startt); INSERT INTO run_stats SELECT 'after 1', stats.* FROM stats; g_start := dbms_utility.get_cpu_time; g_startt := dbms_utility.get_time; END; --比较结束，并打印比较信息 PROCEDURE rs_stop(p_difference_threshold IN NUMBER DEFAULT 0) IS v_rownum INTEGER := 0; BEGIN g_run2 := (dbms_utility.get_cpu_time - g_start); g_runt2 := (dbms_utility.get_time - g_startt); dbms_output.put_line('Run1 ran in ' || g_run1 || ' cpu hsecs' || ' , elapsed time:' || g_runt1); dbms_output.put_line('Run2 ran in ' || g_run2 || ' cpu hsecs' || ' , elapsed time:' || g_runt2); IF (g_run2 <> 0) THEN dbms_output.put_line('run 1 ran in ' || round(g_run1 / g_run2 * 100, 2) || '% of the time'); END IF; dbms_output.put_line(chr(9)); INSERT INTO run_stats SELECT 'after 2', stats.* FROM stats; dbms_output.put_line(rpad('linenumber', 20) || rpad('Name', 50) || lpad('Run1', 12) || lpad('Run2', 12) || lpad('Diff', 12)); FOR x IN (SELECT rpad(a.name, 50) || to_char(b.value - a.value, '999,999,999') || to_char(c.value - b.value, '999,999,999') || to_char(((c.value - b.value) - (b.value - a.value)), '999,999,999') data FROM run_stats a, run_stats b, run_stats c WHERE a.name = b.name AND b.name = c.name AND a.runid = 'before' AND b.runid = 'after 1' AND c.runid = 'after 2' AND abs((c.value - b.value) - (b.value - a.value)) > p_difference_threshold ORDER BY abs((c.value - b.value) - (b.value - a.value))) LOOP dbms_output.put_line(rpad(to_char(v_rownum), 20) || x.data); v_rownum := v_rownum + 1; END LOOP; v_rownum := 0; dbms_output.put_line(chr(9)); dbms_output.put_line('Run1 latches total versus runs -- difference and pct'); dbms_output.put_line(rpad('linenumber', 20) || lpad('Run1', 12) || lpad('Run2', 12) || lpad('Diff', 12) || lpad('Pct', 10)); FOR x IN (SELECT to_char(run1, '999,999,999') || to_char(run2, '999,999,999') || to_char(diff, '999,999,999') || to_char(round(run1 / decode(run2, 0, to_number(0), run2) * 100, 2), '99,999.99') || '%' data FROM (SELECT SUM(b.value - a.value) run1, SUM(c.value - b.value) run2, SUM((c.value - b.value) - (b.value - a.value)) diff FROM run_stats a, run_stats b, run_stats c WHERE a.name = b.name AND b.name = c.name AND a.runid = 'before' AND b.runid = 'after 1' AND c.runid = 'after 2' AND a.name LIKE 'LATCH%')) LOOP dbms_output.put_line(rpad(to_char(v_rownum), 20) || x.data); v_rownum := v_rownum + 1; END LOOP; END; END; /
CREATE OR REPLACE PROCEDURE proc_fortest1(i_x INTEGER DEFAULT 10, i_y INTEGER DEFAULT 10, i_z INTEGER DEFAULT 10) IS PRAGMA AUTONOMOUS_TRANSACTION; l_i INTEGER := i_x; l_j INTEGER := i_y; l_k INTEGER := i_z; l_sql VARCHAR2(1024) := 'insert into fort(i,j,k)values(:x,:y,:z)'; l_starttime DATE := SYSDATE; BEGIN FOR i IN 1 .. l_i LOOP FOR j IN 1 .. l_j LOOP FOR k IN 1 .. l_k LOOP EXECUTE IMMEDIATE l_sql USING i, j, k; END LOOP; END LOOP; END LOOP; COMMIT; dbms_output.put_line('proc_fortest1 run times(s):' || (SYSDATE - l_starttime) * 24 * 60 * 60); END proc_fortest1; / CREATE OR REPLACE PROCEDURE proc_fortest2(i_x INTEGER DEFAULT 10, i_y INTEGER DEFAULT 10, i_z INTEGER DEFAULT 10) IS PRAGMA AUTONOMOUS_TRANSACTION; l_i INTEGER := i_x; l_j INTEGER := i_y; l_k INTEGER := i_z; l_starttime DATE := SYSDATE; l_sql VARCHAR2(1024) := 'INSERT INTO fort (i, j, k) SELECT i.rn, j.rn, k.rn FROM (SELECT rownum rn FROM dual CONNECT BY rownum <= :l_i) i, (SELECT rownum rn FROM dual CONNECT BY rownum <= :l_j) j, (SELECT rownum rn FROM dual CONNECT BY rownum <= :l_k) k'; BEGIN EXECUTE IMMEDIATE l_sql USING l_i, l_j, l_k; COMMIT; dbms_output.put_line('proc_fortest2 run times(s):' || (SYSDATE - l_starttime) * 24 * 60 * 60); END proc_fortest2; / CREATE OR REPLACE PROCEDURE proc_fortest(i_limite_value INTEGER DEFAULT 0, i_x INTEGER DEFAULT 10, i_y INTEGER DEFAULT 10, i_z INTEGER DEFAULT 10) IS BEGIN runstats_pkg.rs_start(); proc_fortest1(i_x, i_y, i_z); runstats_pkg.rs_middle(); proc_fortest2(i_x, i_y, i_z); runstats_pkg.rs_stop(i_limite_value); END proc_fortest; /
5、输出结果：
SQL>set serveroutput on
SQL>set linesize 300
SQL>exec proc_fortest(0,10,20,30);
proc_fortest1 run times(s):0 proc_fortest2 run times(s):0 Run1 ran in 22 cpu hsecs , elapsed time:28 Run2 ran in 2 cpu hsecs , elapsed time:3 run 1 ran in 1100% of the time linenumber Name Run1 Run2 Diff 0 LATCH.library cache pin allocation 1 0 -1 1 STAT...change write time 1 0 -1 2 STAT...physical read total multi block requests 0 1 1 3 STAT...messages sent 3 2 -1 4 LATCH.OS process allocation 1 0 -1 5 LATCH.SGA IO buffer pool latch 0 1 1 6 STAT...heap block compress 5 6 1 7 LATCH.flashback mapping 0 1 1 8 LATCH.KMG MMAN ready and startup request latch 1 0 -1 9 LATCH.lgwr LWN SCN 3 1 -2 10 LATCH.mostly latch-free SCN 3 1 -2 11 LATCH.multiblock read objects 8 6 -2 12 STAT...consistent gets - examination 15 17 2 13 STAT...cleanout - number of ktugct calls 15 17 2 14 STAT...active txn count during cleanout 15 17 2 15 STAT...deferred (CURRENT) block cleanout applicati 2 4 2 16 STAT...commit cleanouts successfully completed 12 14 2 17 STAT...commit cleanouts 12 14 2 18 LATCH.Consistent RBA 3 1 -2 19 LATCH.undo global data 21 18 -3 20 STAT...calls to kcmgcs 16 19 3 21 STAT...workarea executions - optimal 6 9 3 22 LATCH.library cache lock 4 0 -4 23 STAT...consistent changes 17 21 4 24 STAT...user I/O wait time 8 3 -5 25 STAT...physical reads cache prefetch 0 5 5 26 STAT...physical reads prefetch warmup 0 5 5 27 STAT...sorts (memory) 1 6 5 28 LATCH.redo writing 9 3 -6 29 LATCH.redo allocation 9 3 -6 30 LATCH.simulator lru latch 64 56 -8 31 LATCH.messages 15 6 -9 32 STAT...calls to get snapshot scn: kcmgss 2 16 14 33 STAT...consistent gets from cache 20 35 15 34 STAT...consistent gets 20 35 15 35 STAT...recursive cpu usage 19 3 -16 36 STAT...CPU used by this session 24 3 -21 37 LATCH.simulator hash latch 79 56 -23 38 STAT...Elapsed Time 30 3 -27 39 STAT...sorts (rows) 2,341 2,394 53 40 LATCH.cache buffers lru chain 81 27 -54 41 STAT...free buffer requested 71 12 -59 42 STAT...physical reads cache 71 11 -60 43 LATCH.flashback allocation 77 17 -60 44 STAT...physical reads 71 11 -60 45 STAT...redo ordering marks 67 2 -65 46 STAT...physical read IO requests 71 6 -65 47 STAT...physical reads for flashback new 67 2 -65 48 STAT...calls to kcmgas 68 3 -65 49 STAT...physical read total IO requests 71 6 -65 50 LATCH.object queue header operation 147 28 -119 51 STAT...redo entries 6,046 86 -5,960 52 STAT...recursive calls 6,003 4 -5,999 53 STAT...execute count 6,001 2 -5,999 54 STAT...session logical reads 6,299 178 -6,121 55 STAT...db block gets 6,279 143 -6,136 56 STAT...db block gets from cache 6,279 143 -6,136 57 STAT...db block changes 12,131 147 -11,984 58 LATCH.shared pool 12,002 5 -11,997 59 LATCH.library cache pin 24,012 7 -24,005 60 LATCH.library cache 24,017 8 -24,009 61 LATCH.cache buffers chains 30,998 588 -30,410 62 STAT...physical read total bytes 581,632 90,112 -491,520 63 STAT...physical read bytes 581,632 90,112 -491,520 64 STAT...undo change vector size 530,232 17,572 -512,660 65 STAT...redo size 1,617,712 122,516 -1,495,196 Run1 latches total versus runs -- difference and pct linenumber Run1 Run2 Diff Pct 0 91,584 862 -90,722 10,624.59%