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

 

1. 개요
오라클의 바인드 변수 사용유무에 따른 실행 속도 차이 확인


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 31 32 33 34 35 36 37 38 39 40 41 42

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

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 31 32 33 34 35

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. 바인드 변수 미사용 시 실행속도

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 31 32 33 34 35 36 37 38 39 40 41 42 43

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문장 수 만큼 하드파싱이 일어난다.
다. 실행속도 및 메모리 공간활용면에서 바인드 변수 사용을 권장한다.