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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.

Other related posts: