Oracle 21c에 Hash-based Distinct Aggregation 기능이 추가되었습니다. 이 기능은 COUNT (DISTINCT) 표현식을 2번 이상 사용한 쿼리에 대해 해시 방식의 집계를 사용으로써 성능을 개선합니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT CEIL (ROWNUM / 1000), CEIL (ROWNUM / 1000) FROM XMLTABLE ('1 to 10000000');
아래는 19.3 버전에서 COUNT (DISTINCT) 표현식을 2번 사용한 쿼리의 실행 계획입니다. 수행에 4.85초가 소요되고, 실행 계획 2번에 SORT GROUP BY 오퍼레이션이 표시됩니다.
-- 2: 19.3
SELECT COUNT (DISTINCT c1), COUNT (DISTINCT c2) FROM t1;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |00:00:04.85 | 17941 | 17931 | | | |
| 1 | SORT GROUP BY | | 1 |00:00:04.85 | 17941 | 17931 | 1045K| 1045K| 928K (0)| -- !
| 2 | TABLE ACCESS FULL| T1 | 10M|00:00:00.96 | 17941 | 17931 | | | |
-------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(DISTINCT "C2")[22], COUNT(DISTINCT "C1")[22]
2 - "C1"[NUMBER,22], "C2"[NUMBER,22]
아래는 21.3 버전에서 COUNT (DISTINCT) 표현식을 2번 사용한 쿼리의 실행 계획입니다. 수행에 1.13초가 소요되고, 실행 계획 2번에 HASH GROUP BY 오퍼레이션이 표시됩니다. 아울러 Outline Data 항목에 USE_HASH_AGGREGATION(@"SEL$1" GROUP_BY) 힌트가 표시되고, Column Projection Information 항목에서 rowset이 사용된 것을 확인할 수 있습니다.
-- 3: 21.3
SELECT COUNT (DISTINCT c1), COUNT (DISTINCT c2) FROM t1;
----------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |00:00:01.13 | 17935 | 17931 |
| 1 | HASH GROUP BY | | 1 |00:00:01.13 | 17935 | 17931 | -- !
| 2 | TABLE ACCESS FULL| T1 | 10M|00:00:00.17 | 17935 | 17931 |
----------------------------------------------------------------------------
Outline Data
-------------
USE_HASH_AGGREGATION(@"SEL$1" GROUP_BY)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(DISTINCT "C2")[22], COUNT(DISTINCT "C1")[22]
2 - (rowset=256) "C1"[NUMBER,22], "C2"[NUMBER,22]
쿼리에 NO_USE_HASH_AGGREGATION 힌트를 사용하면 19.3 버전과 동일한 실행 계획이 생성됩니다. 다만 rowset이 사용되어 수행에 3.36초가 소요됩니다.
-- 4
SELECT /*+ NO_USE_HASH_AGGREGATION */ COUNT (DISTINCT c1), COUNT (DISTINCT c2) FROM t1;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |00:00:03.36 | 17935 | 17931 | | | |
| 1 | SORT GROUP BY | | 1 |00:00:03.36 | 17935 | 17931 | 1045K| 1045K| 928K (0)| -- !
| 2 | TABLE ACCESS FULL| T1 | 10M|00:00:00.17 | 17935 | 17931 | | | |
-------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(DISTINCT "C2")[22], COUNT(DISTINCT "C1")[22]
2 - (rowset=256) "C1"[NUMBER,22], "C2"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1
- NO_USE_HASH_AGGREGATION
참고로 이 기능은 _sqlexec_hash_based_distagg_ser_civ_enabled 파라미터와 관련이 있습니다.
-- 5
NAME VALUE DEFAULT_VALUE DESCRIPTION
------------------------------------------- ----- ------------- ------------------------------------------------------------
_sqlexec_hash_based_distagg_ser_civ_enabled TRUE TRUE enable hash based distinct aggregation in serial/CIV queries
_sqlexec_hash_based_distagg_ser_civ_enabled 파라미터를 FALSE로 설정하면 앞선 쿼리와 동일한 실행 계획이 생성됩니다.
-- 6
SELECT /*+ OPT_PARAM('_sqlexec_hash_based_distagg_ser_civ_enabled' 'false') */ COUNT (DISTINCT c1), COUNT (DISTINCT c2) FROM t1;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |00:00:03.39 | 17935 | 17931 | | | |
| 1 | SORT GROUP BY | | 1 |00:00:03.39 | 17935 | 17931 | 1045K| 1045K| 928K (0)| -- !
| 2 | TABLE ACCESS FULL| T1 | 10M|00:00:00.17 | 17935 | 17931 | | | |
-------------------------------------------------------------------------------------------------------
Outline Data
-------------
OPT_PARAM('_sqlexec_hash_based_distagg_ser_civ_enabled' 'false')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(DISTINCT "C2")[22], COUNT(DISTINCT "C1")[22]
2 - (rowset=256) "C1"[NUMBER,22], "C2"[NUMBER,22]