by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Runstats

Version 2 by 임주영
on 10월 25, 2011 21:12.


 
compared with
Current by study.gurubee
on 8월 03, 2019 17:48.


 
Key
These lines were removed. This word was removed.
These lines were added. This word was added.

View page history


There are 29 changes. View first change.

 Runstats는 방법들을 비교한 후 어느 것이 우수한지를 보여주기 위한 소규모 벤치마킹 둘로 사용한다.
  * Runstats는 방법들을 비교한 후 어느 것이 우수한지를 보여주기 위한 소규모 벤치마킹 둘로 사용한다.
  
\\
  
 h5. Runstats는 세가지만을 측정
 - 벽시계 또는 경과시간
 - 시스템 통계
 - 래칭 (이보고서의 핵심)
  
Runstats는 독립적으로, 즉 단일 사용자 데이터베이스에서 사용될때 가장 효과적이다.
 따라서 이제부터는 다른 세션이 시스템 부하나 잠금을 유발하지 않는 상태에서 다양한 접근법이 초래한 통계와 잠금활동을 측정해 보기로 하자.
  * Runstats는 독립적으로, 즉 단일 사용자 데이터베이스에서 사용될때 가장 효과적이다.
 * 따라서 이제부터는 다른 세션이 시스템 부하나 잠금을 유발하지 않는 상태에서 다양한 접근법이 초래한 통계와 잠금활동을 측정해 보기로 하자.
  
h3.Runstats 설치
  h2. Runstats 설치
  
h4.V$ 테이블 엑세스를 위한 뷰의 생성
  h4. V$ 테이블 엑세스를 위한 뷰의 생성
 - 엑세스 대상 V$테이블(매력적인 동적 성능 테이블)에는 V$STATNAME, V$MYSTAT 그리고 V$LATCH가 있다.
 
 {code:SQL}
 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;
  
 뷰가 생성되었습니다.
 {code}
  
\\
  
 - 위의 뷰를 사용하기위해서는 V$STATNAME, V$MYSTAT 그리고 V$LATCH에 대한 SELECT권한이 필요하다.
 
 {code:SQL}
 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;
  
 권한이 부여되었습니다.
 {code}
  
\\
  
 h4. 임시테이블 생성
 
 - 통계수집용 소규모 테이블 필요
 
 {code:SQL}
 tkyte@PROD9I> create global temporary table run_stats
  2 ( runid varchar2(15),
  3 name varchar2(80),
  4 value int )
  5 on commit preserve rows;
  
 테이블이 생성되었습니다.
 {code}
  
\\
  
 h4. Runstats 패키지 생성
 - RS_START(Runstats Start) Runstats가 시작되면서 호출된다.
 - RS_MIDDLE 예상대로 도중에 호출된다.
 - RS_STOP작업을 마치고 보고서를 인쇄한다.
  
\\
  
 {code:SQL}
 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 /
  
 패키지가 생성되었습니다.
 {code}
 
 \\
  
 {code:SQL}
 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>
 {code}
  
\\
  
h2. Runstats 사용
 h6. 테스트 CASE
 * 프로덕션의 코드에 대한 미봉책(개발자가 코드를 교정하는 동안 도움을 주는 것)을 서버 설정을 통해 보여 줄 예정이며, 두 번째는 미봉책 코드와 실제코드를 비교할 예정
  
h3.Runstats 사용
 *테스트 CASE*
 프로덕션의 코드에 대한 미봉책(개발자가 코드를 교정하는 동안 도움을 주는 것)을 서버 설정을 통해 보여 줄 예정이며, 두 번째는 미봉책 코드와 실제코드를 비교할 예정
  \\
  
두 검증 루틴
  h6. 두 검증 루틴
 - PARSE_BIND_EXECUTE_CLOSE 루틴: 항상 커서의 파싱,바인딩,실행 그리고 닫기의 순서를 밟는 루틴
 - BIND_EXECUTE 루틴: 한번의 파싱과 바인딩/실행을 반복하는 루틴으로 구현
  
\\
  
 {code:SQL}
 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 /
  
 패키지가 생성되었습니다.
 {code}
  
\\
  
 {code:SQL}
 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>
 {code}
  
h4.미봉책 테스트
  \\
  
 h4. 미봉책 테스트
 - 미봉책을 사용하면 확장성이 우수한 시스템을 얻을 수 있다는 첫번째 가설을 검증
 - 마치 PL/SQL의 정적 SQL에서와 같이 오라클이 백그라운드에서 커서를 캐시에 저장할지의 여부를 제어하는 매개변수인 SESSION_CACHED_CURSORS 이다.
  
 {code:SQL}
 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
 {code}
 
 - 흥미롭게도 커서 캐싱이 활성화된 채로 실행된 코드는 많은 사람들이 엄청나게 빠를것이라고 예상하는 것과 달리 조금밖에 빠르지 않다.
  
\\
  
 {code:SQL}
 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>
 {code}
 
 - 스위치를 바꾸기만 했을 뿐인데도 라이브러리 캐시와 공유 풀의 래치가 20%가 줄었다.
  
h4.모범사례 테스트
  \\
  
 h4. 모범사례 테스트
 - PARSE_BIND_EXECUTE_CLOSE 루틴을 BIND_EXECUTE 루틴과 비교
  
 {code:SQL}
 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
 {code}
 
 - 실행시간의 차이가 엄청나다는 것을 알수 있다.
  
\\
  
 {code:SQL}
 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>
 {code}
 
 - BIND_EXECUTE 루틴은 PARSE_BIND_EXECUTE_CLOSE 루틴이 사용한 잠금의 1/4정도 밖에 사용하자 않았음을 알 수 있다.
  
  
 h2. 문서에 대하여
  
 * 이 문서는 [오라클클럽|http://www.gurubee.net] [대용량 데이터베이스 스터디|제6차 데이터베이스 스터디] 모임에서 작성하였습니다.
 * {color:red}*이 문서의 내용은 THOMAS KYTE 저, 박민호 역의 이펙티브오라클을 참고했습니다.*{color}