5. 바인드 변수의 중요성
2010.06.28 08:04
5. 바인드 변수의 중요성
- 인덱스 생성 -> 통계정보 수집 -> 실행계획 (정상적 인덱스 사용 확인) - (우분투9, 오라클 10gR2)
SQL> create table t as select * from all_objects;
Table created.
SQL> update t set object_id = rownum;
49832 rows updated.
SQL> create unique index t_idx on t(object_id);
Index created.
SQL> analyze table t compute statistics;
Table analyzed.
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 | 28 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 28 | 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;
System altered.
- 바인드 변수 사용 - (우분투9, 오라클 10gR2)
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:02.82
SQL> SELECT sql_text, loads, parse_calls, executions, fetches
2 FROM v$sql
3 WHERE sql_text LIKE '%test1%'
4 AND sql_text NOT LIKE '%v$sql%'
5 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
Elapsed: 00:00:00.13
20,000번 SQL 수행 시간 : 2.82초(교재 : 1.23 초)
v$sql 뷰를 이용하여 커서의 수행통계 확인
SQL 트레이스를 이용한 리포트 결과 (교재 결과 인용 - p.274)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 20000 0.16 0.17 0 0 0 0
Execute 20000 0.22 0.42 0 0 0 0
Fetch 20000 0.45 0.47 0 60000 0 20000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 60000 1.23 1.07 0 60000 0 20000
Misses in library cache during parse: 1
- 바인드 변수 사용 X, 조건절에 Literal 상수값 동적 사용 - (우분투9, 오라클 10gR2)
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:34.37
SQL> SELECT SUBSTR(sql_text, 56, 25) sql_text, loads, parse_calls, executions, fetches
2 FROM v$sql
3 WHERE sql_text LIKE '%test2%'
4 AND sql_text NOT LIKE '%v$sql%'
5 AND sql_text NOT LIKE '%DECLARE%';
SQL_TEXT LOADS PARSE_CALLS EXECUTIONS FETCHES
------------------------- ---------- ----------- ---------- ----------
E object_id = 19892 1 1 1 1
E object_id = 19768 1 1 1 1
E object_id = 19821 1 1 1 1
E object_id = 19571 1 1 1 1
451 rows selected.
Elapsed: 00:00:00.21
20,000번 SQL 수행 시간 : 34.37초 (교재 9.03 초 7.3배)
이전 결과와 12.2배 차이
v$sql 뷰를 이용하여 커서의 수행통계 확인
수행된 SQL 각각에 대해 커서가 따로 생성, 매번 하드파싱 일으킴 (경과 시간 차이의 원인)
교재 결과를 기준으로 결과 건수가 2,319개인 것을 볼 때 SQL Area 중 17,681개는 이미 캐시에서 밀려나고 없음
(테스트 결과 기준 시 결과 건수가 451개로 SQL Area 중 19,549개는 캐시에서 밀려남)
※ 참고 Shared Pool Size
SQL> SELECT * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 1218992
Variable Size 100664912
Database Buffers 180355072
Redo Buffers 2973696
☞ 옵티마이저 힌트도 없고, 로직 수정도 없이 프로그램 작성 패턴만 조금 변경했지만 성능 및 시스템 리소스 사용 측면에서 엄청난 차이 발생
동일한 작업의 SQL을 계속 호출 시 해당 프로시저가 하나 씩 생성(옵티마이저 & Row-Source Generator)
조건의 값이 들어간 데이터 분포가 편중되지 않을 시 프로시저의 내부 처리 루틴은 모두 같을 것이기에 조건의 값을 파라미터로 받아 하나의 프로시저로 처리하도록 하는 것이 효율적
☞ 파라미터 Driven 방식으로 SQL 작성 방법 제공
→ 변수 사용 (하나의 프로시저를 공유로 반복 및 재사용 효과)
→ 메모리 사용량과 파싱 소요시간 감소
→ 시스템 전반의 메모리와 CPU 사용률을 낮춰 데이터 베이스 성능과 확장성을 높이는데 기여
동시 사용자 접속이 많을 때 영향력 ↑
※ 참고
바인드 변수 사용 원칙에 따라 라이브러리 캐시 경합 발생으로 시스템 정상 가동이 어려울 수 있음
☞ cursor_sharing 파라미터 변경 (응급처방 용)
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
27 | 8. 애플리케이션 커서 캐싱 | 토시리 | 2010.06.29 | 6622 |
26 | 9. Static vs. Dynamic SQL [1] | balto | 2010.07.04 | 18382 |
25 | 10. Dynamic SQL 사용 기준 | balto | 2010.07.04 | 8653 |
24 | 11. Static SQL 구현을 위한 기법들 | 실천하자 | 2010.07.05 | 12139 |
23 | 4. Array Processing 활용 | 휘휘 | 2010.07.05 | 18301 |
22 | 1. Call 통계 | 실천하자 | 2010.07.05 | 10467 |
21 | 5. Fetch Call 최소화 | 휘휘 | 2010.07.05 | 16891 |
20 | 5장. 데이터베이스 Call 최소화 원리 | 휘휘 | 2010.07.05 | 6106 |
19 | 2. User Call vs. Recursive Call | 토시리 | 2010.07.07 | 9063 |
18 | 3. 데이터베이스 Call이 성능에 미치는 영향 | 토시리 | 2010.07.07 | 11674 |
17 | 6장. I/O 효율화 원리 | 휘휘 | 2010.07.08 | 6428 |
16 | 4. Prefetch | balto | 2010.07.10 | 28467 |
15 | 5. Direct Path I/O | balto | 2010.07.10 | 12215 |
14 | 8. PL/SQL 함수 호출 부하 해소 방안 | 토시리 | 2010.07.11 | 14054 |
13 | 6. 페이지 처리의 중요성 | 실천하자 | 2010.07.11 | 6826 |
12 | 2. Memory vs. Disk I/O | 휘휘 | 2010.07.12 | 7451 |
11 | 3. Single Block vs. Multiblock I/O | 휘휘 | 2010.07.12 | 9181 |
10 | 7. PL/SQL 함수의 특징과 성능 부하 | 실천하자 | 2010.07.12 | 12622 |
9 | 1. 블록 단위 I/O | 토시리 | 2010.07.12 | 9401 |
8 | 1. Library Cache Lock | balto | 2010.07.17 | 12815 |