05 바인드 변수의 중요성

• 테스트 준비
  {section}
  {column:width=50}

SQL> CREATE TABLE t AS SELECT * FROM all_objects;

테이블이 생성되었습니다.

SQL> UPDATE t SET object_id = ROWNUM;

6989 행이 갱신되었습니다.

SQL> CREATE UNIQUE INDEX t_idx ON t(object_id);

인덱스가 생성되었습니다.

SQL> ANALYZE TABLE t COMPUTE STATISTICS;

테이블이 분석되었습니다.

SQL> SET AUTOT TRACEONLY EXPLAIN

SQL> SELECT object_name FROM t WHERE object_id = 1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2929955852

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1000)

SQL> SET AUTOT OFF

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

{column}
{column:width=50}{column}
{section}

• 바인드변수 테스트
  {section}
  {column:width=50}

SQL> SET TIMING ON
SQL> DECLARE
  2    TYPE rc IS REF CURSOR;
  3    l_rc rc;
  4    l_object_name t.object_name%TYPE;
  5  BEGIN
  6    FOR i IN 1 .. 20000
  7    LOOP
  8      OPEN l_rc FOR
  9        'SELECT /* test1 */ object_name
 10           FROM t
 11          WHERE object_id = :x' USING i;
 12      FETCH l_rc INTO l_object_name;
 13      CLOSE l_rc;
 14    END LOOP;
 15  END;
 16  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.96
SQL> SELECT sql_text
  2       , loads
  3       , parse_calls
  4       , executions
  5       , fetches
  6    FROM v$sql
  7   WHERE sql_text LIKE '%test1%'
  8     AND sql_text NOT LIKE '%v$sql%'
  9     AND sql_text NOT LIKE '%DECLARE%'
 10  ;

SQL_TEXT                        LOADS PARSE_CALLS EXECUTIONS FETCHES
------------------------------- ----- ----------- ---------- -------
SELECT ... WHERE object_id = :x     1       20000      20000   20000

경   과: 00:00:00.04

{column}
{column:width=50}{column}
{section}

1. 1. 하드파싱 1회에 20000회 실행 : 커서 공유 확인
   2. PARSE_CALLS이 20000회 발생된 이유는 Dynamic Sql 을 사용했기 때문
   3. Static Sql 을 사용했다면 PARSE_CALLS 1회만 발생.

• 리터럴 상수 테스트
  {section}
  {column:width=50}

SQL> DECLARE
  2    TYPE rc IS REF CURSOR;
  3    l_rc rc;
  4    l_object_name t.object_name%TYPE;
  5  BEGIN
  6    FOR i IN 1 .. 20000
  7    LOOP
  8      OPEN l_rc FOR
  9        'SELECT /* test2 */ object_name
 10           FROM t
 11          WHERE object_id = ' || i;
 12      FETCH l_rc INTO l_object_name;
 13      CLOSE l_rc;
 14    END LOOP;
 15  END;
 16  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:08.62

SQL> SELECT SUBSTR(sql_text, 56, 25) sql_text
  2       , loads
  3       , parse_calls
  4       , executions
  5       , fetches
  6    FROM v$sql
  7   WHERE sql_text LIKE '%test2%'
  8     AND sql_text NOT LIKE '%v$sql%'
  9     AND sql_text NOT LIKE '%DECLARE%'
 10  ;

SQL_TEXT                LOADS PARSE_CALLS EXECUTIONS FETCHES
----------------------- ----- ----------- ---------- -------
WHERE object_id = 15977     1           1          1       1
WHERE object_id = 15978     1           1          1       1
WHERE object_id = 15979     1           1          1       1
WHERE object_id = 15980     1           1          1       1
WHERE object_id = 15981     1           1          1       1
...                                                        
WHERE object_id = 19996     1           1          1       1
WHERE object_id = 19997     1           1          1       1
WHERE object_id = 19998     1           1          1       1
WHERE object_id = 19999     1           1          1       1
WHERE object_id = 20000     1           1          1       1

4228 개의 행이 선택되었습니다.

경   과: 00:00:01.62

{column}
{column:width=50}{column}
{section}

1. 1. 수행시간 증가 : 0.96 초 ==> 8.62 초 (약 9배)
   2. 수행횟수만큼 커서 생성 되어 공유 영역에서 15772건이 밀려나고 4228건만 남아 있음
   3. 엄청남 성능 저하 : 커서 공유 안됨, 하드 파싱 2만회

• 바인드 변수 사용의 효과
  1. 커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요시간을 줄여준다.
  2. 시스템 전반의 메모리와 cpu 사용률을 낮춰 데이터베이스 성능과 확장성을 높이는대 기여
  3. 동시사용자 접속이 많을 경우 영향력이 크다.