오라클에서 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문장 수 만큼 하드파싱이 일어난다. 다. 실행속도 및 메모리 공간활용면에서 바인드 변수 사용을 권장한다.
비교가 확실하게 되는군요~
요즘 pl/sql 하세요>? ㅋ
성능고도화에 나온 예제 실습한거야. ㅎㅎ