Generating the AWR Report

In this section we will learn about how to generate awr report but before generating lets discuss something about AWR Report .

     One day i asked my DBA friends how you all are get to know about your database performance statistics and Workload .So they said oracle itself collecting all this statistical data automatically in every hours wise and we collect the information from the report and that report is named as AWR report .

What is AWR Report ?
The full form of AWR is Automatic Workload Repository .It collects, processes, and maintains performance statistics for problem detection and self-tuning purposes like SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time . This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

Steps for generating AWR Report :

Step 1: Log in sys with sysdba privilege.Here notice the path from where you calling the sqlplus that   is the place where awr report by default will be generated .
In my case path is :
C:\User\Abhishek

 Step 2: After successful log in we need the file which will used to generate the AWR report .The

location of the file is  @$ORACLE_HOME\rdbms\admin\awrrpt.sql
For RAC Environment in same path use awrrpti.sql instead of awrrpt .here i am using single instance so my file path is E:\app\Abhishek\product\11.1.0\db_1\RDBMS\ADMIN\awrrpt.sql 
The moment you call this sqlfile it will ask for report type by default it is html you can also generated it in txt file but better is html for better reading point of view and easy understanding .

Step 3: Now it will display the Instance Repository schema information like

DB Id , Inst Num , DB Name , Instance  ,Host . If it is RAC Environment then will also show the cluster id  and node information as well .
     After displaying the Information it will ask for Number of Days for which you want to generate the AWR report .Here i have given for 5 Days.

 Step 4:  Once you Enter the No of Days it will display the snap shot which are available for that time

 Frame .In my case doing in this testing database which is not alwas running but in Real it will display all snap for every hours .
 Now say for Example i want to generate the AWR report for 13-JUL-15 1am - 2pm then the available snap id id 159 and 160 will put 159 in place of Begin snap because this the beginning snap id for the 1am and 160 as End snap because this is the ending snap id  2pm.


Step 5:  Now it as ask for the file name  by default it will take based on your snap id but it will always 

better to give the meaning name so that if you share this report with developer for tuning or dba team show they can get the report generated for which time .I am following this naming convention
which is AWRRPT for report type then Date then the starting time and Ending time .

Step 6: Now you can see the below screen the AWR report has been generate in the location which i

mentioned in step 1 (C:\User\Abhishek) . Once the file is being generated type file name and open through cmd prompt / terminal .

I am interested in hearing your feedback about this AWR Report Generation , so that I can improve my lab series on sql for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: