Common skills of awr

Awr is retained for 10 days and collected hourly:

Begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (14400,060); end

Collect awr manually once:

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ()

Perform a snapshot manually:

Exec dbms_workload_repository.create_snapshot; (to memorize this, go to the manual when you use it, lose face)

Create an AWR baseline

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id,end_snap_id, baseline_name)

@? / rdbms/admin/awrrpt generates AWR report

@? / rdbms/admin/awrddrpt AWR comparison report

@? / rdbms/admin/awrgrpt RAC Global AWR

Dbms_workload_repository manages AWR manually

1. Modify snapshot settings

[sql] view plaincopyprint?

Check the number of days retained by awr and the collection frequency:

Sys@ORCL > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL-1301772781 + 00000 01 DEFAULT 00.0 + 00008 00lv 0015 00.0

Modify the number of days retained by awr and the collection frequency:

Sys@ORCL > exec dbms_workload_repository.modify_snapshot_settings (retention = > 15,1440) interval = > 30)

The PL/SQL process completed successfully. Sys@ORCL > select * from dba_hist_wr_control DBID SNAP_INTERVAL RETENTION TOPNSQL-1301772781 + 00000 00000 30 purl 00.0 + 00015 00:00:00.0 DEFAULT

Interval: when the interval is set to 0, oracle will disable automatic and manual snapshots.

Retention: when the save time is set to 0, oracle will permanently save this snapshot.

2. Create and delete snapshots

[sql] view plaincopyprint?

Manually perform an awr snapshot:

Sys@ORCL > exec dbms_workload_repository.create_snapshot ()

The PL/SQL process completed successfully.

Delete the awr report (specify the delete scope):

Sys@ORCL > exec dbms_workload_repository.drop_snapshot_range (low_snap_id = > 10 million snapshots id = > 12)

The PL/SQL process completed successfully. Sys@ORCL > exec dbms_workload_repository.drop_snapshot_range (low_snap_id = > 628 grained snapshots id = > 629), and the PL/SQL process has been completed successfully.

The dba_hist_snapshot view lists information about available snapshots.

SELECT * FROM dba_hist_snapshot

3. Create and delete baselines

[sql] view plaincopyprint?

Sys@ORCL > exec dbms_workload_repository.create_baseline (start_snap_id = > 630 department snapshots id = > 635 department name = > 'baseline'), and the PL/SQL process has been completed successfully. Sys@ORCL > select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline BASELINE_NAME START_SNAP_ID END_SNAP_ID- -baseline 630,635 SYSTEM_MOVING_WINDOW 636 637 sys@ORCL > exec dbms_workload_repository.drop_baseline (baseline_name = > 'baseline' Cascade = > true) The PL/SQL process completed successfully. Sys@ORCL > select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline BASELINE_NAME START_SNAP_ID END_SNAP_ID- -SYSTEM_MOVING_WINDOW 636637 sys@ORCL > exec dbms_workload_repository.create_baseline (to_date ('2013-11-03 00001) 'yyyy-mm-dd hh34:mi:ss'), to_date (' 2013-11-03 06mm hh34:mi:ss' hh34:mi:ss'), 'base line2') The PL/SQL process completed successfully. Sys@ORCL > select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline BASELINE_NAME START_SNAP_ID END_SNAP_ID- -base line 685 686 base line2 685 686 SYSTEM_MOVING_WINDOW 640 696

4. Automatically create AWR baselines

[sql] view plaincopyprint?

The sys@ORCL > alter session set NLS_DATE_FORMAT= 'yyyy-mm-dd hh34:mi:ss'; session has changed. Sys@ORCL > exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template (- > day_of_week = > 'WEDNESDAY',-> hour_in_day = > 0,-> duration = > 6,-> start_time = >' 2013-11-01, 0000,-> end_time = > 2013-12-31 -> baseline_name_prefix = > 'Batch Baseline',-> template_name = > 'Batch Template',-> expiration = > 365) The PL/SQL process completed successfully. Sys@ORCL > select t.template_name, 2 t.template_type, 3 t.start_time, 4 t.end_time, 5 t.day_of_week, 6 t.hour_in_day, 7 t.duration 8 from dba_hist_baseline_template t TEMPLATE_NAME TEMPLATE_ START_TIME END_TIME DAY_OF_WE HOUR_IN_DAY DURATION -- Batch Template REPEATING 2013-11-01 00:00:00 2013-12-31 06:00:00 WEDNESDAY 06 sys@ORCL > exec DBMS_WORKLOAD_REPOSITORY.drop_baseline_template ('Batch Template') The PL/SQL process completed successfully.

Day_of_week: days or weeks, at which time the baseline is repeated. Specify one of the following values: ('SUNDAY',' MONDAY', 'TUESDAY',' WEDNESDAY','THURSDAY', 'FRIDAY',' SATURDAY', 'ALL')

Hour_in_day:0-23, the execution baseline starts at this hour.

Duration: duration (hours).

Start_time: the start time when the baseline was created.

End_time: the end time when the baseline was created.

Expiration: the number of days that the baseline expires.

For the above template, a fixed baseline will be created every Wednesday based on the time window from 0:00 to 6:00