Detailed description of Oracle Database AWR instances

Last time we introduced:Oracle DatabaseMediumAWRIn this article, we will introduce the use of AWR in detail. Next, let's take a look at this part.

AWR is automatically generated by ORACLE, but you can also manually create, delete, and modify it using the DBMS_WORKLOAD_REPOSITORY package. You can run the desc command to view the process in the package. Below we will only introduce several common ones:

1. manually create a snapshot

 
 

  
  
SQL> select count (*) from wrh $ _ active_session_history;
  
  
 
  
  
COUNT (*)
  
  
 
  
  
----------
  
  
 
  
  
317
  
  
 
  
  
SQL> begin
  
  
 
  
  
2 dbms_workload_repository.create_snapshot ();
  
  
 
  
  
3 end;
  
  
 
  
  
4/
  
  
 
  
  
The PL/SQL process is successfully completed.
  
  
 
  
  
SQL> select count (*) from wrh $ _ active_session_history;
  
  
 
  
  
COUNT (*)
  
  
 
  
  
----------
  
  
 
  
  
320
 
 

2. manually delete snapshots of a specified range

 
 

  
  
SQL> select * from wrh $ _ active_session_history where snap_id = 96;
  
  
 
  
  
SNAP_ID DBID INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME
  
  
 
  
  
-------------------------------------------------------------------------
  
  
 
  
  
96 1160732652 1 236930 06-10-07 11.262.164.20.morning
  
  
 
  
  
96 1160732652 1 236930 06-10-07 11.262.164.20.morning
  
  
 
  
  
96 1160732652 1 236930 06-10-07 11.262.164.20.morning
  
  
 
  
  
SQL> begin
  
  
 
  
  
2 dbms_workload_repository.drop_snapshot_range (low_snap_id = & gt; 96, high_snap_id = & gt; 96, dbid = & gt; 1160732652 );
  
  
 
  
  
3 end;
  
  
 
  
  
4/
  
  
 
  
  
The PL/SQL process is successfully completed.
  
  
 
  
  
SQL> select * from wrh $ _ active_session_history where snap_id = 96;
  
  
 
  
  
Unselected row
 
 

3. Modify the collection time and statistical information retention time

 
 

  
  
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
  
  
 
  
  
Parameter Name type input/output default value?
  
  
 
  
  
---------------------------------
  
  
 
  
  
RETENTION NUMBER IN DEFAULT
  
  
 
  
  
INTERVAL NUMBER IN DEFAULT
  
  
 
  
  
TOPNSQL NUMBER IN DEFAULT
  
  
 
  
  
DBID NUMBER IN DEFAULT
 
 

You can modify the retention period of awr information by modifying the retention parameter. The default value is seven days, and the minimum value is one day. If the retention is set to zero, automatic cleanup is disabled. if awr finds that sysaux space is insufficient, it deletes the oldest snapshots to reuse the space. at the same time, a warning will be sent to the dba, telling the sysaux space is insufficient in the warning log ).

You can modify the interval parameter to modify the sampling frequency of awr information. The minimum value is 10 minutes, and the default value is 60 minutes. The typical values are 10, 20, 30, 60, 120, and so on. Set interval to 0 to disable automatic snapshot capturing. For example, change the collection interval to 30 minutes. And keep for 5 days. Note: The unit is minutes ):

 
 

  
  
SQL> select * from dba_hist_wr_control;
  
  
 
  
  
DBID SNAP_INTERVAL RETENTION TOPNSQL
  
  
 
  
  
-----------------------------------------------------------------
  
  
 
  
  
1160732652 + 00000 01:00:00. 0 + 00007 00:00:00. 0 DEFAULT
  
  
 
  
  
SQL> exec dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 5*24*60 );
  
  
 
  
  
The PL/SQL process is successfully completed.
  
  
 
  
  
SQL> SELECT * from dba_hist_wr_control;
  
  
 
  
  
DBID SNAP_INTERVAL RETENTION TOPNSQL
  
  
 
  
  
-----------------------------------------------------------------
  
  
 
  
  
1160732652 + 00000 00:30:00. 0 + 00005 00:00:00. 0 DEFAULT
  
  
 
  
  
SQL>
 
 

4. Set baselines

Baseline) is a mechanism, so that you can mark the snapshot information set at an important time. A baseline is defined between one snapshot. snapshots are identified by their snapshot serial number. Each baseline has only one snapshot.

A typical performance tuning practice starts from collecting the baseline set of metrics, making changes, and then collecting another baseline set. You can compare the two sets to check the effect of the changes. In AWR, you can compare existing snapshots of the same type.

Assume that a highly resource-intensive process named apply_interest runs between and, corresponding to the snapshot ID 95 to 98. We can define a baseline named apply_interest_1 for these snapshots:

 
 

  
  
SQL> select * From dba_hist_baseline;
  
  
 
  
  
Unselected row
  
  
 
  
  
