Asynchronous Global Index Maintenance

개요

Oracle 12c에 Asynchronous Global Index Maintenance 기능이 추가되었습니다. 이 기능은 글로벌 인덱스의 관리 작업을 비동기로 수행함으로써 글로벌 인덱스를 가진 테이블에 대한 DROP PARTITION과 TRUNCATE PARTITION의 성능을 개선합니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
 
CREATE TABLE t1 (c1, c2)
PARTITION BY RANGE (c1) (
    PARTITION p1 VALUES LESS THAN (5000001)
  , PARTITION p2 VALUES LESS THAN (MAXVALUE)
)
AS
SELECT ROWNUM, 'X' FROM XMLTABLE ('1 to 10000000');
 
CREATE INDEX t1_x1 ON t1 (c1);

 

비교를 위해 *_INDEXES 뷰와 ANALYZE INDEX 문의 수행 결과를 조회하겠습니다. *_INDEXES 뷰의 orphaned_entries 값은 NO, INDEX_STATS 테이블의 del_lf_rows 값은 0입니다.

-- 2-1
SELECT index_name, status, orphaned_entries
  FROM user_indexes
 WHERE index_name = 'T1_X1';
 
INDEX_NAME STATUS ORPHANED_ENTRIES
---------- ------ ----------------
T1_X1      VALID  NO
 
1개의 행이 선택되었습니다.
 
-- 2-2
ANALYZE INDEX t1_x1 VALIDATE STRUCTURE;
 
SELECT name, blocks, lf_rows, lf_blks, br_rows, br_blks, del_lf_rows, btree_space, used_space
  FROM index_stats;
 
NAME  BLOCKS  LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE
----- ------ -------- ------- ------- ------- ----------- ----------- ----------
T1_X1  29696 10000000   29100   29099      48           0   233185536  209263810
 
1개의 행이 선택되었습니다.

 

기본 동작

ALTER TABLE TRUNCATE PARTITION 문에 UPDATE GLOBAL INDEXES 절을 사용하면 Asynchronous Global Index Maintenance 기능이 동작합니다.

ALTER TABLE [schema.] table TRUNCATE PARTITION partition UPDATE GLOBAL INDEXES;

 

아래와 같이 p1 파티션을 TRUNCATE하겠습니다.

-- 3
ALTER TABLE t1 TRUNCATE PARTITION p1 UPDATE GLOBAL INDEXES;
 
경   과: 00:00:00.03

 

TRUNCATE 후 *_INDEXES 뷰의 orphaned_entries 값은 YES, INDEX_STATS 테이블의 del_lf_rows 값은 5,000,000으로 변경됩니다.

-- 4-1
SELECT index_name, status, orphaned_entries
  FROM user_indexes
 WHERE index_name = 'T1_X1';
 
INDEX_NAME STATUS ORPHANED_ENTRIES
---------- ------ ----------------
T1_X1      VALID  YES
 
1개의 행이 선택되었습니다.
 
-- 4-2
ANALYZE INDEX t1_x1 VALIDATE STRUCTURE;
 
SELECT name, blocks, lf_rows, lf_blks, br_rows, br_blks, del_lf_rows, btree_space, used_space
  FROM index_stats;
 
NAME  BLOCKS  LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE
----- ------ -------- ------- ------- ------- ----------- ----------- ----------
T1_X1  29696 10000000   29100   29099      48     5000000   233185536  209263810
 
1개의 행이 선택되었습니다.

 

아래 쿼리는 각각 p1, p2 파티션을 조회합니다. 5-1번 실행 계획에서 TRUNCATE된 p1 파티션에 대한 블록 I/O가 발생하지 않는 것을 알 수 있습니다. 이것은 Predicate Information 항목의 TBL$OR$IDX$PART$NUM(<?>,0,8,0,"T1".ROWID)=1 필터 조건에 의한 동작으로 보입니다.

-- 5-1: P1
SELECT * FROM t1 WHERE c1 BETWEEN 1 AND 100;
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name  | Pstart| Pstop | A-Rows | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |       |       |       |      0 |       3 |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1    |     1 |     1 |      0 |       3 |
|*  2 |   INDEX RANGE SCAN                         | T1_X1 |       |       |      0 |       3 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1">=1 AND "C1"<=100)
       filter(TBL$OR$IDX$PART$NUM(<?>,0,8,0,"T1".ROWID)=1)
 
-- 5-2: P2
SELECT * FROM t1 WHERE c1 BETWEEN 5000001 AND 5000100;
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name  | Pstart| Pstop | A-Rows | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |       |       |       |    100 |       6 |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1    |     2 |     2 |    100 |       6 |
|*  2 |   INDEX RANGE SCAN                         | T1_X1 |       |       |    100 |       4 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1">=5000001 AND "C1"<=5000100)
       filter(TBL$OR$IDX$PART$NUM(<?>,0,8,0,"T1".ROWID)=1)

 

