예전에 작성한 Right Growing Index 경합 해소 글에서 인스턴스 ID의 가상 칼럼을 서브 파티션 키로 사용해 GC 경합을 해소하는 방법을 소개했습니다. 이 방법은 아래와 같이 이미 서브 파티션을 생성한 경우에는 사용할 수 없습니다. 이번 글에서 다중 칼럼 리스트 파티션을 사용해 GC 경합을 해소하는 방법을 살펴보겠습니다.
-- 1
CREATE TABLE t1 (
dt DATE
, id NUMBER
, vl NUMBER
)
PARTITION BY RANGE (dt)
SUBPARTITION BY HASH (id) SUBPARTITION TEMPLATE (
SUBPARTITION sp_1
, SUBPARTITION sp_2
) (
PARTITION p_20240101 VALUES LESS THAN (DATE '2050-01-02')
, PARTITION p_20240102 VALUES LESS THAN (DATE '2050-01-03')
, PARTITION p_maxvalue VALUES LESS THAN (MAXVALUE)
);
테스트 환경은 19.21입니다.
-- 2
SELECT version_full FROM product_component_version;
VERSION_FULL
------------
19.21.0.0.0
1 row selected.
참고로 이 방법을 사용하기 위해서는 30887435 버그가 Fix되어야 합니다. 30887435 버그에 대한 내용은 하단 링크를 참고하세요.
-- 3-1
SELECT bugno, value, description, is_default
FROM v$system_fix_control
WHERE bugno = 30887435;
BUGNO VALUE DESCRIPTION IS_DEFAULT
-------- ----- ------------------------------------------ ----------
30887435 0 control scanning to unnecessary partitions 1
1 row selected.
-- 3-2
ALTER SESSION SET "_fix_control" = '30887435:1';
테스트를 위해 아래와 같이 테이블을 생성하겠습니다. 일 파티션을 위해 dt 칼럼을 일자로 TRUNC한 dd 가상 칼럼을 추가하고, CG 경합을 해소하기 위해 inst_id 가상 칼럼을 추가합니다. RANGE 파티션을 dd 칼럼과 inst_id 칼럼의 LIST 파티션으로 변경합니다.
-- 4
ALTER SESSION SET "_partition_large_extents" = FALSE;
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
dt DATE
, id NUMBER
, vl NUMBER
, dd DATE INVISIBLE AS (TRUNC (dt, 'DD'))
, inst_id NUMBER INVISIBLE AS (SYS_CONTEXT ('USERENV', 'INSTANCE'))
)
PARTITION BY LIST (dd, inst_id)
SUBPARTITION BY HASH (id) SUBPARTITION TEMPLATE (
SUBPARTITION sp_1
, SUBPARTITION sp_2
) (
PARTITION p_20240101_1 VALUES ((DATE '2050-01-01', 1))
, PARTITION p_20240101_2 VALUES ((DATE '2050-01-01', 2))
, PARTITION p_20240102_1 VALUES ((DATE '2050-01-02', 1))
, PARTITION p_20240102_2 VALUES ((DATE '2050-01-02', 2))
, PARTITION p_default VALUES (DEFAULT)
);
아래와 같이 1번, 2번 인스턴스에서 INSERT 문을 수행합니다.
-- 5-1: instance 1
INSERT
INTO t1 (dt, id)
SELECT DATE '2050-01-01' + NUMTODSINTERVAL (ROWNUM - 1, 'HOUR') AS dt
, ROWNUM AS id
FROM XMLTABLE ('1 to 48');
COMMIT;
-- 5-2: instance 2
INSERT
INTO t1 (dt, id)
SELECT DATE '2050-01-01' + NUMTODSINTERVAL (ROWNUM - 1, 'HOUR') AS dt
, ROWNUM AS id
FROM XMLTABLE ('1 to 48');
COMMIT;
아래 쿼리의 결과에서 파티션 별로 24개의 로우가 삽입된 것을 확인할 수 있습니다.
-- 6
SELECT TBL$OR$IDX$PART$NUM (TUNA.T1, 0, 1, 0, ROWID) AS pp
, dd
, inst_id
, COUNT (*) AS cnt
FROM t1
GROUP BY TBL$OR$IDX$PART$NUM (TUNA.T1, 0, 1, 0, ROWID)
, dd
, inst_id;
PP DD INST_ID CNT
-- ------------------- ------- ---
1 2050-01-01 00:00:00 1 24
2 2050-01-01 00:00:00 1 24
3 2050-01-02 00:00:00 1 24
4 2050-01-02 00:00:00 1 24
4 rows selected.
아래 쿼리의 실행 계획에서 파티션 Pruning이 동작하는 것을 확인할 수 있습니다. 옵티마이저에 의해 파티션 키인 dd 칼럼이 아닌 dt 칼럼을 사용해도 파티션 Pruning이 동작하지만 dt 칼럼이 DATE 타입이 아닌 TIMESTAMP 타입인 경우에는 파티션 Pruning이 동작하지 않습니다. 아울러 앞서 언급한 30887435 버그가 Fix되지 않아도 파티션 Pruning이 동작하지 않습니다.
-- 7-1
SELECT * FROM t1;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 96 | 177 |
| 1 | PARTITION LIST ALL | | 1 | 1 | 5 | 96 | 177 |
| 2 | PARTITION HASH ALL | | 5 | 1 | 2 | 96 | 177 |
| 3 | TABLE ACCESS STORAGE FULL| T1 | 10 | 1 | 10 | 96 | 177 |
----------------------------------------------------------------------------------------
-- 7-2
SELECT * FROM t1 WHERE dt = DATE '2050-01-01';
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 | 89 |
| 1 | PARTITION LIST ITERATOR | | 1 | KEY | KEY | 2 | 89 |
| 2 | PARTITION HASH ALL | | 3 | 1 | 2 | 2 | 89 |
|* 3 | TABLE ACCESS STORAGE FULL| T1 | 6 | | | 2 | 89 |
----------------------------------------------------------------------------------------
-- 7-3
SELECT * FROM t1 WHERE id = 1;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 | 89 |
| 1 | PARTITION LIST ALL | | 1 | 1 | 5 | 2 | 89 |
| 2 | PARTITION HASH SINGLE | | 5 | 2 | 2 | 2 | 89 |
|* 3 | TABLE ACCESS STORAGE FULL| T1 | 5 | | | 2 | 89 |
----------------------------------------------------------------------------------------
-- 7-4
SELECT * FROM t1 WHERE dt = DATE '2050-01-01' AND id = 1;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 | 45 |
| 1 | PARTITION LIST ITERATOR | | 1 | KEY | KEY | 2 | 45 |
| 2 | PARTITION HASH SINGLE | | 3 | 2 | 2 | 2 | 45 |
|* 3 | TABLE ACCESS STORAGE FULL| T1 | 3 | | | 2 | 45 |
----------------------------------------------------------------------------------------
인덱스 파티션 Pruning을 테스트하기 위해 아래와 같이 로컬 인덱스를 생성하겠습니다.
-- 8
CREATE INDEX t1_x1 ON t1 (dt, id) LOCAL;
아래 쿼리의 실행 계획에서 인덱스 파티션 Pruning이 동작하는 것을 확인할 수 있습니다.
-- 9-1
SELECT * FROM t1 WHERE dt = DATE '2050-01-01';
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 | 7 |
| 1 | PARTITION LIST ITERATOR | | 1 | KEY | KEY | 2 | 7 |
| 2 | PARTITION HASH ALL | | 3 | 1 | 2 | 2 | 7 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 | 6 | | | 2 | 7 |
|* 4 | INDEX RANGE SCAN | T1_X1 | 4 | | | 2 | 5 |
---------------------------------------------------------------------------------------------------------
-- 9-2
SELECT * FROM t1 WHERE dt = DATE '2050-01-01' AND id = 1;
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 | 5 |
| 1 | PARTITION LIST ITERATOR | | 1 | KEY | KEY | 2 | 5 |
| 2 | PARTITION HASH SINGLE | | 3 | 2 | 2 | 2 | 5 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 | 3 | | | 2 | 5 |
|* 4 | INDEX RANGE SCAN | T1_X1 | 2 | | | 2 | 3 |
---------------------------------------------------------------------------------------------------------
아래 구문으로 파티션을 관리할 수 있습니다. 인스턴스를 확장하는 경우에는 주석처럼 파티션을 추가해야 합니다.
-- 10-1
ALTER TABLE t1 SPLIT PARTITION p_default INTO (
PARTITION p_20240103_1 VALUES ((DATE '2050-01-03', 1))
, PARTITION p_20240103_2 VALUES ((DATE '2050-01-03', 2))
--, PARTITION p_20240103_3 VALUES ((DATE '2050-01-03', 3))
--, PARTITION p_20240103_4 VALUES ((DATE '2050-01-03', 4))
, PARTITION p_default);
-- 10-2
ALTER TABLE t1 MERGE PARTITIONS p_20240101_1, p_20240101_2
INTO PARTITION p_20240101 UPDATE INDEXES;
관련링크