• Runstats는 방법들을 비교한 후 어느 것이 우수한지를 보여주기 위한 소규모 벤치마킹 둘로 사용한다.


Runstats는 세가지만을 측정
  • 벽시계 또는 경과시간
  • 시스템 통계
  • 래칭 (이보고서의 핵심)
  • Runstats는 독립적으로, 즉 단일 사용자 데이터베이스에서 사용될때 가장 효과적이다.
  • 따라서 이제부터는 다른 세션이 시스템 부하나 잠금을 유발하지 않는 상태에서 다양한 접근법이 초래한 통계와 잠금활동을 측정해 보기로 하자.

Runstats 설치

V$ 테이블 엑세스를 위한 뷰의 생성

  • 엑세스 대상 V$테이블(매력적인 동적 성능 테이블)에는 V$STATNAME, V$MYSTAT 그리고 V$LATCH가 있다.

tkyte@PROD9I> 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;

뷰가 생성되었습니다.


  • 위의 뷰를 사용하기위해서는 V$STATNAME, V$MYSTAT 그리고 V$LATCH에 대한 SELECT권한이 필요하다.

tkyte@PROD9I> @connect "/as sysdba"
sys@PROD9I> grant select on v_$statname to tkyte;

권한이 부여되었습니다.

sys@PROD9I> grant select on v_$mystat to tkyte;

권한이 부여되었습니다.

sys@PROD9I> grant select on v_$latch to tkyte;

권한이 부여되었습니다.


임시테이블 생성

  • 통계수집용 소규모 테이블 필요

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

테이블이 생성되었습니다.


Runstats 패키지 생성

  • RS_START(Runstats Start) Runstats가 시작되면서 호출된다.
  • RS_MIDDLE 예상대로 도중에 호출된다.
  • RS_STOP작업을 마치고 보고서를 인쇄한다.



tkyte@PROD9I> 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  /

패키지가 생성되었습니다.



tkyte@PROD9I> create or replace package body runstats_pkg
 2  as
 3
 4  g_start number;
 5  g_run1     number;
 6  g_run2     number;
 7
 8  procedure rs_start
 9  is