아래와 같이 p1 테이블에 데이터를 삽입하겠습니다.

-- 6
INSERT INTO t1 SELECT ROWNUM, 'X' FROM XMLTABLE ('1 to 5000000');
COMMIT;

 

인덱스 공간이 재사용되지 못해 INDEX_STATS 테이블의 blocks 값이 29,696에서 45,056로 늘어난 것을 볼 수 있습니다.

-- 7
ANALYZE INDEX t1_x1 VALIDATE STRUCTURE;
 
SELECT name, blocks, lf_rows, lf_blks, br_rows, br_blks, del_lf_rows, btree_space, used_space
  FROM index_stats;
 
NAME  BLOCKS  LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE
----- ------ -------- ------- ------- ------- ----------- ----------- ----------
T1_X1  45056 15000000   44132   44131      98     5000000   353843136  313394236
 
1개의 행이 선택되었습니다.

 

p1 파티션을 다시 조회하면 블록 I/O가 p2 파티션을 조회한 5-2번 쿼리와 동일한 것을 알 수 있습니다. 같은 데이터를 다시 입력해도 조회 성능이 저하되지 않는 것으로 보입니다.

-- 8
SELECT * FROM t1 WHERE c1 BETWEEN 1 AND 100;
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name  | Pstart| Pstop | A-Rows | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |       |       |       |    100 |       6 |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1    |     1 |     1 |    100 |       6 |
|*  2 |   INDEX RANGE SCAN                         | T1_X1 |       |       |    100 |       4 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1">=1 AND "C1"<=100)
       filter(TBL$OR$IDX$PART$NUM(<?>,0,8,0,"T1".ROWID)=1)

 

CLEANUP 동작

글로벌 인덱스의 orphaned entries를 정리하기 위해 매일 오전 2시에 DBMS_PART.CLEANUP_GIDX_JOB 프로시저가 자동으로 수행됩니다. DBMS_PART.CLEANUP_GIDX_JOB 프로시저는 CLEANUP 작업을 직렬로 수행하기 때문에 인덱스 크기가 큰 경우 많은 시간이 소요될 수 있습니다. 수행 시간을 단축하기 위해 PARALLEL 매개변수에 병렬도를 지정할 수 있습니다. OPTIONS 매개변수는 CLEANUP_ORPHANS나 COALESCE를 지정할 수 있습니다. CLEANUP_ORPHANS는 orphaned entries만 정리하고, COALESCE는 블록 병합을 함께 수행합니다. 기본값은 CLEANUP_ORPHANS입니다.

-- 9-1
SELECT b.program_action, b.number_of_arguments, c.repeat_interval
  FROM dba_scheduler_jobs a
     , dba_scheduler_programs b
     , dba_scheduler_schedules c
 WHERE a.owner = 'SYS'
   AND a.job_name = 'PMO_DEFERRED_GIDX_MAINT_JOB'
   AND b.owner = a.program_owner
   AND b.program_name = a.program_name
   AND c.owner = a.schedule_owner
   AND c.schedule_name = a.schedule_name;
 
PROGRAM_ACTION             NUMBER_OF_ARGUMENTS REPEAT_INTERVAL
-------------------------- ------------------- ---------------------------------------------
dbms_part.cleanup_gidx_job                   2 FREQ=DAILY; BYHOUR=02; BYMINUTE=0; BYSECOND=0
 
1개의 행이 선택되었습니다.
 
-- 9-2
SELECT argument_name, argument_position, argument_type, default_value
  FROM dba_scheduler_program_args
 WHERE owner = 'SYS'
   AND program_name = 'PMO_DEFERRED_GIDX_MAINT';
 
ARGUMENT_NAME ARGUMENT_POSITION ARGUMENT_TYPE DEFAULT_VALUE
------------- ----------------- ------------- -------------
PARALLEL                      1 VARCHAR2
OPTIONS                       2 VARCHAR2
 
2 행이 선택되었습니다.
 
-- 9-3:
EXEC DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', 'PARALLEL', '2');
 
-- 9-4: CLEANUP_ORPHANS or COALESCE
EXEC DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', 'OPTIONS', 'COALESCE');

 

참고로 DBMS_PART.CLEANUP_GIDX_JOB 프로시저는 SYS.INDEX_ORPHANED_ENTRY_V$ 뷰에서 CLEANUP 대상을 조회합니다.

-- 10-1
SELECT index_owner, index_name, index_subname, table_owner, table_name, table_subname
  FROM sys.index_orphaned_entry_v$;
 
INDEX_OWNER INDEX_NAME INDEX_SUBNAME TABLE_OWNER TABLE_NAME TABLE_SUBNAME
----------- ---------- ------------- ----------- ---------- -------------
TUNA        T1_X1                    TUNA        T1
 
1개의 행이 선택되었습니다.
 
-- 10-2
SELECT indexobj#, tabpartdobj# FROM sys.index_orphaned_entry$;
 
INDEXOBJ# TABPARTDOBJ#
--------- ------------
   224430       224428
 
