Computer >> 컴퓨터 >  >> 프로그래밍 >> 데이터베이스

Oracle SYSAUX 테이블스페이스 관리 방법 총정리

Oracle® 10g에서는 PERMANENT, READ WRITE, EXTENT MANAGEMENT LOCAL, SEGMENT SPACE MANAGEMENT AUTO와 같은 필수 속성을 가진 새로운 필수 테이블스페이스인 SYSAUX가 도입되었습니다. 이 글에서는 SYSAUX 테이블스페이스가 점점 커질 때 이를 어떻게 효과적으로 관리할 수 있는지 살펴보겠습니다.

SYSAUX 테이블스페이스란?

SYSAUX 테이블스페이스를 활용하면 다음과 같은 이점을 얻을 수 있습니다.

  • 옵션 설치 및 제거 과정에서 발생하는 SYSTEM 테이블스페이스의 단편화(fragmentation) 방지
  • SYSTEM 테이블스페이스의 손상 및 공간 부족 위험 감소
  • 데이터베이스 관리자(DBA)의 유지보수 부담 경감
  • Oracle 옵션 및 기능과 관련된 모든 보조 데이터베이스 메타데이터를 저장하고, 관련 테이블스페이스 수를 줄여 통합 관리
Oracle SYSAUX 테이블스페이스 관리 방법 총정리

SYSAUX 점유자(Occupants) 확인하기

SYSAUX 테이블스페이스에는 26개의 점유자(occupant)가 있으며, 아래 쿼리로 확인할 수 있습니다.

SQL> select OCCUPANT_NAME,OCCUPANT_DESC
     from V$SYSAUX_OCCUPANTS
     order by SPACE_USAGE_KBYTES desc
Oracle SYSAUX 테이블스페이스 관리 방법 총정리

이후 버전의 Oracle Database에서는 더 많은 구성 요소가 추가되었습니다.

Oracle SYSAUX 테이블스페이스 관리 방법 총정리

다음 이미지는 각 Oracle Database 버전별로 추가되거나 지원이 중단된(deprecated) 구성 요소를 보여줍니다.

Oracle SYSAUX 테이블스페이스 관리 방법 총정리 Oracle SYSAUX 테이블스페이스 관리 방법 총정리

사전 예방적 SYSAUX 테이블스페이스 관리

SYSAUX 테이블스페이스를 사전에 모니터링하고 관리하려면 다음 작업을 수행하세요.

  1. AUTOEXTEND off로 설정하여 SYSAUX 테이블스페이스가 무분별하게 확장되지 않도록 합니다.

  2. STATISTICS_LEVEL 파라미터 값을 확인합니다.

    - **ALL** 값은 때때로 많은 시스템 자원을 소모할 수 있습니다.
- **Basic**과 **Typical** 값은 상대적으로 적은 자원을 소비합니다.

  3. 어드바이저(advisor), 베이스라인(baseline), SQL 튜닝 세트(SQL tuning set)의 사용 방식을 점검합니다. 어드바이저는 스냅샷 범위를 삭제할 계획이라 하더라도 스냅샷 정보를 계속 유지해야 할 수 있습니다.

  4. 쿼리를 실행하여 SYSAUX 테이블스페이스에서 가장 많은 공간을 차지하는 sysaux_occupant가 무엇인지 파악합니다.

문제 발생 후 SYSAUX 테이블스페이스 관리(복구 조치)

SYSAUX 테이블스페이스가 비정상적으로 커지는 주요 원인은 다음과 같습니다.

  • 지나치게 긴 보존 기간(retention period) 설정
  • 세그먼트 어드바이저(segment advisor) 데이터의 과도한 증가
  • 활성 세션 히스토리(ASH, Active Session History)의 과도한 증가

아래 섹션에서 각 문제에 대한 복구 조치 방법을 안내합니다.

AWR 보존 기간 점검

AWR(Automatic Workload Repository)은 문제 진단과 성능 튜닝을 위해 성능 통계를 수집하여 메모리와 데이터베이스 테이블에 저장합니다. 저장된 데이터는 설정된 보존 기간에 따라 시스템이 삭제합니다. 보존 기간이 너무 길면 그만큼 SYSAUX 테이블스페이스의 공간을 더 많이 차지하게 되므로, 적절한 보존 기간을 설정하는 것이 중요합니다.

다음 쿼리로 현재 보존 기간을 확인할 수 있습니다.

SQL> SELECT retention FROM dba_hist_wr_control;

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS 프로시저를 사용하면 보존 기간을 수동으로 변경할 수 있습니다. 아래 예제는 보존 기간을 5760분(4일: 4일 × 24시간 × 60분 = 5760분)으로 설정합니다.

SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT(RETENTION=>5760);

SYSAUX 테이블스페이스 내 최대 오브젝트 확인

다음 쿼리를 실행하여 테이블스페이스에서 가장 큰 공간을 차지하는 점유자를 식별합니다.

SQL> select OCCUPANT_NAME, SCHEMA_NAME, MOVE_PROCEDURE, SPACE_USAGE_KBYTES
     from v$sysaux_occupants;
Oracle SYSAUX 테이블스페이스 관리 방법 총정리

move 프로시저가 null이 아닌 경우, 해당 프로시저를 사용해 점유자를 다른 테이블스페이스로 이동할 수 있습니다.

다음은 WKSYS 점유자를 XYZ 테이블스페이스로 이동하는 예제입니다.

SQL> execute WKSYS.MOVE_WK('XYZ');

활성 세션 히스토리(ASH) 점검

ASH가 AWR 데이터에서 과도하게 공간을 소비하는지 확인하려면 다음 스크립트를 실행합니다.

SQL> @$ORACLE_HOME/rdbms/admin/awrinfo.sql
Oracle SYSAUX 테이블스페이스 관리 방법 총정리

ASH 사용률은 약 1.1% 수준이면 정상으로 간주됩니다. 사용률이 높다면 고아(orphaned) ASH 행을 삭제해야 합니다. 다음 쿼리로 고아 행 여부를 확인할 수 있습니다.

SQL> SELECT COUNT(1) Orphaned_ASH_Rows FROM wrh$_active_session_history a
     WHERE NOT EXISTS (SELECT 1 FROM wrm$_snapshot WHERE snap_id  = a.snap_id
     AND dbid= a.dbid AND instance_number = a.instance_number);
Oracle SYSAUX 테이블스페이스 관리 방법 총정리

조회 결과 값이 0보다 크면 아래 쿼리로 고아 행을 삭제합니다.

SQL> DELETE FROM wrh$_active_session_history a WHERE NOT EXISTS
     (SELECT 1 FROM wrm$_snapshot WHERE snap_id = a.snap_id
     AND dbid = a.dbid AND instance_number = a.instance_number);

그런 다음, 해제된 공간을 회수하기 위해 WRH$_ACTIVE_SESSION_HISTORY 테이블을 축소(shrink)합니다.

 SQL> alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;

결론

이 글에서는 SYSAUX 테이블스페이스의 개념을 소개하고, 기본 점유자들의 공간 증가를 모니터링·관리하는 방법과 테이블스페이스에 잘못 저장된 비기본(non-default) 오브젝트를 식별하는 방법을 안내했습니다. 위에서 설명한 예방 조치와 복구 절차를 꾸준히 적용하면 SYSAUX 테이블스페이스의 공간 문제를 효과적으로 관리할 수 있습니다.

궁금한 점이 있다면 피드백 탭을 통해 의견을 남기거나 질문을 보내주세요. 언제든지 대화를 시작할 수 있습니다.