1. 바인드 변수의 중요성
   1. 바인드변수란?
      1. 일반적인방식
      2. 바인드 변수방식
      3. 바인드변수 테스트
      4. Literal 상수값으로 바인드 변수테스트

바인드 변수의 중요성

바인드변수란?

일반적인방식

SELECT * FROM EMP WHERE EMPNO ='123';

바인드 변수방식

SELECT * FROM EMP WHERE EMONO = :EMPNO;

바인드변수 테스트

SQL> create table t as
  2  select * from all_objects;

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

SQL> update t set object_id = rownum;

61575 행이 갱신되었습니다.

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

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

SQL> analyze table t compute statistics;

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

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 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    29 |     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;

시스템이 변경되었습니다.


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:05.31

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%';

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


경   과: 00:00:00.01


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    20000      0.20       0.16          0          0          0           0
Execute  20000      0.39       0.34          0          0          0           0
Fetch    20000      0.39       0.34          0      60000          0       20000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    60000      0.98       0.86          0      60000          0       20000

• SQl 실행 : 2만번
• Paser Call 수행 : 2만번
• 하드파싱 : 1번
• 위 실행 결과로 하드파싱 1번으로 인하여 Shared_pool 적재하는 작업이 한번수행한걸로 확인할수잇다.

Literal 상수값으로 바인드 변수테스트

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:13.20

앞의 바인드변수 테스트에서 걸린 시간은: 00:00:05.31
바인드변수를 사용하였을경우 2.5배정도 차이가 있는걸 확인할수있다.

     Sql_text	Loads	Parse_calls	Executions	Fetches

     WHERE object_id = 1	1	1	1	1
     WHERE object_id = 1	1	1	1	1
     WHERE object_id = 1	1	1	1	1
     WHERE object_id = 1	1	1	1	1
     WHERE object_id = 1	1	1	1	1
......
......
     WHERE object_id = 1	1	1	1	1
     WHERE object_id = 1	1	1	1	1
     WHERE object_id = 1	1	1	1	1
     WHERE object_id = 1	1	1	1	1

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

경   과: 00:00:00.34

For Loop 를 통한 수행된 SQL 쿼리들이 각각의 커서가 따로 생성하여 shared_pool 공유영역에 남아있지않은 자료들도 존재할수있다.
하드파싱도 각각하므로 성능저하를 가져올수있다.