[펌] SQL 바인딩 변수와 리터럴 상수 테스트

05 바인드 변수의 중요성

• 테스트 준비

  SQL> CREATE TABLE t AS SELECT * FROM all_objects; 테이블이 생성되었습니다. SQL> UPDATE t SET object_id = ROWNUM; 6989 행이 갱신되었습니다. SQL> CREATE UNIQUE INDEX t_idx ON t(object_id); 인덱스가 생성되었습니다. SQL> ANALYZE TABLE t COMPUTE STATISTICS; 테이블이 분석되었습니다. SQL> SET AUTOT 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 | 20 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 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 AUTOT 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:00.96 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%' 10 ; SQL_TEXT LOADS PARSE_CALLS EXECUTIONS FETCHES ------------------------------- ----- ----------- ---------- ------- SELECT ... WHERE object_id = :x 1 20000 20000 20000 경 과: 00:00:00.04

  1. 하드파싱 1회에 20000회 실행 : 커서 공유 확인
  2. PARSE_CALLS이 20000회 발생된 이유는 Dynamic Sql 을 사용했기 때문
  3. Static Sql 을 사용했다면 PARSE_CALLS 1회만 발생.

• 리터럴 상수 테스트

  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:08.62 SQL> SELECT SUBSTR(sql_text, 56, 25) 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%' 10 ; SQL_TEXT LOADS PARSE_CALLS EXECUTIONS FETCHES ----------------------- ----- ----------- ---------- ------- WHERE object_id = 15977 1 1 1 1 WHERE object_id = 15978 1 1 1 1 WHERE object_id = 15979 1 1 1 1 WHERE object_id = 15980 1 1 1 1 WHERE object_id = 15981 1 1 1 1 ... WHERE object_id = 19996 1 1 1 1 WHERE object_id = 19997 1 1 1 1 WHERE object_id = 19998 1 1 1 1 WHERE object_id = 19999 1 1 1 1 WHERE object_id = 20000 1 1 1 1 4228 개의 행이 선택되었습니다. 경 과: 00:00:01.62

  1. 수행시간 증가 : 0.96 초 ==> 8.62 초 (약 9배)
  2. 수행횟수만큼 커서 생성 되어 공유 영역에서 15772건이 밀려나고 4228건만 남아 있음
  3. 엄청남 성능 저하 : 커서 공유 안됨, 하드 파싱 2만회

• 바인드 변수 사용의 효과
  1. 커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요시간을 줄여준다.
  2. 시스템 전반의 메모리와 cpu 사용률을 낮춰 데이터베이스 성능과 확장성을 높이는대 기여
  3. 동시사용자 접속이 많을 경우 영향력이 크다.

문서정보

• 이 문서는 오라클클럽에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://www.gurubee.net/pages/viewpage.action?pageId=4948362&
• 오라클클럽 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.