Management of AWR Baseline

Using the Dbms_workload_repository package to manage baseline

1. Create baseline

--View an existing snapshot in the Dba_hist_snapshot view to determine the snapshot scope to use.

Select Snap_id,dbid,begin_interval_time,end_interval_time,snap_level from Dba_hist_snapshot;

snap_id DBID begin_interval_time end_interval_time snap_level

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

220853307 04-mar-13 02.00.49.845 pm 04-mar-13 03.00.58.970 pm 1

220853307 04-mar-13 03.00.58.970 pm 04-mar-13 04.00.08.328 pm 1

220853307 04-mar-13 04.00.08.328 pm 04-mar-13 05.00.17.091 pm 1

220853307 04-mar-13 05.00.17.091 pm 04-mar-13 06.00.26.037 pm 1

220853307 04-mar-13 06.00.26.037 pm 04-mar-13 07.00.35.429 pm 1

220853307 04-mar-13 07.00.35.429 pm 04-mar-13 08.00.44.059 pm 1

220853307 06-mar-13 10.30.05.000 pm 06-mar-13 10.40.56.516 pm 1

220853307 07-mar-13 09.08.50.000 pm 07-mar-13 09.19.47.771 pm 1

220853307 07-mar-13 09.19.47.771 pm 07-mar-13 10.00.53.958 pm 1

220853307 07-mar-13 10.00.53.958 pm 07-mar-13 10.59.09.642 pm 1

220853307 07-mar-13 10.59.09.642 PM 08-mar-13 12.00.13.313 AM 1

220853307 08-mar-13 10.20.00.000 am 08-mar-13 10.30.57.436 am 1

--Create a BASELINE using the Create_baseline stored procedure.

Dbms_workload_repository. Create_baseline (

start_snap_id in number,

end_snap_id in number,

Baseline_name in VARCHAR2,

More Wonderful content: http://www.bianceng.cn/database/Oracle/

dbid in number DEFAULT NULL,

Expiration in number DEFAULT NULL);

BEGIN

Dbms_workload_repository. Create_baseline (start_snap_id => 21,

end_snap_id =>, Baseline_name => ' peak baseline ',

dbid => 220853307, expiration => 30);

End;

/

--21 is the starting snapshot serial number, and 25 is the ending snapshot sequence number. Expiration => 30 indicates that the baseline will be in 30 days

--delete automatically after

-When you create a baseline, the system automatically assigns a unique baseline ID to the newly created baseline. Can be viewed through the Dba_hist_baseline view.

Select Dbid,baseline_id,baseline_name,expiration,creation_time from Dba_hist_baseline;

DBID baseline_id baseline_name Expiration creation_time

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

220853307 1 Peak Baseline 30 2013-03-08 11:03:03

220853307 0 System_moving_window 2013-03-02 14:23:12

2. Delete Baseline

BEGIN

Dbms_workload_repository. Drop_baseline (baseline_name => ' peak BASELINE ',