데이터베이스 유지 관리

I. 데이터베이스 유지 관리

    1. 사전 데이터베이스 유지 관리

    2. 사후 데이터베이스 유지 관리

II. Oracle 옵티마이저

III. 옵티마이저 통계

IV. 통계 수집 환경 설정

V. 통계 레벨

VI. AWR(Automatic Workload Repository)

    1. AWR  

    2. AWR Infrastructure

    3. AWR Baseline

    4. AWR 관리

VII. ADDM(Automatic Database Diagnostic Monitor)

    1. ADDM

    2. ADDM 권장 사항

VIII. Advisory 프레임워크

    1.  ADDM(Automatic Database Diagnostic Monitor)

    2. Memory Advisor

    3. MTTR(Mean-Time-To-Recover) Advisor

    4. Segment Advisor

    5. SQL Access Advisor

    6. SQL Tuning Advisor

    7. Undo Management Advisor

    8. Data Recovery Advisor

    9. SQL Repair Advisor

IX. DBMS_ADVISOR 패키지

---

I. 데이터베이스 유지 관리

1. 사전( Proactive ) 데이터베이스 유지 관리

    • 오라클 데이터베이스의 주요 요소를 포함하는 정교한 Infrastructure를 사용하면 사전 데이터베이스 유지 관리를 손쉽게

      수행할 수 있습니다.

    • AWR은 각 오라클 데이터베이스에 내장된 Repository로 오라클 데이터베이스 서버는 정기적으로 모든 주요 통계 및 작업

      로드 정보의 스냅샷을 만들고 이 데이터를 AWR에 저장합니다.

    • AWR은 관리자나 데이터베이스 자체에서 캡처된 데이터를 분석할 수 있습니다.

    • 데이터베이스는 자동화된 업무를 사용하여 정기적 백업, 옵티마이저 통계 Refresh, 데이터베이스 상태 검사 등과 같인

       일상적인 유지 관리 작업을 수행합니다. 

2. 사후 데이터베이스 유지 관리

    • 자동으로 해결할 수 없고 관리자에게 알려야 하는 문제에 대해 오라클 데이터베이스 서버는 Server-generated alert를

       제공합니다.

    • 오라클 데이터베이스 서버는 기본적으로 자체 모니터되며 문제를 통지하기 위한 alert을 보냅니다.

    • alert는 관리자에게 통지되며 보고된 문제를 해결하기 위한 권장 사항을 제공하기도 합니다.

    • 권장사항은 각각 서브 시스템을 담당하는 수많은 Advisor로부터 생성됩니다. 

II. Oracle 옵티마이저

    • 옵티마이저는 SQL 문에 대한 실행 계획을 생성하는 오라클 데이터베이스의 일부입니다.

    • 실행 계획은 결정은 SQL 문을 처리하는데 있어서 중요한 단계이며, 실행 시간에 큰 영향을 줄 수 있습니다.

    • 실행 계획이란 명령문을 실행하기 위해 순서대로 수행되는 일련의 작업을 말합니다.

    •  옵티마이저는 query에서 지정된 조건과 참조되는 객체와 관련된 많은 요소들을 고려합니다.

    • 옵티마이저에 필요한 정보는 아래와 같습니다.

        ৹ 시스템뿐만 아니라 스키마 객체에 대해 수집된 통계 정보 

        ৹ 딕셔너리의 정보

        ৹ WHERE 절 수식자

        ৹ 개발자가 제공한 힌트

