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

커서공유




04 커서공유

(1) 커서란?

  • 공유커서(Shared Cursor)
    1. 라이브러리 캐시에 공유되어 있는 Shared SQL Area
    2. SQL을 실행하는데 필요한 루틴(routine)을 정의한 것
    3. Java의 클레스
  • 세션커서(Session Cursor)
    1. Private SQL Area에 저장되는 커서
    2. 라이브러리 캐시에 공유되어 있는 공유커서를 인스턴스화하기 위해 PGA 에 메모리공간을 할당
    3. 실제 대이터를 추출하기 위한 준비작업
    4. Java의 클레스를 상속받은 객체
  • 애플리케이션 커서(Application Cursor)
    1. 세션커서를 가리키는 핸들
    2. 세션커서를 핸들링하기 위한 애플리케이션 리소스 할당

그림 4-7

(2) 커서공유

  • 공유커서
    1. 라이브러리캐시에 공유되어 있는 커서
    2. v$sql을 통해 확인
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
시스템이 변경되었습니다.
SQL> SELECT /* cursor_test */
  2         empno, ename, job, sal, deptno
  3    FROM emp
  4   WHERE empno = 7369
  5  ;

     EMPNO ENAME                JOB                       SAL     DEPTNO
---------- -------------------- ------------------ ---------- ----------
      7369 SMITH                CLERK                     800         20

SQL> SELECT sql_id
  2       , parse_calls
  3       , loads
  4       , executions
  5       , invalidations
  6       , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
  7    FROM v$sql
  8   WHERE sql_text LIKE '%cursor_test%'
  9     AND sql_text NOT LIKE '%V$SQL%'
 10  ;

SQL_ID                     PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
9v6cbm9canuj8                        1          1          1             0          0

SQL> SELECT /* cursor_test */
  2         empno, ename, job, sal, deptno
  3    FROM emp
  4   WHERE empno = 7369
  5  ;

     EMPNO ENAME                JOB                       SAL     DEPTNO
---------- -------------------- ------------------ ---------- ----------
      7369 SMITH                CLERK                     800         20

SQL> SELECT /* cursor_test */
  2         empno, ename, job, sal, deptno
  3    FROM emp
  4   WHERE empno = 7369
  5  ;

     EMPNO ENAME                JOB                       SAL     DEPTNO
---------- -------------------- ------------------ ---------- ----------
      7369 SMITH                CLERK                     800         20

SQL> SELECT sql_id
  2       , parse_calls
  3       , loads
  4       , executions
  5       , invalidations
  6       , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
  7    FROM v$sql
  8   WHERE sql_text LIKE '%cursor_test%'
  9     AND sql_text NOT LIKE '%V$SQL%'
 10  ;

SQL_ID                     PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
9v6cbm9canuj8                        3          1          3             0          0

  • v$sql 조회항목의 의미
    1. parse_call : 라이브러리 캐시에서 SQL을 찾으려는 요청횟수
    2. loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
    3. executions : SQL을 수행한 횟수
    4. reloads : 커서가 무효화된 횟수. 커서가 참조하고 있는 오브젝트에 변화가 일어났음을 의미.
  • v$sql 조회결과의 해석
    1. 같은 SQL이 3번 수행되어 한번의 하드파싱이 일어나 공유커서에 저장되었으며
    2. 이는 저장된 공유커서가 재사용되었음을 의미한다.
  • 다른 세션으로 접속하여 테스트
    SQL> disconn
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production에서 분리되었습니다.
    
    SQL> conn scott/tiger
    연결되었습니다.
    
    SQL> SELECT /* cursor_test */
      2         empno, ename, job, sal, deptno
      3    FROM emp
      4   WHERE empno = 7369
      5  ;
    
         EMPNO ENAME                JOB                       SAL     DEPTNO
    ---------- -------------------- ------------------ ---------- ----------
          7369 SMITH                CLERK                     800         20
    
    SQL> SELECT sql_id
      2       , parse_calls
      3       , loads
      4       , executions
      5       , invalidations
      6       , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
      7    FROM v$sql
      8   WHERE sql_text LIKE '%cursor_test%'
      9     AND sql_text NOT LIKE '%V$SQL%'
     10  ;
    
    SQL_ID                     PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    -------------------------- ----------- ---------- ---------- ------------- ----------
    9v6cbm9canuj8                        4          1          4             0          0
    
    
  • 조회 결과
    1. 다른 세션에서도 공유된 커서가 재사용되었음을 알 수 있다.
  • 커서가 재사용되지 않는 경우는? 공유커서의 무효화
    1. 커서에서 참조하는 오브젝트의 변화(DDL 등, DML은 무관)
  • 컬럼의 추가/삭제/변경 : 이전의 커서가 잘못된 컬럼참조를 할수 있으므로
  • 인덱스의 추가 : 이전에 수립된 실행계획이 최적이 아닐 수 있으므로
  • 통계정보 생성 : 마찬가지로
  • 통계정보 수집 테스트
    SQL> BEGIN
      2      DBMS_STATS.GATHER_TABLE_STATS
      3      ( ownname       => USER
      4      , tabname       => 'EMP'
      5      , no_invalidate => FALSE
      6      );
      7  END;
      8  /
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SQL> SELECT sql_id
      2       , parse_calls
      3       , loads
      4       , executions
      5       , invalidations
      6       , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
      7    FROM v$sql
      8   WHERE sql_text LIKE '%cursor_test%'
      9     AND sql_text NOT LIKE '%V$SQL%'
     10  ;
    
    선택된 레코드가 없습니다.
    
    SQL> SELECT /* cursor_test */
      2         empno, ename, job, sal, deptno
      3    FROM emp
      4   WHERE empno = 7369
      5  ;
    
         EMPNO ENAME                JOB                       SAL     DEPTNO
    ---------- -------------------- ------------------ ---------- ----------
          7369 SMITH                CLERK                     800         20
    
    SQL> SELECT sql_id
      2       , parse_calls
      3       , loads
      4       , executions
      5       , invalidations
      6       , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
      7    FROM v$sql
      8   WHERE sql_text LIKE '%cursor_test%'
      9     AND sql_text NOT LIKE '%V$SQL%'
     10  ;
    
    SQL_ID                     PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    -------------------------- ----------- ---------- ---------- ------------- ----------
    9v6cbm9canuj8                        1          2          1             1          1
    
  • 커서 공유의 키(식별자)
    1. 커서가 공유되려면 쿼리를 식별할 수 있는 키가 있어야 하는데 이 키는 쿼리문장(sql_fulltext) 그 자체이다.
    2. 10g 부터는 sql_id 가 식별자의 역할을 할 수 있다.

