PHV2 is available in OTHER_XML in DBA_HIST_SQL_PLAN :-)
On Fri, Apr 3, 2015 at 5:34 PM, Stefan Koehler <contact@xxxxxxxx> 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: @OracleSK
Mohamed Houri <mohamed.houri@xxxxxxxxx> hat am 3. April 2015 um 21:07geschrieben:
predicate part and taking into account any object name changes because the
Hi Stephan
It is the phv2 that identifies an execution plan by including 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.intelligently included the phv2 in addition to the plan_hash_value
Carlos Sierra SQLT (and may be the new Mauro Pagano tool as well)
chance to have a different phv2 for the same couple (sql_id,
As per regards to the actual case there is fairly likely a very little
plan_hash_value)--
Best Regards
Mohamed Houri
//www.freelists.org/webpage/oracle-l
