1. 커서(Cursor)란?

  • 커서(Cursor)는 상황에 따라 여러 가지 의미로 사용되고 있으며 크게 아래의 3가지 의미를 말함
    • 공유 커서(shared cursor)
    • 세션 커서(session cursor)
    • 애플리케이션 커서(application cursor)

1) 공유 커서(shared cursor)

  • JAVA, VB, Pro*C, PL/SQL등에서 SQL을 수행하면 Server Process는 해당 SQL이 Library Cache에 공유되어
    있는지를 먼저 확인하고 없으면 최적화를 통한 실행계획을 생성, 존재하면 Library Cache에 공유되어
    있는 Cursor를 재활용하는데 이 때 Cursor를 공유 커서(shared cursor)라 부름

2) 세션 커서(session cursor)

  • Library Cache에 공유되어 있는 커서를 실행할때는 PGA 영역에 'Private SQL Area' 메모리 영역을 할당하며
    이는 'Persisten Area', 'Runtime Area'로 나뉜다.
  • Persisten Area : 바인드 변수 등을 저장함. 실행이 종료된 후 커서가 닫힐 때 해제됨
  • Runtime Area : SELECT문은 모든 레코드를 Fetch 완료하거나 실행을 취소할 때 해제되지만
    Insert, Update, Delete는 실행이 종료됨가 동시에 해제됨
  • 이 때, 'Shared SQL Area(Library Cache)'를 읽어 커서를 실행하는데 필요한 정보들을 Private SQL Area에 담고,
    shared cursor를 가리키는 포인터를 유지하며, 커서의 상태(open, bound, execute, close 등) 정보도 관리함
  • 이런 과정들을 "커서를 오픈한다."라고 함
  • 이를 JAVA의 Class와 비교한다면 'Shared SQL Area(Library Cache)'는 Class이고 'Private SQL Area'는
    Class를 이용한 객체(Object)이다.

3) 애플리케이션 커서(application cursor)

  • PGA에 있는 커서를 핸들링하려면 JAVA, VB, Pro*C, PL/SQL 같은 클라이언트 애플리케이션에도 리소스를 할당해야
    하는데, 이 또한 커서라는 용어를 사용함

2. 커서 공유

  • 오라클에서 "커서를 공유한다."라고 함은, 라이브러리 캐시의 공유 커서를 말함.
  • 라이브버리 캐시에 공유돼 있는 커서의 수행 통계를 v$sql을 통해 조회해 볼 수 있음.

-- 1. 오라클 버전 확인
SELECT *
FROM   V$VERSION
;

BANNER
-------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production

-- 2. 테이블 생성
DROP TABLE EMP;

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  MGR NUMBER(4),
                  HIREDATE DATE,
                  SAL NUMBER(7, 2),
                  COMM NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST', 7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

COMMIT;

-- 3. 라이브러리 캐시 비움
ALTER SYSTEM FLUSH SHARED_POOL;

-- 4. 1회 조회
SELECT /* CURSOR_TEST */
       EMPNO,
       ENAME,
       JOB,
       SAL,
       DEPTNO
FROM   EMP
WHERE  EMPNO = 7788
;

     EMPNO ENAME                JOB                       SAL     DEPTNO
---------- -------------------- ------------------ ---------- ----------
      7788 SCOTT                ANALYST                  3000         20

1 row selected.

-- 5. V$SQL 조회
SELECT SQL_ID,
       PARSE_CALLS,
       LOADS,
       EXECUTIONS,
       INVALIDATIONS,
       DECODE(SIGN(INVALIDATIONS), 1, (LOADS - INVALIDATIONS), 0) RELOADS
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%CURSOR_TEST%'
AND    SQL_TEXT NOT LIKE '%V$SQL%'
;


SQL_ID                     PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
6gyd34836phnb                        1          1          1             0          0

1 row selected.

-- 6. 2회 조회
SELECT /* CURSOR_TEST */
       EMPNO,
       ENAME,
       JOB,
       SAL,
       DEPTNO
FROM   EMP
WHERE  EMPNO = 7788
;

     EMPNO ENAME                JOB                       SAL     DEPTNO
---------- -------------------- ------------------ ---------- ----------
      7788 SCOTT                ANALYST                  3000         20

1 row selected.

-- 7. 3회 조회
SELECT /* CURSOR_TEST */
       EMPNO,
       ENAME,
       JOB,
       SAL,
       DEPTNO
FROM   EMP
WHERE  EMPNO = 7788
;

     EMPNO ENAME                JOB                       SAL     DEPTNO
---------- -------------------- ------------------ ---------- ----------
      7788 SCOTT                ANALYST                  3000         20

1 row selected.

-- 8. V$SQL 조회
SELECT SQL_ID,
       PARSE_CALLS,
       LOADS,
       EXECUTIONS,
       INVALIDATIONS,
       DECODE(SIGN(INVALIDATIONS), 1, (LOADS - INVALIDATIONS), 0) RELOADS
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%CURSOR_TEST%'
AND    SQL_TEXT NOT LIKE '%V$SQL%'
;

SQL_ID                     PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
6gyd34836phnb                        3          1          3             0          0

1 row selected.

PARSE_CALLS라이브러리 캐시에서 SQL 커서를 찾으려는 요청 횟수
LOADS하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
EXECUTIONSSQL을 수행한 횟수
INVALIDATIONS커서가 무효화된 횟수. 커서가 참조하고 있는 오브젝트에 중요한 변화가 일어났음을 의미함.
  • 위의 결과는 SHARED Pool을 모두 비우고 테스트를 시작했으므로 SQL을 첫 번째 수행할 때 하드파싱이 일어난 것이며,
    공유된 하나의 커서를 세번 재사용했음을 알 수 있다.
  • 지금까지는 같은 세션에서 커서 공유 테스트를 해 보았고, 다른 세션에서도 공유가 되는지 테스트를 해봄

-- 1. 새로 연결
conn user/password@instance

Connected.

-- 2. 4회 조회
SELECT /* CURSOR_TEST */
       EMPNO,
       ENAME,
       JOB,
       SAL,
       DEPTNO
FROM   EMP
WHERE  EMPNO = 7788
;

     EMPNO ENAME                JOB                       SAL     DEPTNO
---------- -------------------- ------------------ ---------- ----------
      7788 SCOTT                ANALYST                  3000         20

1 row selected.

-- 3. V$SQL 조회
SELECT SQL_ID,
       PARSE_CALLS,
       LOADS,
       EXECUTIONS,
       INVALIDATIONS,
       DECODE(SIGN(INVALIDATIONS), 1, (LOADS - INVALIDATIONS), 0) RELOADS
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%CURSOR_TEST%'
AND    SQL_TEXT NOT LIKE '%V$SQL%'
;

SQL_ID                     PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
6gyd34836phnb                        4          1          4             0          0

  • 이렇게 다른 세션에서도 커서를 공유할 수 있지만 아래의 이유로 커서 공유를 할 수 없을 수 있다.
    • 커서가 참조하고 있던 오브젝트에 컬럼이 추가/삭제되는 경우
    • 커서가 참조하고 있던 오브젝트에 새로운 인덱스가 만들어지는 경우
    • 커서가 참조하고 있던 오브젝트의 통계를 새로 수집하는 경우
  • 통계정보 생성 이후 커서 공유 테스트

-- 1. 통계정보 생성
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'EMP',
                                  CASCADE => TRUE,
                                  NO_INVALIDATE => FALSE
                                  );
END;
/

PL/SQL procedure successfully completed.

-- 2. 5회 조회
SELECT /* CURSOR_TEST */
       EMPNO,
       ENAME,
       JOB,
       SAL,
       DEPTNO
FROM   EMP
WHERE  EMPNO = 7788
;

     EMPNO ENAME                JOB                       SAL     DEPTNO
---------- -------------------- ------------------ ---------- ----------
      7788 SCOTT                ANALYST                  3000         20

1 row selected.
;

-- 3. V$SQL 조회
SELECT SQL_ID,
       PARSE_CALLS,
       LOADS,
       EXECUTIONS,
       INVALIDATIONS,
       DECODE(SIGN(INVALIDATIONS), 1, (LOADS - INVALIDATIONS), 0) RELOADS
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%CURSOR_TEST%'
AND    SQL_TEXT NOT LIKE '%V$SQL%'
;

