Re: Oracle Apps concurrent program issue

 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_at_soocs.de>:

> 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: _at_OracleSK
>
> > Tim Gorman <tim_at_evdbt.com> hat am 3. April 2015 um 17:39 geschrieben:
> >
> > Please note that there is only SQL_PLAN_HASH_VALUE value reported, so
> plan instability is not an issue.
> > >
> > > On Apr 2, 2015, at 20:54, Kumar Madduri < ksmadduri_at_gmail.com
> <mailto:ksmadduri_at_gmail.com> > wrote:
> > >
> > > I found sql_plan_hash_value 2624643529 was executed 4100 times
> and this was what was running yesterday when the problem happened(can
> confirm
> > > that from the time period).
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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