1개의 행이 선택되었습니다.

 

아래의 네 가지 방법을 사용하면 글로벌 인덱스의 orphaned entries를 수동으로 정리할 수 있습니다.

-- 11-1
DBMS_SCHEDULER.RUN_JOB (SYS.PMO_DEFERRED_GIDX_MAINT_JOB, TRUE);
 
-- 11-2
EXEC DBMS_PART.CLEANUP_GIDX ('TUNA', 'T1', '2', 'CLEANUP_ORPHANS');
 
-- 11-3: CLEANUP = 'COALESCE', CLEANUP ONLY = 'CLEANUP_ORPHANS'
ALTER INDEX t1_x1 COALESCE CLEANUP PARALLEL 2;
-- ALTER INDEX t1_x1 COALESCE CLEANUP ONLY PARALLEL 2;
 
-- 11-4
ALTER INDEX t1_x1 REBUILD PARALLEL 2 ONLINE;

 

INDEX COALESCE CLEANUP은 ONLINE으로 수행할 수 없으며 21.59초가 소요됩니다. 수행 후 *_INDEXES 뷰의 orphaned_entries 값은 NO, INDEX_STATS 테이블의 del_lf_rows 값은 0으로 변경되고, sys.index_orphaned_entry_v$ 뷰의 결과가 반환되지 않습니다.

-- 12-1
ALTER INDEX t1_x1 COALESCE CLEANUP PARALLEL 2;
 
경   과: 00:00:21.59
 
-- 12-2
SELECT index_name, status, orphaned_entries
  FROM user_indexes
 WHERE index_name = 'T1_X1';
 
INDEX_NAME STATUS ORPHANED_ENTRIES
---------- ------ ----------------
T1_X1      VALID  NO
 
1개의 행이 선택되었습니다.
 
-- 12-3
ANALYZE INDEX t1_x1 VALIDATE STRUCTURE;
 
SELECT name, blocks, lf_rows, lf_blks, br_rows, br_blks, del_lf_rows, btree_space, used_space
  FROM index_stats;
 
NAME  BLOCKS  LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE
----- ------ -------- ------- ------- ------- ----------- ----------- ----------
T1_X1  45056 10000000   29135   29134      98           0   233867136  209264235
 
1개의 행이 선택되었습니다.
 
-- 12-4
SELECT * FROM sys.index_orphaned_entry_v$;
 
선택된 레코드가 없습니다.

 

INDEX REBUILD는 ONLINE으로 수행할 수 있으며 5.27초가 소요됩니다. 전체 테이블의 크기와 TRUNCATE되는 파티션의 크기에 따라 선택적으로 INDEX COALESCE CLEANUP과 INDEX REBUILD를 사용할 수 있습니다.

-- 13-1
ALTER INDEX t1_x1 REBUILD PARALLEL 2 ONLINE;
 
경   과: 00:00:05.27
 
-- 13-2
ANALYZE INDEX t1_x1 VALIDATE STRUCTURE;
 
SELECT name, blocks, lf_rows, lf_blks, br_rows, br_blks, del_lf_rows, btree_space, used_space
  FROM index_stats;
 
NAME  BLOCKS  LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE
----- ------ -------- ------- ------- ------- ----------- ----------- ----------
T1_X1  29480 10000000   29101   29100      49           0   233201568  209264182
 
1개의 행이 선택되었습니다.

 

아래는 지금까지 수행한 테스트에 대한 INDEX_STATS 테이블의 조회 결과입니다. blocks 값에서 INDEX COALESCE CLEANUP이 미사용 공간을 해제하지 않는다는 것을 알 수 있습니다.

NAME  BLOCKS  LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE
----- ------ -------- ------- ------- ------- ----------- ----------- ----------
T1_X1  29696 10000000   29100   29099      48           0   233185536  209263810
T1_X1  29696 10000000   29100   29099      48     5000000   233185536  209263810 -- TRUNCATE PARTITION UPDATE GLOBAL INDEXES
T1_X1  45056 15000000   44132   44131      98     5000000   353843136  313394236 -- INSERT
T1_X1  45056 10000000   29135   29134      98           0   233867136  209264235 -- INDEX COALESCE CLEANUP
T1_X1  29480 10000000   29101   29100      49           0   233201568  209264182 -- INDEX REBUILD

 

관련 링크

• MOS - Is It Possible To Execute The Job PMO_DEFERRED_GIDX_MAINT_JOB With Parallel Degree? (Doc ID 2126370.1)
• MOS - Bug 24515918 - PMO_DEFERRED_GIDX_MAINT_JOB causes TX contention on busy tables and runs serially (Doc ID 24515918.8)
• Richard Foote - 12c Asynchronous Global Index Maintenance Part I (Where Are We Now ?)
• Richard Foote - 12c Asynchronous Global Index Maintenance Part II (The Space Between)
• Toad World Blog - Oracle Database 12c Deferred Global Index