[DB튜닝]AWR 활용한 정보 얻기

오라클10g의 AWR기능을 활용하면 튜닝에 필요한 많은 정보를 얻을 수 있다.

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 2090 and 2100
                 and SQL_ID='1g8h29fbpv5yu';

   1.2 SQL_ID알고 최근 SNAP_ID를 구하여 활용 
        SQL> select max(SNAP_ID)  as SNAP_ID

               from dba_hist_sqlbind

              where SQL_ID='92rpbbrrb3bqj';

              SNAP_ID

        -------
        2099

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

2. 스냅샷 직접 생성(수동)
   ---------------------
    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.


3. SNAP_ID 범위 지정하여 삭제
   --------------------------
   SQL> exec dbms_workload_repository.drop_snapshot_range(10, 15);


4. 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

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