5. 바인드 변수의 중요성

• 인덱스 생성 -> 통계정보 수집 -> 실행계획 (정상적 인덱스 사용 확인) - (우분투9, 오라클 10gR2)

SQL> create table t as select * from all_objects;

Table created.

SQL> update t set object_id = rownum;

49832 rows updated.

SQL> create unique index t_idx on t(object_id);

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> set autotrace 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 |     28 |      2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |      1 |     28 |      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 autotrace off
SQL> alter system flush shared_pool;

System altered.

• 바인드 변수 사용 - (우분투9, 오라클 10gR2)
  

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 procedure successfully completed.

Elapsed: 00:00:02.82

SQL> SELECT sql_text, loads, parse_calls, executions, fetches
  2  FROM   v$sql
  3  WHERE  sql_text LIKE '%test1%'
  4  AND    sql_text NOT LIKE '%v$sql%'
  5  AND    sql_text NOT LIKE '%DECLARE%';

SQL_TEXT                                                     LOADS PARSE_CALLS EXECUTIONS    FETCHES
---------------------------------------------------------- ------- ----------- ---------- ----------
SELECT /* test1 */ object_name FROM t WHERE object_id = :x     1     20000        20000      20000


Elapsed: 00:00:00.13

20,000번 SQL 수행 시간 : 2.82초(교재 : 1.23 초)

v$sql 뷰를 이용하여 커서의 수행통계 확인

SQL 트레이스를 이용한 리포트 결과 (교재 결과 인용 - p.274)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    20000      0.16       0.17          0          0          0           0
Execute  20000      0.22       0.42          0          0          0           0
Fetch    20000      0.45       0.47          0      60000          0       20000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    60000      1.23       1.07          0      60000          0       20000

Misses in library cache during parse: 1

• 바인드 변수 사용 X, 조건절에 Literal 상수값 동적 사용 - (우분투9, 오라클 10gR2)
  

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 procedure successfully completed.

Elapsed: 00:00:34.37

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

SQL_TEXT               LOADS PARSE_CALLS EXECUTIONS    FETCHES
------------------------- ---------- ----------- ---------- ----------
E object_id = 19892           1           1      1         1
E object_id = 19768           1           1      1         1

E object_id = 19821           1           1      1         1
E object_id = 19571           1           1      1         1

451 rows selected.

Elapsed: 00:00:00.21

20,000번 SQL 수행 시간 : 34.37초 (교재 9.03 초 7.3배)

이전 결과와 12.2배 차이

v$sql 뷰를 이용하여 커서의 수행통계 확인

수행된 SQL 각각에 대해 커서가 따로 생성, 매번 하드파싱 일으킴 (경과 시간 차이의 원인)

교재 결과를 기준으로 결과 건수가 2,319개인 것을 볼 때 SQL Area 중 17,681개는 이미 캐시에서 밀려나고 없음

(테스트 결과 기준 시 결과 건수가 451개로 SQL Area 중 19,549개는 캐시에서 밀려남)

※ 참고 Shared Pool Size

SQL> SELECT * from v$sga;

NAME                           VALUE
-------------------- ----------
Fixed Size                     1218992
Variable Size              100664912
Database Buffers         180355072
Redo Buffers                  2973696

☞ 옵티마이저 힌트도 없고, 로직 수정도 없이 프로그램 작성 패턴만 조금 변경했지만 성능 및 시스템 리소스 사용 측면에서 엄청난 차이 발생

동일한 작업의 SQL을 계속 호출 시 해당 프로시저가 하나 씩 생성(옵티마이저 & Row-Source Generator)

조건의 값이 들어간 데이터 분포가 편중되지 않을 시 프로시저의 내부 처리 루틴은 모두 같을 것이기에 조건의 값을 파라미터로 받아 하나의 프로시저로 처리하도록 하는 것이 효율적

☞ 파라미터 Driven 방식으로 SQL 작성 방법 제공

→ 변수 사용 (하나의 프로시저를 공유로 반복 및 재사용 효과)

→ 메모리 사용량과 파싱 소요시간 감소

→ 시스템 전반의 메모리와 CPU 사용률을 낮춰 데이터 베이스 성능과 확장성을 높이는데 기여

    동시 사용자 접속이 많을 때 영향력 ↑

※ 참고

    바인드 변수 사용 원칙에 따라 라이브러리 캐시 경합 발생으로 시스템 정상 가동이 어려울 수 있음

    ☞ cursor_sharing 파라미터 변경 (응급처방 용)