SQL> select * from wrm $ _ baseline;
  
  
 
  
  
Unselected row
  
  
 
  
  
SQL> exec dbms_workload_repository.create_baseline (95, 98, 'apply _ interest_1 ');
  
  
 
  
  
The PL/SQL process is successfully completed.
 
 

This operation takes the snapshot number from 95 to 98 as part of the baseline specified above. View existing baselines:

 
 

  
  
SQL> select * from dba_hist_baseline;
  
  
 
  
  
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME
  
  
 
  
  
-----------------------------------------------------------------------------------------------------------------------
  
  
 
  
  
1160732652 1 apply_interest_1 95 06-10-07 11.0000005.375 am 98 06-10-07 01.44.58.062 pm
  
  
 
  
  
SQL> select * from wrm $ _ baseline;
  
  
 
  
  
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
  
  
 
  
  
-------------------------------------------------------------------------
  
  
 
  
  
1160732652 1 apply_interest_1 95 98
  
  
 
  
  
SQL>
 
 

After some adjustment steps, we can create another baseline-assuming the name is apply_interest_2, and then compare the measurement only for those snapshots related to the two baselines.

 
 

  
  
SQL> exec dbms_workload_repository.create_baseline (92, 94, 'apply _ interest_2 ');
  
  
 
  
  
The PL/SQL process is successfully completed.
 
 

Separating snapshots in just a few sets like this helps research the impact of adjustment on performance metrics. You can use drop_baseline () to delete the baseline after analysis. Snapshots can be retained or deleted in cascade mode ). In addition, when the cleanup routine starts to delete the old snapshots, the snapshots related to the baseline are not cleared, allowing further analysis.

5. delete a baseline

To delete a baseline:

 
 

  
  
SQL> exec dbms_workload_repository.drop_baseline (baseline_name => 'apply _ interest_1 ', cascade => false );
  
  
 
  
  
The PL/SQL process is successfully completed.
  
  
 
  
  
SQL> select * from wrh $ _ active_session_history where snap_id in (95,96, 97,98 );
  
  
 
  
  
SNAP_ID DBID INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME
  
  
 
  
  
----------------------------------------------------------------------------
  
  
 
  
  
95 1160732652 1 235360 06-10-07 10.56.29.872 AM
  
  
 
  
  
95 1160732652 1 235230 06-10-07 10.54.19.857 AM
  
  
 
  
  
95 1160732652 1 233130 06-10-07 10.19.19.478 AM
  
  
 
  
  
95 1160732652 1 232830 06-10-07 10.14.18.859 AM
  
  
 
  
  
95 1160732652 1 232250-07 10.04.38.481 AM
  
  
 
  
  
97 1160732652 1 238600 06-10-07 12.33.08.420 pm
  
  
 
  
  
97 1160732652 1 238600 06-10-07 12.33.08.420 pm
  
  
 
  
  
97 1160732652 1 238600 06-10-07 12.33.08.420 pm
  
  
 
  
  
97 1160732652 1 238600 06-10-07 12.33.08.420 pm
  
  
 
  
  
97 1160732652 1 238600 06-10-07 12.33.08.420 pm
  
  
 
  
  
97 1160732652 1 238600 06-10-07 12.33.08.420 pm
  
  
 
  
  
SNAP_ID DBID INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME
  
  
 
  
  
----------------------------------------------------------------------------
  
  
 
  
  
97 1160732652 1 238420 06-10-07 11.50.55.686 AM
  
  
 
  
  
97 1160732652 1 238230 06-10-07 11.47.45.687 AM
  
  
 
  
  
98 1160732652 1 239140 06-10-07 01.42.00.976 pm
  
  
 
  
  
98 1160732652 1 239140 06-10-07 01.42.00.976 pm
  
  
 
  
  
98 1160732652 1 239140 06-10-07 01.42.00.976 pm
  
  
 
  
  
98 1160732652 1 239140 06-10-07 01.42.00.976 pm
  
  
 
  
  
98 1160732652 1 239140 06-10-07 01.42.00.976 pm
  
  
 
  
  
98 1160732652 1 239130 06-10-07 01.272.164.161 afternoon
  
  
 
  
  
98 1160732652 1 239130 06-10-07 01.272.164.161 afternoon
  
  
 
  
  
98 1160732652 1 239130 06-10-07 01.272.164.161 afternoon
  
  
 
  
  
21 rows have been selected.
  
  
 
  
  
SQL> exec dbms_workload_repository.drop_baseline (baseline_name => 'apply _ interest_2 ', cascade => true );
  
  
 
  
  
The PL/SQL process is successfully completed.
  
  
 
  
  
SQL> select * from wrh $ _ active_session_history where snap_id in (92,93, 94 );
  
  
 
  
  
Unselected row
  
  
 
  
  
SQL>
 
 

The above are some detailed operations on the use of Oracle Database AWR. This article will introduce it here. I hope this introduction will be helpful to you!