Re: kkslce [KKSCHLPIN2] mutex contention on hard parse of PQ with binds

*"More commonly the first plan wasn't used (and people were puzzled by seeing two child cursors with two plans) which would happen because the first parse call would (just like "explain plan") assume the bind variables were character types and the real bind variables turned out to have a different type when the second parse call appeared."*

I think that only used execution plans are put in v$sql_plan. Those that are produced by the optimizer and not used by the query should not have any entry in v$sql_plan.

Isn't it?

I have remarked this during a test on an execution plan produced by the CBO but not used because it is constrained by a SPM baseline. In this particular case the following query didn't show the phv2 of the CBO plan

SELECT

        p.sql_id
       ,p.plan_hash_value
       ,p.child_number
       ,t.phv2
     FROM
       v$sql_plan p
      ,xmltable('for $i in /other_xml/info
       where $i/_at_type eq "plan_hash_2"
                  return $i'
                  passing xmltype(p.other_xml)
                  columns phv2 number path '/') t
      WHERE p.sql_id = '4sdth4kka4ykw'
      AND   p.other_xml is not null;

I have just remodelled a case in 12.1.0.1.0 and the following query:

select distinct sql_id, *full_plan_hash_value* from v$sql_plan
where sql_id = '209hkkf5gpp47';

shows only the SPM plan (used one) and not the CBO plan constrained one.

Or may be it is the case only for plans constrained by a SPM baseline?

Best regards
Mohamed Houri

PS : spot by the way that the old PHV2 that we were deriving from v$sql.other_xml seems to be externalized in the new full_plan_hash_value column

2015-09-15 13:25 GMT+02:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>:

>
>
> Stefan,
>
> That's the sort of thing.
>
> I guess the bug aspect of that example is that the "describe" parse call
> is used for the execute even though the binds haven't been peeked (because
> they weren't there).
>
> More commonly the first plan wasn't used (and people were puzzled by
> seeing two child cursors with two plans) which would happen because the
> first parse call would (just like "explain plan") assume the bind
> variables were character types and the real bind variables turned out to
> have a different type when the second parse call appeared.
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
>
> ________________________________________
> From: Stefan Koehler [contact_at_soocs.de]
> Sent: 15 September 2015 11:49
> To: Jonathan Lewis; oracle-l-freelists
> Subject: RE: kkslce [KKSCHLPIN2] mutex contention on hard parse of PQ with
> binds
>
> Hi Jonathan,
> i guess you are talking about what Mauro and me have talked about here:
>
> -
> https://www.freelists.org/post/oracle-l/In-what-circumstances-might-optimizer-not-choose-the-lowest-cost,15
> -
> https://www.freelists.org/post/oracle-l/In-what-circumstances-might-optimizer-not-choose-the-lowest-cost,18
>
> Basically it is OCI and fix 9630092 is also disabled by default with 12c
> (or at least with 12.1.0.1 as i have no 12.1.0.2 at hand right now).
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
>
> > Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> hat am 15. September 2015
> um 12:25 geschrieben:
> >
> > What's the environment from which you are calling the SQL ? Is it a
> basic SQL*Plus session, or some type of front-end that might be causing the
> > double parse with binds anyway ? I forget which mid-tier, or driver it
> was but there used to be common questions about seeing statement with bind
> > variables being parsed twice, once with no values for the bind variable,
> then a second time because the values were only sent on a second call.
> --
> 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