그룹핑 결과로 NL 조인을 수행하는 경우 그룹핑 알고리즘에 따라 Buffer Pinning에 차이가 발생할 수 있습니다.
참고로 그룹핑 알고리즘은 _gby_hash_aggregation_enabled 파라미터와 관련이 있습니다.
NAME VALUE DEFAULT_VALUE DESCRIPTION
----------------------------- ----- ------------- -------------------------------------------------
_gby_hash_aggregation_enabled TRUE TRUE enable group-by and aggregation using hash scheme
테스트를 위해 아래와 같이 테이블과 인덱스를 생성하겠습니다. t2_x1 인덱스는 클러스터링 팩터가 매우 좋습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS SELECT CEIL (ROWNUM / 10) AS c1 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT ROWNUM AS c1, LPAD ('X', 1000, 'X') AS c2 FROM XMLTABLE ('1 to 1000');
CREATE INDEX t2_x1 ON t2 (c1);
아래 쿼리는 t1 테이블을 그룹핑한 결과로 t2 테이블을 NL 조인합니다. _gby_hash_aggregation_enabled 파라미터를 FALSE로 설정하여 소트 그룹핑 알고리즘(SORT GROUP BY)이 사용되었습니다. Buffer Pinning에 의해 6번 INDEX RANGE SCAN 오퍼레이션에서 979개, 7번 TABLE ACCESS BY INDEX ROWID 오퍼레이션에서 886개의 블록 I/O가 감소니다.
-- 2
ALTER SESSION SET "_gby_hash_aggregation_enabled" = FALSE;
SELECT /*+ LEADING(A) USE_NL(A B) */
*
FROM (SELECT c1 FROM t1 GROUP BY c1) a
, t2 b
WHERE b.c1 = a.c1;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 | 862 |
| 1 | NESTED LOOPS | | 1 | 1000 | 862 |
| 2 | NESTED LOOPS | | 1 | 1000 | 159 |
| 3 | VIEW | | 1 | 1000 | 19 |
| 4 | HASH GROUP BY | | 1 | 1000 | 19 | -- !
| 5 | TABLE ACCESS FULL | T1 | 1 | 10000 | 19 |
|* 6 | INDEX RANGE SCAN | T2_X1 | 1000 | 1000 | 140 | -- 860 = 1000 - 140
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1000 | 1000 | 703 | -- 297 = 1000 - 703
--------------------------------------------------------------------------
아래 쿼리는 _gby_hash_aggregation_enabled 파라미터를 TRUE로 설정하여 집계에 해시 그룹핑 알고리즘(HASH GROUP BY)이 사용되었습니다. Buffer Pinning에 의해 6번 INDEX RANGE SCAN 오퍼레이션에서 860개, 7번 TABLE ACCESS BY INDEX ROWID 오퍼레이션에서 297개의 블록 I/O가 감소합니다. 소트 알고리즘은 집계 결과를 정렬하여 반환하지만 해시 알고리즘은 집계 결과를 무작위로 반환합니다. 이로 인해 Buffer Pinning에 차이가 발생한 것입니다.
-- 3
ALTER SESSION SET "_gby_hash_aggregation_enabled" = TRUE;
SELECT /*+ LEADING(A) USE_NL(A B) */
*
FROM (SELECT c1 FROM t1 GROUP BY c1) a
, t2 b
WHERE b.c1 = a.c1;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 | 862 |
| 1 | NESTED LOOPS | | 1 | 1000 | 862 |
| 2 | NESTED LOOPS | | 1 | 1000 | 159 |
| 3 | VIEW | | 1 | 1000 | 19 |
| 4 | HASH GROUP BY | | 1 | 1000 | 19 | -- !
| 5 | TABLE ACCESS FULL | T1 | 1 | 10000 | 19 |
|* 6 | INDEX RANGE SCAN | T2_X1 | 1000 | 1000 | 140 | -- 860 = 1000 - 140
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1000 | 1000 | 703 | -- 297 = 1000 - 703
--------------------------------------------------------------------------
_gby_hash_aggregation_enabled 파라미터가 TRUE로 설정되어 있더라도 ORDER BY 절이나 NO_USE_HASH_AGGREGATION 힌트를 사용하여 소트 그룹핑 알고리즘을 사용할 수 있습니다.
-- 4-1
SELECT /*+ LEADING(A) USE_NL(A B) */
*
FROM (SELECT c1 FROM t1 GROUP BY c1 ORDER BY c1) a
, t2 b
WHERE b.c1 = a.c1;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 | 187 |
| 1 | NESTED LOOPS | | 1 | 1000 | 187 |
| 2 | NESTED LOOPS | | 1 | 1000 | 43 |
| 3 | VIEW | | 1 | 1000 | 19 |
| 4 | SORT GROUP BY | | 1 | 1000 | 19 |
| 5 | TABLE ACCESS FULL | T1 | 1 | 10000 | 19 |
|* 6 | INDEX RANGE SCAN | T2_X1 | 1000 | 1000 | 24 |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1000 | 1000 | 144 |
--------------------------------------------------------------------------
-- 4-2
SELECT /*+ LEADING(A) USE_NL(A B) */
*
FROM (SELECT /*+ NO_USE_HASH_AGGREGATION */ c1 FROM t1 GROUP BY c1) a
, t2 b
WHERE b.c1 = a.c1;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 | 187 |
| 1 | NESTED LOOPS | | 1 | 1000 | 187 |
| 2 | NESTED LOOPS | | 1 | 1000 | 43 |
| 3 | VIEW | | 1 | 1000 | 19 |
| 4 | SORT GROUP BY | | 1 | 1000 | 19 |
| 5 | TABLE ACCESS FULL | T1 | 1 | 10000 | 19 |
|* 6 | INDEX RANGE SCAN | T2_X1 | 1000 | 1000 | 24 |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1000 | 1000 | 144 |
--------------------------------------------------------------------------
그룹핑 결과가 작을 경우 인라인 뷰를 통해 해시 그룹핑을 수행한 후 별도로 정렬을 수행하여 Buffer Pinning 효과를 높일 수 있습니다.
-- 5
SELECT /*+ LEADING(A) USE_NL(A B) */
*
FROM (SELECT /*+ NO_MERGE */
c1
FROM (SELECT /*+ NO_MERGE */ c1 FROM t1 GROUP BY c1)
ORDER BY c1) a
, t2 b
WHERE b.c1 = a.c1;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 | 187 | |
| 1 | NESTED LOOPS | | 1 | 1000 | 187 | |
| 2 | NESTED LOOPS | | 1 | 1000 | 43 | |
| 3 | VIEW | | 1 | 1000 | 19 | |
| 4 | SORT ORDER BY | | 1 | 1000 | 19 | 110K (0)|
| 5 | VIEW | | 1 | 1000 | 19 | |
| 6 | HASH GROUP BY | | 1 | 1000 | 19 | 1341K (0)|
| 7 | TABLE ACCESS FULL | T1 | 1 | 10000 | 19 | |
|* 8 | INDEX RANGE SCAN | T2_X1 | 1000 | 1000 | 24 | |
| 9 | TABLE ACCESS BY INDEX ROWID| T2 | 1000 | 1000 | 144 | |
-------------------------------------------------------------------------------------
Buffer Pinning으로 인한 블록 I/O 감소와 해시 그룹핑 알고리즘을 통한 성능 향상은 trade-off 관계를 가집니다. 기본적으로 해시 그룹핑 알고리즘을 사용하고 Buffer Pinning 효과를 높이기 위해 선택적으로 소트 그룹핑 알고리즘을 사용하는 편이 바람직합니다.