개요
Oracle 21c에 Hash-based Set Operation 기능이 추가되었습니다. 이 기능은 UNION, INTERSECT, MINUS 연산을 해시 방식으로 수행함으로써 성능을 개선합니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 (c1, c2) AS SELECT ROWNUM + 5000, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
UNION 연산자
아래는 19.3 버전에서 UNION 연산자를 사용한 쿼리의 실행 계획입니다. 실행 계획 1번에 SORT UNIQUE 오퍼레이션이 표시됩니다.
-- 2: 19.3
SELECT * FROM t1 UNION SELECT * FROM t2;
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15000 | 314 | | | |
| 1 | SORT UNIQUE | | 15000 | 314 | 2462K| 794K| 2188K (0)|
| 2 | UNION-ALL | | 20000 | 314 | | | |
| 3 | TABLE ACCESS FULL| T1 | 10000 | 157 | | | |
| 4 | TABLE ACCESS FULL| T2 | 10000 | 157 | | | |
----------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=2) STRDEF[22], STRDEF[100]
2 - STRDEF[22], STRDEF[100]
3 - "T1"."C1"[NUMBER,22], "T1"."C2"[VARCHAR2,100]
4 - "T2"."C1"[NUMBER,22], "T2"."C2"[VARCHAR2,100]
아래는 21.3 버전에서 UNION 연산자를 사용한 쿼리의 실행 계획입니다. 실행 계획 1번에 HASH UNIQUE 오퍼레이션이 표시됩니다. 아울러 Outline Data 항목에 USE_HASH_AGGREGATION(@"SET$1" UNIQUE) 힌트가 표시되고, Column Projection Information 항목에서 rowset이 사용된 것을 확인할 수 있습니다.
-- 3: 21.3
SELECT * FROM t1 UNION SELECT * FROM t2;
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15000 | 314 | | | |
| 1 | HASH UNIQUE | | 15000 | 314 | 2804K| 1122K| 3095K (0)|
| 2 | UNION-ALL | | 20000 | 314 | | | |
| 3 | TABLE ACCESS FULL| T1 | 10000 | 157 | | | |
| 4 | TABLE ACCESS FULL| T2 | 10000 | 157 | | | |
----------------------------------------------------------------------------------
Outline Data
-------------
USE_HASH_AGGREGATION(@"SET$1" UNIQUE)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=2; rowset=256) STRDEF[22], STRDEF[100]
2 - (rowset=256) STRDEF[22], STRDEF[100]
3 - (rowset=256) "T1"."C1"[NUMBER,22], "T1"."C2"[VARCHAR2,100]
4 - (rowset=256) "T2"."C1"[NUMBER,22], "T2"."C2"[VARCHAR2,100]
쿼리에 NO_USE_HASH_AGGREGATION(@SET$1) 힌트를 사용하면 19.3 버전과 동일한 실행 계획이 생성됩니다. 다만 집합 연산이 수행된 쿼리 블록을 지정해야 힌트가 동작합니다.
-- 4
SELECT /*+ NO_USE_HASH_AGGREGATION(@SET$1) */ * FROM t1 UNION SELECT * FROM t2;
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT UNIQUE | |
| 2 | UNION-ALL | |
| 3 | TABLE ACCESS FULL| T1 |
| 4 | TABLE ACCESS FULL| T2 |
------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SET$1
- NO_USE_HASH_AGGREGATION(@SET$1 UNIQUE)
INTERSECT 연산자
아래는 19.3 버전에서 INTERSECT 연산자를 사용한 쿼리의 실행 계획입니다. t1, t2 테이블에 대해 각각 SORT UNIQUE 오퍼레이션을 수행한 후 INTERSECTION 연산을 수행합니다.
-- 5: 19.3
SELECT * FROM t1 INTERSECT SELECT * FROM t2;
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 314 | | | |
| 1 | INTERSECTION | | 5000 | 314 | | | |
| 2 | SORT UNIQUE | | 10000 | 157 | 1612K| 623K| 1432K (0)|
| 3 | TABLE ACCESS FULL| T1 | 10000 | 157 | | | |
| 4 | SORT UNIQUE | | 10000 | 157 | 1612K| 623K| 1432K (0)|
| 5 | TABLE ACCESS FULL| T2 | 10000 | 157 | | | |
----------------------------------------------------------------------------------
아래는 23.1 버전에서 INTERSECT 연산자를 사용한 쿼리의 실행 계획입니다. 실행 계획 1번에 INTERSECTION HASH 오퍼레이션이 표시됩니다. 아울러 Outline Data 항목에 HASHSET_BUILD(@"SEL$1") 힌트가 표시되는 것을 확인할 수 있습니다.
-- 6: 21.3
SELECT * FROM t1 INTERSECT SELECT * FROM t2;
---------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | 0 | | | |
| 1 | INTERSECTION HASH | | 5000 | 314 | 2100K| 1120K| 2863K (0)|
| 2 | TABLE ACCESS FULL| T1 | 10000 | 157 | | | |
| 3 | TABLE ACCESS FULL| T2 | 10000 | 157 | | | |
---------------------------------------------------------------------------------
Outline Data
-------------
HASHSET_BUILD(@"SEL$1")
HASHSET_BUILD 힌트는 21.1 버전에 추가되었으며 INVERSE 힌트가 존재하지 않습니다. INTERSECT 연산자도 NO_USE_HASH_AGGREGATION(@SET$1) 힌트를 사용하면 19.3 버전과 동일한 실행 계획이 생성됩니다.
-- 7
SELECT name, inverse, version FROM v$sql_hint WHERE name = 'HASHSET_BUILD';
NAME INVERSE VERSION
------------- ------- -------
HASHSET_BUILD 21.1.0
1개의 행이 선택되었습니다.
MINUS 연산자
아래는 19.3 버전에서 MINUS 연산자를 사용한 쿼리의 실행 계획입니다. INTERSECT 연산자처럼 t1, t2 테이블에 대해 각각 SORT UNIQUE 오퍼레이션을 수행한 후 MINUS 연산을 수행합니다.
-- 8: 19.3
SELECT * FROM t1 MINUS SELECT * FROM t2;
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 314 | | | |
| 1 | MINUS | | 5000 | 314 | | | |
| 2 | SORT UNIQUE | | 10000 | 157 | 1612K| 623K| 1432K (0)|
| 3 | TABLE ACCESS FULL| T1 | 10000 | 157 | | | |
| 4 | SORT UNIQUE | | 10000 | 157 | 1612K| 623K| 1432K (0)|
| 5 | TABLE ACCESS FULL| T2 | 10000 | 157 | | | |
----------------------------------------------------------------------------------
아래는 23.1 버전에서 MINUS 연산자를 사용한 쿼리의 실행 계획입니다. 실행 계획 1번에 MINUS HASH 오퍼레이션이 표시됩니다.
-- 9: 21.3
SELECT * FROM t1 MINUS SELECT * FROM t2;
---------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | 0 | | | |
| 1 | MINUS HASH | | 5000 | 314 | 2107K| 1124K| 2863K (0)|
| 2 | TABLE ACCESS FULL| T1 | 10000 | 157 | | | |
| 3 | TABLE ACCESS FULL| T2 | 10000 | 157 | | | |
---------------------------------------------------------------------------------
_sqlexec_hash_based_set_operation_enabled 파라미터
Hash-based Set Operation 기능은 _sqlexec_hash_based_set_operation_enabled 파라미터와 관련이 있습니다.
-- 10
NAME VALUE DEFAULT_VALUE DESCRIPTION
----------------------------------------- ----- ------------- ---------------------------------------
_sqlexec_hash_based_set_operation_enabled TRUE TRUE enable/disable hash based set operation
_sqlexec_hash_based_set_operation_enabled 파라미터를 FALSE로 설정하고 UNION, INTERSECT, MINUS 연산을 수행하면 UNION 연산만 해시 방식으로 수행된 것을 확인할 수 있습니다. UNION 연산은 힌트에 의해 파라미터가 오버라이드되는 것으로 보입니다. (2024-10-06 업데이트 참고)
-- 11-1
ALTER SESSION SET "_sqlexec_hash_based_set_operation_enabled" = FALSE;
-- 11-2
SELECT * FROM t1 UNION SELECT * FROM t2;
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH UNIQUE | |
| 2 | UNION-ALL | |
| 3 | TABLE ACCESS FULL| T1 |
| 4 | TABLE ACCESS FULL| T2 |
------------------------------------
Outline Data
-------------
OPT_PARAM('_sqlexec_hash_based_set_operation_enabled' 'false')
Outline Data
-------------
USE_HASH_AGGREGATION(@"SET$1" UNIQUE)
-- 11-3
SELECT * FROM t1 INTERSECT SELECT * FROM t2;
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INTERSECTION | |
| 2 | SORT UNIQUE | |
| 3 | TABLE ACCESS FULL| T1 |
| 4 | SORT UNIQUE | |
| 5 | TABLE ACCESS FULL| T2 |
------------------------------------
-- 11-4
SELECT * FROM t1 MINUS SELECT * FROM t2;
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MINUS | |
| 2 | SORT UNIQUE | |
| 3 | TABLE ACCESS FULL| T1 |
| 4 | SORT UNIQUE | |
| 5 | TABLE ACCESS FULL| T2 |
------------------------------------
[2024-10-06]
UNION 연산자에 대한 HASH UNIQUE 오퍼레이션은 Hash-based Set Operation 개선과 무관하며 22387320 Fix Control에 의한 개선으로 보입니다.
-- 12-1
SELECT bugno, value, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 22387320;
BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
-------- ----- --------------------------------------------------- ------------------------
22387320 1 use HASH UNIQUE with UNION operator when applicable 21.1.0
1 row selected.
-- 12-2
ALTER SESSION SET "_fix_control" = '22387320:0';
-- 12-3
SELECT * FROM t1 UNION SELECT * FROM t2;
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT UNIQUE | |
| 2 | UNION-ALL | |
| 3 | TABLE ACCESS FULL| T1 |
| 4 | TABLE ACCESS FULL| T2 |
------------------------------------