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 항목 등을 확인해 보시면 된다.