效能比較工具runStats_pkg

壹頁書發表於2014-09-19
runStats_pkg是Tom大師寫的一個指令碼,
用於比較兩個語句的時間,統計資料和栓鎖使用情況。
指令碼下載連結:
https://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701

下面以edmond帳號為例，使用runStats比較繫結變數的差異

首先,給edmond帳號系統表的授權。
conn / as sysdba
create user edmond identified by edmond;
grant connect,resource to edmond;
grant create view to edmond;
grant select on sys.v_$statname to edmond;
grant select on sys.v_$mystat to edmond;
grant select on sys.v_$latch to edmond;
grant select on sys.v_$timer to edmond;

然後,執行runStats指令碼
  1. create global temporary table run_stats
  2. ( runid varchar2(15),
  3.   name varchar2(80),
  4.   value int )
  5. on commit preserve rows;

  7. create or replace view stats
  8. as select 'STAT...' || a.name name, b.value
  9.       from sys.v_$statname a, sys.v_$mystat b
  10.      where a.statistic# = b.statistic#
  11.     union all
  12.     select 'LATCH.' || name, gets
  13.       from sys.v_$latch
  14.     union all
  15.     select 'STAT...Elapsed Time', hsecs from sys.v_$timer;

  17. create or replace package runstats_pkg
  18. as
  19.     procedure rs_start;
  20.     procedure rs_middle;
  21.     procedure rs_stop( p_difference_threshold in number default 0 );
  22. end;
  23. /
  24.     
  25. create or replace package body runstats_pkg
  26. as

  28. g_start number;
  29. g_run1 number;
  30. g_run2 number;

  32. procedure rs_start
  33. is
  34. begin
  35.     delete from run_stats;

  37.     insert into run_stats
  38.     select 'before', stats.* from stats;
  39.         
  40.     g_start := dbms_utility.get_cpu_time;
  41. end;

  43. procedure rs_middle
  44. is
  45. begin
  46.     g_run1 := (dbms_utility.get_cpu_time-g_start);
  47.  
  48.     insert into run_stats
  49.     select 'after 1', stats.* from stats;
  50.     g_start := dbms_utility.get_cpu_time;

  52. end;

  54. procedure rs_stop(p_difference_threshold in number default 0)
  55. is
  56. begin
  57.     g_run2 := (dbms_utility.get_cpu_time-g_start);

  59.     dbms_output.put_line
  60.     ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' );
  61.     dbms_output.put_line
  62.     ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' );
  63.         if ( g_run2 <> 0 )
  64.         then
  65.     dbms_output.put_line
  66.     ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
  67.       '% of the time' );
  68.         end if;
  69.     dbms_output.put_line( chr(9) );

  71.     insert into run_stats
  72.     select 'after 2', stats.* from stats;

  74.     dbms_output.put_line
  75.     ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
  76.       lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );

  78.     for x in
  79.     ( select rpad( a.name, 30 ) ||
  80.              to_char( b.value-a.value, '999,999,999' ) ||
  81.              to_char( c.value-b.value, '999,999,999' ) ||
  82.              to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
  83.         from run_stats a, run_stats b, run_stats c
  84.        where a.name = b.name
  85.          and b.name = c.name
  86.          and a.runid = 'before'
  87.          and b.runid = 'after 1'
  88.          and c.runid = 'after 2'
  89.          -- and (c.value-a.value) > 0
  90.          and abs( (c.value-b.value) - (b.value-a.value) )
  91.                > p_difference_threshold
  92.        order by abs( (c.value-b.value)-(b.value-a.value))
  93.     ) loop
  94.         dbms_output.put_line( x.data );
  95.     end loop;

  97.     dbms_output.put_line( chr(9) );
  98.     dbms_output.put_line
  99.     ( 'Run1 latches total versus runs -- difference and pct' );
  100.     dbms_output.put_line
  101.     ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
  102.       lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );

  104.     for x in
  105.     ( select to_char( run1, '999,999,999' ) ||
  106.              to_char( run2, '999,999,999' ) ||
  107.              to_char( diff, '999,999,999' ) ||
  108.              to_char( round( run1/decode( run2, 0, to_number(null), run2) *100,2 ), '99,999.99' ) || '%' data
  109.         from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
  110.                       sum( (c.value-b.value)-(b.value-a.value)) diff
  111.                  from run_stats a, run_stats b, run_stats c
  112.                 where a.name = b.name
  113.                   and b.name = c.name
  114.                   and a.runid = 'before'
  115.                   and b.runid = 'after 1'
  116.                   and c.runid = 'after 2'
  117.                   and a.name like 'LATCH%'
  118.                 )
  119.     ) loop
  120.         dbms_output.put_line( x.data );
  121.     end loop;
  122. end;

  124. end;
  125. /
接下來建立測試表和過程
create table t(x int);
insert into t select rownum from dual connect by level<=10000;

沒有繫結變數的測試過程
create or replace procedure p1
as
    l_cnt number;
begin
    for i in 1 .. 10000
    loop
        execute immediate 'select count(*) from t where x=' || i into l_cnt;
    end loop;
end;
/

繫結變數的測試過程
create or replace procedure p2
as
    l_cnt number;
begin
    for i in 1 .. 10000
    loop
        select count(*) into l_cnt from t where x=i;
    end loop;
end;
/

開始測試:
set serveroutput on;
alter system flush shared_pool;

exec runStats_pkg.rs_start;
exec p1;
exec runStats_pkg.rs_middle;
exec p2;
exec runStats_pkg.rs_stop(1000);

測試結果
可以看到第一個過程解析了近1w次,而第二個過程僅僅解析了7次
非繫結變數的方式導致了大量的栓鎖使用.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1273911/，如需轉載，請註明出處，否則將追究法律責任。

相關文章