중복 조회로 랜덤 I/O를 감소시키는 튜닝 기법을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS
SELECT ROWNUM AS c1, CEIL (ROWNUM / 100) AS c2, LPAD ('X', 100, 'X') AS c3
FROM XMLTABLE ('1 to 1000000')
ORDER BY ORA_HASH (ROWNUM);
CREATE TABLE t2 AS
SELECT ROWNUM AS c1, MOD (ROWNUM, 10000) AS c2, LPAD ('X', 100, 'X') AS c3
FROM XMLTABLE ('1 to 1000000')
ORDER BY ORA_HASH (ROWNUM);
CREATE INDEX t1_x1 ON t1 (c1, c2);
CREATE INDEX t2_x1 ON t2 (c1, c2);
해시 조인시 32,350개의 블록 I/O가 발생한다.
-- 2
SELECT /*+ LEADING(A) USE_HASH(B) FULL(A) FULL(B) */
*
FROM t1 a
, t2 b
WHERE a.c1 < 10001
AND b.c1 = a.c2
AND b.c2 = 1;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 32350 | |
|* 1 | HASH JOIN | | 1 | 100 | 32350 | 2902K (0)|
|* 2 | TABLE ACCESS FULL| T1 | 1 | 10000 | 16173 | |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 100 | 16174 | |
--------------------------------------------------------------------------
NL 조인도 10,069개의 블록 I/O가 발생한다.
-- 3
SELECT /*+ LEADING(A) USE_NL(B) INDEX(A) INDEX(B) */
*
FROM t1 a
, t2 b
WHERE a.c1 < 10001
AND b.c1 = a.c2
AND b.c2 = 1;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 10069 |
| 1 | NESTED LOOPS | | 1 | 100 | 10069 |
| 2 | NESTED LOOPS | | 1 | 100 | 10061 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 10000 | 10035 |
|* 4 | INDEX RANGE SCAN | T1_X1 | 1 | 10000 | 35 |
|* 5 | INDEX RANGE SCAN | T2_X1 | 10000 | 100 | 26 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 100 | 100 | 8 |
-----------------------------------------------------------------------------------
CLUSTER_BY_ROWID 힌트로 CF를 개선시키더라도 7,524개의 블록 I/O가 발생하는 것을 확인할 수 있다.
-- 4
SELECT /*+ LEADING(A) USE_NL(B) INDEX(A) INDEX(B) CLUSTER_BY_ROWID(A) */
*
FROM t1 a
, t2 b
WHERE a.c1 < 10001
AND b.c1 = a.c2
AND b.c2 = 1;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 7524 | |
| 1 | NESTED LOOPS | | 1 | 100 | 7524 | |
| 2 | NESTED LOOPS | | 1 | 100 | 7516 | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 10000 | 7489 | |
| 4 | SORT CLUSTER BY ROWID | | 1 | 10000 | 28 | 487K (0)|
|* 5 | INDEX RANGE SCAN | T1_X1 | 1 | 10000 | 28 | |
|* 6 | INDEX RANGE SCAN | T2_X1 | 10000 | 100 | 27 | |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 100 | 100 | 8 | |
----------------------------------------------------------------------------------------------
아래 쿼리는 t1_x1 인덱스만 읽고 t2 테이블을 조인하여 건수를 줄이고, 그 결과로 t1 테이블을 ROWID로 한번 더 조인함으로써 t1 테이블의 랜덤 액세스를 감소시켰다. 블록 I/O가 169로 감소했다.
-- 5
SELECT /*+ OPT_PARAM('_OPTIMIZER_JOIN_ELIMINATION_ENABLED', 'FALSE') ORDERED USE_NL(B C) */
c.*, b.*
FROM t1 a
, t2 b
, t1 c
WHERE a.c1 < 10001
AND b.c1 = a.c2
AND b.c2 = 1
AND c.ROWID = a.ROWID;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 169 |
| 1 | NESTED LOOPS | | 1 | 100 | 169 |
| 2 | NESTED LOOPS | | 1 | 100 | 69 |
|* 3 | INDEX RANGE SCAN | T1_X1 | 1 | 10000 | 35 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 10000 | 100 | 34 |
|* 5 | INDEX RANGE SCAN | T2_X1 | 10000 | 100 | 26 |
| 6 | TABLE ACCESS BY USER ROWID | T1 | 100 | 100 | 100 | -- !
-----------------------------------------------------------------------------------
아래 쿼리도 비슷한 기법으로 t2 테이블까지 ROWID로 조인하는 방식이다. 블록 I/O는 위 쿼리와 동일하다.
-- 6
SELECT /*+ OPT_PARAM('_OPTIMIZER_JOIN_ELIMINATION_ENABLED', 'FALSE') ORDERED USE_NL(B C D) */
c.*, d.*
FROM t1 a
, t2 b
, t1 c
, t2 d
WHERE a.c1 < 10001
AND b.c1 = a.c2
AND b.c2 = 1
AND c.ROWID = a.ROWID
AND d.ROWID = b.ROWID;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 169 |
| 1 | NESTED LOOPS | | 1 | 100 | 169 |
| 2 | NESTED LOOPS | | 1 | 100 | 161 |
| 3 | NESTED LOOPS | | 1 | 100 | 61 |
|* 4 | INDEX RANGE SCAN | T1_X1 | 1 | 10000 | 35 |
|* 5 | INDEX RANGE SCAN | T2_X1 | 10000 | 100 | 26 |
| 6 | TABLE ACCESS BY USER ROWID| T1 | 100 | 100 | 100 |
| 7 | TABLE ACCESS BY USER ROWID | T2 | 100 | 100 | 8 | -- !
--------------------------------------------------------------------------
쿼리가 복잡하고 조인 순서 제어가 어렵다면 Subquery Pushing을 사용할 수도 있다. 서브 쿼리로 입력되는 값이 연속되고 소수인 경우에만 캐싱 효과가 있다. 블록 I/O가 279개로 Buffer Pinning보다는 효과가 크지 않은 것을 확인할 수 있다.
-- 7
SELECT /*+ LEADING(A) USE_NL(B) INDEX(A) INDEX(B) */
*
FROM t1 a
, t2 b
WHERE a.c1 < 10001
AND EXISTS (SELECT /*+ NO_UNNEST PUSH_SUBQ */
1
FROM t2 x
WHERE x.c1 = a.c2
AND x.c2 = 1)
AND b.c1 = a.c2
AND b.c2 = 1;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 279 |
| 1 | NESTED LOOPS | | 1 | 100 | 279 |
| 2 | NESTED LOOPS | | 1 | 100 | 271 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 100 | 245 |
|* 4 | INDEX RANGE SCAN | T1_X1 | 1 | 100 | 145 |
|* 5 | INDEX RANGE SCAN | T2_X1 | 100 | 1 | 110 | -- !
|* 6 | INDEX RANGE SCAN | T2_X1 | 100 | 100 | 26 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 100 | 100 | 8 |
-----------------------------------------------------------------------------------
'Oracle > Tuning' 카테고리의 다른 글
| IS NULL OR 방식 옵션 조건 처리 (0) | 2019.07.20 |
|---|---|
| 인덱스 유형에 따른 Buffer Pinning (0) | 2019.05.08 |
| 바인드 변수 크기에 따른 커서 생성 (0) | 2019.04.23 |
| 테이블 전체 건수 조회 (0) | 2019.04.19 |
| TIMESTAMP 인덱스 - Right-Growing 경합 #2 (0) | 2019.03.06 |
