오라클 성능 고도화 원리와 해법 I (2012년)
애플리 케이션 커서 캐싱
- 새션 커서를 캐싱하면 SGA의 공유 커서를 빠르게 찾아가서 커서를 오픈 할 수 있다. 하지만 세션 커서 캐시에 있는 SQL을 수행 하더라도 공유 커서 힙을 PIN하고 실행에 필요한 메모리 공간을 PGA에 할당 하는 작업을 반복하게 된다. 이 과정 마저 생략 하고 빠르게 수행 하는 방법을 의미한다.(Shared pool에 있는 공유커서를 실행 하고, PGA를 인스턴스화)
- Parse Call을 발생 시키지 않고 SQL을 반복 수행 가능으로 오라클 정식 용어가 아님
Pro*eil
For(;;){
EXEc ORAcLE OPTION (HOLD_CURSOR=YES) ; 플리케이션 커서와 세션 커서와의 관계 ( 반복적인 sql문이 재사용 되는 것을 막아주므로 옵티마이저는 sql을 재 해석 할 필요가 없다)
EXEC ORACLE OPTION (RELEASE_CURSOR=NO) ;//context area에 oracle cursor를 계속 유지시켜 주며, 이때 할당된 memory는 다시 사용하기 위해 계속 유지 (No)
EXEC SQL INSERT .... ; // SQL 쿼리문 수행
EXEC ORACLE OPTION ( RELASE_CURSOR=YES) ; //용된 memory해제
}
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 0.18 0.14 0 0 0 0
Fetch 5000 0.17 0.23 0 10000 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 0.35 0.37 0 10000 0 5000
Misses in library cache during parse: 1
-- Parse Call 이 한번만 발생했고, 이후 4,999 번 수행할 때는 Parse Call 이 전혀 발생하지 않음
-- ( 최초 Parse Call 이 발생한 시점에 라이브러리 캐시에서 커서를 찾지 못해 하드 파싱을 수행한
-- 사실도 라이브러리 캐시 Miss 항목을 통해 읽을 수 있어야 한다. )
(1) 바인드 변수를 사용 하지 않는 경우
public void prePareNoBinding(int cnt)throws Exception{
PreparedStatement pstmt = null;
ResultSet rs = null;
for( int i = 0; i < cnt; i ++){
pstmt = conn.prepareStatement(" SELECT "+i+","+i+" ,'test' ,a.* FROM EMP a WHERE a.ENAME LIKE 'W%' ");
rs = pstmt.executeQuery();
rs.close();
pstmt.close();
}
}
SELECT 99,99 ,'test' ,a.*
FROM
EMP a WHERE a.ENAME LIKE 'W%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 8 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=52 us)
바인드 변수를 사용하지 않아 하드 파싱이 반복적으로 발생(별도의 쿼리로 보고 있음)
(2) 바인드 변수를 사용 하지만, 커서를 캐싱 하지 않는 경우
public void preNoCaching(int cnt)throws Exception{
PreparedStatement pstmt = null;
ResultSet rs = null;
for( int i = 0; i < cnt; i ++){
pstmt = conn.prepareStatement(" SELECT a.* ,?, ?, ? FROM EMP a WHERE a.ENAME LIKE 'W%' ");
pstmt.setInt(1, i);
pstmt.setInt(2, i);
pstmt.setString(3, "test");
rs = pstmt.executeQuery();
rs.close();
pstmt.close();
}
}
SELECT a.* ,:1, :2, :3
FROM
EMP a WHERE a.ENAME LIKE 'W%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100 0.04 0.04 0 1 0 0
Execute 100 0.00 0.00 0 1 0 0
Fetch 100 0.00 0.00 0 700 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300 0.04 0.05 0 702 0 100
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=71 us)
하드 파싱은 발생되지 않으나 parse call과 excute call은 for 문 횟수 만큼 발생된다.
(3) 커서를 닫지 않고 재사용 할때
public void preCursorHoding(int cnt)throws Exception{
PreparedStatement pstmt = null;
ResultSet rs = null;
pstmt = conn.prepareStatement(" SELECT a.* ,?, ?, ? FROM EMP a WHERE a.ENAME LIKE 'W%' ");
for( int i = 0; i < cnt; i ++){
pstmt.setInt(1, i);
pstmt.setInt(2, i);
pstmt.setString(3, "test");
rs = pstmt.executeQuery();
rs.close();
}
pstmt.close();
}
SELECT a.* ,:1, :2, :3
FROM
EMP a WHERE a.ENAME LIKE 'W%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.05 0 1 0 0
Execute 100 0.00 0.00 0 1 0 0
Fetch 100 0.00 0.00 0 700 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 0.07 0.06 0 702 0 100
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
100 TABLE ACCESS FULL EMP (cr=700 pr=0 pw=0 time=2932 us)
parse call을 한번만 호출되는 것으로 보아 LIBARARY CAHCE 안에 있는 쿼리문을 실행 단계(excute call)에서 다시 변수 값을 매핑 하여 실행 하고 있습니다.
(4) 묵시적 캐싱 기능
public void preCursorCaching(int cnt)throws Exception{
((OracleConnection)conn).setStatementCacheSize(1);
((OracleConnection)conn).setImplicitCachingEnabled(true);
for( int i = 0; i < cnt; i ++){
PreparedStatement pstmt = conn.prepareStatement(" SELECT a.* ,?, ?, ? FROM EMP a WHERE a.ENAME LIKE 'W%' ");
pstmt.setInt(1, i);
pstmt.setInt(2, i);
pstmt.setString(3, "test");
ResultSet rs = pstmt.executeQuery();
rs.close();
pstmt.close();
}
}
SELECT a.* ,:1, :2, :3
FROM
EMP a WHERE a.ENAME LIKE 'W%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.04 0 1 0 0
Execute 100 0.00 0.00 0 1 0 0
Fetch 100 0.00 0.00 0 700 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 0.06 0.04 0 702 0 100
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
100 TABLE ACCESS FULL EMP (cr=700 pr=0 pw=0 time=2575 us)
for문안에서 PreparedStatement를 반복적으로 열고 닫았지만 묵시적 캐싱을 사용하여 (3)번보다 약간 좋은 결과 보여주고있다.
PL/SQL 커서 캐싱
- Static SQL은 자동으로 커서 캐싱이 발상하지만 Dynamic SQL, Cursor Variable(Ref Cursor)일때는 자동으로 캐싱 효과가 사라진다.
- 오라클 9i까지는 OPEN_CURSORS의 파라미터수에 결정이 되었지만 10g는 SESSION_CACHE_CURSORS 파라미터에 의해 결정되어진다.
(1) 불규칙적인 데이터 입력(비 연속적 데이터 입력)
SQL> alter session set session_cached_cursors=100;
-- 3번 이상 수행한 같은 SQL 문장은 자동으로 수행이 된다. 현재는 수동으로 100을 입력
세션이 변경되었습니다.
SQL> create table t ( x number);
테이블이 생성되었습니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 i number;
3 begin
4 for i in 1.. 100 loop
5 execute immediate 'insert into t values('|| mod(i, 10) ||')';
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select count('x') sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
10 100 100
-- 비 연속적 자료 등록으로 매번 parse call이 호출 되었고, SQL 쿼리문은 총 10개가 만들어졌다.
(2) 규칙적인 데이터 입력 session_cached_cursors 설정 한경우 (연속적 데이터 입력)
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> alter session set session_cached_cursors=100; --3번 이상 수행한 같은 SQL 문장은 자동으로 수행이 된다. 현재는 수동으로 100을 입력
세션이 변경되었습니다.
SQL> declare
2 i number;
3 begin
4 for i in 1.. 100 loop
5 execute immediate 'insert into t values('|| ceil(i) ||')';
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select count('x') sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
10 10 100
연속적인 데이터 등록으로 parse call이 SQL 개수 만큼 10개가 발생 했다.
(3) 규칙적인 데이터 입력 session_cached_cursors 0으로 초기화 한 경우 (연속적 데이터 입력)
SQL> alter session set session_cached_cursors=0; --캐시를 0으로 초기화
세션이 변경되었습니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 i number;
3 begin
4 for i in 1.. 100 loop
5 execute immediate 'insert into t values('|| ceil(i) ||')';
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select count('x') sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
10 100 100
데이터를 연속 적으로 입력을 하였으나, session_cached_cursors을 0으로 초기화로 인해 parse call이 매번 실행 되었다.
STATIC SQL PL/SQL 커서 캐싱
(4) 불규칙적인 데이터 입력
SQL> alter session set session_cached_cursors=100;
세션이 변경되었습니다.
SQL> drop table t;
테이블이 삭제되었습니다.
SQL> create table t ( x number);
테이블이 생성되었습니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 i number;
3 begin
4 for i in 1.. 100 loop
5 insert into t values(mod(i, 10));
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select count('x') sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
0
(5) 규칙적인 데이터 입력
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> alter session set session_cached_cursors=100; --3번 이상 수행한 같은 SQL 문장은 자동으로 수행이 된다. 현재는 수동으로 100을 입력
세션이 변경되었습니다.
SQL> declare
2 i number;
3 begin
4 for i in 1.. 100 loop
5 insert into t values( ceil(i) ) ;
6 end loop;
7 commit;
8 end;
9 /
SQL> select count('x') sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
0
(6) STATIC SQL 규칙적인 데이터 입력 (session_cached_cursors =0으로 설정)
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> alter session set session_cached_cursors=0;
세션이 변경되었습니다.
SQL> declare
2 i number;
3 begin
4 for i in 1.. 100 loop
5 insert into t values( ceil(i) ) ;
6 end loop;
7 commit;
8 end;
9 /
SQL> select count('x') sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
0
(7) 규칙적인 데이터 입력[FOR 문 밖]
SQL> alter session set session_cached_cursors=100;
세션이 변경되었습니다.
SQL> drop table t;
테이블이 삭제되었습니다.
SQL> create table t ( x number);
테이블이 생성되었습니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL>
SQL> declare
2 i number;
3 begin
4 insert into t values(1);
5 insert into t values(1);
6 insert into t values(1);
7 end;
8 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select count('x') sql_cnt
2 ,sum(parse_calls) as parse_calls
3 ,sum(executions) as executions
4 from v$sql
5 where sql_text like 'insert into t values%';
SQL_CNT PARSE_CALLS EXECUTIONS
---------- ----------- ----------
0
- STATIC SQL은 SQL_CNT 가 발생을 하지 않아 PASSE_CALLS 및 EXECUTEIONS도 발생 하지 않는다.
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3103
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.