performance tuning

How to run Oracle ASH Report for Oracle performance Tuning

ASH Report or Active Session History report is a very useful tool available in Oracle 11g provided for Oracle Performance Tuning.
This report was introduced in Oracle 10gR2.
By running this you can get a report of your oracle database performance very quickly. The different sections in the report help to identify performance tuning related issues quickly.

This report is usually run when you need to tune a SQL or you want to investigate performance bottlenecks in your Oracle database.

The report can be run from Oracle Enterprise Manager Database Control or Grid Control (OEM). It can also be run from command line.

The steps below shows how to run the ASH report using OEM

1. Login to Oracle Enterprise Manager 11g Grid Control
2. Go to Targets and then from the menu below select All Targets.
3. Find the Database Instance where you want to run ASH report
4. Click on the database Instance Link. Now the Database instance page will be displayed.
5. Click on the tab called Performance
6. It should ask for username/password. If you want to can select to login as SYSDBA
7. When the performance Tab is displayed find the Run ASH Report link from top right corner on the screen and click on it.
8. Now Run Ash Report will be displayed
9. Here you select the date and time for the period you want to run the ASH report for. Select Date and Time.
10. There is no need to enter a value for SID but if you want you can enter if you want to run this for a particular SID.
11. Then click on the Generate Report button.
12. You will see a message saying that the report is now running. After a few seconds the report will be displayed.

The ASH report contains a wide range of information which is very useful for performance tuning. Basically based on the purpose of your running the ASH report you can directly go to relevant sections. For example if you are looking for tuning SQL performance then you can go directly to Top SQL section.

In Top SQL sections you can identify top five SQL statements which are

Oracle ASH Report for Performance Tuning

The first column shows the SQL IDs. Click on that link will display the actual statement that is being run.

The report also identified the following:
Top Events
Load Profile
Top Java
Top Call Types
Top Sessions
Top Objects/Files/Latches
Activity Over Time
and much more