AWR

▣ AWR(Automatic Workload Repository)

 - 1시간에 한번씩 snapshot을 생성하고 일주일이 지나면 삭제함

 

▣ Snapshot Retention : 저장기간

▣ Snapshot Interval : 생성 주기

 

S SYS> @fp

Enter value for key: WORKLOAD

OBJECT_NAME

------------------------------

DBMS_WORKLOAD_REPOSITORY      관리해주는 패키지

PRVT_WORKLOAD_NOPRIV

PRVT_WORKLOAD

WRI$_ADV_WORKLOAD

▣ 스냅샷 리스트

S SYS> @fv

Enter value for key: DBA%SNAP%

VIEW_NAME

------------------------------

DBA_SNAPSHOTS

DBA_SNAPSHOT_LOGS

DBA_REGISTERED_SNAPSHOTS

DBA_REGISTERED_SNAPSHOT_GROUPS

DBA_HIST_SNAPSHOT

DBA_HIST_SNAP_ERROR

6 rows selected.

S SYS> spool DBMS_WORKLOAD_REPOSITORY

S SYS> desc DBMS_WORKLOAD_REPOSITORY

S SYS> spool off

S SYS> !vi DBMS_WORKLOAD_REPOSITORY.lst

   

PROCEDURE CREATE_BASELINE

S SYS> desc DBA_REGISTERED_SNAPSHOT_GROUPS         SNAPSHOT ID 얻기위해 질의

S SYS> select * from DBA_REGISTERED_SNAPSHOT_GROUPS;

no rows selected

   

EM > Automatic Workload Repository

Snapshots 17

   

S SYS> select count(*) from DBA_HIST_SNAPSHOT;

  COUNT(*)

----------

        17

 S SYS> select count(*) from DBA_SNAPSHOTS;      현재 남아있는 스냅샷

  COUNT(*)            (Preserve Snapshot Sets : 0)

----------

         2

 스냅샷 생성 후 확인

 S SYS> select count(*) from DBA_HIST_SNAPSHOT;

  COUNT(*)

----------

        18

   

스크립트로 생성

S SYS>  !vi DBMS_WORKLOAD_REPOSITORY.lst

 PROCEDURE CREATE_SNAPSHOT

    Argument Name                  Type                    In/Out Default?

    ------------------------------ ----------------------- ------ --------

    FLUSH_LEVEL                    VARCHAR2                IN     DEFAULT

    FUNCTION CREATE_SNAPSHOT RETURNS NUMBER

    Argument Name                  Type

S SYS> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

PL/SQL procedure successfully completed.

S SYS> select count(*) from dba_hist_snapshot;

  COUNT(*)

----------  

        19

   

S SYS> desc DBA_HIST_SNAPSHOT

S SYS> select SNAP_ID,STARTUP_TIME from DBA_HIST_SNAPSHOT

S SYS> !vi DBMS_WORKLOAD_REPOSITORY.lst

PROCEDURE CREATE_BASELINE

 Argument Name                  Type                    In/Out Default? 

 ------------------------------ ----------------------- ------ --------

 START_SNAP_ID                  NUMBER                  IN

 END_SNAP_ID                    NUMBER                  IN

 BASELINE_NAME                  VARCHAR2                IN

 DBID                           NUMBER                  IN     DEFAULT

S SYS> exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(60,61,'TEST_BASE');

PL/SQL procedure successfully completed.

   

S SYS> @fv

Enter value for key: DBA%HIST_BASE%

VIEW_NAME

------------------------------

DBA_HIST_BASELINE

   

S SYS> @fv

Enter value for key: DBA%HIST_BASE%

VIEW_NAME

------------------------------

DBA_HIST_BASELINE

   

S SYS> select count(*) from DBA_HIST_BASELINE;

  COUNT(*)

----------

         1

   

S SYS> select * from DBA_HIST_BASELINE ;

      DBID BASELINE_ID BASELINE_NAME                                                   START_SNAP_ID

---------- ----------- ---------------------------------------------------------------- -------------

START_SNAP_TIME                                                             END_SNAP_ID

--------------------------------------------------------------------------- -----------

END_SNAP_TIME

---------------------------------------------------------------------------

1214711371           1 TEST_BASE                                                          60

09-JUN-09 04.00.14.272 PM                                                            61

10-JUN-09 01.00.25.511 PM

   

begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(17280,120); end;     => 설정

S SYS> select TO_CHAR(retention), TO_CHAR(snap_interval), most_recent_snap_time from sys.wrm$_wr_control;   => 확인

TO_CHAR(RETENTION) TO_CHAR(SNAP_INTER

------------------ ------------------

MOST_RECENT_SNAP_TIME

---------------------------------------------------------------------------

+00012 00:00:00.0  +00000 02:00:00.0

10-JUN-09 02.13.14.601 PM