Re: Oracle Apps concurrent program issue
Date: Fri, 3 Apr 2015 23:34:51 +0200 (CEST)
Message-ID: <1500281722.90674.1428096891323.JavaMail.open-xchange_at_app03.ox.hosteurope.de>
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-lReceived on Fri Apr 03 2015 - 23:34:51 CEST