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

애플리케이션 커서 캐싱




08 애플리 케이션 커서 캐싱

정의

1) 새션 커서를 캐싱하면 SGA의 공유 커서를 빠르게 찾아가서 커서를 오픈 할 수 있다. 하지만 세션 커서 캐시에 있는 SQL을 수행 하더라도 공유 커서 힙을 PIN하고 실행에 필요한 메모리 공간을 PGA에 할당 하는 작업을 반복하게 된다. 이 과정 마저 생략 하고 빠르게 수행 하는 방법을 의미한다.(Shared pool에 있는 공유커서를 실행 하고, PGA를 인스턴스화)
2) 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도 발생 하지 않는다.

문서정보

Labels

애플리케이션 애플리케이션 Delete
커서 커서 Delete
캐싱 캐싱 Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 5월 26, 2012

    안길환 says:

    1. PL/SQL에서 STATIC SQL 의 사용 예제 테스트 2. 동일 세션에서 위의 1번에 실행한 PL/SQL을 여러번 실행(FOR문 밖에서...

    1. PL/SQL에서 STATIC SQL 의 사용 예제 테스트
    2. 동일 세션에서 위의 1번에 실행한 PL/SQL을 여러번 실행(FOR문 밖에서 똑같은 값으로 테스트)