# Different Explain Plan for F9 vs F5?? **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [February 21, 2014, 11:42am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905 "2014-02-21T11:42:44Z")\ **Posts on this page:** 5\ **Page:** 2
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [February 24, 2014, 1:32pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/21 "2014-02-24T13:32:22Z")
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] ---
**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\ **Post date:** [February 24, 2014, 1:34pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/22 "2014-02-24T13:34:48Z")
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 πŸ™‚ ---
**Author:** ![phyllis.helton\_1150](https://avatars.discourse-cdn.com/v4/letter/p/ac8455/32.png) [@phyllis.helton\_1150](https://forums.toadworld.com/u/phyllis.helton_1150)\ **Post date:** [February 25, 2014, 5:40am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/23 "2014-02-25T05:40:29Z")
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 ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [February 25, 2014, 8:55am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/24 "2014-02-25T08:55:53Z")
> 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](http://www.oracle.com/technetwork/articles/sql/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. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [February 25, 2014, 10:03am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/25 "2014-02-25T10:03:31Z")
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] [Previous page](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905.md?page=1)