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: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
