RE: Historical Plan & Time

Yes, you can query the AWR - but the queries provided below only show the first & last time of the sql_id, not the specific plan. To find the details of the plan, you'd want to run something like this:  

select min(snap_time), max(snap_time) from dba_hist_active_sess_history where sql_plan_hash_value = <known plan_hash_value>  

Or:  

select sql_plan_hash_value, min(snap_time), max(snap_time) from dba_hist_active_sess_history where sql_id = <your sql_id> group by sql_plan_hash_value;  

And you can query dba_hist_sql_plan for the details of any given plan in the AWR.  

Or, better yet:  

Run $ORACLE_HOME/rdbms/admin/awrsqrpt.sql  

This will give you a nicely formatted output of all plans currently in the AWR for a given sql_id, along with the first and last snap_ids, executions stats, etc. for each plan and it's available in your choice of HTML or text.  

Regards,
Brandon  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeremy Paul Schneider

You can also query the AWR:  

SQL> select min(snap_id), max(snap_id) from dba_hist_sqlstat where sql_id='1nh4y7yurm73g';

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l