(3) Child 커서를 공유하지 못하는 경우

  • Child 커서란?
    1. 100% 동일한 SQL 문장인데도 불구하고 공유되지 않는 경우 Child 커서를 생성하게 된다.
  • 테스트
    SQL> SELECT * FROM emp;
    
    SQL> SELECT sql_id
      2       , version_count
      3       , optimizer_mode
      4       , address
      5       , hash_value
      6    FROM v$sqlarea
      7   WHERE sql_text LIKE '%SELECT * FROM emp%'
      8     AND sql_text NOT LIKE '%V$SQL%'
      9  ;
    
    SQL_ID        VERSION_COUNT OPTIMIZER_MODE ADDRESS  HASH_VALUE
    ------------- ------------- -------------- -------- ----------
    4u5074pdutxzs             2 ALL_ROWS       2FF85AF4 1538062328
    
    SQL> SELECT sql_id
      2       , child_number
      3       , optimizer_mode
      4       , address
      5       , hash_value
      6       , parsing_user_id
      7    FROM v$sql
      8   WHERE sql_text LIKE '%SELECT * FROM emp%'
      9     AND sql_text NOT LIKE '%V$SQL%'
     10  ;
    
    SQL_ID        CHILD_NUMBER OPTIMIZER_MODE ADDRESS  HASH_VALUE PARSING_USER_ID
    ------------- ------------ -------------- -------- ---------- ---------------
    4u5074pdutxzs            0 ALL_ROWS       2FF85AF4 1538062328              33
    4u5074pdutxzs            1 ALL_ROWS       2FF85AF4 1538062328              36
    
    SQL> SELECT * FROM dual;
    
    SQL> SELECT sql_id
      2       , version_count
      3       , optimizer_mode
      4       , address
      5       , hash_value
      6    FROM v$sqlarea
      7   WHERE sql_text LIKE '%SELECT * FROM dual%'
      8     AND sql_text NOT LIKE '%V$SQL%'
      9  ;
    
    SQL_ID        VERSION_COUNT OPTIMIZER_MODE ADDRESS  HASH_VALUE
    ------------- ------------- -------------- -------- ----------
    3vjxpmhhzngu4             2 ALL_ROWS       2FF5EDA4  570048324
    
    SQL> SELECT sql_id
      2       , child_number
      3       , optimizer_mode
      4       , address
      5       , hash_value
      6       , parsing_user_id
      7    FROM v$sql
      8   WHERE sql_text LIKE '%SELECT * FROM dual%'
      9     AND sql_text NOT LIKE '%V$SQL%'
     10  ;
    
    SQL_ID        CHILD_NUMBER OPTIMIZER_MODE ADDRESS  HASH_VALUE PARSING_USER_ID
    ------------- ------------ -------------- -------- ---------- ---------------
    3vjxpmhhzngu4            1 ALL_ROWS       2FF5EDA4  570048324              36
    
  • 동일한 SQL 문장이 Child커서를 갖게 되는 이유
    1. 오브젝트명은 같지만 유저에 따라 다른 오브젝트를 가리킬때
    2. 오브젝트 변경으로 무효화된 커서를 처음 사용하려 했으나 특정세션이 아직 기존 커서를 사용중(pin)일때
    3. 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를때
    4. 입력된 바인드값의 길이가 크게 다를 때
    5. NLS 파라미터가 다를때
    6. SQL트레이스를 활성화시켰을 때
  • v$sql_shared_cursor
    1. 새로운 Child커서가 기존커서와 공유되지 못한 이유를 설명해준다
    2. 267페이지 Desc 참조
  • 옵티마이저 모드 변경 테스트
    SQL> ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';
    
    SQL> SELECT * FROM emp;
    
    SQL> SELECT sql_id
      2       , child_number
      3       , child_address
      4       , optimizer_mode_mismatch
      5       , optimizer_mismatch
      6       , auth_check_mismatch
      7       , translation_mismatch
      8    FROM v$sql_shared_cursor
      9   WHERE sql_id = '4u5074pdutxzs'
     10  ;
    
    SQL_ID                     CHILD_NUMBER CHILD_AD OP OP AU TR
    -------------------------- ------------ -------- -- -- -- --
    4u5074pdutxzs                         0 335BFBEC N  N  N  N
    4u5074pdutxzs                         1 2FFC2AB8 N  N  Y  Y
    4u5074pdutxzs                         2 2FFCE6B4 Y  N  N  N
    
  • 입력된 바인드변수값이 다를때 테스트
    SQL> CREATE TABLE t(c VARCHAR2(4000));
    
    SQL> ALTER SYSTEM FLUSH SHARED_POOL;
    
    SQL> DECLARE
      2      l_cnt            NUMBER;
      3      l_child_cnt      NUMBER;
      4      l_prev_child_cnt NUMBER;
      5      l_bind_value     VARCHAR2(4000);
      6      l_sql_id         VARCHAR2(13);
      7  BEGIN
      8      l_prev_child_cnt := 0;
      9
     10      FOR c IN 1..4000
     11      LOOP
     12          l_bind_value := LPAD('A', c, '0');
     13
     14          SELECT COUNT(*)
     15            INTO l_cnt
     16            FROM t
     17           WHERE c = l_bind_value
     18          ;
     19
     20          -- 맨 처음에만 sql_id를 찾아 출력
     21          IF c = 1 THEN
     22              SELECT prev_sql_id
     23                INTO l_sql_id
     24                FROM v$session
     25               WHERE sid = USERENV('SID')
     26                 AND username IS NOT NULL
     27                 AND prev_hash_value <> 0
     28              ;
     29
     30              DBMS_OUTPUT.PUT_LINE('SQL_ID --> ' || l_sql_id);
     31          END IF;
     32
     33          SELECT COUNT(*)
     34            INTO l_child_cnt
     35            FROM v$sql
     36           WHERE sql_id = l_sql_id
     37          ;
     38
     39          IF l_prev_child_cnt < l_child_cnt THEN -- 새 Child 커서가 생길 때마다
     40              DBMS_OUTPUT.PUT_LINE(c);
     41              l_prev_child_cnt := l_child_cnt;
     42          END IF;
     43      END LOOP;
     44  END;
     45  /
    SQL_ID --> f6qwn8zavty07
    1
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SQL> SELECT * FROM v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    • 테스트 결과 책과 다르게 나옴
      • 1, 33, 129, 2001

