AWR in-depth analysis (automatic workload repository)

Automatic workload Repository It is an important component introduced by 10 Gb.
Stores the detailed information and information about the database activity status in the recent period. The default value is 7 Days
The predecessor of AWR is statspack, while statspack is public.Source codeOf

Therefore, there are also statspack sources.CodeIt is a useful aid for understanding AWR.

AWR, naiSysOwns, takes snapshots, and stores them onSysauxThe tablespace is also

What is a snapshot?

A snapshot is a set of performance statistics captured at a specific time and stored in AWR.
The snapshot is used to calculate the change rate of statistical information.

How to identify a snapshot?

Each snapshot is identified by a snapshot serial number (snap_id), which is unique in AWR.

How to modify a snapshot?

By default, Each snapshot60Generated every minute
You can adjust the interval by changing the snapshot interval parameter.
However, adjusting the interval may affect the diagnostic accuracy.
For example, if you set interval to 4 hours, the peak value that appears within the 60-minute interval may be omitted.
Use dbms_workload_repository.modify_snapshot_settings stored procedure or Database Control to change the settings for controlling snapshot collection
In Database Control:
"Administration" → "statistics management" → "automatic workload repository" → "edit"

How to Create a snapshot?

Use Database Control or dbms_workload_repository.create_snapshot
Supports manual snapshot acquisition and automatic snapshots generated by the system.
If you want to capture system behavior at two specific time points inconsistent with the automatic plan, you need to use manual snapshots

What is a baseline?

Baseline, simply put, isReferences
A snapshot set is defined as a snapshot, and each baseline corresponds to one (and only corresponds to one) snapshot.
A baseline is used to retain snapshot data. Therefore, a baseline snapshot is retained until the baseline is deleted.

How to identify a baseline?

A baseline can be identified by a user's name or an identifier generated by the system.
The baseline identifier is unique in the lifecycle of the database.

How to Create a baseline?

Use Database Control or run dbms_workload_repository.create_baseline

When to create a baseline?

Generally, a snapshot set is set in some representative time periods in the past to compare with the current system behavior.

AWRAll the data in the report comes from the AWR view.Dba_hist _All system tables starting
However, how to effectively analyze AWR reports may require DBA experienceAccumulated over time