5 Replies Latest reply: Mar 26, 2013 8:55 AM by Girish Sharma RSS

    How to find out plan for 2 days old query?


      I am on v11.2.0.3 on Linux.

      Couple of days ago we had a one off performance issue. A process, which usually take 5 mins took 20 mins that day. We know the time when the process was running. I tried the AWR report. Our snapshot time window is 1 hour. I can see one of the SQLs from the process in AWR report under the section "Top 10 SQLs by elapsed time". But AWR does not have plans (and other stats) of that SQL.

      If I want to pull out the plan (and all possible details) of that particular session from 2 nights ago. What is the best tool for this purpose?
      Can ASH do this job?

      Thanks in advance.