NAVEEN

Saturday, February 6, 2010

Automatic Workload Repository (AWR) in Oracle Database 10g

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

AWR Features
The AWR is used to collect performance statistics including:

Wait events used to identify performance problems.
Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
Object usage statistics.
Resource intensive SQL statements.
The repository is a source of information for several other Oracle 10g features including:

Automatic Database Diagnostic Monitor
SQL Tuning Advisor
Undo Advisor
Segment Advisor


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.

Workload Repository Reports
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g

Overview
The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes:

CPU load
Memory usage
I/O usage
Resource intensive SQL
Resource intensive PL/SQL and Java
RAC issues
Application issues
Database configuration issues
Concurrency issues
Object contention

The recommendations may include:

Hardware changes
Database configuration changes
Schema changes
Application changes
Using other advisors
Enterprise Manager
The obvious place to start viewing ADDM reports is Enterprise Manager. The "Performance Analysis" section on the "Home" page is a list of the top five findings from the last ADDM analysis task.

Specific reports can be produced by clicking on the "Advisor Central" link, then the "ADDM" link. The resulting page allows you to select a start and end snapshot, create an ADDM task and display the resulting report by clicking on a few links.
addmrpt.sql Script
The addmrpt.sql script can be used to create an ADDM report from SQL*Plus.

Related Views
The following views can be used to display the ADDM output without using Enterprise Manager or the GET_TASK_REPORT function:

DBA_ADVISOR_TASKS - Basic information about existing tasks.
DBA_ADVISOR_LOG - Status information about existing tasks.
DBA_ADVISOR_FINDINGS - Findings identified for an existing task.
DBA_ADVISOR_RECOMMENDATIONS - Recommendations for the problems identified by an existing task.

Redo log Management.

The most crucial structure for recovery operations is the online redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.

To get logfile details:
SELECT * FROM v$log;


Add logfile to database:

ALTER DATABASE
ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;

Add logfile to log group:

ALTER DATABASE
ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')
SIZE 500K;