[Oracle] 인덱스 저장용 테이블스페이스 정비 절차
데이터베이스 설계 시 테이블(Data)과 인덱스(Index)의 저장 공간을 분리하는 것은 오라클 관리의 교과서적인 베스트 프랙티스(Best Practice)라고 하네요. 테이블과 인덱스 저장용 테이블스페이스를 분리할 경우의 잇점을 Gemini에게 물어보니 아래와 같이 친절하게 대답해 줍니다. 정리하기로 결정한 걸 잘 했다는 생각이 드네요.
- I/O 분산을 통한 성능 최적화:
만약 인덱스와 테이블이 서로 다른 물리적 디스크(또는 다른 스토리지 컨트롤러)에 위치한 테이블스페이스에 있다면, 인덱스를 읽는 작업과 테이블을 읽는 작업이 동시에(Parallel) 일어날 수 있어 응답 속도가 빨라집니다. - 장애 발생 시 효율적인 복구 전략 수립:
테이블 데이터가 담긴 테이블스페이스는 최우선으로 복구해야 하지만, 인덱스는 데이터만 있다면 언제든REBUILD로 재생성할 수 있습니다. 핵심 데이터가 담긴 TS만 자주 백업하고, 인덱스 TS는 상대적으로 백업 주기를 길게 가져가는 등 운영의 묘를 살릴 수 있습니다. - 데이터의 단편화(Fragmentation) 감소:
테이블은 주로 INSERT 위주로 공간을 채우지만, 인덱스는 데이터의 수정/삭제에 따라 빈 공간(Leaf Block)이 불규칙하게 생깁니다. 공간을 섞어서 쓰면 단편화가 심해져 스캔 속도가 느려집니다. 인덱스를 별도 공간에 두면, 나중에 인덱스만 통째로REBUILD하여 공간을 깔끔하게 압축하고 최적화하기가 훨씬 수월합니다. - 인덱스 용량 관리의 가독성 향상:
특정 테이블스페이스를 OFFLINE 하거나 점검할 때, 인덱스만 따로 관리할 수 있어 업무 영향도를 세밀하게 조정할 수 있습니다.
그런데 회사 테이블스페이스를 살펴보니 테이블과 인덱스 저장용 테이블스페이스를 각각 생성하였으나, DBA와 개발자들이 구분없이 사용하다보니 당초 목적과 반하게 막 섞여있었습니다. 그래서 지금이라도 베스트 프랙티스로 적용하기 위해 인덱스는 인덱스용 테이블스페이스로 리빌드하려고 합니다.
- 테이블과 인덱스 저장용 테이블스페이스 현황 파악
- REBUILD 대상 인덱스 크기 및 타겟 테이블스페이스 여유 공간 확인
- 필요 시 타겟 데이블스페이스 공간을 사전에 확보
- 인덱스 Rebuild를 위한 DDL 확보 및 실행
1. 테이블과 인덱스 저장용 테이블스페이스 현황 파악
- 상태가 ‘ONLINE’인 테이블스페이스에 저장된 테이블 및 인덱스의 요약 현황을 확인합니다. “테이블용 테이블스페이스에 저장된 인덱스”(REBUILD 대상)와 “인덱스용 테이블스페이스에 저장된 테이블”을 확인합니다.
SELECT S.TABLESPACE_NAME, SUM(DECODE(S.SEGMENT_TYPE, 'TABLE', 1, 0)) AS TABLE_CNT, SUM(DECODE(S.SEGMENT_TYPE, 'INDEX', 1, 0)) AS INDEX_CNT FROM DBA_SEGMENTS S, DBA_TABLESPACES T WHERE S.TABLESPACE_NAME = T.TABLESPACE_NAME AND S.TABLESPACE_NAME NOT IN ('SYSAUX', 'SYSTEM') AND SUBSTR(S.SEGMENT_TYPE, 1, 5) IN ('TABLE', 'INDEX') -- 파티션 포함 AND S.SEGMENT_TYPE NOT LIKE 'LOB%' -- LOB 객체 제외 AND T.STATUS = 'ONLINE' GROUP BY S.TABLESPACE_NAME ORDER BY 1; - 테이블용 테이블스페이스에 들어가 있는 인덱스 목록을 리스트업합니다.(파란색 부분을 수정하여 사용)
SELECT S.TABLESPACE_NAME, S.OWNER, S.SEGMENT_TYPE, S.SEGMENT_NAME, ROUND(S.BYTES/1024/1024, 2) AS SIZE_MB FROM DBA_SEGMENTS S, DBA_TABLESPACES T WHERE S.TABLESPACE_NAME = T.TABLESPACE_NAME AND S.TABLESPACE_NAME IN (테이블용 테이블스페이스 리스트) AND SUBSTR(S.SEGMENT_TYPE, 1, 5) = 'INDEX' AND S.SEGMENT_TYPE NOT LIKE 'LOB%' AND T.STATUS = 'ONLINE' ORDER BY 1, 2, 3, 4;
2. REBUILD 대상 인덱스 크기 및 타겟 테이블스페이스 여유 공간 확인
- REBUILD할 인덱스의 크기와 타겟 테이블스페이스의 여유 공간을 확인합니다. 타겟 테이블스페이스의 여유 공간은 REBUILD할 인덱스의 크기의 120% 이상 확보해야 합니다. REBUILD 과정에서 발생하는 일시적인 오버헤드 때문에 공간 부족 에러가 날 수 있습니다.
SELECT (SELECT ROUND(SUM(S.BYTES) / 1024 / 1024, 2) FROM DBA_SEGMENTS S WHERE S.TABLESPACE_NAME IN (테이블용 테이블스페이스 리스트) AND SUBSTR(S.SEGMENT_TYPE, 1, 5) = 'INDEX' AND S.SEGMENT_TYPE NOT LIKE 'LOB%') AS INDEX_SIZE_MB, (SELECT ROUND(SUM(F.BYTES) / 1024 / 1024, 2) FROM DBA_FREE_SPACE F WHERE F.TABLESPACE_NAME = 'TARGET_TS_NAME') AS FREE_SPACE_MB FROM DUAL;
3. 필요 시 타겟 데이블스페이스 공간을 사전에 확보
타겟 테이블스페이스 여유 공간이 부족하다면 데이블스페이스 공간을 사전에 확보해야 합니다. 데이터 파일이 저장되는 디렉토리의 물리적인 디스크 여유 공간을 확인하는 것도 잊지 말아야 합니다.($ df -hT)
- 데이터파일의 사용량 및 여유 공간을 확인합니다.(
SQL> show parameter db_create_file_dest;)SELECT D.TABLESPACE_NAME, D.FILE_NAME, -- {db_create_file_dest}/<SID>/datafile/데이터파일.dbf ROUND(D.BYTES/1024/1024, 2) AS CURRENT_MB, ROUND(D.MAXBYTES/1024/1024, 2) AS MAX_MB, CASE WHEN NVL(D.MAXBYTES,0) = 0 THEN '0 %' ELSE ROUND((D.MAXBYTES - D.BYTES) / D.MAXBYTES * 100, 2) || ' %' END AS AVAIL_PER, D.AUTOEXTENSIBLE, D.STATUS FROM DBA_DATA_FILES D WHERE 1 = 1 AND D.TABLESPACE_NAME = 'TARGET_TS_NAME' ORDER BY 1, 2; - [시나리오 1: 기존 데이터 파일에 여유 디스크 공간이 있을 때 (Resize)]
예를 들면 현재 데이터 파일이 10GB인데, 서버 디스크에는 여유가 있고 파일 크기 한계(32GB)까지는 아직 여유가 있을 때 사용합니다. RESIZE의 최대 크기는 DB_BLOCK_SIZE 파라미터로 결정됩니다(8192 -> 32GB).ALTER DATABASE DATAFILE '{db_create_file_dest}/<SID>/datafile/데이터파일.dbf' RESIZE 30G; - [시나리오 2: 파일이 최대 크기(32GB)에 도달했을 때 (Add Datafile)]
기존 데이터 파일이 이미 최대 크기 한계(32GB)를 넘었거나, 다른 디스크 드라이브의 공간을 사용해야 할 때 사용합니다. MAXSIZE는 DB_BLOCK_SIZE 파라미터로 결정되며(8192 -> 32GB), Unlimited 옵션은 사용하지 않는 게 좋습니다.ALTER TABLESPACE TARGET_TS_NAME ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 500M MAXSIZE 30G;
- [시나리오 3: 관리 자동화가 필요할 때 (Autoextend ON)]
현재는 공간이 충분하지만, 인덱스 리빌드 시점에 자동으로 공간을 늘리고 싶을 때 사용합니다.ALTER DATABASE DATAFILE '{db_create_file_dest}/<SID>/datafile/데이터파일.dbf' AUTOEXTEND ON NEXT 500M MAXSIZE 30G;
4. 인덱스 Rebuild를 위한 DDL 생성 및 실행
- REBUILD할 인덱스를 확인하고 리빌드, 환경리셋, 통계정보 갱신을 위한 DDL을 생성합니다.(REBUILD_DDL, RESET_DDL, STATS_DDL)
SELECT CASE WHEN SEGMENT_TYPE = 'INDEX' THEN 'ALTER INDEX ' || OWNER || '."' || SEGMENT_NAME || '" REBUILD TABLESPACE TARGET_TS_NAME PARALLEL 4 ONLINE NOLOGGING;' WHEN SEGMENT_TYPE = 'INDEX PARTITION' THEN 'ALTER INDEX ' || OWNER || '."' || SEGMENT_NAME || '" REBUILD PARTITION "' || PARTITION_NAME || '" TABLESPACE TARGET_TS_NAME PARALLEL 4 ONLINE NOLOGGING;' WHEN SEGMENT_TYPE = 'INDEX SUBPARTITION' THEN 'ALTER INDEX ' || OWNER || '."' || SEGMENT_NAME || '" REBUILD SUBPARTITION "' || PARTITION_NAME || '" TABLESPACE TARGET_TS_NAME PARALLEL 4 ONLINE NOLOGGING;' END AS REBUILD_DDL FROM DBA_SEGMENTS WHERE TABLESPACE_NAME IN (테이블용 테이블스페이스 리스트) AND SUBSTR(SEGMENT_TYPE, 1, 5) = 'INDEX' -- 파티션 인덱스까지 포함 AND SEGMENT_TYPE NOT LIKE 'LOB%' --AND OWNER = '사용자' ORDER BY TABLESPACE_NAME, SEGMENT_NAME; SELECT DISTINCT 'ALTER INDEX ' || OWNER || '."' || SEGMENT_NAME || '" NOPARALLEL LOGGING;' AS RESET_DDL, 'EXEC DBMS_STATS.GATHER_INDEX_STATS(''' || OWNER || ''', ''' || SEGMENT_NAME || ''', degree => 4);' AS STATS_DDL, 'SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE OWNER = ''' || OWNER || ''' AND INDEX_NAME = ''' || SEGMENT_NAME || ''';' AS ANALYZE_DML FROM DBA_SEGMENTS WHERE TABLESPACE_NAME IN (테이블용 테이블스페이스 리스트) AND SUBSTR(SEGMENT_TYPE, 1, 5) = 'INDEX' -- 파티션 인덱스까지 포함 AND SEGMENT_TYPE NOT LIKE 'LOB%' --AND OWNER = '사용자' ORDER BY RESET_DDL - REBUILD_DDL를 실행하여 인덱스를 REBUILD합니다. 서비스 중단 없이 REBUILD를 진행하려면 ONLINE 옵션을 추가합니다(Enterprise 버전만 가능). NOLOGGING 옵션을 추가하면 Redo 로그 생성을 최소화하여 더 빨라집니다.
- RESET_DDL를 실행하여 병렬처리를 1로 원복하고, 로깅 설정을 복구합니다.
- STATS_DDL를 실행하여 통계 정보 갱신합니다(선택).
DBA_OPTSTAT_OPERATIONS뷰를 통해 통계 정보 갱신 작업의 성공여부를 점검하고, DBA_INDEXES뷰의 LAST_ANALYZED 컬럼 날짜가 현재 시간인지 확인합니다.SELECT OPERATION, TARGET, TO_CHAR(START_TIME, 'yyyy-mm-dd hh:mi:ss') AS START_TIME, TO_CHAR(END_TIME, 'yyyy-mm-dd hh:mi:ss') AS END_TIME, CASE WHEN EXTRACT(HOUR FROM (END_TIME - START_TIME)) > 0 THEN EXTRACT(MINUTE FROM (END_TIME - START_TIME)) || '시간 ' ELSE '' END || CASE WHEN EXTRACT(MINUTE FROM (END_TIME - START_TIME)) > 0 THEN EXTRACT(MINUTE FROM (END_TIME - START_TIME)) || '분 ' ELSE '' END || ROUND(EXTRACT(SECOND FROM (END_TIME - START_TIME)),2) || '초' AS DURATION FROM DBA_OPTSTAT_OPERATIONS WHERE OPERATION = 'gather_index_stats' ORDER BY START_TIME DESC, TARGET;
