ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 绑定变量实验(run_stats)

绑定变量实验(run_stats)

原创 Linux操作系统 作者:deszjj 时间:2012-07-24 00:01:50 0 删除 编辑

在有绑定变化和不绑定变量情况下,latch资源征用的对比测试,测试是让一条sql语句执行10000次,然后给出给子的执行过程中产生的资源使用情况。

SYS@ orcl >create user test identified by test default tablespace users;

User created.

SYS@ orcl >grant dba to test;

Grant succeeded.

SYS@ orcl >grant select on sys.v_$statname to test;

Grant succeeded.

SYS@ orcl >grant select on sys.V_$mystat to test;

Grant succeeded.

SYS@ orcl >grant select on sys.v_$latch to test;

Grant succeeded.

SYS@ orcl >grant select on sys.v_$timer to test;

Grant succeeded.

SYS@ orcl >conn test/test
Connected.
TEST@ orcl >create global temporary table run_stats
  2  ( runid varchar2(15),
  3  name varchar2(80),
  4  value int )
  5  on commit preserve rows;

Table created.

 

TEST@ orcl >create or replace view stats
  2  as select 'STAT...' || a.name name ,b.value
  3  from v$statname a ,v$mystat b
  4  where a.statistic#=b.statistic#
  5  union all
  6  select 'LATCH.' || name ,gets
  7  from v$latch
  8  union all
  9  select 'STAT...Elapsed Time ', hsecs from v$timer;

View created.
TEST@ orcl >create or replace view stats
  2  as select 'STAT...' || a.name name ,b.value
  3  from v$statname a ,v$mystat b
  4  where a.statistic#=b.statistic#
  5  union all
  6  select 'LATCH.' || name ,gets
  7  from v$latch
  8  union all
  9  select 'STAT...Elapsed Time ', hsecs from v$timer;

View created.

 

TEST@ orcl >create or replace package runstats_pkg
  2  as
  3  procedure rs_start;
  4  procedure rs_middle;
  5  procedure rs_stop( p_difference_threshold in number default 0 );
  6  end;
  7  /

Package created.

TEST@ orcl >

 

TEST@ orcl >create or replace package runstats_pkg
  2  as
  3  procedure rs_start;
  4  procedure rs_middle;
  5  procedure rs_stop( p_difference_threshold in number default 0 );
  6  end;
  7  /

