(available since Oracle 10g)
AWR collects (through snapshots), processes, and maintains performance statistics.
Several tools such as ADDM (Automated Database Diagnostic Monitor), AWR Reports analyses the snapshots to perform performance analysis against a baseline snapshot.
Most of the Oracle Database self-management functionality depend of AWR:
Snapshots are made once every hour (snapshot interval) by default.
Snapshots are retained by default for 8 days (retention period) as you have normally two different workloads between the week and the weekend due to batch job.
The retention period must be large enough to capture at least one complete workload cycle. If there is a monthly workload peak, a retention period of one month may be necessary.
TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, sharable Memory, and Version Count).
From 12g.
The ID of the container to which the data pertains. Possible values include:
The default window baseline value is the current AWR retention_period.
With the default settings, a typical system with an average of 10 concurrent active sessions can require approximately 200 to 300 MB of space for its AWR data.
The details of the control view can be seen in the Oracle Database Reference
select * from DBA_HIST_WR_CONTROL;
| DBID | SNAP_INTERVAL | RETENTION | TOPNSQL |
|---|---|---|---|
| 3001565062 | 0 1:0:0.0 | 7 0:0:0.0 | DEFAULT |
select * from DBA_HIST_SNAPSHOT
More details: DBA_HIST_SNAPSHOT
select * from DBA_HIST_BASELINE
DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRICS (
baseline_name IN VARCHAR2,
dbid IN NUMBER DEFAULT NULL,
instance_num IN NUMBER DEFAULT NULL)
RETURN awr_baseline_metric_type_table PIPELINED;
See the table DBA_HIST_BASELINE_TEMPLATE
Historical data (snapshot) stored in AWR can be seen using the following DBA_HIST views:
SELECT
DBID
, SQL_ID
, SQL_TEXT
FROM
dba_hist_sqltext
WHERE
sql_text LIKE ‘%your query%’;
SELECT
-- Snapshot Id
SNAP_ID
-- Sql Id
, SQL_ID
, SUM( CPU_TIME_DELTA ) CPU_TIME
, SUM( ELAPSED_TIME_DELTA ) ELAPSED_TIME
, SUM( EXECUTIONS_DELTA ) EXECUTIONS_DELTA
FROM
DBA_HIST_SQLSTAT
GROUP BY
SNAP_ID
, SQL_ID;
where:
The DBMS_WORKLOAD_REPOSITORY package lets you manage the Workload Repository, performing operations such as managing snapshots and baselines.
The DBA role is required to invoke the DBMS_WORKLOAD_REPOSITORY procedures.
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention => 11520,
interval => 60,
topnsql => 100,
dbid => 3310949047,
CON_ID => ); -- From 12c
END;
/
where:
To disable automatic snapshot collection, the snapshot interval must be set to 0.
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
-- sqlplus
exec dbms_workload_repository.create_snapshot;
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (
low_snap_id => 22,
high_snap_id => 32,
dbid => 3310949047);
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
start_snap_id => 270,
end_snap_id => 280,
baseline_name => 'peak baseline',
dbid => 3310949047,
expiration => 30);
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => '2012-04-02 17:00:00 PST',
end_time => '2012-04-02 20:00:00 PST',
baseline_name => 'baseline_120402',
template_name => 'template_120402',
expiration => 30,
dbid => 3310949047);
END;
/
-- a baseline template named template_120402 is created that will generate a baseline named baseline_120402
-- for the time period from 5:00 p.m. to 8:00 p.m. on April 2, 2012
-- on the database with a database ID of 3310949047. The baseline will expire after 30 days.
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'monday',
hour_in_day => 17,
duration => 3,
expiration => 30,
start_time => '2012-04-02 17:00:00 PST',
end_time => '2012-12-31 20:00:00 PST',
baseline_name_prefix => 'baseline_2012_mondays_',
template_name => 'template_2012_mondays',
dbid => 3310949047);
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
baseline_name => 'peak baseline',
cascade => FALSE,
dbid => 3310949047);
END;
/
-- To drop the associated snapshots along with the baseline, set the cascade parameter to TRUE
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (
template_name => 'template_2012_mondays',
dbid => 3310949047);
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
old_baseline_name => 'peak baseline',
new_baseline_name => 'peak mondays',
dbid => 3310949047);
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
window_size => 30,
dbid => 3310949047);
END;
/
You can either use OEM, which is the Enterprise Database Control to view the AWR snapshots, or generate AWR reports.
You can run awrrpti.sql script to generate the report.
%sqlplus / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql