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
@jloracle
________________________________
From: Mohamed Houri [mohamed.houri@xxxxxxxxx]
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/@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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>:
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
@jloracle
________________________________________
From: Stefan Koehler [contact@xxxxxxxx<mailto:contact@xxxxxxxx>]
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: @OracleSK
Jonathan Lewis--
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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.
