이펙티브 오라클 (2008년)
- 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정도 밖에 사용하자 않았음을 알 수 있다.
"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3531
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.