Right Growing Index(이후 RGI)는 enq: TX - index contention 대기가 발생할 수 있으며, RAC인 경우 gc buffer busy 대기가 함께 발생할 수 있습니다.
아래 t1_x1 인덱스는 인덱스 키 칼럼이 TIMESTAMP 값으로 전형적인 RGI입니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
ts TIMESTAMP
, val VARCHAR2(4000)
)
PARTITION BY RANGE (ts) (
PARTITION p205001 VALUES LESS THAN (TIMESTAMP '2050-02-01 00:00:00')
, PARTITION p205002 VALUES LESS THAN (TIMESTAMP '2050-03-01 00:00:00')
, PARTITION p205003 VALUES LESS THAN (TIMESTAMP '2050-04-01 00:00:00')
);
CREATE INDEX t1_x1 ON t1 (ts) LOCAL;
경합을 해소하기 위해 Reverse Key 인덱스를 생성할 수 있지만 Index Range Scan이 불가능한 문제가 있습니다.
-- 2-1
DROP INDEX t1_x1;
CREATE INDEX t1_x1 ON t1 (ts) REVERSE LOCAL;
-- 2-2
SELECT /*+ INDEX(T1) */
*
FROM t1
WHERE ts BETWEEN TIMESTAMP '2050-03-01 00:00:00' AND TIMESTAMP '2050-03-02 00:01:00';
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE SINGLE | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T1 |
|* 3 | INDEX FULL SCAN | T1_X1 |
----------------------------------------------------
가장 일반적인 방안은 해시 서브파티션을 구성하는 것입니다. 이 방안은 enq: TX - index contention 대기를 완화할 수 있지만 gc buffer busy 대기를 제어할 수 없는 한계가 있습니다.
-- 3-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
ts TIMESTAMP
, val VARCHAR2(4000)
)
PARTITION BY RANGE (ts)
SUBPARTITION BY HASH (ts)
SUBPARTITION TEMPLATE (
SUBPARTITION sp1
, SUBPARTITION sp2
, SUBPARTITION sp3
, SUBPARTITION sp4
) (
PARTITION p205001 VALUES LESS THAN (TIMESTAMP '2050-02-01 00:00:00')
, PARTITION p205002 VALUES LESS THAN (TIMESTAMP '2050-03-01 00:00:00')
, PARTITION p205003 VALUES LESS THAN (TIMESTAMP '2050-04-01 00:00:00')
);
CREATE INDEX t1_x1 ON t1 (ts) LOCAL;
-- 3-2
SELECT /*+ INDEX(T1) */
*
FROM t1
WHERE ts BETWEEN TIMESTAMP '2050-03-01 00:00:00' AND TIMESTAMP '2050-03-02 00:01:00';
-----------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE SINGLE | | 3 | 3 |
| 2 | PARTITION HASH ALL | | 1 | 4 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 | 9 | 12 |
|* 4 | INDEX RANGE SCAN | T1_X1 | 9 | 12 |
-----------------------------------------------------------------------------
아래와 같이 인스턴스 ID로 리스트 서브파티션을 구성하면 gc buffer busy 대기까지 해소할 수 있습니다. 하지만 과도한 입력이 발생하면 인스턴스 내에서 enq: TX - index contention 대기가 발생할 수 있습니다.
-- 4-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
ts TIMESTAMP
, val VARCHAR2(4000)
, inst_id NUMBER INVISIBLE AS (SYS_CONTEXT ('USERENV', 'INSTANCE'))
)
PARTITION BY RANGE (ts)
SUBPARTITION BY LIST (inst_id)
SUBPARTITION TEMPLATE (
SUBPARTITION sp1 VALUES (1)
, SUBPARTITION sp2 VALUES (2)
, SUBPARTITION spd VALUES (DEFAULT)
) (
PARTITION p205001 VALUES LESS THAN (TIMESTAMP '2050-02-01 00:00:00')
, PARTITION p205002 VALUES LESS THAN (TIMESTAMP '2050-03-01 00:00:00')
, PARTITION p205003 VALUES LESS THAN (TIMESTAMP '2050-04-01 00:00:00')
);
CREATE INDEX t1_x1 ON t1 (ts) LOCAL;
-- 4-2
SELECT /*+ INDEX(T1) */
*
FROM t1
WHERE ts BETWEEN TIMESTAMP '2050-03-01 00:00:00' AND TIMESTAMP '2050-03-02 00:01:00';
-----------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE SINGLE | | 3 | 3 |
| 2 | PARTITION LIST ALL | | 1 | 3 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 | 7 | 9 |
|* 4 | INDEX RANGE SCAN | T1_X1 | 7 | 9 |
-----------------------------------------------------------------------------
아래와 같이 인스턴스 ID와 세션 ID의 MOD 값으로 리스트 서브파티션을 구성하면 enq: TX - index contention 대기와 gc buffer busy 대기를 모두 최소화할 수 있습니다.
-- 5-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
ts TIMESTAMP
, val VARCHAR2(4000)
, inst_id NUMBER INVISIBLE AS (SYS_CONTEXT ('USERENV', 'INSTANCE'))
, sid NUMBER INVISIBLE AS (MOD (SYS_CONTEXT ('USERENV', 'SID'), 2))
)
PARTITION BY RANGE (ts)
SUBPARTITION BY LIST (inst_id, sid)
SUBPARTITION TEMPLATE (
SUBPARTITION sp10 VALUES (1, 0)
, SUBPARTITION sp11 VALUES (1, 1)
, SUBPARTITION sp20 VALUES (2, 0)
, SUBPARTITION sp21 VALUES (2, 1)
, SUBPARTITION spd VALUES (DEFAULT)
) (
PARTITION p205001 VALUES LESS THAN (TIMESTAMP '2050-02-01 00:00:00')
, PARTITION p205002 VALUES LESS THAN (TIMESTAMP '2050-03-01 00:00:00')
, PARTITION p205003 VALUES LESS THAN (TIMESTAMP '2050-04-01 00:00:00')
);
CREATE INDEX t1_x1 ON t1 (ts) LOCAL;
-- 5-2
SELECT /*+ INDEX(T1) */
*
FROM t1
WHERE ts BETWEEN TIMESTAMP '2050-03-01 00:00:00' AND TIMESTAMP '2050-03-02 00:01:00';
-----------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE SINGLE | | 3 | 3 |
| 2 | PARTITION LIST ALL | | 1 | 5 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 | 11 | 15 |
|* 4 | INDEX RANGE SCAN | T1_X1 | 11 | 15 |
-----------------------------------------------------------------------------
인스턴스가 추가되면 단순히 서브파티션 템플릿을 변경하면 되지만 sid 가상 칼럼을 변경할 수 없으므로 향후 부하 증가를 예측하여 충분한 서브파티션을 구성할 필요가 있습니다. 만약 sid 가상 칼럼을 변경하여 서브파티션을 추가해야 한다면 신규 테이블을 생성하여 기존 테이블을 대체하고 기존 데이터를 서브파티션 Exchange를 통해 이행해야 합니다.
-- 6
ALTER TABLE t1 MODIFY sid NUMBER AS (MOD (SYS_CONTEXT ('USERENV', 'SID'), 4));
ORA-54020: 하위 분할 열이므로 가상 열 표현식을 변경할 수 없습니다.
관련 링크