04 커서공유

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

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

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

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성 업무의 쿼리라면 치명적일 수 있음
     바인드 변수를 사용하여 해결.

1. HOME
2. [종료]대용량 데이터베이스 스터디
3. 2010년 상반기 대용량데이터베이스 스터디
4. 커서공유