RE: kkslce [KKSCHLPIN2] mutex contention on hard parse of PQ with binds
Date: Tue, 15 Sep 2015 14:11:03 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282A3FA9_at_EXMBX01.thus.corp>
Mohamed,
I wouldn't like to guarantee past behaviour on the basis of current behaviour - especially when you bring SPMs and the CBO creating execution plans which it declines to use because of an existing SQL Baseline and the optimizer "knows" that the plan it is creating may be discarded.
Historically I THINK (emphasis required) that "child cursor" and "execution plan" were virtually synonymous - so if you saw a child cursor you knew there should have been an execution plan in memory for it at some point.
This isn't strictly true, of course, but lots of things get into the library cache (or, at least) x$kglob which you might not expect to be there - even, for example - SQL statements with parse errors:
SQL> select rubbish from rubbish;
select rubbish from rubbish
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select kglnaobj from x$kglob where kglnaobj like '%rubbish%rubbish%';
KGLNAOBJ
select kglnaobj from x$kglob where kglnaobj like '%rubbish%rubbish%' select kglnaobj from x$kglob where kglnaobj like '%rubbish%rubbish%' select rubbish from rubbish
select rubbish from rubbish
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Mohamed Houri [mohamed.houri_at_gmail.com] Sent: 15 September 2015 14:25
To: Jonathan Lewis
Cc: oracle-l-freelists
Subject: Re: kkslce [KKSCHLPIN2] mutex contention on hard parse of PQ with binds
Jonathan,
"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<mailto: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<mailto: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<mailto: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-lReceived on Tue Sep 15 2015 - 16:11:03 CEST