Steps to Generate AWR Reports

Automatic Workload Repository


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.

Step 1: Go to $ORACLE_HOME.

Step 2: Once in $ORACLE_HOME go to cd rdbms and then cd admin.

Step 3: Run command ‘ls –ltr awr*’.

Step 4: Go to ‘sqlplus / as sysdba

Step 5: In sqlplus the following is run ‘SQL> @awrrpt.sql. Select the format for the report as either ‘HTML’ or ‘TEXT’.

  
Step 6: Select number of days you want to go back or just hit enter for listing all
completed snapshots, if you press ENTER it will give you all snapshot .

Step 7: Then specify Begin and End snapshot Ids.

Step 8: Here you specify the name of the report or select the default name assigned.

Exit Sql , And Search for your report_name in the Same Directory .

 Just As Note :

@?/rdbms/admin/awrrpti.sql – Script can be used for the specified database and instance so the script in addition will prompt for dbid and instance number.

@?/rdbms/admin/awrddrpt.sql – Script can be used to generate a combine report of 2 different snapshots for comparison of performance in one report so it will prompt for two pairs of snapshot snapshots to compare against.


@?/rdbms/admin/awrinfo.sql – Script print AWR information like space usage by AWR, subset of AWR snap ids, Automatic Database Diagnostic Monitor (ADDM), Active Session History (ASH) information.


@?/rdbms/admin/awrrpt.sql – It is an interactive script that can be used to generate HTML or text reports. The script prompts for type of report requested and prompts for number of days of snapshots to choose from so based on that it displays snap ids to choose the start and end snapshot it.

The Next Post will be How to read AWR Tips .

 
 Enjoy

Osama Mustafa