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>:
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]
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> hat am 15. September 2015um 12:25 geschrieben:
basic SQL*Plus session, or some type of front-end that might be causing the
What's the environment from which you are calling the SQL ? Is it a
double parse with binds anyway ? I forget which mid-tier, or driver itwas 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.
--
//www.freelists.org/webpage/oracle-l
