SCOTT@orcl > create table t as select * from all_objects;
테이블이 생성되었습니다.
SCOTT@orcl > update t set object_id = rownum;
41058 행이 갱신되었습니다.
SCOTT@orcl > create unique index t_idx on t(object_id);
인덱스가 생성되었습니다.
SCOTT@orcl > analyze table t compute statistics;
테이블이 분석되었습니다.
SCOTT@orcl > set autotrace traceonly explain
SCOTT@orcl > 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)
| 바인드변수 테스트 |
|---|
| {code:sql} |
SCOTT@orcl > set autotrace off
SCOTT@orcl > alter system flush shared_pool;
시스템이 변경되었습니다.
SCOTT@orcl > set timing on
SCOTT@orcl > DECLARE
2 TYPE rc IS REF CURSOR;
3
4 l_rc rc;
5 l_object_name t.object_name%TYPE;
6 BEGIN
7 FOR i IN 1 .. 20000
8 LOOP
9 OPEN l_rc FOR 'SELECT /* test1 */ object_name
10 FROM t
11 WHERE object_id = :x' USING i;
12
13 FETCH l_rc INTO l_object_name;
14
15 CLOSE l_rc;
16 END LOOP;
17 END;
18 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.84
SCOTT@orcl > 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
경 과: 00:00:00.20
위 실행 결과로 하드파싱(LOADS) 1번으로 인하여 Shared_pool 적재하는 작업이 한번수행한걸로 확인 할 수 있다.
|| Literal 상수값으로 바인드 변수테스트 ||
|{code:sql}
SCOTT@orcl > DECLARE
2 TYPE rc IS REF CURSOR;
3
4 l_rc rc;
5 l_object_name t.object_name%TYPE;
6 BEGIN
7 FOR i IN 1 .. 20000
8 LOOP
9 OPEN l_rc FOR 'SELECT /* test2 */ object_name
10 FROM t
11 WHERE object_id = ' || i;
12
13 FETCH l_rc INTO l_object_name;
14
15 CLOSE l_rc;
16 END LOOP;
17 END;
18 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:21.86
앞의 바인드변수 테스트에서 걸린 시간은 00:00:00.84
바인드변수를 사용하지 않았을때 걸린 시간은 00:00:21.86
20배 정도의 차이가 있는 것을 확인 할 수 있다.
SCOTT@orcl > SELECT sql_text
2 , loads
3 , parse_calls
4 , executions
5 , fetches
6 FROM v$sql
7 WHERE sql_text LIKE '%test2%'
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 WHERE object_id = 5865 1 1 1 1
...
...
...
SELECT /* test1 */ object_name FROM t WHERE WHERE object_id = 5865 1 1 1 1
20000 개의 행이 선택되었습니다.
경 과: 00:00:37.19
- For Loop 를 통한 수행된 SQL 쿼리들이 각각의 커서가 따로 생성됨을 볼 수 있다.
- sql을 수행할 때마다 매번 하드파싱을 일으킨 것을 알 수 있다.
procedure 삼성전자_거래(){..........}
procedure 국민은행_거래(){..........}
procedure 롯데쇼핑_거래(){..........}
procedure 유한양행_거래(){..........}
procedure 동국제강_거래(){..........}
procedure 대우조선_거래(){..........}
하지만 바인드변수를 사용하게 되면 같은 SQL이 여러개 실행이 되지 않고, 옵티마이저 또한 프로시저의 처리 루틴이 같다면
여러개 생성하기보다 아래처럼 파라미터를 받아 하나의 프로시져로 처리가 가능하도록 해준다
procedure 거래 (종목 in varchar 2) {..........}
위의 프로시져처럼 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공되는데, 그것이 바인드 변수를 사용하는 것이다.