Monday, October 28, 2013

AWR Report in Oracle

How to take AWR Report in Oracle:
AWR: Automatic Workload Repository
The AWR is used to collect performance statistics including:

1. Wait events used to identify performance problems.
2. 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.
3. Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
4. Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
5. Object usage statistics.
6. Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:
1. Automatic Database Diagnostic Monitor
2. SQL Tuning Advisor
3. Undo Advisor
4. Segment Advisor
So basically you need to run SNAP_SHOT two times to get AWR Report in between. One before your application
runs and one after your application runs.
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.

How To Take AWR Report:
Login to Unix Systems where Oracle is running:
oracle@DBDomain:~$ sqlplus / as SYSDBA
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 22 14:42:42 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
SQL>SELECT SNAP_ID, DBID from DBA_HIST_SNAPSHOT order by SNAP_ID;
//Remember only the last record (last SNAP_ID, it will be a numeric value).
...........................................
RUNNNNNNNNNNNNN YOUR APPLICATION Who populates DATA into DBBBBBBBBBBBBBB..........
...........................................
SQL>EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
SQL>SELECT SNAP_ID, DBID from DBA_HIST_SNAPSHOT order by SNAP_ID;

//Remember the last two records (one SNAP_ID before your application run, second after run).
//Now Run below commands:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2835430333 ENSDB 1 ENSDB

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2835430333 1 ENSDB ENSDB DBDomain

Using 2835430333 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ --------- ------------------ -----
ENSDB ENSDB 119 22 Oct 2013 13:00 1
120 22 Oct 2013 14:01 1
121 22 Oct 2013 14:39 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 120
Begin Snapshot Id specified: 120

Enter value for end_snap: 121
End Snapshot Id specified: 121


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_120_121.html. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/DeepakModi/AWR_Report_22_OCT_SUCCESSFUL_100TPS.html
===============================================================
That's it. File has been created as "/home/oracle/DeepakModi/AWR_Report_22_OCT_SUCCESSFUL_100TPS.html".

===================END, Thank You.=================================

No comments:

Post a Comment