SORT CLUSTER BY ROWID 오퍼레이션은 예상 카디널리티에 따라 정렬 크기가 달라질 수 있으며, 이로 인해 Buffer pinning 효과가 달라질 수 있습니다. 부정확한 오브젝트 통계로 인해 SORT CLUSTER BY ROWID 오퍼레이션의 정렬 크기가 감소하여 Buffer pinning 효과가 감소하는 경우 통계 정보를 수집하거나 DYNAMIC_SAMPLING 힌트를 사용하여 성능을 개선할 수 있습니다.
테스트를 위해 아래와 같이 t1 테이블과 CF가 나쁜 t1_x1 인덱스를 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2) AS
SELECT NVL (NULLIF (MOD (ROWNUM, 100), 0), 100)
, LPAD ('X', 100, 'X')
FROM XMLTABLE ('1 to 1000000');
CREATE INDEX t1_x1 ON t1 (c1);
EXEC DBMS_STATS.GATHER_TABLE_STATS (NULL, 'T1');
아래 쿼리는 나쁜 CF로 인해 1001K 개의 블록 I/O가 발생합니다.
-- 2
SELECT /*+ INDEX(T1) */
COUNT (c2)
FROM t1
WHERE c1 > 0;
----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 1001K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1001K|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1000K| 1000K| 1001K|
|* 3 | INDEX RANGE SCAN | T1_X1 | 1000K| 1000K| 1952 |
----------------------------------------------------------------------------------
CLUSTER_BY_ROWID 힌트를 추가하여 쿼리를 수행하면 Buffer pinning 효과로 인해 블록 I/O가 17104개로 감소합니다.
-- 3-1
SELECT /*+ INDEX(T1) CLUSTER_BY_ROWID(T1) */
COUNT (c2)
FROM t1
WHERE c1 > 0;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 17104 | |
| 1 | SORT AGGREGATE | | 1 | 1 | 17104 | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1000K| 1000K| 17104 | |
| 3 | SORT CLUSTER BY ROWID | | 1000K| 1000K| 1952 | 25M (0)|
|* 4 | INDEX RANGE SCAN | T1_X1 | 1000K| 1000K| 1952 | |
---------------------------------------------------------------------------------------------
Outline Data
-------------
CLUSTER_BY_ROWID(@"SEL$1" "T1"@"SEL$1" SORT BATCH=NO)
-- 3-2: 10053 trace
Dumping Hints
=============
atom_hint=(@=000001E0C9456F58 err=0 resol=1 used=1 token=2067 org=1 lvl=3 txt=CLUSTER_BY_ROWID ("T1" SORT BATCH=NO BATCHSIZE=0) )
부정확한 통계를 가정하기 위해 t1 테이블의 통계를 삭제하고, 앞선 쿼리를 다시 수행하면 Workarea 크기가 739K로 감소하고 블록 I/O가 459K 개로 증가합니다.
-- 4-1
EXEC DBMS_STATS.DELETE_TABLE_STATS (NULL, 'T1', no_invalidate => FALSE);
-- 4-2
SELECT /*+ INDEX(T1) CLUSTER_BY_ROWID(T1) */
COUNT (c2)
FROM t1
WHERE c1 > 0;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 459K| |
| 1 | SORT AGGREGATE | | 1 | 1 | 459K| |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 62547 | 1000K| 459K| |
| 3 | SORT CLUSTER BY ROWID | | 11258 | 1000K| 1952 | 739K (0)|
|* 4 | INDEX RANGE SCAN | T1_X1 | 11258 | 1000K| 1952 | |
---------------------------------------------------------------------------------------------
OPT_ESTIMATE 힌트로 예상 카디널리티를 조정하면 Workarea 크기가 25M로 증가하고 블록 I/O가 17104 개로 감소합니다. OPT_ESTIMATE 힌트는 적절한 ROWS 값을 지정해야 하므로 활용하기 어렵습니다.
-- 5
SELECT /*+ INDEX(T1) CLUSTER_BY_ROWID(T1) OPT_ESTIMATE(INDEX_SCAN T1 T1_X1 ROWS=1000000) */
COUNT (c2)
FROM t1
WHERE c1 > 0;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 17104 | |
| 1 | SORT AGGREGATE | | 1 | 1 | 17104 | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 62547 | 1000K| 17104 | |
| 3 | SORT CLUSTER BY ROWID | | 1000K| 1000K| 1952 | 25M (0)|
|* 4 | INDEX RANGE SCAN | T1_X1 | 1000K| 1000K| 1952 | |
---------------------------------------------------------------------------------------------
OPT_ESTIMATE 힌트 대신 DYNAMIC_SAMPLING 힌트를 사용하면 앞선 쿼리와 동일한 결과를 얻을 수 있습니다.
-- 6
SELECT /*+ INDEX(T1) CLUSTER_BY_ROWID(T1) DYNAMIC_SAMPLING(2) */
COUNT (c2)
FROM t1
WHERE c1 > 0;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 17104 | |
| 1 | SORT AGGREGATE | | 1 | 1 | 17104 | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1042K| 1000K| 17104 | |
| 3 | SORT CLUSTER BY ROWID | | 1042K| 1000K| 1952 | 25M (0)|
|* 4 | INDEX RANGE SCAN | T1_X1 | 1042K| 1000K| 1952 | |
---------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
관련 링크