Automatic workload repository is a collection of persistent system performance statistics owned by SYS.
It resides in SYSAUX tablespace. By default snapshot are generated once every 60min and maintained for 7 days by default.
you can generate AWR reports by running SQL scripts:
1)The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
2)The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
3) The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
4) The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
5) The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
6) The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Few Views which helps while generating the AWR report
DBA_HIST_SNAPSHOT
DBA_HIST_WR_CONTROL
DBA_HIST_BASELINE
How to Modify the AWR SNAP SHOT SETTINGS:
=====================================
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/
Creating the Baseline:
======================
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 10,
end_snap_id => 100,
baseline_name => 'AWR First baseline');
END;
/
Dropping the AWR baseline:
==========================
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
baseline_name => 'AWR First baseline');
END;
/
Dropping the AWR snaps in range:
================================
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(row_snap_id=>40,
High_snap_id=>80);
END;
/
Creating SNAPSHOT Manually:
===========================
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/
Workload Repository Views:
==========================
The following workload repository views are available:
* V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
* V$METRIC - Displays metric information.
* V$METRICNAME - Displays the metrics associated with each metric group.
* V$METRIC_HISTORY - Displays historical metrics.
* V$METRICGROUP - Displays all metrics groups.
* DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
* DBA_HIST_BASELINE - Displays baseline information.
* DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
* DBA_HIST_SNAPSHOT - Displays snapshot information.
* DBA_HIST_SQL_PLAN - Displays SQL execution plans.
* DBA_HIST_WR_CONTROL - Displays AWR settings.
Subscribe to:
Post Comments (Atom)
This comment has been removed by the author.
ReplyDelete