Oracle Advisor

● ADVISOR H2

- ADDM (Automatic Database Diagnostic Monitor) (10g) H3

- SQL Tuning Advisor (10g) H4

- SQL Access Advisor (Optimizing Access Paths)

Tune MView

- Data Recovery Advisor (RMAN)

- Memory Advisors

Memory Advisor

SGA Advisor

Buffer Cache Advisor

Shared pool Advisor

PGA Advisor

- Undo Advisor (10g)

- MTTR Advisor

- Optimizer Statistics Advisor

- Space Advisor

- Segment Advisor (10g)

- SQL Repair Advisor

- Compression Advisor

- SPM Evolve Advisor

- Default Advisor

- SQL Workload Manager

- In-Memory Advisor

■ADDM

■ 개요

ADDM는 AWR의 데이터를 분석하여 성능 병목 현상을 식별

각 문제에 대한 근본 원인(root cause)을 찾고, 개선을 위한 권장 사항(recommendations)을 제공

STATISTICS_LEVEL=TYPICAL 또는 ALL로 설정된 경우, AWR 스냅샷이 생성될 때마다 ADDM 분석 작업이 자동으로 수행되고 해당 결과 및 권장 사항이 DB에 저장됨

주요 ADDM 분석 항목:

CPU load

Memory usage

I/O usage

Resource intensive SQL

Resource intensive PL/SQL and Java

RAC issues

Application issues

Database configuration issues

Concurrency issues

Object contention

주요 권장사항(recommendations) 항목:

Hardware changes(변경사항)

Database configuration changes

Schema changes

Application changes

Using other advisor

-

전제조건:

STATISTICS_LEVEL = TYPICAL or ALL
CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC+TUNING

■ addm report 수행 권한:

sqlplus / as sysdba
GRANT ADVISOR TO REPORT_USER; --> sql tuning advisor API를 수행하기 위한 권한
GRANT SELECT_CATALOG_ROLE TO REPORT_USER;
GRANT EXECUTE ON dbms_workload_repository TO REPORT_USER;