III. 옵티마이저 통계

    • 옵티마이저 통계에는 테이블, 열, 인덱스 및 시스템 통계가 포함됩니다.

    • 테이블 및 인덱스에 대한 통계는 데이터 딕셔너리에 저장됩니다.

    • 통계는 실시간 데이터를 제공하기 위한것이 아니라 옵티마이저에 데이터 저장과 분산에 관해 통계적으로 올바른 스냅샷을

       제공하기 위한 것입니다.

    • 통계적으로 올바른 스냅샷을 사용하여 옵티마이저는 데이터 액세스 방법을 결정할 수 있습니다.

    • 수집되는 통계에는 아래의 내용이 포함됩니다.

        ৹ 데이터베이스 블록에 있는 테이블 또는 인덱스의 크기

        ৹ 행 수

        ৹ 평균 행 크기 및 체인수 ( 테이블에만 해당 )

        ৹ 삭제된 최하위 행의 높이 및 수 ( 인덱스에만 해당 )

    • 데이터가 삽입 및 수정되면 통계에서 수집되는 값이 변경됩니다.

    • 데이터 분상 통계를 실시간으로 유지 관리하면 성능에 상당한 영향을 미치게 되므로 통계는 테이블 및 인덱스에서 통계를

       주기적으로 수집하여 갱신합니다.

    • 옵티마이저 통계는 기본적으로 하루에 한 번씩 미리 정의된 유지 관리 윈도우 동안 실행되는 자동 유지 관리 작업에 의해

       자동으로 수집됩니다.

    • 시스템 통계는 옵티마이저가 사용하는 운영체제의 특성으로 자동으로 수집되지 않습니다.

    • 옵티마이저 통계는 AWR 스냅샷에서 수집되는 데이터베이스 성능 통계와는 다릅니다.

 

IV. 통계 수집 환경 설정

    • DBMS_STATS.GATHER_*_STATS 프로시저를 다양한 레벨에서 호출하여 전체 데이터베이스 또는 테이블등의 개별 객체에

       대한 통계를 수집할 수 있습니다.

    • GATER_*_STATS 프로시저를 호출하면 다양한 파라미터가 기본값으로 허용되고, 제공되는 기본값은

      데이터베이스에 포함된 대부분의 객체에서 문제없이 작동합니다.

    • Oracle 11g에서는 각 객체에 대해 수동으로 작업을 실행하는 대신 객체, 스키마 또는 데이터베이스에 대한 Global 환경

      설정을 지정할 때 사용되고, 이러한 프로시저는 기본값으로 허용되는 모든 파라미터에 적용됩니다.

    • 모든 객체 환경 설정은 단일 테이블에 보관됩니다.

    • 스키마 레벨에서 환경설정을 변경하면 이전에 테이블 레벨에서 설정한 환경 설정이 겹쳐 쓰입니다.

    • 객체 레벨에서 설정하지 않은 모든 환경 설정은 Global 레벨 환경으로 설정됩니다

    • 테이블, 스키마 및 데이터베이스 레벨에서 DBMS_STATS.DELETE_*_PREFS 프로시저를 사용하면 환경 설정이 삭제될 수

      있습니다

    • DBMS_STATS.RESET_PARAM_DEFAULTS 프로시저를 사용하면 Global 환경 설정을 권장 값으로 재설정할 수 있습니다.

    • 환경 설정

        ৹ CASCADE

           - 인덱스 통계가 테이블 통계 수집의 일부로 수집되는지 여부를 결정합니다.

        ৹ DEGREE

           - 통계 수집 시 사용되는 병렬도를 설정합니다

        ৹ PUBLISH

           - 통계를 딕셔너리에 게시할 것인지 아니면 전용 영역에 저장할 것이지를 결정하는 데 사용됩니다

           - DBA가 PUBLISH_PENDING_STATS 프로시저를 사용하여 통계 정보를 데이터 딕셔너리에 게시하기 전에 검증할 수

             있습니다

        ৹ STALE_PERCENT

           - 객체의 통계가 오래된 것인지를 판단할 때 기준으로 삼을 임계값 레벨을 결정하는 데 사용됩니다.

           -  이 값은 마지막 통계 수집 이후 수정된 행의 비율입니다.         

        ৹ INCREMENTAL

           -Partitioning 된 테이블의 Global 통계를 Incremental 방식으로 수집하는 데 사용됩니다.

        ৹ METHOD_OPT

           - 열 통계를 수집하는 데 사용되는 열 및 히스토그램 파라미터를 결정합니다

        ৹ GRANULARITY

           - 수집할 통계의 세분화 정도를 결정합니다.

           - partition table의 경우에만 해당합니다.

        ৹ NO_INVALIDATE

           - 커서를 무효화할지 여부를 결정하는 데 사용됩니다.

        ৹ ESTIMATE_PERCENT

           - 올바른 통계를 얻기 위해 샘플링할 행의 수를 결정하는 데 사용됩니다.

           - 테이블에 있는 행의 수의 비율입니다.

 

