[오라클]AWR Report 생성 및 분석

DBA 계정으로 sqlplus 로그인하여 실행 
  
(성능통계 수집 기간 및 보관주기 보기) 
select snap_interval, retention from dba_hist_wr_control; 

  

(20분 간격으로 2일보관주기로 변경) 

begin 

  dbms_workload_repository.modify_snapshot_settings ( 

      interval => 20, 

      retention => 2*24*60 

  ); 

end; 



* AWR snapshot 생성 (시작과 종료시 아래 처럼 실행) 
- 1노드에 대해서 snapshot 됨 
execute dbms_workload_repository.create_snapshot; 
  
- 전체 노드에 대해서 snapshot 됨 
exec sys.dbms_workload_repository.create_snapshot('ALL'); 
  
  
* report 출력시 (해당 결과파일 local로 받아서 *.html형식으로) 
$ORACLE_HOME/rdbms/admin/awrrpt.sql 수행 
  실행시 입력 값 
  Enter value for report_type: text    << text format 으로 보고서 생성 
  Enter value for num_days: 1  << 최근 하루 동안의 snap 조회 
  Enter value for end_snap: 1271  << 09:08 ~ 14:00 구간에 대한 조회 요청 
  Enter value for report_name: awrrpt_1_1266_1271.txt  << 보고서 이름 지정 
  
  
* 분석 point 

1.  Load Profile 분석 
Snap 구간 동안의 DBMS 성능 통계를 보여준다. 기본적인 DBMS 성능의 Baseline을 제공한다. 
초당 Transactions  및 SQL 호출 수 등을 통해 DBMS의 Activity 를 분석한다. 
  
2.  메모리 성능 분석 
Shared Pool 및 Buffer Cache의 Hit Rate 등 메모리 활용의 적절성을 분석한다. 
  
3.  이벤트 분석 
CPU time이 높은 비율로 유지되어야 하며 기타 I/O 를 위한 Wait이나 Lock 발생여부를 분석한다. 
  
4.  TOP SQL 분석 
SQL ordered by Gets 항목 분석을 통해 I/O를 많이 유발하는 Bad SQL을 찾아서 튜닝한다 
============================================================================================== 
============================================================================================== 
Knowledge 등록 건 (LGCNS 공공 DA 김승철 차장) ========================================================== 
  
오라클10g의 AWR기능을 활용하면 튜닝에 필요한 많은 정보를 얻을 수 있다. 
MMON 백그라운드 프로세스와 여러 개의 슬레이브 프로세스를 통해 자동으로 매 시간마다 스냅샷 정보를 수집한다. 
수집된 데이타는 7일 후 자동으로 삭제된다. 스냅샷 주기와 보관 주기는 사용자에 의해 설정 가능하다. 

1. AWRRPT에서 SNAP_ID와 SQL_ID로 바인드 변수 찾기 
  ------------------------------------------------- 

  1.1 awrrpt의 레포트파일의 SNAP_ID 및 SQL_ID를 활용 
        SQL> select * 
                from dba_hist_sqlbind 
              where SNAP_ID=2099 
                and SQL_ID='92rpbbrrb3bqj'; 

        SQL> select * 
                from dba_hist_sqlbind 
              where SNAP_ID between 18797 and 18814  -- between [Begin Snap] and [End Snap] 
                and SQL_ID='1g8h29fbpv5yu'; 

  1.2 SQL_ID알고 최근 SNAP_ID를 구하여 활용 
        SQL> select max(SNAP_ID) from dba_hist_sqlbind where SQL_ID='92rpbbrrb3bqj'; 
                2099 

        SQL> select * 
              from dba_hist_sqlbind 
              where SNAP_ID=2099 
                and SQL_ID='92rpbbrrb3bqj'; 