(4) Parent 커서를 공유하지 못하는 경우

  • 의미적으로는 같은 Sql구문이지만 문자열에 차이가 있는 경우
    1. 공백문자 또는 줄바꿈
    2. 대소문자 구분
    3. 테이블 Owner 명시
    4. 주석
    5. 옵티마이져 힌트 : first_rows, all_rows
    6. 조건절의 비교값
    7. 기타 등등
  • 커서 공유 실패
    1. 1,2,3,4번은 같은 실행계획이지만 문자열을 조금 다르게 기술함으로 인해
      서로 다른 sql이 되어 각각 하드 파싱을 일으키며 Shared Pool공간을 낭비
    2. 커서 공유 실패를 예방하려면 Sql 작성 표준을 정해 이를 준수하도록 해야 한다.
      (예) 오라클 예약어는 대문자로 사용자 객체명은 소문자로 통일
      한줄에 쭈욱 나열하지 말고 의미가 달라지는 구문은 줄바꿈
    3. 5번은 의도적으로 실행계획을 달리 가져가고자 함
    4. 6번은 리터럴값을 문자열로 처리해서 sql 실행
      반복 수행문안에서 사용되거나 자주 사용되는 OLTP성 업무의 쿼리라면 치명적일 수 있음
      바인드 변수를 사용하여 해결.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.