V. 통계 레벨

    •  STATISTICS_LEVEL 초기화 파라미터는 자동 유지 관리 업무를 포함하여 다양한 통계 및 Advisor 캡처를 제어합니다.

    •  자동 유지 관리 업무에는 옵티마이저 통계 수집 업무가 포함됩니다

    •  STATISTICS_LEVEL 파라미터는 다음 레벨로 설정할 수 있습니다.   

        ৹  BASIC

            - AWR 통계 및 metric이 계산되지 않습니다.

            - 자동 옵티마이저 통계 업무와 모든 Advisor 및 Server-generated alert는 비활성화됩니다. 

        ৹  TYPICAL

            - 데이터베이스 자체 관리에 필요한 주요 통계가 수집됩니다.

            - 통계는 일반적으로 오라클 데이터베이스 동작을 모니터하는데 필요한 항목을 나타냅니다.

            - 오래되거나 잘못된 통계로 인해 SQL 문의 성능이 저하될 가능성을 줄여주는 자동 통계 수집이 포함됩니다. 

        ৹ ALL

             - 가능한 모든 통계가 캡처됩니다

             - 캡처 레벨을 사용하는 경우 시간이 지정된 OS 통계 및 계획 실행 통계가 추가됩니다

             - 통계는 대부분의 경우 필요하지 않으며 최적의 성능을 유지하려면 비활성화해야 합니다.

             - 특정 진단 테스트의 경우 이러한 통계가 필여할 수도 있습니다. 

VI. AWR(Automatic Workload Repository)

1. AWR 

    •  Oracle Database의 구성 요소가 문제 감지 및 자체 튜닝 목적을 위해 통계를 수집, 유지 관리 및 활용할 수 있도록 서비스를

        제공하는 Infrastructure입니다

    •  데이터베이스 통계, Metrics 등에 대한 데이터 웨어하우스로 간주할 수 있습니다.

    •  기본적으로 데이터베이스는 SGA에서 60분마다 자동으로 통계 정보를 캡처하여 스냅샷의 형태로 AWR에 저장합니다.

    •   스냅샷은 MMON이라는 백그라운드 프로세스에 의해 디스크에 저장됩니다.

    •  기본적으로 스냅샷은 8일간 보관되고, 스냅샷 간격과 Retention 간격 모두 수정할 수 있습니다.

    •  AWR에는 수백 개의 테이블이 포함되어 있으며 이 테이블은 모두 SYSMAN 스키마에 속해 있으며 SYSAUX 테이블 스페이스에

       저장되어 있습니다.

 

2. AWR Infrastructure

    • 통계의 메모리 버전은 MMON 백그라운드 프로세스에 의해 정기적으로 디스크에 전달됩니다

    •  AWR을 통해 오라클 데이터베이스는 DBA의 개입 없이 기록 통계 데이터를 자동으로 캡처할 수 있도록 합니다.            

    • AWR Infrastructure는 크게 두 부분으로 구성됩니다

        ৹ Oracle Database 구성 요소에서 통계 수집에 사용하는 In-memory 통계 수집 설비

           - 이 통계는 성능상의 이유로 메모리에 저장됩니다

           - 메모리에 저장된 통계는 Dynamic Performance 뷰를 통해 액세스 할 수 있습니다.

        ৹ 설비의 지속 부분을 나타내는 AWR 스냅샷

           - AWR 스냅샷은 데이터 딕셔너리 뷰와 Enterprise Manager Database Control을 통해서 액세스 할 수 있습니다.

    • 통계는 다음의 이유로 인해 지속 저장 영역에 저장됩니다

        ৹ 통계는 Instance Crash가 발생해도 유지되어야 합니다

        ৹ 일부 분석에는 Baseline 비교를 위해 과거 기록 데이터가 필요합니다

        ৹ 메모리 오버플로우가 발생할 수 있습니다. 

        ৹ 메모리 부족으로 인해 오래된 통계가 새 통계로 바뀔 때 나중에 사용하기 위해 이전 데이터를 저장할 수 있습니다.

 

3. AWR Baseline

    • 일반적으로 AWR baseline은 AWR에서 태그를 지정하고 보존하는 중요 기간에 대한 스냅샷 데이터의 집합입니다.

    • Baseline은 스냅샷의 쌍으로 정의되며, 스냅샷은 스냅샷 시퀸스 번호 또는 시작 및 종료 시간으로 식별됩니다.

    • 각 스냅샷 집합에는 시작 및 종료 스냅샷이 있고 그 사이에 모든 스냅샷이 포함되어 있습니다.

    • 스냅샷 집합은 스냅샷 데이터를 보유하는데 사용됩니다

    • 기본적으로 스냅샷 집합에 포함된 스냅샷은 스냅샷 집합이 삭제될 때까지 유지됩니다.

    • 만기 값은 스냅샷을 보유할 일수로 설정할 수 있습니다.

    • Baseline은 유저가 제공한 이름으로 식별됩니다.

    • CREATE_BASELINE 프로시저를 실행하여 스냅샷 집합에서 Baseline을 생성하고 이름 및 스냅샷 식별자의 쌍을 지정합니다.

    • 데이터베이스가 존재하는 동안 고유한 Baseline 식별자가 새로 생성된 Baseline에 지정됩니다

    • 현재 의 시스템 동작과 비교하는 데 사용할 과거의 대표적인 기간으로부터 Baseline을 설정합니다.

    • Database Controlfile에서 Baseline을 사용하여 임계값 기반 Alert를 설정할 수도 있습니다.

    • CREATE_BASELINE 프로시저의 만료 파라미터를 사요하여 만료 시간을 일수로 설정할 수 있습니다.

 

4. AWR 관리

    • AWR 설정에는 Retention 기간, 수집 간격 및 수집 레벨이 포함됩니다.

    • 설정을 임으로 즐이면 Advisor를 포함하여 AWR에 종속된 구성 요소의 기능이 떨어집니다.

    • 설정을 늘리면 Advisor의 권장 기능이 향상되지만 대신 스냅샷 저장에 필요한 공간이 늘어나고 스냅샷 정보 수집에 소모되는

       성능 요구 사항이 증가합니다.

VII. ADDM(Automatic Database Diagnostic Monitor)

1. ADDM

    •  다른 advisor와 달리 ADDM ( 자동 데이터베이스 진단 모니터 )는 각 AWR 스냅샷 생성 후마다 자동으로 실행됩니다.

    • 스냅샷이 생성될 때마다 ADDM은 마지막 두 스냅샷에 해당하는 기간을 분석합니다.

    •  ADDM은 Instance를 사전에 모니터 하여 심각한 문제로 발전하기 전에 대부분의 병목 지점을 감지합니다.

    •  대부분의 경우 ADDM은 감지된 문제의 해결 방법을 제시하며 이 방법으로 얻게 되는 이점을 수량화하여 보여주기도 합니다.

    • ADDM에 의해 감지되는 일반적인 문제는 다음과 같습니다

        ৹ CPU 병목 지점

        ৹ 부실한 Oracle Net 연결 관리

        ৹ Lock 경합

        ৹ 입출력 ( I/O ) 영향

        ৹ 너무 작은 데이터베이스 Instance 메모리 구조 크기

        ৹ 로드량이 많은 SQL 문

        ৹ 높은 PL/SQL 및 JAVA 시간

        ৹ 높은 체크포인트  로드 및 원인

2. ADDM 권장 사항

    • ADDM은 시스템에 대한 다양한 변경 사항을 고려합니다

    • 다음과 같은 권장 사항이 포함될 수 있습니다.

        ৹ 하드웨어 변경

            - CPU 추가 또는 I/O 서브 시스템 구성 변경

        ৹ 데이터베이스 구성

            - 초기화 파라미터 설정 변경

        ৹ 스키마 변경

            - 테이블이나 인덱스에 Hash-partitioning 수행 또는 ASSM (자동 세그먼트 공간 관리 ) 사용

        ৹ 응용 프로그램 변경

           - 수퀸스에 캐시 옵션 사용 또는 바인드 변수 사용

        ৹ 다른 Advisor 사용

           - 로드량이 많은 SQL에 SQL Tuning Advisor 실행 또는 자주 사용되는 객체에 Segment Advisor 실행 

VIII. Advisory 프레임워크

    • Advisor는 개별 서버 구성 요소의 리소스 활용 및 성능에 대한 유용한 피드백을 제공합니다.

    • Advisor Infrastructure가 제공하는 주요 장점은 다음과 같습니다.

        ৹ 모든 Advisor가 동일한 인터페이스를 사용합니다

        ৹ Advisor가 Workload Repository를 사용하여 공통 데이터 소스와 결과 저장 영역을 가집니다.

 

