| - 오라클은 스칼라 서브쿼리 수행 시 입력 값(메인쿼리에서 받은)과 출력 값을 내부 캐시(Query Execution Cache)에 저장 - _query_execution_cache_max_size 파라미터 사이즈 만큼 캐시 사용 - 스칼라 서브쿼리가 수행될 때 '입력 값'이 캐시에 있으면 저장된 '출력 값'을 리턴 - 캐시에서 찾지 못한 경우 쿼리 수행, 결과값은 캐시에 저장 - 스칼라 서브쿼리의 캐싱 효과는 입력 값의 종류가 적을 때 성능 개선 효과 |
테스트를 위해 아래와 같이 테이블을 생성하자.
CREATE TABLE t1 AS SELECT ROWNUM AS c1, NVL (NULLIF (MOD (ROWNUM, 100), 0), 100) AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 10000');
CREATE INDEX t1_x1 ON t1 (c1, c2);
CREATE INDEX t1_x2 ON t1 (c2, c1);
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t2_x2 ON t2 (c2);
아래 쿼리는 서브 쿼리에 1에서 10000까지의 순번이 입력된다. 서브 쿼리 Caching 효과가 발생하지 않았지만(Starts = 10000), 버퍼 Pinning 효과에 의해 166개의 블록 I/O가 발생했다.
-- T1 데이터 조회 순서
T1_C1|T1_C2|
-----+-----+
1| 1|
2| 2|
3| 3|
4| 4|
5| 5|
6| 6|
7| 7|
8| 8|
9| 9|
10| 10|
11| 11|
12| 12|
13| 13|
14| 14|
15| 15|
16| 16|
...
SELECT /*+ INDEX(A) */
COUNT ((SELECT /*+ INDEX(X) */ MAX(x.c2) FROM t2 x WHERE x.c1 = a.c1))
FROM t1 a
WHERE a.c1 > 0
;
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 115 (100)| | 1 |00:00:00.03 | 193 | 40 |
| 1 | SORT AGGREGATE | | 10000 | 1 | 26 | | | 10000 |00:00:00.02 | 166 | 21 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 10000 | 100 | 2600 | 2 (0)| 00:00:01 | 10000 |00:00:00.01 | 166 | 21 |
|* 3 | INDEX RANGE SCAN | T2_X1 | 10000 | 40 | | 1 (0)| 00:00:01 | 10000 |00:00:00.01 | 148 | 21 |
| 4 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.03 | 193 | 40 |
|* 5 | INDEX RANGE SCAN | T1_X1 | 1 | 10000 | 126K| 28 (0)| 00:00:01 | 10000 |00:00:00.01 | 27 | 19 |
-------------------------------------------------------------------------------------------------------------------------------------------------
아래 쿼리는 서브 쿼리에 1, 101, 201, 301 ... 순서의 값이 100회 반복 입력된다. 서브 쿼리 Caching 효과가 발생하지 않았고, 버퍼 Pinning 효과도 발생하지 않아 13,421개의 블록 I/O가 발생했다.
-- T1 데이터 조회 순서
T1_C1|T1_C2|
-----+-----+
1| 1|
101| 1|
201| 1|
301| 1|
401| 1|
501| 1|
601| 1|
701| 1|
801| 1|
901| 1|
1001| 1|
1101| 1|
1201| 1|
1301| 1|
...
SELECT /*+ INDEX(A) */
COUNT ((SELECT /*+ INDEX(X) */ MAX(x.c2) FROM t2 x WHERE x.c1 = a.c1))
FROM t1 a
WHERE a.c2 > 0
;
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 653 (100)| | 1 |00:00:00.03 | 13448 | 19 |
| 1 | SORT AGGREGATE | | 10000 | 1 | 26 | | | 10000 |00:00:00.02 | 13421 | 0 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 10000 | 100 | 2600 | 2 (0)| 00:00:01 | 10000 |00:00:00.02 | 13421 | 0 |
|* 3 | INDEX RANGE SCAN | T2_X1 | 10000 | 40 | | 1 (0)| 00:00:01 | 10000 |00:00:00.01 | 11621 | 0 |
| 4 | SORT AGGREGATE | | 1 | 1 | 26 | | | 1 |00:00:00.03 | 13448 | 19 |
|* 5 | INDEX RANGE SCAN | T1_X2 | 1 | 10000 | 253K| 28 (0)| 00:00:01 | 10000 |00:00:00.01 | 27 | 19 |
-------------------------------------------------------------------------------------------------------------------------------------------------
아래 쿼리는 서브 쿼리에 1에서 100까지의 값이 100회씩 입력된다. 서브 쿼리 Caching 효과와 버퍼 Pinning 효과에 의해 9개의 블록 I/O가 발생했다.
-- T1 데이터 조회 순서
T1_C1|T1_C2|
-----+-----+
1| 1|
101| 1|
201| 1|
301| 1|
401| 1|
501| 1|
601| 1|
701| 1|
801| 1|
901| 1|
1001| 1|
1101| 1|
1201| 1|
1301| 1|
...
SELECT /*+ INDEX(A) */
COUNT ((SELECT /*+ INDEX(X) */ MAX(x.c1) FROM t2 x WHERE x.c2 = a.c2))
FROM t1 a
WHERE a.c2 > 0
;
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 115 (100)| | 1 |00:00:00.01 | 36 | 1 |
| 1 | SORT AGGREGATE | | 100 | 1 | 26 | | | 100 |00:00:00.01 | 9 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 100 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 9 | 1 |
|* 3 | INDEX RANGE SCAN | T2_X2 | 100 | 40 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 8 | 1 |
| 4 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.01 | 36 | 1 |
|* 5 | INDEX RANGE SCAN | T1_X2 | 1 | 10000 | 126K| 28 (0)| 00:00:01 | 10000 |00:00:00.01 | 27 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------
-- 참조
https://hrjeong.tistory.com/359
http://wiki.gurubee.net/pages/viewpage.action?pageId=6259012
'DBMS > SQL튜닝' 카테고리의 다른 글
| ORDER BY 컬럼명이 가리키는 컬럼은? (0) | 2023.07.03 |
|---|---|
| 펑션 위치별 실행 횟 수 (0) | 2023.07.02 |
| INLIST ITERATOR 성능개선(where 절 in 조건) (0) | 2023.06.29 |
| 스칼라서브쿼리 실행계획 순서 및 실행통계 (0) | 2023.06.28 |
| SELECT 절 펑션 수행 횟수 확인 (0) | 2023.06.26 |
댓글