AWR Report 정리본

## AWR REPORT SETTING ##
1. 특정 기간 동안의 Database level성능 @$ORACLE_HOME/rdbms/admin/awrrpt.sql

2. Database ID와 Instance ID를 지정후 성능 Report 작성 [AWR을 Export/Import 후 다수의 Database에 대한 통계
   자료가 존재시 혹은 RAC 환경 등에서 , Database ID 와 Instance ID 지정이 필요시]
   @ORACLE_HOME/rdbms/admin/awrrpti.sql
   
3. 특정 SQL문에 대한 성능 Report작성 : SQL_ID 값을 입력해야 한다.[1,2에서 주요 Top을 차지하는 SQL문에 대한 특정기간 동안의 Reporting]
   @awrsqrpt.sql
   
4. 특정 SQL문에 대한 성능 Report작성[특정 Database 혹은 Instance에 대해서, : 마찬가지로 SQL_ID 값을 입력해야 한다.]
   @awrsqrpi.sql
    성능 비교 자료
    
5. 두개의 특정 시간 사이의 생성[H/W 변경, Application 변경등 Database 성능에 영향을 줄수 있는 작업 전/후 비교시 사용]
   @awrddrpt.sql
   
6. 두개의 특정 시간 사이의 성능 비교 자료 생성[특정 Database 혹은 Instance 에 대해서]
   @awrddrpi.sql
SQL> select snap_interval, retention from dba_hist_wr_control;

--> SNAP_INTERVAL : 스냅샷 주기 , RETENTION : 보관주기

SQL> BEGIN
       dbms_workloadd_repository.modify_snapshot_settings(interval=>20,retention=>2*24*60);
       
       ----- interval=>20 스냅샷주기를 20분단위로 하겠다.
       ----- 보관주기를 2일로 하겠다.
       );
       END;
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (
     RETENTION => 0,
     INTERVAL => 0,
     DBID => -- SELECT DBID FROM V$DATABASE);
            
SQL> select snap_interval, retention from dba_hist_wr_control;

# SYS 스키마의 SYSAUX 테이블스페이스 내에 저장되어 있으며, WRM$_* 또는 WRH$_* 의 네임 포맷을 갖습니다.
  WRM$_* 테이블은 수집 대상 데이타베이스 및 스냅샵에 관련한 메타데이타 정보를, WRH$_* 테이블은 실제
  수집된 통계 정보를 저장하는데 사용됩니다.(WRH$_*의 H는 "HISTORICAL",WRM$_*의 M은 "METADATA"의 약자를 의미합니다.)
#################################################################################################################
  
## 타임 모델 (Time Model) ##

ORACLE 10G는 여러가지 자원에 관련한 실제 사용 시간을 확인하기 위한 타임 모델(time model)을 구현하고 있습니다. 전체
시스템 관련 소요 시간 통계는 V$SYS_TIME_MODEL 뷰에 저장됩니다. V$SYS_TIME_MODEL 뷰에 대한 쿼리 결과의 예가 아래와 같습니다.

SQL> SELECT STAT_NAME, VALUE FROM V$SYS_TIME_MODEL;

STAT_NAME    VALUE
---------  ---------
DB time    791943871  --> 인스턴스 시작 후 DB 사용 누적치

위에서 DB TIME 이라는 통계정보는 인스턴스 시작 이후 데이타베이스가 사용한 시간의 누적치를 의미합니다.
샘플 작업을 실행한 다음 다시 뷰를 조회했을 때 표시되는 DB TIME 의 값과 이전 값의 차이가 해당 작업을 위해
데이타베이스가 사용한 시간이 됩니다. 
시스템 / 데이타베이스 레벨이 아닌 세션 레벨의 통계를 원한다면 V$SESS_TIME_MODEL 뷰를 이용할 수 있습니다.
V$SESS_TIME_MODEL 뷰는 현재 연결 중인 ACTIVE/INACTIVE 세션들의 통계를 제공합니다.
세션의 SID 값을 지정해서 개별 세션의 통계를 확인할 수 있습니다.
  
또 새롭게 추가된 Active Session History(ASH)는 AWR과 마찬가지로 향후 분석 작업을 위해 세션 성능통계를 버퍼에 저장합니다.
V$ACTIVE_SESSION_HISTORY 등을 통해 조회된다는 사실입니다. 데이타는 1초 단위로 수집되며, 엑티브 세션만이
수집 대상이 됩니다. 버퍼는 순환적인 형태로 관리되며, 저장 메모리 용량이 가득 차는경우 오래된 데이타부터 순서대로 삭제됩니다.
이벤트를 위해 대기 중인 세션의 수가 얼마나 되는지 확인하려면 아래와 같이 조회하면 됩니다.

