Hi Bert,
On 24/02/14 21:16, Bert Scalzo wrote:
I've blogged about this and how you ca use Toad to see both the expected
vs. actual explain plans 
And for once, I had actually read it! 
I'm also wondering if, perhaps, bind variable peeking might be playing a part here?
When the query is first run with bind variable, Oracle could be peeking to see what's what, and giving one execution plan, then later, when it seems (different?) values, it might be rethinking the plan based on these new values.
I know the older versions of Oracle didn't do this, so the first execution of a statement could have dramatic effects on subsequent ones if the first set of binds managed to build a decent plan for that set of values, but a horrendous plan for all the rest. However, I think the later versions, 11g perhaps, have gone all "adaptive" and can, at some point, rethink the plan if the values change dramatically.
I'm not 100% sure though, so quoting me might not be the smartest move!
-- Cheers,
Norm. [TeamT]
Hey Norm. You are spot on - that’s perfect example of why one needs to always check the actual plan and hence why it’s great that Toad supports that - if users would just set the options to work that way 
Welcome back Norm! How nice that your databases are all up to date now.
These are good thoughts, but I'm not sure if they apply. I have been viewing the statement as it runs and see which plan it is using, at least when I am using the F5, because I have plenty of time to catch it in the session browser. I wasn't aware of using tracing to see the actual plan used, I'll give that a try to see what is happening when I run using F9, though I have looked in v_$sql_plan and seen the index it selected, thinking that was the "actual" index used.
I was wondering if somehow the bind variable values came into play, but if I'm using the same values running the statement each way, that doesn't make sense it would treat them differently. When I actually hard-coded the values, even F9 ran slowly because it selected the slower index.
Blessings,
Phyllis
I was wondering if somehow the bind variable values came into play
This depends on which Oracle version you are in.
In 11g there are new feature Adoptive cursor (www.oracle.com/.../11g-sqlplanmanagement-101938.html) which should lower bind values problems.
If you are on 10g database then binds could really make big mess in Oracle plan.
Hi Phyllis,
On 25/02/14 13:40, phyllis.helton_1150 wrote:
Reply by phyllis.helton_1150
Welcome back Norm! How nice that your databases are all up to date now.
Thanks, we are even getting some play time on 12c!
... I wasn't aware of using tracing to see the
actual plan used, I'll give that a try to see what is happening when I
run using F9
use dbms_monitor.start_trace and stop_trace if it is your own session. I would turn on waits and binds as well.
Failing that, the old favourite 10046 trace is still there:
alter session set TRACEFILE_IDENTFIER=phyllis;
alter session set events '10046 trace name context forever, level 12';
do sql here
alter session set events '10046 trace name context off';
My fingers know how to type that lot in without me having to use any thinking power!
, though I have looked in v_$sql_plan and seen the index it
selected, thinking that was the "actual" index used.
As far as I'm aware, it should be! You might get a hint or two from V$SQL_PLAN_STATISTICS as well, use the same sql_id as you do in V$SQL_PLAN and see what pops out. You might see huge numbers for something that you don't see with F5, perhaps.
I was wondering if somehow the bind variable values came into play, but
if I'm using the same values running the statement each way, that
doesn't make sense it would treat them differently. When I actually
hard-coded the values, even F9 ran slowly because it selected the slower
index.
If you hard code the values it is entirely possible that Oracle will select a different plan to when you use binds. If you find that the plan is not as optimal as it could be, using the slow index that you mention, make sure you force Oracle to run a hard parse on the statement.
It could be that an existing execution plan exists. One way to force a hard parse is to "explain plan for..." but that won't really tell you how it was actually executed, as it doesn't execute it.
You could flush the shared pool as well, that would force a reload and (hopefully) a hard parse, but I think the best way is to just change the formatting of the statement or add a different comment at the top.
It's only when the hard parse takes place that you get a new execution plan built. If Oracle decides that a soft parse is fine, the statement will be using a previously generated execution plan, which might not be the best plan.
Finally, for now anyway, what is the setting of CURSOR_SHARING? Hopefully it's EXACT.
-- Cheers,
Norm. [TeamT]
