Oracle 11g AWR Series 3: Management of AWR baseline

Use the dbms_workload_repository package to manage baseline

1. Create a baseline

-- Check the existing snapshots in the dba_hist_snapshot view to determine the snapshot range to be used.

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
------------------------------------------------------------------------------------------
21 220853307 04-Mar-13 02.00.49.845 PM 04-Mar-13 03.00.58.970 pm 1
22 220853307 04-Mar-13 03.00.58.970 PM 04-Mar-13 04.0020.8.328 pm 1
23 220853307 04-Mar-13 04.0020.8.328 PM 04-Mar-13 05.00.172.1691 pm 1
24 220853307 04-Mar-13 05.00.172.1691 PM 04-Mar-13 06.00.262.1637 pm 1
25 220853307 04-Mar-13 06.00.262.1637 PM 04-Mar-13 07.00.35.429 pm 1
26 220853307 04-Mar-13 07.00.35.429 PM 04-Mar-13 08.00.44.059 pm 1
27 220853307 06-Mar-13 10.3020.5.000 PM 06-Mar-13 10.40.56.516 pm 1
28 220853307 07-Mar-13 09.08.50.000 PM 07-Mar-13 09.19.47.771 pm 1
29 220853307 07-Mar-13 09.19.47.771 PM 07-Mar-13 10.00.53.958 pm 1
30 220853307 07-Mar-13 10.00.53.958 PM 07-Mar-13 10.5920.9.20.pm 1
31 220853307 07-Mar-13 10.5920.9.20.pm 08-Mar-13 12.00.13.313 am 1
32 220853307 08-Mar-13 10.20.00.000 am 08-Mar-13 10.30.57.436 am 1

-- Use the create_baseline stored procedure to create a baseline.

Dbms_workload_repository.create_baseline (
Start_snap_id in number,
End_snap_id in number,
Baseline_name in varchar2,
Dbid in number default null,
Expiration in number default null );

Begin
Dbms_workload_repository.create_baseline (start_snap_id => 21,
End_snap_id => 25, baseline_name => 'peak baseline ',
Dbid => 220853307, expiration => 30 );
End;
/

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

-- When you create a baseline, the system automatically assigns a unique baseline ID to the newly created baseline. You can view it in 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 11:03:03
220853307 0 system_moving_window 14:23:12
 
2. Delete baseline

Begin
Dbms_workload_repository.drop_baseline (baseline_name => 'peak baseline ',
Cascade => false, dbid => 3310949047 );
End;
/

-- Set the cascade parameter to false to delete only. Set this parameter to true to delete all snapshots associated with the baseline.

3. Rename baseline

-- Query the name of the original baseline
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 11:03:03
220853307 0 system_moving_window 14:23:12

-- Rename baseline using the rename_baseline Stored Procedure

Begin
Dbms_workload_repository.rename_baseline (
Old_baseline_name => 'peak baseline ',
New_baseline_name => 'peak maomi ',
Dbid => 220853307 );
End;
/

11:44:49 sys @ prod> select dbid, baseline_id, baseline_name, expiration, creation_time from dba_hist_baseline;

Dbid baseline_id baseline_name expiration creation_time
----------------------------------------------------------------------
220853307 1 peak maomi 30 11:03:03
220853307 0 system_moving_window 14:23:12
 

4. Display baseline metrics

If an adaptive threshold is used, baseline will contain the AWR data used by the database to calculate the measurement threshold. Select_baseline_metrics Function
Summary Statistics of metric values during baseline.

11:44:57 sys @ prod> select dbid, baseline_id, baseline_name, expiration, creation_time from dba_hist_baseline;

Dbid baseline_id baseline_name expiration creation_time
----------------------------------------------------------------------
220853307 1 peak maomi 30 11:03:03
220853307 0 system_moving_window 14:23:12
 
-------
Begin
Dbms_workload_repository.select_baseline_metrics (
Baseline_name => 'peak maomi ',
Dbid = & gt; 220853307,
Instance_num => '1 ');
End;
/

5. Modify the default window size of the default moving window baseline.

12:27:34 sys @ prod> select dbid, baseline_id, baseline_name, moving_window_size from dba_hist_baseline;

Dbid baseline_id baseline_name moving_window_size
-----------------------------------------------------------
1 peak maomi 220853307
220853307 0 system_moving_window 8

Begin
Dbms_workload_repository.modify_baseline_window_size (
Window_size => 30,
Dbid => 220853307 );
End;
/

Begin
*
Error at line 1:
ORA-13541: system moving window baseline size (2592000) greater than retention (691200)
ORA-06512: At "SYS. dbms_workload_repository", line 686
ORA-06512: At Line 2

An error is reported because the size of the window has exceeded the current AWR retention period. Therefore, you must first increase the AWR retention period by using the following method:

Begin
Dbms_workload_repository.modify_snapshot_settings (retention => 43200,
Interval => 30, topnsql => 100, dbid => 220853307 );
End;

/

For reprint, please indicate the source and original article links:

Http://blog.csdn.net/xiangsir/article/details/8654275