Oracle 10g provides a wealth of features, and my favorite among them is AWR. AWR has come to my rescue in critical situations where our  customers face performance issues. AWR collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information

Performance can degrade due to a variety of reason like high load, problems with network, addition of new code, poor optimizer statistics etc… When we  are committed toward giving 100% customer satisfaction, the AWR feature comes handy in such situations, and helps us to work towards a better diagnosis.

You can generate an AWR report of database activity between two snapshots by running the awrrpt.sql script in the rdbms/admin directory under $ORACLE_HOME. This script offers reports formatted as plain text or HTML.  You can use Enterprise Manager to generate AWR reports as well.

The DBA can control the interval and retention of snapshot generation by the dbms_workload_repository.

modify_snapshot_settings procedure. For example:

Note that taking manual snapshots is also supported in conjunction with the automatic snapshots that the system generates. For this, the dbms_workload_repository.create_snapshot procedure is used.

The snapshots are used for computing the rate of change of a statistic. This is mainly used for performance analysis. A snapshot sequence numer (snap_id) identifies each snapshot, which is unique within the Workload Repository.

Typically, the DBA would be able to view the AWR data through Oracle Enterprise Manager or AWR reports. However, we can view the statistics by querying the following views as well.

AWR helps to meet the immediate performance needs of customers and give quick resolution in customer’s critical business hours. Also, as AWR collects detailed run-time statistics at specified intervals, it helps us to get the details of a performance issue that had occurred in the past. This helps in identifying recurring issues and paves the way to arrive at and implement permanent solutions.

Metalink Reference: 748642.1

Viewers of this post also viewed...