SQL> SELECT session_id||','||session_serial# SID, n.name, wait_time, time_waited
     FROM v$active_session_history a, v$event_name n
     WHERE n.event# = a.event#;
     
SID             NAME                    WAIT_TIME   TIME_WAITED
------   -----------------             ----------  -------------
166,1    log file parallel write            0           288
166,1    log file parallel write            0           223
161,1    control file sequential read       0         39401
166,1    log file parallel write            0         30367
146,271  null event                         0          2712
167,1    db file parallel write             0           385
  
위 쿼리는 이벤트 별로 대기하는 데 얼마나 많은 시간이 사용되었는지를 알려줍니다. 특정 wait 
이벤트에 대한 드릴다운을 수행할 때에도 ASH 뷰를 이용할 수 있습니다. 예를 들어, 세션 중 하나가
buffer busy wait상태에 있는 경우 정확히 어떤 세그먼트에  wait 이벤트가 발생했는지 확인하는 것이 가능합니다.
이때 ASH 뷰의 CURRENT_OBJ# 컬럼과 DBA_OBJECTS 뷰를 조인하면 문제가 되는 세그먼트를 확인할 수 있다.

ASH 뷰는 그 밖에도 병렬 쿼리 서버 세션에 대한 기록을 저장하고 있으므로, 병렬 쿼리의 WAIT 이벤트를 진단하는 데 유용하게 사용된다.
레코드가 병렬 쿼리의 slave process로서 활용되는 경우, coordinator server sesion 의 SID 는 QCC_SESSION_ID 컬럼으로 확인할 수 있다.
SQL_ID 컬럼은 WAIT 이벤트를 발생시킨 SQL구문의 ID 를 의미하며, 이 컬럼과 V$SQL 뷰를 조인하여 문제를 발생시킨 SQL구문을 찾아낼 수 있다.
CLIENT_ID 컬럼은 웹 어플리케이션과 같은 공유 사용자 환경에서 클라이언트를 확인하는 데 유용하며, 이값은 DBMS_SESSION.SET_INDENTIFIER를 
통해 설정이 가능하다.

## SNAPSHOT 찍기 ##
SQL> SELECT SYSDATE FROM DUAL;
SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME BEGIN, END_INTERVAL_TIME END FROM SYS.DBA_HIST_SNAPSHOT; --> 스냅샵 아이디를 본다.
SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; --> 수동으로 스냅샷을 찍는다.
SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME BEGIN, END_INTERVAL_TIME END FROM SYS.DBA_HIST_SNAPSHOT; --> 스냅샵 아이디를 본다.
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(10,15): --> SNAPSHOT ID를 범위로 지정해서 삭제한다.
SQL> SELECT SNAP_ID, STARTUP_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 1,2; --> 정렬해서 스냅샵 아이디를 본다.

## SNAPSHOT을 ID를 묶어서 이름을 지정한다. ##
SQL> SELECT DBID, BASELINE_ID, BASELINE_NAME, START_SNAP_ID, END_SNAP_ID FROM DBA_HIST_BASELINE;
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(17,20,'TEST_BASE_1');
SQL> SELECT DBID, BASELINE_ID, BASELINE_NAME, START_SNAP_ID, END_SNAP_ID FROM DBA_HIST_BASELINE;
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE('TEST_BASE_1');

 

### SQL QUERY ADVISOR ###
SQL> CONN sys/<password> AS SYSDBA
SQL> GRANT ADVISOR TO scott; ?? scott 유저에게 advisor 권한 부여
SQL> CONN scott/tiger
SQL> SELECT ename from emp e
WHERE job = 'SALESMAN'
AND comm not in (SELECT comm FROM emp
WHERE ename=e.ename AND comm is not null); ?? 문제의 쿼리
선택된 레코드가 없습니다.

SQL> DECLARE
2 l_task_id varchar2(30);
3 l_sql varchar2(2000);
4 BEGIN
5 l_sql := 'select ename from emp e where job = :job and comm not in
6 (select comm from emp where ename=e.ename and comm is not null)';
7 l_task_id := dbms_sqltune.create_tuning_task ( ?? dbms_sqltune 패키지를 사용.
8 sql_text => l_sql,
9 user_name => 'SCOTT', ?? [주의] 유저명은 대문자로 해야함.
10 scope => 'COMPREHENSIVE',
11 time_limit => 120,
12 task_name => 'sql_advisor_test14' ?? 테스크 이름
13 );
14 dbms_sqltune.execute_tuning_task ('sql_advisor_test14'); ?? 튜닝 테스크 실행
15 END;
16 /
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> set serveroutput on size 999999
SQL> set long 999999
SQL> select dbms_sqltune.report_tuning_task ('sql_advisor_test14') from dual; ?? 생성된 테스크 보고서 보기

 

http://cafe.naver.com/prodba

 출처 :