How to performance tune a sql statement in OEM using tuning adviser.

 You have seen a sql that has been taking longer and people have complained for performance issues. At this point if your company uses Oracle enterprise manager OEM, you can use it to performance tune the sql. The sql can show up in the top sessions or you may already been given a sqlid and you can search it in OEM under performance/sql search.  Its shown in top sessions and are taking longer and more resources.

1. Login in to the top activity in the oem.
2. Click the sql which is running and you want to tune. In my case its the highlighted one in yellow.
   
3. The next screen will show the wait event related to the sql. It looks like below and i need the plan from a prior date not from today.  
   
4. Click the view data and select historical view from highlighted tab.
   
   
5. In actions tab select tuning adviser.
   
6. Set a reasonable name, time period you want to use for analysis how many min. In my case i am doing it comprehensive analysis so i can get profile recommendation as well. Run it for immediately and hit submit.
   
7. Wait for the task to finish and give recommendations.
   
8. So oracle has given me an analysis. The following indexes have not been analysed and query is running slow.
   
9. Now all i do is click and select the ones i want to implement and hit implement. Make sure to keep a top activity window open on the side to see performance of the database after implementing a change and good idea to open an alert log for the database. NOTE: Make sure to get NOC approvals or approvals before running submit.