Venkysaid

How to fetch historical execution plan? Lets say a query is slow today but was working well yesterday. Change in execution plan is evident from change in hash value. How to find the yesterday execution plan?

Yasir
If you have access to AWR, you can use dbms_xplan.display_awr to understand the execution plan of a SQL statement from AWR (Assumption here is that SQL elapsed time of the SQL statement exceeded the threshold and so,captured in AWR tables. )
Tim Gorman has written an excellent script and it is very useful to identify the sql history quickly. Search for Sqlhistory.sql in the page below: