바인드 변수 크기에 따른 커서 생성

오라클 데이터베이스는 바인드 변수 크기에 따라 4개의 범위로 커서를 생성한다. 바인드 변수가 다수라면 경우의 수만큼 커서가 생성될 수 있다. 이번 글에서 관련 내용을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 VARCHAR2(4000));

아래와 같이 테스트를 수행했다. 2-2는 바인드 변수 입력 값의 크기를 변경했고, 2-3은 바인드 변수의 길이를 변경했다. 2-3보다 2-2가 일반적인 상황이다.

-- 2-1
ALTER SYSTEM FLUSH SHARED_POOL;

-- 2-2
DECLARE
    v1 VARCHAR2(4000);
    v2 VARCHAR2(4000);
BEGIN
    FOR i IN 1 .. 4000 LOOP
        v1 := LPAD ('X', i, 'X');
        SELECT /*+ TEST1 */ MAX (c1) INTO v2 FROM t1 WHERE c1 = v1;
    END LOOP;
END;
/

-- 2-3
DECLARE
    v_sql_text VARCHAR2(4000) := q'[
DECLARE
    v1 VARCHAR2({S}) := 'X';
    v2 VARCHAR2(4000);
BEGIN
    SELECT /*+ TEST2 */ MAX (c1) INTO v2 FROM t1 WHERE c1 = v1;
END;]';
BEGIN
    FOR i IN 1 .. 4000 LOOP
        EXECUTE IMMEDIATE REPLACE (v_sql_text, '{S}', i);
    END LOOP;
END;
/

V$SQL 뷰를 조회해보면 2-2는 알려진대로 4개의 범위로 커서가 생성되었지만, 2-3는 3개의 범위로 커서가 생성되었다. 2-2는 커서 캐싱으로 인해 파싱이 1번만 발생한 반면, 2-3은 파싱이 4000번 발생했다.

-- 3
SELECT   SUBSTR (sql_text, 12, 5) sql_id, child_number, parse_calls, rows_processed
    FROM v$sql
   WHERE sql_text LIKE 'SELECT /*+ TEST%'
ORDER BY sql_text, child_number;

SQL_TEXT SQL_ID        CHILD_NUMBER PARSE_CALLS ROWS_PROCESSED
-------- ------------- ------------ ----------- --------------
TEST1    0f2rcd5ua464f            0           1             32 --   32
TEST1    0f2rcd5ua464f            1           0             96 --  128
TEST1    0f2rcd5ua464f            2           0           1872 -- 2000
TEST1    0f2rcd5ua464f            3           0           2000 -- 4000
TEST2    fw8nnx9dp2s85            0          33             32 --   32
TEST2    fw8nnx9dp2s85            1          96             96 --  128
TEST2    fw8nnx9dp2s85            2        3871           3872 -- 4000

7 행이 선택되었습니다.

V$SQL_SHARED_CURSOR 뷰를 살펴보면 bind_length_upgradeable 열이 N으로 표시되는 것을 확인할 수 있다.

-- 4
SELECT   sql_id, child_number, bind_mismatch, hash_match_failed, bind_length_upgradeable
    FROM v$sql_shared_cursor
   WHERE sql_id IN ('0f2rcd5ua464f', 'fw8nnx9dp2s85')
ORDER BY sql_id, child_number;

SQL_ID        CHILD_NUMBER BIND_MISMATCH HASH_MATCH_FAILED BIND_LENGTH_UPGRADEABLE
------------- ------------ ------------- ----------------- -----------------------
0f2rcd5ua464f            0 N             N                 N
0f2rcd5ua464f            1 N             Y                 Y
0f2rcd5ua464f            2 N             Y                 Y
0f2rcd5ua464f            3 N             Y                 Y
fw8nnx9dp2s85            0 N             N                 N
fw8nnx9dp2s85            1 N             Y                 Y
fw8nnx9dp2s85            2 N             Y                 Y

7 행이 선택되었습니다.