AWR / 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:

•       Get the database ID.

•       Select the Snap ID for the corresponding start and end time.
•       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.
•      Enter the details obtained above into the parameters for DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML
•      Highlight the line press [F9] to run.
•      In the Script Output tab, press [Ctrl]+[A] to select all the output and then [Ctrl]+[Ins] to copy.
•      Then paste into a text file and rename the file extension to HTM (or HTML).

-- Return a list of all AWR snaps shots. -- You need to select the snap_id for the start and end period. SELECT snap_id start1, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY 1 asc; -- SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(<dbid>, <instance>, <start_id>, <end_id>)); -- Insert the database ID, the instance number and the start and end snap_ids. -- 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. -- Highlight line below and run with [F9] then use Ctrl+A on the output, press Ctrl+Ins to copy and paste into a HTML file. SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(2321251481, 1, 60943, 60945));

Generating an ASH Report:

•     Get the database ID.
•     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.
•      Enter the details obtained above into the parameters for DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML
•      Highlight the line press [F9] to run.
•      In the Script Output tab, press [Ctrl]+[A] to select all the output and then [Ctrl]+[Ins] to copy.
•      Then paste into a text file and rename the file extension to HTM (or HTML).

-- Insert the database ID, the instance number and the start and end times. -- Highlight line below and run with [F9] then use Ctrl+A on the output, press Ctrl+Ins to copy and paste into a HTML file. SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(2321251481, 1,     TO_DATE('30/07/2014 13:52','DD/MM/YYYY HH24:MI'),     TO_DATE('30/07/2014 14:00','DD/MM/YYYY HH24:MI')));