2. 실행중인 SQL을 SID로 찾아 PLAN 보기 
  ------------------------------------ 

  SQL> select 'select * from TABLE(dbms_xplan.display_cursor('''||sql_id||''','||SQL_CHILD_NUMBER||')) ;' 
          from  v$session 
        where  sid = 4194; 

  결과 : select * from TABLE(dbms_xplan.display_cursor('bqxzbkrtt26gj',0)) ; 

  -- 결과를 실행 
  SQL> select * from TABLE(dbms_xplan.display_cursor('bqxzbkrtt26gj',0)) ;                                      


3. V$SESSION의 SQL_HASH_VALUE로 SQL 찾기 
  -------------------------------------------- 

  SQL> select sql_text 
          from v$sqltext 
        where hash_value = 2555467871 
        order by piece; 


4. /*+ gather_plan_statistics */ 힌트와 dbms_xplan.display_cursor 패키지를 이용한 플랜보기 
  --------------------------------------------------------------------------------------- 

  - statistics_level = all 인 경우에는 Hint 불필요 
  - SQL 실행 시 Row Source 레벨의 통계 정보 수집 
  - E-Rows(예측 Row 수)와 A-Rows(실제 Row 수)의 비교를 통해 통계정보의 오류를 파악할 수 있음 
  - Optimizer가 얼마나 합리적인 실행 계획을 세우느냐는 Cardinality, 즉 예상 Row수의 정확성에 달려 있음 

  SQL> select /*+ gather_plan_statistics */ * from tb_test where id < 1000; 
  또는 
  SQL> alter session statistics_level = ALL; 
  SQL> select * from tb_test where id < 1000; 

  SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 
  ---------------------------------------------------------------------------------------------------------
  | Id  | Operation                  | Name            | Starts | E-Rows | A-Rows |  A-Time  | Buffers | 
  ---------------------------------------------------------------------------------------------------------
  |  1 |  TABLE ACCESS BY INDEX ROWID|  TB_TEST        |      1 |      1 |  1000 |00:00:00.01 |    140 | 
  |*  2 |  INDEX RANGE SCAN          |  TB_TEST_IDX    |      1 |      1 |  1000 |00:00:00.01 |      70 | 
              : 
  - 주요 항목 설명 
    . E-Rows: 예측 Row 수 
    . A-Rows: 실제 Row 수 
    . A-Time: 실제 소요 시간 
    . Buffers: Logical Reads 

참고) dbms_xplan.display_cursor(sql_id, child_number, format)의 format 종류 
  - Basic 
  - Typical 
  - Outline 
  - All 
  - Advanced 
  * allstats last 
  * +peeked_binds : 예) dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +peeked_binds'); 


5. 스냅샷 직접 생성(수동) 
  --------------------- 

    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> SELECT snap_id, startup_time FROM dba_hist_snapshot ORDER BY 1,2; 

            SNAP_ID    STARTUP_TIME 
            ---------- -------------------- 
            10        2007/12/19 10:27:32.000 <-- 삭제할 첫번째 스냅샷 
            11        2007/12/19 10:27:32.000 
            12        2007/12/19 10:27:32.000 
            13        2007/12/19 10:27:32.000 
            14        2007/12/19 10:27:32.000 
            15        2007/12/19 10:27:32.000 <-- 삭제할 마지막 스냅샷 
            16        2007/12/19 10:27:32.000 
            17        2007/12/19 10:27:32.000 

            12 rows selected. 


6. SNAP_ID 범위 지정하여 삭제 
  -------------------------- 

    SQL> exec dbms_workload_repository.drop_snapshot_range(10, 15); 


7. AWR 스냅샷 주기와 보관 주기 설정 
  -------------------------------- 

  1] 스냅샷주기(1시간,default) 및 보관주기(7일,default) 조회 
      SQL> SELECT snap_interval , retention FROM dba_hist_wr_control; 
            SNAP_INTERVAL              RETENTION 
            -------------------------- --------------------------- 
            +00000 01:00:00.0          +00007 00:00:00.0 

  2] 스냅샷주기(10분) 및 보관주기(15일)을 변경 
      SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS 
                                                  (interval  => 10,        -- 분단위 
                                                  retention => 15*24*60); -- 15일 

  3] 스냅샷주기(10분) 및 보관주기(15일) 조회 
      SQL> SELECT snap_interval , retention FROM dba_hist_wr_control; 
            SNAP_INTERVAL              RETENTION 
            -------------------------- --------------------------- 
            +00000 00:10:00.0          +00015 00:00:00.0 

8. AWR Report 생성 
  ---------------- 

  과거의 DB의 상태를 awrrpt를 이용하여 확인할 수 있다. 

  SQL> connect / as sysdba 

  SQL> @?/rdbms/admin/awrrpt.sql 실행 

            : 

  Enter value for report_type: html 입력 

            : 

  Enter value for num_days: 8 입력 

  Listing the last 8 days of Completed Snapshots 

                                                                                    Snap 
  Instance    DB Name        Snap Id    Snap Started        Level 
  ------------ ------------ --------- ------------------ ----- 
  DB_SID      DB_NAME      20159 01 Aug 2008 00:00        1 
                                          20160 01 Aug 2008 01:00      1 
                                          20161 01 Aug 2008 02:00      1 
                                          20162 01 Aug 2008 03:00      1 
                                          20163 01 Aug 2008 04:00      1 
                                          20164 01 Aug 2008 05:00      1 
                                          20165 01 Aug 2008 06:00      1 
                                          20166 01 Aug 2008 07:00      1 
                                          20167 01 Aug 2008 08:00      1 
                                          20168 01 Aug 2008 09:00      1 
                                              : 

                                          20333 08 Aug 2008 06:00      1 
                                          20334 08 Aug 2008 07:00      1 
                                          20335 08 Aug 2008 08:00      1 
                                          20336 08 Aug 2008 09:00      1  --- begin 
                                          20337 08 Aug 2008 10:00      1 
                                          20338 08 Aug 2008 11:00      1 
                                          20339 08 Aug 2008 12:00      1  --- end 
                                          20340 08 Aug 2008 13:00      1 
                                          20341 08 Aug 2008 14:00      1 
                                          20342 08 Aug 2008 15:00      1 

  Enter value for begin_snap: 20336 입력 --- begin 

  Enter value for end_snap  : 20339 입력  --- end 


  Enter value for report_name: awrrpt_20080808_09-12_DB_SID.html 입력 

    awrrpt_20080808_09-12_DB_SID.html 파일을 ftp로 pc로 다운로드 받은 후 
  열어서 SQL ordered by Elapsed Time 항목 등을 확인해 보시면 된다. 

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR). 
AWR Features 

The AWR is used to collect performance statistics including: 

Wait events used to identify performance problems. 
Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views. 
Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view. 
Some system and session statistics from the V$SYSSTAT and V$SESSTAT views. 
Object usage statistics. 
Resource intensive SQL statements. 
The repository is a source of information for several other Oracle 10g features including: 

Automatic Database Diagnostic Monitor 
SQL Tuning Advisor 
Undo Advisor 
Segment Advisor 
Snapshots 
By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using: 






BEGIN 
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( 

retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL. 

interval => 30); -- Minutes. Current value retained if NULL. 

END; 
/ 

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics. 







Extra snapshots can be taken and existing snapshots can be removed using: 


EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; 

BEGIN 
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range ( 
low_snap_id => 22, 
high_snap_id => 32); 
END; 
/ 

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view. 







Baselines 


A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing like: 


BEGIN 

DBMS_WORKLOAD_REPOSITORY.create_baseline ( 
start_snap_id => 210, 
end_snap_id => 220, 
baseline_name => 'batch baseline'); 
END; 
/ 

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted: 



BEGIN 

DBMS_WORKLOAD_REPOSITORY.drop_baseline ( 
baseline_name => 'batch baseline', 
cascade => FALSE); 
END; 
/ 

Baseline information can be queried from the DBA_HIST_BASELINE view. 







Workload Repository Views 


The following workload repository views are available: 

 V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second. 
V$METRIC - Displays metric information. 
V$METRICNAME - Displays the metrics associated with each metric group. 
V$METRIC_HISTORY - Displays historical metrics. 
V$METRICGROUP - Displays all metrics groups. 
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history. 
DBA_HIST_BASELINE - Displays baseline information. 
DBA_HIST_DATABASE_INSTANCE - Displays database environment information. 
DBA_HIST_SNAPSHOT - Displays snapshot information. 
DBA_HIST_SQL_PLAN - Displays SQL execution plans. 
DBA_HIST_WR_CONTROL - Displays AWR settings. 
Workload Repository Reports 


Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows: 


@$ORACLE_HOME/rdbms/admin/awrrpt.sql 



@$ORACLE_HOME/rdbms/admin/awrrpti.sql 


The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly. 







Enterprise Manager 


The automated workload repository administration tasks have been included in Enterprise Manager. The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link, then the "Workload Repository" link under the "Workload" section. The page allows you to modify AWR settings or manage snapshots without using the PL/SQL APIs.