ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用runstats_pkg

使用runstats_pkg

原创 Linux操作系统 作者:landf 时间:2011-03-09 09:37:35 0 删除 编辑
第一步,
通过两个演示procedure开始。第一个procedure每次都进行分析、赋值、执行和关闭游标的工作,另一个是正确编写的procedure,它只分析一次,重复赋值/执行:
create or replace package demo_pkg
as
       procedure parse_bind_execute_close(p_input in varchar2);
       procedure bind_execute(p_input in varchar2);
end;
/
 
create or replace package body demo_pkg is
       g_first_time boolean := TRUE;
       g_cursor       number;
      
       procedure parse_bind_execute_close(p_input in varchar2)
       as
              l_cursor     number;
              l_output     varchar2(4000);
              l_status     number;
       begin
               l_cursor  := dbms_sql.open_cursor;
               dbms_sql.parse(l_cursor,
                                               'select * from dual where dummy = :x',
                                               dbms_sql.native);
               dbms_sql.bind_variable(l_cursor,':x',p_input);
               dbms_sql.define_column(l_cursor,1,l_output,4000);
               l_status  := dbms_sql.execute(l_cursor);
               if  (dbms_sql.fetch_rows(l_cursor)<=0)  then
                    l_output := null;
               else
                     dbms_sql.column_value(l_cursor,1,l_output);
               end if;
               dbms_sql.close_cursor(l_cursor);
       end parse_bind_execute_close;  
 
       procedure bind_execute(p_input in varchar2)
       as
              l_output     varchar2(4000);
              l_status     number;
       begin
               if (g_first_time)  then
                    g_cursor  := dbms_sql.open_cursor;
                    dbms_sql.parse(g_cursor,
                                                    'select * from dual where dummy = :x',
                                                    dbms_sql.native);
                    dbms_sql.define_column(g_cursor,1,l_output,4000);
                    g_first_time := FALSE;
               end if;
               
               dbms_sql.bind_variable(g_cursor,':x',p_input);
               l_status  := dbms_sql.execute(g_cursor);
               if  (dbms_sql.fetch_rows(g_cursor)<=0)  then
                    l_output := null;
               else
                     dbms_sql.column_value(g_cursor,1,l_output);
               end if;
       end bind_execute;
 
end;
/
              
一、快速适应测试,
现在,准备度量第一个假设:如果使用快速适应(是一个参数---SESSION_CACHED_CURSORS,控制Oracle是否在后台高速缓存游标),我们可以得到系统更大的可伸缩性。不启用游标高速缓存执行一个语句1000次,然后再启用游标高速缓存执行该语句1000次。
SQL> set serveroutput on
SQL>  begin
  2   runstats_pkg.rs_start;
  3   execute immediate
  4   'alter session set session_cached_cursors=0';
  5   for i in 1..1000 loop
  6       demo_pkg.parse_bind_execute_close('Y');
  7   end loop;
  8   runstats_pkg.rs_middle;
  9   execute immediate
 10   'alter session set session_cached_cursors=100';
 11   for i in 1..1000 loop
 12       demo_pkg.parse_bind_execute_close('Y');
 13   end loop;
 14   runstats_pkg.rs_stop(500);
 15   end;
 16   /
Run1 ran in 15 hsecs
Run2 ran in 14 hsecs
run 1 ran in 107.14% of the time
Name                                  Run1        Run2        Diff
STAT...session cursor cache hi           0       1,000       1,000
LATCH.library cache                 20,117      16,147      -3,970
LATCH.library cache lock            10,105       6,072      -4,033
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
46,468      38,550      -7,918    120.54%
PL/SQL 过程已成功完成。
这里通过简单的切换消除了库高速缓存和共享池中的20%的栓锁。
 
二、最好习惯的测试
接下来,需要比较PARSE_BIND_EXECUTE_CLOSE和BIND_EXECUTE,使用runstats比较两个procedure:
SQL> begin
  2  execute immediate
  3  'alter session set session_cached_cursors=0';
  4  runstats_pkg.rs_start;
  5  for i in 1..1000 loop
  6      demo_pkg.parse_bind_execute_close('Y');
  7  end loop;
  8  runstats_pkg.rs_middle;
  9  for i in 1..1000 loop
 10      demo_pkg.bind_execute('Y');
 11  end loop;
 12  runstats_pkg.rs_stop(500);
 13  end;
 14  /
Run1 ran in 18 hsecs
Run2 ran in 6 hsecs
run 1 ran in 300% of the time
Name                                  Run1        Run2        Diff
STAT...opened cursors cumulati       1,001           2        -999
STAT...parse count (total)           1,001           2        -999
STAT...recursive calls               6,003       3,005      -2,998
LATCH.library cache pin             10,010       2,012      -7,998
LATCH.library cache lock            10,004          12      -9,992
LATCH.library cache                 20,013       2,023     -17,990
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
46,195      10,172     -36,023    454.14%
PL/SQL 过程已成功完成。
可以看到BIND_EXECUTE使用了PARSE_BIND_EXECUTE_CLOSE的四分之一的栓锁。

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

下一篇: 创建Statspack
请登录后发表评论 登录
全部评论

注册时间:2008-02-14

  • 博文量
    68
  • 访问量
    494675