10  begin
11      delete from run_stats;
 12
 13      insert into run_stats
 14      select 'before', stats.* from stats;
 15
 16      g_start := dbms_utility.get_time;
 17  end;
 18
 19  procedure rs_middle
 20  is
 21  begin
 22      g_run1 := (dbms_utility.get_time-g_start);
 23
 24      insert into run_stats
 25      select 'after 1', stats.* from stats;
 26      g_start := dbms_utility.get_time;
 27
 28  end;
 29
 30  procedure rs_stop(p_difference_threshold in number default 0)
 31  is
 32  begin
 33      g_run2 := (dbms_utility.get_time-g_start);
 34
 35      dbms_output.put_line
 36          ( 'Run1 ran in ' || g_run1 || ' hsecs' );
 37      dbms_output.put_line
 38          ( 'Run2 ran in ' || g_run2 || ' hsecs' );
 39      dbms_output.put_line
 40      ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
 41        '% of the time' );
 42          dbms_output.put_line( chr(9) );
 43
 44      insert into run_stats
 45      select 'after 2', stats.* from stats;
 46
 47      dbms_output.put_line
 48      ( rpad( 'Name', 30 ) || lpad( 'Run1', 10 ) ||
 49        lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
 50
 51      for x in
 52      ( select rpad( a.name, 30 ) ||
 53               to_char( b.value-a.value, '9,999,999' ) ||
 54               to_char( c.value-b.value, '9,999,999' ) ||
 55               to_char( ( (c.value-b.value)-(b.value-a.value)), '9,999,999' ) data
 56          from run_stats a, run_stats b, run_stats c
 57         where a.name = b.name
 58           and b.name = c.name
 59           and a.runid = 'before'
 60           and b.runid = 'after 1'
 61           and c.runid = 'after 2'
 62           and (c.value-a.value) > 0
 63           and abs( (c.value-b.value) - (b.value-a.value) )
 64                 > p_difference_threshold
 65         order by abs( (c.value-b.value)-(b.value-a.value))
 66      ) loop
 67          dbms_output.put_line( x.data );
 68      end loop;
 69
 70          dbms_output.put_line( chr(9) );
 71      dbms_output.put_line
 72      ( 'Run1 latches total versus runs -- difference and pct' );
 73      dbms_output.put_line
 74      ( lpad( 'Run1', 10 ) || lpad( 'Run2', 10 ) ||
 75        lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
 76
 77      for x in
 78      ( select to_char( run1, '9,999,999' ) ||
 79               to_char( run2, '9,999,999' ) ||
 80               to_char( diff, '9,999,999' ) ||
 81               to_char( round( run1/run2*100,2 ), '999.99' ) || '%' data
 82          from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
 83                        sum( (c.value-b.value)-(b.value-a.value)) diff
 84                   from run_stats a, run_stats b, run_stats c
 85                  where a.name = b.name
 86                    and b.name = c.name
 87                    and a.runid = 'before'
 88                    and b.runid = 'after 1'
 89                    and c.runid = 'after 2'
 90                    and a.name like 'LATCH%'
 91                  )
 92      ) loop
 93          dbms_output.put_line( x.data );
 94      end loop;
 95  end;
 96
 97  end;
 98  /

패키지 본문이 생성되었습니다.

tkyte@PROD9I>


Runstats 사용

테스트 CASE
  • 프로덕션의 코드에 대한 미봉책(개발자가 코드를 교정하는 동안 도움을 주는 것)을 서버 설정을 통해 보여 줄 예정이며, 두 번째는 미봉책 코드와 실제코드를 비교할 예정


두 검증 루틴
  • PARSE_BIND_EXECUTE_CLOSE 루틴: 항상 커서의 파싱,바인딩,실행 그리고 닫기의 순서를 밟는 루틴
  • BIND_EXECUTE 루틴: 한번의 파싱과 바인딩/실행을 반복하는 루틴으로 구현



tkyte@PROD9I> create or replace package demo_pkg
  2  as
  3      procedure parse_bind_execute_close( p_input in varchar2 );
  4      procedure bind_execute( p_input in varchar2 );
  5  end;
  6  /

패키지가 생성되었습니다.



tkyte@PROD9I> create or replace package body demo_pkg
  2  as
  3
  4  g_first_time boolean := TRUE;
  5  g_cursor     number;
  6
  7  procedure parse_bind_execute_close( p_input in varchar2 )
  8  as
  9      l_cursor   number;
 10      l_output   varchar2(4000);
 11      l_status   number;
 12  begin
 13      l_cursor := dbms_sql.open_cursor;
 14      dbms_sql.parse( l_cursor,
 15                     'select * from dual where dummy = :x',
 16                      dbms_sql.native );
 17      dbms_sql.bind_variable( l_cursor, ':x', p_input );
 18      dbms_sql.define_column( l_cursor, 1, l_output, 4000 );
 19      l_status := dbms_sql.execute( l_cursor );
 20      if ( dbms_sql.fetch_rows( l_cursor ) <= 0 )
 21      then
 22          l_output := null;
 23      else
 24          dbms_sql.column_value( l_cursor, 1, l_output );
 25      end if;
 26      dbms_sql.close_cursor( l_cursor );
 27  end parse_bind_execute_close;
 28
 29  procedure bind_execute( p_input in varchar2 )
 30  as
 31      l_output   varchar2(4000);
 32      l_status   number;
 33  begin
 34      if ( g_first_Time )
 35      then
 36          g_cursor := dbms_sql.open_cursor;
 37          dbms_sql.parse( g_cursor,
 38                         'select * from dual where dummy = :x',
 39                         dbms_sql.native );
 40          dbms_sql.define_column( g_cursor, 1, l_output, 4000 );
 41          g_first_time := FALSE;
 42      end if;
 43
 44      dbms_sql.bind_variable( g_cursor, ':x', p_input );
 45      l_status := dbms_sql.execute( g_cursor );
 46      if ( dbms_sql.fetch_rows( g_cursor ) <= 0 )
 47      then
 48          l_output := null;
 49      else
 50          dbms_sql.column_value( g_cursor, 1, l_output );
 51      end if;
 52  end bind_execute;
 53
 54  end;
 55  /

패키지 본문이 생성되었습니다.

tkyte@PROD9I>


미봉책 테스트

  • 미봉책을 사용하면 확장성이 우수한 시스템을 얻을 수 있다는 첫번째 가설을 검증
  • 마치 PL/SQL의 정적 SQL에서와 같이 오라클이 백그라운드에서 커서를 캐시에 저장할지의 여부를 제어하는 매개변수인 SESSION_CACHED_CURSORS 이다.

tkyte@PROD9I> begin
  2      runstats_pkg.rs_start;
  3      execute immediate
  4      'alter session set session_cached_cursors=0';
  5      for i in 1 .. 1000
  6      loop
  7          demo_pkg.parse_bind_execute_close( 'Y' );
  8      end loop;
  9      runstats_pkg.rs_middle;
 10      execute immediate
 11      'alter session set session_cached_cursors=100';
 12      for i in 1 .. 1000
 13      loop
 14          demo_pkg.parse_bind_execute_close( 'Y' );
 15      end loop;
 16      runstats_pkg.rs_stop(500);
 17  end;
 18  /
Run1 ran in 22 hsecs
Run2 ran in 16 hsecs
run 1 ran in 137.5% of the time

  • 흥미롭게도 커서 캐싱이 활성화된 채로 실행된 코드는 많은 사람들이 엄청나게 빠를것이라고 예상하는 것과 달리 조금밖에 빠르지 않다.



Name                                Run1      Run2      Diff
STAT...session cursor cache hi         0     1,002     1,002
LATCH.library cache pin alloca    10,116     8,077    -2,039
LATCH.library cache pin           18,137    16,094    -2,043
LATCH.shared pool                  6,217     4,132    -2,085
LATCH.library cache               25,402    20,275    -5,127

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
66,170    54,788   -11,382 120.77%

PL/SQL 처리가 정상적으로 완료되었습니다.

tkyte@PROD9I>

  • 스위치를 바꾸기만 했을 뿐인데도 라이브러리 캐시와 공유 풀의 래치가 20%가 줄었다.


모범사례 테스트

  • PARSE_BIND_EXECUTE_CLOSE 루틴을 BIND_EXECUTE 루틴과 비교

tkyte@PROD9I> begin
  2      execute immediate
  3      'alter session set session_cached_cursors=100';
  4      runstats_pkg.rs_start;
  5      for i in 1 .. 1000

  6      loop
  7          demo_pkg.parse_bind_execute_close( 'Y' );
  8      end loop;
  9      runstats_pkg.rs_middle;
 10      for i in 1 .. 1000
 11      loop
 12          demo_pkg.bind_execute( 'Y' );
 13      end loop;
 14      runstats_pkg.rs_stop(500);
 15  end;
 16  /
Run1 ran in 18 hsecs
Run2 ran in 6 hsecs
run 1 ran in 300% of the time

  • 실행시간의 차이가 엄청나다는 것을 알수 있다.



Name                                Run1      Run2      Diff
STAT...session cursor cache hi     1,000         1      -999
STAT...opened cursors cumulati     1,001         1    -1,000
STAT...parse count (total)         1,001         1    -1,000
STAT...recursive calls             6,002     3,003    -2,999
LATCH.shared pool                  4,016         5    -4,011
LATCH.library cache pin alloca     8,018         7    -8,011
LATCH.library cache pin           16,028     2,018   -14,010
LATCH.library cache               20,073     2,022   -18,051

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
54,209    10,132   -44,077 535.03%

PL/SQL 처리가 정상적으로 완료되었습니다.

tkyte@PROD9I>

  • BIND_EXECUTE 루틴은 PARSE_BIND_EXECUTE_CLOSE 루틴이 사용한 잠금의 1/4정도 밖에 사용하자 않았음을 알 수 있다.