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

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

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

1 2 3

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

-- 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번 발생했다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- 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으로 표시되는 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- 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 행이 선택되었습니다.