Package created.


 
TEST@ orcl >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', 10 ) ||
 lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
 
 for x in
 ( select rpad( a.name, 30 ) ||
 to_char( b.value-a.value, '9,999,999' ) ||
 to_char( c.value-b.value, '9,999,999' ) ||
 to_char( ( (c.value-b.value)-(b.value-a.value)), '9,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.put_line( chr(9) );
 dbms_output.put_line
 ( 'Run1 latches total versus runs -- difference and pct' );
 dbms_output.put_line
 ( lpad( 'Run1', 10 ) || lpad( 'Run2', 10 ) ||
 lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
 
 for x in
 ( select to_char( run1, '9,999,999' ) ||
 to_char( run2, '9,999,999' ) ||
 to_char( diff, '9,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;
 /  

下面开始做测试对比,先创建一个t表

TEST@ orcl >create table t (x int);

Table created.
创建第一个存储过程p1,不使用绑定变量方式执行sql 10000次

TEST@ orcl >create or replace procedure p1
  2  as
  3  l_cnt number;
  4  begin
  5  for i in 1 .. 10000
  6  loop
  7  execute immediate 'select count(*) from t where x='|| i into l_cnt;
  8  end loop;
  9  end;
 10  /

Procedure created.


TEST@ orcl >create or replace procedure p1
    as
    l_cnt number;
    begin
    for i in 1 .. 10000
    loop
    execute immediate 'select * from t where x='|| i ;
    end loop;
    end;
   /

创建第二个存储过程p2,使用绑定变量形式执行sql 10000次

TEST@ orcl >create or replace procedure p2
  2      as
  3      l_cnt number;
  4      begin
  5      for i in 1 .. 10000
  6      loop
    select count(*)  into l_cnt from t where x= i ;
  8      end loop;
  9      end;
 10     /

Procedure created.


TEST@ orcl >exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

TEST@ orcl >exec p1;

PL/SQL procedure successfully completed.

TEST@ orcl >exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

TEST@ orcl >exec p2;

PL/SQL procedure successfully completed.

TEST@ orcl >exec runstats_pkg.rs_stop;
Run1 ran in 2835 hsecs
Run2 ran in 932 hsecs
run 1 ran in 304.18% of the time

Name                                Run1      Run2      Diff
STAT...redo ordering marks             0         1         1
STAT...calls to kcmgas                 0         1         1
STAT...redo subscn max counts          0         1         1
LATCH.Consistent RBA                   2         1        -1
LATCH.archive control                  0         1         1
LATCH.SQL memory manager latch         1         0        -1
LATCH.FAL Queue                        0         1         1
LATCH.MinActiveScn Latch               1         0        -1
STAT...session cursor cache co         0         1         1
STAT...user I/O wait time              1         0        -1
STAT...non-idle wait time              1         0        -1
LATCH.managed standby latch            0         1         1
LATCH.FOB s.o list latch               1         0        -1
STAT...physical read IO reques         2         0        -2
STAT...physical reads cache            2         0        -2
STAT...physical reads                  2         0        -2
STAT...physical read total IO          2         0        -2
LATCH.threshold alerts latch           2         0        -2
STAT...bytes received via SQL*     1,188     1,186        -2
LATCH.dummy allocation                 3         1        -2
LATCH.ksz_so allocation latch          2         0        -2
LATCH.OS process: request allo         2         0        -2
LATCH.channel handle pool latc         3         1        -2
LATCH.resmgr:free threads list         2         0        -2
LATCH.parameter table manageme         2         0        -2
LATCH.ksuosstats global area           2         0        -2
LATCH.process group creation           2         0        -2
LATCH.process allocation               2         0        -2
LATCH.archive process latch            0         3         3
LATCH.undo global data                 7         4        -3
LATCH.job_queue_processes para         5         2        -3
LATCH.resmgr:schema config             5         2        -3
LATCH.session state list latch         3         0        -3
LATCH.Real-time plan statistic         4         0        -4
LATCH.parallel query alloc buf         5         1        -4
LATCH.resmgr:actses change sta        17        13        -4
LATCH.resmgr:active threads            7         2        -5
LATCH.active checkpoint queue          9         4        -5
STAT...active txn count during         0         6         6
LATCH.OS process                       7         1        -6
LATCH.In memory undo latch            11         5        -6
STAT...free buffer requested          12         6        -6
STAT...cleanout - number of kt         0         6         6
LATCH.mostly latch-free SCN            9         3        -6
LATCH.lgwr LWN SCN                     9         3        -6
LATCH.session timer                   10         3        -7
LATCH.session allocation              17        10        -7
LATCH.session idle bit                23        16        -7
LATCH.KMG MMAN ready and start        10         3        -7
LATCH.Change Notification Hash        10         3        -7
STAT...workarea memory allocat         8         0        -8
STAT...shared hash latch upgra         8         0        -8
LATCH.OS process allocation           12         3        -9
LATCH.session switching               11         1       -10
LATCH.object queue header heap        19         9       -10
LATCH.ASM db client latch             18         6       -12
LATCH.space background task la        22         7       -15
STAT...non-idle wait count            19         4       -15
LATCH.redo allocation                 31        12       -19
STAT...consistent changes             60        40       -20
STAT...db block changes               75        55       -20
STAT...db block gets from cach        58        38       -20
STAT...db block gets                  58        38       -20
LATCH.redo writing                    34        14       -20
STAT...rows fetched via callba        22         1       -21
STAT...workarea executions - o        24         3       -21
LATCH.active service list             52        20       -32
STAT...cluster key scans              34         0       -34
STAT...cluster key scan block         35         0       -35
STAT...table fetch continued r        43         0       -43
STAT...CCursor + sql area evic        45         0       -45
STAT...undo change vector size     3,268     3,220       -48
STAT...redo size                   4,380     4,332       -48
STAT...sorts (memory)                 58         1       -57
STAT...index fetch by key             69         1       -68
LATCH.channel operations paren       135        43       -92
LATCH.JS queue state obj latch       180        72      -108
LATCH.call allocation                128         2      -126
STAT...opened cursors cumulati    10,157    10,015      -142
STAT...sorts (rows)                  153         3      -150
STAT...execute count              10,191    10,025      -166
LATCH.messages                       247        80      -167
STAT...calls to get snapshot s    10,196    10,021      -175
STAT...index scans kdiixs1           198        17      -181
LATCH.simulator hash latch           212         9      -203
LATCH.checkpoint queue latch         325       113      -212
LATCH.object queue header oper       254        19      -235
STAT...buffer is pinned count        247         7      -240
STAT...consistent gets - exami       313        25      -288
STAT...table fetch by rowid          387        36      -351
LATCH.SQL memory manager worka       628       204      -424
LATCH.shared pool sim alloc          452         1      -451
STAT...consistent gets from ca       693        64      -629
STAT...no work - consistent re       687        56      -631
STAT...buffer is not pinned co       983        88      -895
STAT...parse time cpu                925         2      -923
STAT...consistent gets from ca     1,016        89      -927
STAT...consistent gets             1,016        89      -927
STAT...session logical reads       1,074       127      -947
STAT...file io wait time             953         0      -953
STAT...parse time elapsed            983         1      -982
STAT...recursive cpu usage         1,122        62    -1,060
STAT...CPU used when call star     1,174        70    -1,104
STAT...CPU used by this sessio     1,176        66    -1,110
STAT...DB time                     1,179        68    -1,111
STAT...Elapsed Time                2,840       934    -1,906
LATCH.cache buffers chains         4,104       328    -3,776
STAT...session cursor cache hi       155    10,021     9,866
STAT...sql area evicted            9,904         4    -9,900
STAT...parse count (hard)         10,011         4   -10,007
STAT...enqueue releases           10,018         5   -10,013
STAT...enqueue requests           10,019         5   -10,014
STAT...parse count (total)        10,128        26   -10,102
STAT...physical read total byt    16,384         0   -16,384
STAT...cell physical IO interc    16,384         0   -16,384
STAT...physical read bytes        16,384         0   -16,384
LATCH.enqueue hash chains         20,582       179   -20,403
LATCH.enqueues                    20,580       176   -20,404
STAT...recursive calls            42,524    10,194   -32,330
STAT...session uga memory max    123,452    65,512   -57,940
LATCH.kks stats                   64,702        27   -64,675
STAT...session uga memory         65,512         0   -65,512
LATCH.shared pool simulator      106,061        58  -106,003
LATCH.row cache objects          121,559       200  -121,359
LATCH.shared pool                473,093    10,336  -462,757

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
813,606    12,138  -801,468#######%

PL/SQL procedure successfully completed.

 

看run_stats对比的结果,就可以看出绑定变量的优势,当然绑定变量也有缺点,在对建有索引的字段(包括字段集),且字段(集)的集的势非常大时,使用绑定变量可能会导致查询计划错误,因而会使查询效率非常低,可以继续做相关的实验。

==============================================================

附run_stats 环境搭建

 

TEST@ orcl >create global temporary table run_stats
  2  ( runid varchar2(15),
  3  name varchar2(80),
  4  value int )
  5  on commit preserve rows;

Table created.

 

TEST@ orcl >create or replace view stats
  2  as select 'STAT...' || a.name name ,b.value
  3  from v$statname a ,v$mystat b
  4  where a.statistic#=b.statistic#
  5  union all
  6  select 'LATCH.' || name ,gets
  7  from v$latch
  8  union all
  9  select 'STAT...Elapsed Time ', hsecs from v$timer;

View created.
TEST@ orcl >create or replace view stats
  2  as select 'STAT...' || a.name name ,b.value
  3  from v$statname a ,v$mystat b
  4  where a.statistic#=b.statistic#
  5  union all
  6  select 'LATCH.' || name ,gets
  7  from v$latch
  8  union all
  9  select 'STAT...Elapsed Time ', hsecs from v$timer;

View created.

 

TEST@ orcl >create or replace package runstats_pkg
  2  as
  3  procedure rs_start;
  4  procedure rs_middle;
  5  procedure rs_stop( p_difference_threshold in number default 0 );
  6  end;
  7  /

Package created.

TEST@ orcl >

 

TEST@ orcl >create or replace package runstats_pkg
  2  as
  3  procedure rs_start;
  4  procedure rs_middle;
  5  procedure rs_stop( p_difference_threshold in number default 0 );
  6  end;
  7  /

Package created.


 
TEST@ orcl >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', 10 ) ||
 lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
 
 for x in
 ( select rpad( a.name, 30 ) ||
 to_char( b.value-a.value, '9,999,999' ) ||
 to_char( c.value-b.value, '9,999,999' ) ||
 to_char( ( (c.value-b.value)-(b.value-a.value)), '9,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.put_line( chr(9) );
 dbms_output.put_line
 ( 'Run1 latches total versus runs -- difference and pct' );
 dbms_output.put_line
 ( lpad( 'Run1', 10 ) || lpad( 'Run2', 10 ) ||
 lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
 
 for x in
 ( select to_char( run1, '9,999,999' ) ||
 to_char( run2, '9,999,999' ) ||
 to_char( diff, '9,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;
 /  

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15489979/viewspace-736447/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-04-07

  • 博文量
    42
  • 访问量
    121912