Re: Historical Plan & Time

Oh yeah - almost forgot about DBA_HIST_ACTIVE_SESS_HISTORY. I was just using that view a few days ago too. One caveat though; I'm pretty sure that the ASH data is based on sampling - and may not always pick up some sql statements that run very quickly.

In our case there were two plans getting used with a particular statement and one was very bad and one was good. The good one ran in a second or two and the bad one didn't finish in 8 hours. The calculated costs were so close that a nightly stats job had been occasionally causing the optimizer to switch to the bad one. But when I queried the AWR ASH view it appeared that the bad plan was the only one being used except for a few small exceptions. As always YMMV...

-J

On 6/14/07, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
>
> 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.
>

-- 
Jeremy Schneider
Chicago, IL
http://www.ardentperf.com/category/technical

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