SQL_ID                     PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
6gyd34836phnb                        1          2          1             1          1

1 row selected.

  • 커서가 공유되려면 커서를 식별하는 키 값이 같아야 되는데, 이 식별자는 'SQL 문장'임(V$SQL.SQL_FULLTEXT)

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

  • SQL 문장이 100% 동일한데도 SQL 커서를 공유하지 못하고 별도의 SQL 커서를 생성해야 할 때가 있음
    만약 EMP 테이블이 SCOTT과 HR 스키마에 각각 생성되어 있다면 SCOTT과 HR로 로긴할 때 다른 테이블을
    액세스해야 하고 실행계획도 달라져야 하며 이 때, Child Cursor를 생성함
  • 영구적으로 보관되는 Stored Object(테이블, 인덱스, 함수, 프로시저, 패키지 등)는 이름으로 식별이
    가능하므로 Child 오브젝트가 필요없지만, SQL 커서는 Transient Object는 전체 문자열이 LCO를
    식별하는 식별자이므로 이에 따라 Child 커서가 생성될 수 있다.
  • V$SQLAREA는 Parent 커서 정보를 보여주고, V$SQL은 Child 커서를 보여준다.
    (V$SQLAREA는 V$SQL을 GROUP BY 한 결과임)
  • 아래는 옵티마이저 모드를 변경하여 테스트 한 결과임

-- 1. 옵티마이저 모드 ALL_ROWS 활성화
ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';

-- 2. EMP 테이블 조회
SELECT * FROM EMP;

     EMPNO ENAME                JOB                       MGR HIREDATE            SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
      7788 SCOTT                ANALYST                  7566 09-DEC-82          3000                    20

1 row selected.

-- 3. 옵티마이저 모드 FIRST_ROWS 활성화
ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';

-- 4. EMP 테이블 조회
SELECT * FROM EMP;

     EMPNO ENAME                JOB                       MGR HIREDATE            SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
      7788 SCOTT                ANALYST                  7566 09-DEC-82          3000                    20

1 row selected.

-- 5. V$SQL 조회
SELECT SQL_ID,
       CHILD_NUMBER,
       OPTIMIZER_MODE,
       ADDRESS,
       HASH_VALUE,
       PARSING_USER_ID
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%SELECT * FROM EMP%'
AND    SQL_TEXT NOT LIKE '%V$SQL%'
;

SQL_ID                     CHILD_NUMBER OPTIMIZER_MODE       ADDRESS          HASH_VALUE PARSING_USER_ID
-------------------------- ------------ -------------------- ---------------- ---------- ---------------
4ttqgu8uu8fus                         0 ALL_ROWS             070000010BC93AB8  899955544              44
4ttqgu8uu8fus                         1 FIRST_ROWS           070000010BC93AB8  899955544              44

-- 6. V$SQLAREA 조회
SELECT SQL_ID,
       VERSION_COUNT,
       OPTIMIZER_MODE,
       ADDRESS,
       HASH_VALUE
FROM   V$SQLAREA
WHERE  SQL_TEXT LIKE '%SELECT * FROM EMP%'
AND    SQL_TEXT NOT LIKE '%V$SQL%'
;

SQL_ID                     VERSION_COUNT OPTIMIZER_MODE       ADDRESS          HASH_VALUE
-------------------------- ------------- -------------------- ---------------- ----------
4ttqgu8uu8fus                          2 FIRST_ROWS           070000010BC93AB8  899955544

  • SQL 하나당 여러 개의 Child 커서를 갖는 것은 바람직한 현상은 아님.
    VERSION_COUNT 수치가 높은 SQL일수록 커서를 탐색하는 데 더 많은 시간을 소비하므로 라이브러리 캐시 래치에
    대한 경합 발생 가능성을 증가시킴.
  • 하나의 SQL 문장에 여러 개 Child 커서를 갖게 되는 이유
    • SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때
    • 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시
      하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용중(pin)일 때
    • 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
    • 입력된 바인드 값의 길이가 크게 다를 ?
    • NLS 파라미터를 다르게 설정했을 때
    • SQL 트레이스를 활성화했을 때
  • 아래 테스트는 입력된 바인드 값이 32, 128, 2000 바이트를 넘을 때마다 새로운 Child 커서가 생성됨을 보여줌

