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
2015-04-03 17:59 GMT+02:00 Stefan Koehler <contact@xxxxxxxx>:
Hi guys,
just a little point to mention about the SQL_PLAN_HASH_VALUE. The
SQL_PLAN_HASH_VALUE is not based on the whole execution plan and its
attributes. The
hash value can be the same, even if the predicate section is different for
example. Please check out Kerry's & Randolf's blog post for further
details:
http://kerryosborne.oracle-guy.com/2013/06/sql-gone-bad-but-plan-not-changed/
Unfortunately the predicate section is also not included in AWR.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Tim Gorman <tim@xxxxxxxxx> hat am 3. April 2015 um 17:39 geschrieben:plan instability is not an issue.
Please note that there is only SQL_PLAN_HASH_VALUE value reported, so
<mailto:ksmadduri@xxxxxxxxx> > wrote:
On Apr 2, 2015, at 20:54, Kumar Madduri < ksmadduri@xxxxxxxxx
and this was what was running yesterday when the problem happened(can
I found sql_plan_hash_value 2624643529 was executed 4100 times
confirm
--that from the time period).
//www.freelists.org/webpage/oracle-l
