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

bandai Published: Categories: 미분류

iBatis 3 User Guide 의 String substition 부분에서 찾은 내용입니다.

 

#{} 문법은 iBatis가 PreparedStatement에 대한 Property를 생성하고 안전하게 값을 설정하도록 합니다.

대신 입력된 문자열을 수정해야 하기 때문에 조금 느립니다.

 

${} 문법은 문자열을 수정하지 않고, PreparedStatement parameter(?에 매핑)에

직접 문자열을 집어 넣습니다.?따라서 조금 더 빠르죠.

 

하지만, 이렇게 사용할 경우 사용자의 SQL injection 공격에 무방비 상태가 되어 버리므로 주의가 필요합니다

SELECT a.id, a.name FROM 어떤테이블 a WHERE a.id = #id#

와

2) SELECT a.id, a.name FROM 어떤테이블 a WHERE a.id = $id$

를 놓고 설명을 드리자면

 

1)의 경우 iBatis는 다음과 같은 PreparedStatement를 생성해서 사용하게 됩니다.

SELECT a.id, a.name FROM 어떤테이블 a WHERE a.id = ?

따라서 #id#에 설정되는 값이 바뀌어도 위의 PreparedStatement를 재사용하게 되고,

DB입장에서도 이미 컴파일된 SQL을 사용하게 됩니다.

 

하지만 2)의 경우 iBatis는 $id$를 마치 상수와 같이 처리해서 PreparedStatement를 생성하게 됩니다.

만약 $id$가 1인 경우는

SELECT a.id, a.name FROM 어떤테이블 a WHERE a.id =1

을 생성합니다.

여기서 $id$값이 바뀌어서 2가 되는 경우는

SELECT a.id, a.name FROM 어떤테이블 a WHERE a.id =2

를 또 PreparedStatement로 생성하게 됩니다.

따라서 $를 사용해서 변수를 바인딩하는 경우 해당 변수의 값이 많지 않은 경우에는

바인드할 변수가 없는 PreparedStatement처럼 사용할 수 있기 때문에 수행 능력을 높일 수 있지만,

게시판의 식별자처럼 계속 바뀌는 변수에 대해 사용하는 경우는 오히려 수행 능력이 떨어집니다.

 

저는 대부분 테이블 이름과 같이 SQL자체가 바뀌는 경우 혹은 DDL에서 재활용할 부분이 많을 때에 사용합니다.

 

 

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

출처 :?http://www.jigi.net/4295

Leave a Reply

Your email address will not be published.