테이블스페이스

테이블 스페이스

테이블 스페이스 종류

SYSAUX 테이블스페이스

SYSAUX 테이블스페이스 정리

1. SYSAUX 테이블스페이스가 비대해지는 원인은 대부분 오래된 통계정보 백업(SM/OPTSTAT), AWR 스냅샷 데이터(SM/AWR), 그리고 Optimizer Advisor 정보(SM/ADVISOR) 때문
2. 데이터 파일 크기를 물리적으로 줄이기(RESIZE) 전, 내부에 쌓인 데이터를 정리하고 공간을 확보하는 방법 추천
3. 모든 작업은 SYSDBA 계정으로 수행해야 합니다.

---

1단계: 용량을 많이 차지하는 범인(Component) 확인

• 어떤 컴포넌트가 SYSAUX 공간을 많이 쓰고 있는지 먼저 확인

SELECT occupant_name, space_usage_kbytes/1024 AS "SIZE(MB)" 
  FROM v$sysaux_occupants ORDER BY 2 DESC;

• SM/OPTSTAT: 통계정보 이력 데이터
• SM/AWR: 성능 모니터링 스냅샷 데이터
• SM/ADVISOR: 통계/성능 Advisor 데이터 [1, 4]

---

2단계: 컴포넌트별 데이터 정리 (Purge)

• 조회된 결과에 따라 가장 비대해진 영역 데이터 삭제.

① SM/OPTSTAT (통계정보 이력) 정리 [4]

- 기본 보관 주기(Retention)는 31일. Exadata 환경에서는 이 주기가 길면 수백 GB까지 커질 수 있으므로 주기를 줄이고 오래된 데이터 삭제

-- 1. 보관 주기 확인 (기본값: 31일)

SELECT dbms_stats.get_stats_history_retention FROM dual;

-- 2. 보관 주기를 7일~10일 수준으로 축소 (예: 10일)

EXEC dbms_stats.alter_stats_history_retention(10);

-- 3. 현재 날짜 기준 10일 이전의 통계정보 이력 즉시 삭제 (운영 중 부하 주의)

EXEC dbms_stats.purge_stats(systimestamp - 10);

② SM/AWR (성능 스냅샷) 정리

- AWR 스냅샷 보관 주기(기본 8일)를 조정하고 수동으로 오래된 스냅샷 삭제.

-- 1. 현재 AWR 보관 주기 및 스냅샷 간격 확인

SELECT * FROM dba_hist_wr_control;

-- 2. 보관 주기를 7일(10080분), 간격을 1시간(60분)으로 조정

EXEC dbms_workload_repository.modify_snapshot_settings(retention => 10080, interval => 60);

-- 3. 불필요한 과거 스냅샷 ID 범위를 지정하여 삭제 (DBA_HIST_SNAPSHOT에서 ID 확인 가능)

EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id => 1, high_snap_id => 5000);

③ SM/ADVISOR (Optimizer Advisor Task) 정리 [1] 19c에서는 AUTO_STATS_ADVISOR_TASK가 과도한 공간을 차지하는 경우가 많습니다. 오래된 아티팩트를 제거합니다.

DECLARE
  v_task_name VARCHAR2(100) := 'AUTO_STATS_ADVISOR_TASK';BEGIN
  -- 30일보다 오래된 Advisor 데이터 삭제
  dbms_stats.purge_stats(before_timestamp => sysdate-30); END;
/

---

3단계: 테이블/인덱스 단편화 제거 (Shrink) 및 Reorg

1. 데이터를 PURGE 하더라도 테이블스페이스 내 High Water Mark(HWM)가 내려가지 않아 물리적 파일 크기가 줄어들지 않습니다.
2. 여유 공간을 확보하기 위해 공간을 압축(Shrink)해야 합니다.
3. 가장 많은 공간을 차지하는 대형 테이블(예: WRI$_OPTSTAT_HISTGRM_HISTORY, WRH$_SYSSTAT 등)을 대상으로 수행합니다.

-- 1. 로우 무브먼트 활성화 (Shrink 필수 선행 작업)

ALTER TABLE sys.WRI$_OPTSTAT_HISTGRM_HISTORY ENABLE ROW MOVEMENT;

-- 2. 공간 압축 및 HWM 낮추기 (인덱스까지 함께 정렬)

ALTER TABLE sys.WRI$_OPTSTAT_HISTGRM_HISTORY SHRINK SPACE CASCADE;

-- 3. 로우 무브먼트 비활성화 (원복)

ALTER TABLE sys.WRI$_OPTSTAT_HISTGRM_HISTORY DISABLE ROW MOVEMENT;

⚠️ 주의 (19c 특정 버그 관련):

통계정보 관련 테이블인 WRI$_OPTSTAT_HISTGRM_HISTORY 등에는 Function-based Index가 생성되어 있어 바로 SHRINK가 안 될 수 있습니다.

이 경우 해당 인덱스를 잠시 DROP 하거나 UNUSABLE로 만든 후 SHRINK를 하고, 다시 생성(REBUILD)해야 합니다.

---

4단계: 물리적 데이터 파일 크기 축소 (Resize)

내부 정리가 모두 끝났다면 OS 및 ASM(Exadata 스토리지)에 공간을 반환하기 위해 데이터 파일 크기를 줄입니다.

-- 1. SYSAUX 테이블스페이스 소속 데이터 파일 경로 및 크기 확인

SELECT file_name, bytes/1024/1024 "SIZE(MB)" FROM dba_data_files WHERE tablespace_name = 'SYSAUX';

-- 2. 가능한 크기로 리사이즈 실행 (예: 30GB로 축소 시)

ALTER DATABASE DATAFILE '+DATA/exadb/datafile/sysaux.XXXX.XXXX' RESIZE 30G;

• 만약 중간에 있는 세그먼트 위치 때문에 RESIZE가 특정 크기 이하로 내려가지 않는다면, dba_extents 조회를 통해 테이블스페이스 가장 뒷부분(끝자락)에 위치한 세그먼트를 찾아서 다른 테이블스페이스로 MOVE 했다가 다시 가져오는 작업이 필요할 수 있습니다.

---