개요

대용량 인덱스 빌드 시 TEMP 용량 산정 및 모니터링 방안을 설명하는 문서입니다.

방법

1. 대용량의 인덱스 빌드를 위한 TEMP 용량 산정

1.1. 대용량의 인덱스를 생성하기 위한 정확한 TEMP size 제시

정렬 과정에서 빠른 비교를 위해 소트 키를 노멀라이즈 하는데 이때 원래 데이터의 크기보다 커지게 됩니다. 
이 때문에 table사이즈보다 크게 temp table 사이즈를 주어도 부족하다고 에러가 날 수 있습니다. 
테이블에 실제 데이터 값의 형태에 따라 늘어나는 사이즈가 다르기 때문에 정확한 최소한의 수치는 계산하기 어렵습니다.

만약 인덱스로 잡힌 컬럼이 매우 많고, 그 컬럼들이 많은 수의 string 컬럼들로 구성된 경우 TEMP사이즈는 테이블 사이즈의 2배까지 필요합니다.
그리고 동시에 다른 정렬 쿼리가 돌고 있다면 그 쿼리에서 사용하는 양도 고려하여 잡아야 합니다. 

또한, 인덱스 생성에서 사용한 parallel 옵션의 경우, 메모리 사용량은 늘 수 있지만 TEMP 테이블스페이스 사용량에는 크게 영향을 주지 않을 것 입니다. 
적당한 dop는 cpu개수나 io채널 수에 맞춰 설정하는 것이 좋습니다.

2. 인덱스 빌드 중에 TEMP 사용량 모니터링

2.1. v$tempseg_usage를 사용한 모니터링

인덱스 생성 중에 v$tempseg_usage 모니터링 해서 사용량이 미미할 경우, 점검해 봐야 할 부분을 공유 드립니다. 먼저 본 문서에서 설명할 index 생성 구문은 create index online parallel 입니다.
해당 기능의 동작방식은 우선 unusable 상태의 index를 생성하고,  내부적으로 online rebuild 수행을 합니다.
online rebuild 수행 방식은  사용자가 명기한 인덱스명(XPK_TEST_TAB)과는  다른 이름의 segments(dba_segments에서 사용량이 많은 오브젝트를 order by 조회해 보면 관찰 됩니다)로 데이터를 넣고 수행 완료 시에 segment가 교체(exchange)됩니다. 
즉, 수행이 완료되기 전까지 XPK_TEST_TAB segments는 빈 껍데기만 가지고 있습니다.

2.2. 사례

아래는 특정 사이트에서 대용량의 일반 테이블(1.5T)에 대한 대용량의 인덱스를 생성하는 실제 사례입니다.

<현상>

1.5T의 테이블에 대하여 blevel 6인 2.3T의 인덱스를 drop후 create index online 옵션으로 재생성 시도 했지만, 20시간이 지나도 완료되지 않고, v$tempseg_usage 뷰를 모니터링하면 사용량이 0.27M 밖에 안 되는 현상.

<원인>

20시간 동안 여전히 인덱스 생성 진행 중인 세션에서 취득한 callstack:

000000010bab4abc xi_sort_keystore_load_

000000010b8ffd54 xi_sort_run_load_from_ssgmt

000000010b8d4ff4 xi_sort_2p_merge

000000010afa9a54 op_sort_stage_merge_run

=> sort 노드에서 정렬을 끝내고 결과를 내보내고 있는 중에 있습니다.

즉, temp에 있던 run들을 merge하여 결과를 내보내고 temp sgmt를 반납하기 때문에 temp 사용량이 감소하는 단계에 있었고,  따라서 v$tempseg_usage는 0.27M 사용 중으로 모니터링 됨.

CREATE TABLE 및 CREATE INDEX ONLINE 구문 예시

CREATE TABLE TIBERO.TEST_TAB
(
    BSDT VARCHAR(8) NOT NULL,
    RPT_ID VARCHAR(20) NOT NULL,
    RFACT VARCHAR(5) NOT NULL,
    SRC_FG VARCHAR(5) NOT NULL,
    RPT_TYPE VARCHAR(50) NOT NULL,
    UD1 VARCHAR(16) NOT NULL ,
    UD2 VARCHAR(16) NOT NULL ,
    UD3 VARCHAR(16) NOT NULL ,
    UD4 VARCHAR(16) NOT NULL ,
    UD5 VARCHAR(16) NOT NULL ,
    UD6 VARCHAR(16) NOT NULL ,
    UD7 VARCHAR(16) NOT NULL ,
    UD8 VARCHAR(16) NOT NULL ,
    UD9 VARCHAR(16) NOT NULL ,
    UD10 VARCHAR(16) NOT NULL ,
    UD11 VARCHAR(16) NOT NULL ,
    UD12 VARCHAR(16) NOT NULL ,
    UD13 VARCHAR(16) NOT NULL ,
    UD14 VARCHAR(16) NOT NULL ,
    UD15 VARCHAR(20) NOT NULL ,
    SCEN_ID VARCHAR(30) NOT NULL ,
    PRICE NUMBER NULL,
    VAR NUMBER NULL,
    BASE_PRICE NUMBER NULL,
    VAR_TOT NUMBER NULL,
    VAR_IR NUMBER NULL,
    VAR_EQ NUMBER NULL,
    VAR_FX NUMBER NULL,
    VAR_VL NUMBER NULL
)
TABLESPACE DATA_TS

CREATE UNIQUE INDEX TIBERO.XPK_TEST_TAB ON TIBERO.TEST_TAB
(
    BSDT ,
    RPT_ID ,
    RFACT ,
    SRC_FG ,
    RPT_TYPE ,
    UD1 ,
    UD2 ,
    UD3 ,
    UD4 ,
    UD5 ,
    UD6 ,
    UD7 ,
    UD8 ,
    UD9 ,
    UD10 ,
    UD11 ,
    UD12 ,
    UD13 ,
    UD14 ,
    UD15 ,
    SCEN_ID 
)
ONLINE
PARALLEL 8
NOLOGGING
TABLESPACE INDEX_TS