바인드 변수의 중요성

바인드변수란?

일반적인방식
{code:sql}

SELECT * FROM EMP WHERE EMPNO ='123';



|| 바인드 변수방식 ||
|{code:sql}

SELECT * FROM EMP WHERE EMONO = :EMPNO;

바인드변수 테스트
{code:sql}

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 상수값으로 바인드 변수테스트 ||
|{code:sql}
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 공유영역에 남아있지않은 자료들도 존재할수있다.
하드파싱도 각각하므로 성능저하를 가져올수있다.

바인드 변수의 중요성 (by bshman) [2012.05.14]

바인드 변수의 중요성