thomas kyte\'s runstats(比较2段代码的效率)

使用说明：

a、作用：展示执行两段不同代码方案的代价差别，包括消耗时间、会话集统计和闩定差异

b、rs_stop(p_difference_threshold in number default 0) 列举2段代码效率差别大于threshold的项

1、授权

--&1为当前使用的用户名
grant select on sys.v_$timer to &&1;
grant select on v_$mystat to  &&1;
grant select on sys.v_$statname to &&1;
grant select on sys.v_$latch to &&1;

2、创建视图stats

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

3、创建全局临时表runstats

create global temporary table run_stats
(
runid varchar2(15),
name varchar2(80),
value int
)
on commit preserve rows;
/

4、创建runstats包(含3个存储过程：开始用rs_start，中间用rs_middle，完成用rs_stop并打印报告)

create or replace package runstats_pkg
as
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 NUMBER;
  g_run1  NUMBER;
  g_run2  NUMBER;

  PROCEDURE rs_start IS
  BEGIN
    DELETE FROM run_stats;
 
    INSERT INTO run_stats
      SELECT 'before', stats.* FROM stats;
 
    g_start := dbms_utility.get_time;
  END;

  PROCEDURE rs_middle IS
  BEGIN
    g_run1 := (dbms_utility.get_time - g_start);
    INSERT INTO run_stats
      SELECT 'after 1', stats.* FROM stats;
 
    g_start := dbms_utility.get_time;
  END;

  PROCEDURE rs_stop(p_difference_threshold IN NUMBER DEFAULT 0) IS
  BEGIN
    g_run2 := (dbms_utility.get_time - g_start);
 
    dbms_output.put_line('Run1 ran in' || g_run1 || 'hsecs');
    dbms_output.put_line('Run2 ran in' || g_run2 || 'hsecs');
    dbms_output.put_line('run 1 ran in' || round(g_run1 / g_run2 * 100, 2) || '% of the time');
    dbms_output.put_line(chr(9));
 
    INSERT INTO run_stats
      SELECT 'after 2', stats.* FROM stats;
 
    dbms_output.put_line(rpad('Name', 30) || lpad('Run1', 18) || lpad('Run2', 18) ||
                         lpad('Diff', 18));
 
    FOR x IN (SELECT rpad(a.NAME, 30) || to_char(b.VALUE - a.VALUE, '9,999,999,999,999') ||
                      to_char(c.VALUE - b.VALUE, '9,999,999,999,999') ||
                      to_char(((c.VALUE - b.VALUE) - (b.VALUE - a.VALUE)), '9,999,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 (c.VALUE - a.VALUE) > 0
                 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(x.data);
    END LOOP;
 
    dbms_output.enable(480000);
 
    dbms_output.put_line(chr(9));
    dbms_output.put_line('Run1 latches total versus runs -- difference and pct');
    dbms_output.put_line(lpad('Run1', 18) || lpad('Run2', 18) || lpad('Diff', 18) ||
                         lpad('Pct', 8));
 
    FOR x IN (SELECT to_char(run1, '9,999,999,999,999') || to_char(run2, '9,999,999,999,999') ||
                      to_char(diff, '9,999,999,999,999') ||
                      to_char(round(run1 / run2 * 100, 2), '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(x.data);
    END LOOP;
  END;
END;
/