오라클에서 Bind 변수를 사용해야 되는 이유

1. 개요 오라클의 바인드 변수 사용유무에 따른 실행 속도 차이 확인 2. 테이블 및 인덱스 생성
SQL> create table t
  2  as
  3  select * from dba_objects;

Table created.

SQL> update t set object_id=rownum;

11425 rows updated.

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

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> set autot traceonly explain
SQL> select object_name from t where object_id=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1414422808

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_OBJECT_ID_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;

System altered.
3. 바인드 변수 사용시 실행속도
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:01.72

SQL>  select sql_text, loads, parse_calls, executions, fetches from v$sql
  2  where sql_text like '%test1%'
  3  and sql_text not like '%v$sql%'
  4  and sql_text not like 'declare%';

SQL_TEXT                                      LOADS PARSE_CALLS EXECUTIONS    FETCHES
---------------------------------------- ---------- ----------- ---------- ----------
select /* test1 */ object_name                    1       20000      20000      20000
               from t
      where object_id=:x


Elapsed: 00:00:00.00

해설 : 하드 파싱이 1번 일어났고, 실행시간은 1.72초이다. 4. 바인드 변수 미사용 시 실행속도
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:13.85

SQL>  select sql_text, loads, parse_calls, executions, fetches from v$sql
  2  where sql_text like '%test2%'
  3  and sql_text not like '%v$sql%'
  4  and sql_text not like 'declare%';

SQL_TEXT                                      LOADS PARSE_CALLS EXECUTIONS    FETCHES
---------------------------------------- ---------- ----------- ---------- ----------

select /* test2 */ object_name                    1           1          1          1
               from t
      where object_id=19423

select /* test2 */ object_name                    1           1          1          1
               from t
      where object_id=19178

select /* test2 */ object_name                    1           1          1          1
               from t
      where object_id=19796
... 중략 ...

1115 rows selected.
해설 : 하드 파싱이 11425번 일어났고, 실행시간은 13.85초이다. 5. 결론 가. 바인드 변수를 사용 시 하드 파싱은 최초 1회 일어나며, 이후 생성된 실행계획(Library Cache)을 재사용한다. 나. 바인드 변수 미사용 시 각 sql문장 수 만큼 하드파싱이 일어난다. 다. 실행속도 및 메모리 공간활용면에서 바인드 변수 사용을 권장한다.

You may also like...

3 Responses

  1. 에몽이 말해보세요:

    비교가 확실하게 되는군요~

  2. 타울 말해보세요:

    요즘 pl/sql 하세요>? ㅋ

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다