-- 1. 테이블 생성
CREATE TABLE T(C VARCHAR2(4000));

-- 2. 라이브러리 캐시 비움
ALTER SYSTEM FLUSH SHARED_POOL;

-- 3. BYTE 생성 이미지
WITH TAB AS (SELECT LPAD('A', LEVEL, '0') VAL
             FROM   DUAL
             CONNECT BY LEVEL <= 4000)
SELECT VAL,
       LENGTHB(VAL) BITE
FROM   TAB
;

A      1
0A     2
00A    3
000A   4
0000A  5
00000A 6
........

-- 4. PL/SQL 스크립트
DECLARE
    l_cnt            NUMBER;
    l_child_cnt      NUMBER;
    l_prev_child_cnt NUMBER;
    l_bind_value     VARCHAR2(4000);
    l_sql_id         VARCHAR2(13);
BEGIN
    l_prev_child_cnt := 0;

    FOR c IN 1..4000 LOOP
        l_bind_value := LPAD('A', c, '0');

        SELECT COUNT(*)
        INTO   l_cnt
        FROM   T
        WHERE  C = l_bind_value;

        -- 맨 처음에만 sql_id를 찾아 출력
        IF c = 1 THEN
            SELECT PREV_SQL_ID
            INTO   l_sql_id
            FROM   V$SESSION
            WHERE  SID = USERENV('SID')
            AND    USERNAME IS NOT NULL
            AND    PREV_HASH_VALUE <> 0;

            DBMS_OUTPUT.PUT_LINE('SQL_ID --> ' || l_sql_id);
        END IF;

        SELECT COUNT(*)
        INTO   l_child_cnt
        FROM   V$SQL
        WHERE  SQL_ID = l_sql_id;

        IF l_prev_child_cnt < l_child_cnt THEN -- 새 Child 커서가 생길 때마다
            DBMS_OUTPUT.PUT_LINE(c);

            l_prev_child_cnt := l_child_cnt;
        END IF;
    END LOOP;
END;
/

SQL_ID --> f6qwn8zavty07
1
33
129
2001

-- 5. V$SQL_SHARED_CURSOR 조회
SELECT CHILD_NUMBER,
       BIND_MISMATCH
FROM   V$SQL_SHARED_CURSOR
WHERE  SQL_ID = 'f6qwn8zavty07'
ORDER  BY CHILD_NUMBER
;

CHILD_NUMBER BI
------------ --
           0 N
           1 Y
           2 Y
           3 Y

4 rows selected.

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

  • 이번에는 의미적으로 같고 실행환경이 같음에도 불구하고 커서를 공유하지 못해 Parant 커서 자체가
    여러 개 생성되는 경우임

1) 공백문자 또는 줄바꿈


SELECT * FROM CUSTOMER;
SELECT *      FROM CUSTOMER;

2) 대소문자 구분


SELECT * FROM CUSTOMER;
SELECT * FROM Customer;

3) 테이블 Owner 명시


SELECT * FROM CUSTOMER;
SELECT * FROM HR.CUSTOMER;

4) 주석(Comment)


SELECT * FROM CUSTOMER;
SELECT /* 주석문 */ * FROM CUSTOMER;

5) 옵티마이저 힌트 사용


SELECT * FROM CUSTOMER;
SELECT /*+ ALL_ROWS */ * FROM CUSTOMER;

6) 조건절 비교 값


SELECT * FROM CUSTOMER WHERE CUST_ID = '0000001';
SELECT * FROM CUSTOMER WHERE CUST_ID = '0000002';

  • 이 중 가장 라이브러리 캐시 효율과 직접적으로 관련된 예는 6)번 예제임
  • 이와 같이 조건절에 바인드 변수를 사용하지 않고 서로 다른 Literal 값으로 문자열을 사용하게 되면
    이는 시스템을 장애 상황으로 몰고 갈 수 있다.

문서에 대하여