1.  ADDM(Automatic Database Diagnostic Monitor)

    • ADDM은 60분마다 데이터베이스 성능을 검토하는 서버 기반 Expert입니다

    • ADDM의 목표는 발생 가능성이 있는 시스템 병목 지점을 조기에 감지하여 시스템 성능이 현저하게 떨어지기 전에 해결할

       방법을 제시하는 것입니다.

2. Memory Advisor

    • Memory Advisor는 데이터베이스 Instance가 사용하는 총메모리에 대한 가장 효율적인 설정을 결정할 수 있도록 하는

       다양한 Advisor 기능의 모음입니다,

    • SGA에는 Shared Pool, 데이터베이스 버퍼 캐시,  Java Pool 및 Streams Pool에 대한 Advisor 집합이 있습니다.

    • PGA용 Advisor도 있습니다.

    • Memory Advisor는 Advisor 기능 이외에도 Large Pool 및 Java Pool에 대한 중앙 제어점을 제공합니다.

3. MTTR(Mean-Time-To-Recover) Advisor

    • MTTR Advisor를 사용하여 데이터베이스에서 Instance Crash 후에 Recovery에 필요한 시간을 설정할 수 있습니다.

4. Segment Advisor

    • Segment Advisor는 필요한 공간보다 많은 공간을 소비하는 테이블 및 인덱스를 찾습니다

    • 테이즐 스페이스 또는 스키마 레벨에서 비효율적인 공간 소비가 있는지 검사하고 공간 소비를 줄일 수 있는 스크립트를

      생성합니다.

5. SQL Access Advisor

    • 주어진 기간 내에 실행되는 모든 SQL문을 분석하여 성능 향상에 도움이 되는 추가 인덱스 또는 Materialized view를

       생성하도록 제안합니다.

6. SQL Tuning Advisor

    • 개별 SQL 문을 분석하여 성능 향상을 위한 권장 사항을 제공합니다.

    •  명령문 재작성, Instance 구성 변경 또는 인덱스 추가와 같은 작업이 권장사항에 포함될 수 있습니다.

    •  SQL Tuning Advisor는 직접 호출할 수 없으며 대신 Top SQL 또는 Top Session과 같은 도구 내에서 호출하여 성능에 높은

       영향을 미치는 SQL 문을 최적화할 수 있습니다. 

7. Undo Management Advisor

    • Undo Management Advisor를 사용하여 주어진 Retention 기간을 지원하는데 필요한 언두 테이블스페이스 크기를 결정할

      수 있습니다.

8. Data Recovery Advisor

    • 지속 Data Failure를 자동으로 진단하고, 유저에게 복구 옵션을 제공하며, 유저의 요청 시 복구를 실행합니다.

    • Data Recocery Advisor는 MTTR을 줄이고 자동화된 데이터 Recovery를 위한 중앙 집중식 도구를 제공하는데 사용됩니다.

9. SQL Repair Advisor

    • Automatic Diagmostic Repository에서 문제를 생성하는 심각한 오류로 인해 SQL 문이 실패한 후에는 SQL Repair

      Advisor를 실행합니다.

    • Advisor는 명령문을 분석하여 대부분의 경우에는 패치를 사용해 명령문을 복구할 것 을 권장합니다.

    • 권장사항을 구현하려면 적용되는 SQL 패치는 Query 옵티마이저가 이후의 실행에 대해 다른 실행 계획을 선택하도록 하여

       Failure를 우회합니다.

    • 이 작업은 SQL 문 자체를 변경하지 않고 수행됩니다.

IX. DBMS_ADVISOR 패키지

    • DBMS_ADVISOR 패키지에는 모든 Advisor 모듈에 대한 모든 상수와 프로시저 선언이 포함되어 있습니다

    • 패키지를 사용하여 명령행을 통해 작업을 실행할 수 있습니다.

    • Advisor 프로시저를 실행하려면 ADVISOR 권한이 있어야 합니다.

    • ADVISOR 권한이 있으면 Advisor 프로시저와 뷰 모두에 완전히 액세스 할 수 있게 됩니다.