Re: Oracle Apps concurrent program issue

From: Mauro Pagano <mauro.pagano_at_gmail.com>
Date: Fri, 3 Apr 2015 18:29:43 -0400
Message-ID: <CAAnDMS3KxFomW5xz-8L1tf0+EN0T53e9ca6a6wC82StRjw8aHQ_at_mail.gmail.com>

PHV2 is available in OTHER_XML in DBA_HIST_SQL_PLAN :-)

On Fri, Apr 3, 2015 at 5:34 PM, Stefan Koehler <contact_at_soocs.de> wrote:

> Hi Mohamed,
> thank you for joining in. I also read your blog post about this SPM and
> PHV2 topic on allthingsoracle.com in the past.
>
> Please correct me, if i am wrong but afaik all analysis of this issue are
> based on historical AWR data (e.g. dba_hist_active_sess_history) and in this
> case there is no way to re-calculate the phv2 value (with predicates) as
> the predicate section is not stored in AWR.
>
> Dominic Brooks has also published a solution for phv2 (which is more like
> Carlos's SQLT_PHV1) and AWR in the past (
> https://orastory.wordpress.com/2014/02/05/awr-was-a-baselined-plan-used/
> ), but as far as i understood this it is only including the objects and not
> the predicate section.
>
> Carlos also differs between PHV, SQLT_PHV1 (id, parent_id, operation,
> options, index_columns and object_name) and SQLT_PHV2 (SQLT_PHV1 + access
> and
> filter predicates), but SQLT_PHV2 is not possible with AWR data only:
> http://carlos-sierra.net/2013/06/09/has-my-plan-changed-or-not/
>
> Looking forward to hear your opinion. Thank you.
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
>
> > Mohamed Houri <mohamed.houri_at_gmail.com> hat am 3. April 2015 um 21:07
> geschrieben:
> >
> > Hi Stephan
> >
> > It is the phv2 that identifies an execution plan by including the
> predicate part and taking into account any object name changes because the
> name
> > of an index (or table) participate in the generation of this phv2 value.
> And it is this phv2 (and not the plan_hash_value) that it used by the CBO
> > to decide whether a SPM plan has been reproduced or not.
> >
> > Carlos Sierra SQLT (and may be the new Mauro Pagano tool as well)
> intelligently included the phv2 in addition to the plan_hash_value
> >
> > As per regards to the actual case there is fairly likely a very little
> chance to have a different phv2 for the same couple (sql_id,
> > plan_hash_value)
> >
> > Best Regards
> > Mohamed Houri
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Sat Apr 04 2015 - 00:29:43 CEST