VirtualObjectives

AWR and ASH Reports

Automatic Workload Repository (AWR) collects performance statistics like Wait events used to identify performance problems, Active Session History (ASH) statistics, some system and session statistics, Object usage statistics, Resource intensive SQL statements. This report is quite large and comprehensive.

The following instructions are best followed when using Oracle SQL Developer.

Generating an AWR Report:

  1. Get the database ID.
  2. Select the Snap ID for the corresponding start and end time.
  3. The instance number will always be 1 on a single instance, while in a RAC environment it will be 1, 2, 3... depending on the number of nodes you have. Also in a RAC environment, you should generate one report per node. So all you do is change the Instance number and rerun the SQL.
    You DON'T need to change connection to the node, just use the SAME connection.
  4. Enter the details obtained above into the parameters for DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML
  5. Highlight the line press [F9] to run.
  6. In the Script Output tab, press [Ctrl]+[A] to select all the output and then [Ctrl]+[Ins] to copy.
  7. Then paste into a text file and rename the file extension to HTM (or HTML).

Generating an ASH Report:

  1. Get the database ID.
  2. The instance number will always be 1 on a single instance, while in a RAC environment it will be 1, 2, 3... depending on the number of nodes you have. Also in a RAC environment, you should generate one report per node. So all you do is change the Instance number and rerun the SQL.
    You DON'T need to change connection to the node, just use the SAME connection.
  3. Enter the details obtained above into the parameters for DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML
  4. Highlight the line press [F9] to run.
  5. In the Script Output tab, press [Ctrl]+[A] to select all the output and then [Ctrl]+[Ins] to copy.
  6. Then paste into a text file and rename the file extension to HTM (or HTML).