# 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:** 20\ **Page:** 1
**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 21, 2014, 11:42am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/1 "2014-02-21T11:42:44Z")
Is there a difference in the way that SQL is called (or the bind variables are assigned) using an F5 vs an F9? I have a sql statement that selects from a single table using bind variables. If I run the statement using an F9 and assigning the values using TOADs dialog, I get the results back in msecs, but if I run using an F5 and assigning the variables in the script the SQL uses a different plan and takes over 3 minutes to return. If I run the explain plan on the query, it shows that it is using my desired index but when the script runs, that is not the index that is used. Oh - and when I run this as a script even with a hint telling Oracle which index to use, it ignores my index. I’ve been trying to figure this out for way too long now. Any thoughts? – **Phyllis Helton** BI MIddleware Engineer Digital Strategies, Cru | Insight Team Office ☎ 407-515-4452 [phyllis.helton@cru.org](mailto:phyllis.helton@cru.org) ![](http://emailassets.cru.org/cdn-crumail/cru.png) ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [February 21, 2014, 12:06pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/2 "2014-02-21T12:06:44Z")
Is it query execution or the returning of the data? When you execute with F9, Toad will return somewhere between a screenful of data and 1000 rows, depending on the row size. When you execute with F5, Toad pulls the entire table, spits it out into a SQL\*Plus-ish output AND throws it all into a grid. And it could be the fact that we’re telling Oracle ‘Hey, give me all the rows’ is affecting the index usage. ---
**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 21, 2014, 12:11pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/3 "2014-02-21T12:11:45Z")
Theoretically that could be it, but the query is only returning 23 rows, and 5 columns. How does TOAD tell Oracle to only give x rows? ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [February 21, 2014, 12:17pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/4 "2014-02-21T12:17:45Z")
Oh, well, if there are only 23 rows than it’s not likely the processing of rows that is slowing things down. If my memory is right, you’ve used ODAC, is that right, Phyllis? I’m talking about TSmartQuery’s FetchAll property to get all rows, and if that is set to false, then the FetchRows to control how many rows come back per fetch (and then another fetch is performed if you scroll down far enough in the grid) ---
**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 21, 2014, 12:23pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/5 "2014-02-21T12:23:45Z")
Ok. Actually we were using the DeveloperExpress components. I was hoping it was an Oracle set command that we could take advantage of. I'm actually trying to help one of our Java developers who is trying to get his queries to return in a decent amount of time and when I tried the query in TOAD it came back instantly. Then after the research we discovered that running this in a script caused the different plan. I wish I could just tell him to use TOAD's F9. 🙂 Or that the index hint wasn't being ignored in the script so we could rely on that. ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [February 21, 2014, 12:26pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/6 "2014-02-21T12:26:45Z")
Yeah it’s weird that the hint is ignored completely. I’m not sure what’s going on with that. ---
**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\ **Post date:** [February 21, 2014, 12:40pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/7 "2014-02-21T12:40:45Z")
Can you send the table creation script from the SB? If so, maybe we can replicate here locally. Providing the actual data is always best too, but understand if you can't do that part. ---
**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 21, 2014, 1:15pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/8 "2014-02-21T13:15:47Z")
Thanks Dennis! You may not want me to send the data - there are 47 million rows in the table. 🙂 At the end of the create script is the script our guy is trying to run. We really need Oracle to use the index that starts with the emplid because it runs best. . . Distribution of values that we are selecting on emplid - 13811 distinct values trans\_date - 3157 distinct values stf\_act\_type - 9 distinct values Let me know if I can provide anything else to help with this. . . CREATE TABLE PS\_STAFF\_TRNSACTNS ( STF\_ACCT\_TYPE VARCHAR2(5 BYTE) NOT NULL, EMPLID VARCHAR2(11 BYTE) NOT NULL, TRANS\_DATE DATE NOT NULL, SOURCE\_SYSTEM VARCHAR2(3 BYTE) NOT NULL, SOURCE\_KEY VARCHAR2(26 BYTE) NOT NULL, SOURCE\_CODE\_TYPE VARCHAR2(1 BYTE) NOT NULL, SOURCE\_CODE VARCHAR2(12 BYTE) NOT NULL, TRANS\_AMOUNT NUMBER(9,2) NOT NULL, POSTED\_FLAG VARCHAR2(1 BYTE) NOT NULL, CLEARED\_STATUS VARCHAR2(1 BYTE) NOT NULL, SOURCE\_DATE DATE NOT NULL, DESCR VARCHAR2(30 BYTE) NOT NULL, ADVANCE\_CHK\_NUM INTEGER NOT NULL, AUDIT\_OPRID VARCHAR2(30 BYTE) NOT NULL, TO\_CLEAR\_BY\_DATE DATE ) TABLESPACE STFMEMO RESULT\_CACHE (MODE DEFAULT) PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 3428360K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT ) LOGGING CACHE PARALLEL ( DEGREE 4 INSTANCES 1 ) MONITORING; CREATE INDEX PSASTAFF\_TRNSACTNS ON PS\_STAFF\_TRNSACTNS (EMPLID, “TRANS\_DATE” DESC, STF\_ACCT\_TYPE) LOGGING TABLESPACE STFMEMOI PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1500M NEXT 500M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT ) NOPARALLEL; CREATE INDEX PSBSTAFF\_TRNSACTNS ON PS\_STAFF\_TRNSACTNS (“TRANS\_DATE” DESC, STF\_ACCT\_TYPE, SOURCE\_SYSTEM) LOGGING TABLESPACE STFMEMOI PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1024M NEXT 1024M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT ) NOPARALLEL; CREATE INDEX PSCSTAFF\_TRNSACTNS ON PS\_STAFF\_TRNSACTNS (STF\_ACCT\_TYPE, EMPLID, “TRANS\_DATE” DESC) LOGGING TABLESPACE STFMEMOI PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1024M NEXT 1024M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT ) NOPARALLEL; CREATE INDEX PSDSTAFF\_TRNSACTNS ON PS\_STAFF\_TRNSACTNS (EMPLID, POSTED\_FLAG, STF\_ACCT\_TYPE, TRANS\_AMOUNT) LOGGING TABLESPACE STFMEMOI PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 150M NEXT 50M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT ) NOPARALLEL; CREATE INDEX PSESTAFF\_TRNSACTNS ON PS\_STAFF\_TRNSACTNS (EMPLID, TRANS\_DATE, SOURCE\_SYSTEM, SOURCE\_CODE\_TYPE, SOURCE\_CODE) LOGGING TABLESPACE PSINDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 2029640K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT ) NOPARALLEL; CREATE OR REPLACE TRIGGER AUDIT\_STF\_TRANS after delete on ps\_staff\_trnsactns for each row begin if deleting then insert into ps\_audit\_stf\_trans (AUDIT\_OPRID,AUDIT\_STAMP,AUDIT\_ACTN,STF\_ACCT\_TYPE,EMPLID,TRANS\_DATE, SOURCE\_SYSTEM,SOURCE\_KEY,SOURCE\_CODE\_TYPE, SOURCE\_CODE, TRANS\_AMOUNT,POSTED\_FLAG,SOURCE\_DATE,DESCR) values (USER, SYSDATE, ‘D’, :old.stf\_acct\_type, :old.emplid, :old.trans\_date, :old.source\_system, :old.source\_key, :old.source\_code\_type, :old.source\_code, :old.trans\_amount, :old.posted\_flag, :old.source\_date, :old.descr); end if; end; / ---
**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\ **Post date:** [February 21, 2014, 2:11pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/9 "2014-02-21T14:11:49Z")
I’m not seeing the script you mentioned for some reason 🙂 ---
**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 22, 2014, 5:40am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/10 "2014-02-22T05:40:36Z")
_Distribution of values that we are selecting on_ _emplid - 13811 distinct values_ _trans\_date - 3157 distinct values_ _stf\_act\_type - 9 distinct values_ Data distribution by blocks should have importance as well. SQL plan and real (from run time) sql plan is something that is only relevant. Without that information, it is hard to see whole picture. ---
**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 24, 2014, 6:04am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/11 "2014-02-24T06:04:46Z")
Oops! Here it is: VARIABLE employeeId VARCHAR2(10) EXEC :employeeId := ‘000664706’ VARIABLE spouseEmployeeId VARCHAR2(10) EXEC :spouseEmployeeId := ‘000664706S’ VARIABLE begin VARCHAR2(10) EXEC :begin := ‘01-FEB-13’ VARIABLE end VARCHAR2(10) EXEC :end := ‘31-JAN-14’ VARIABLE staffAccountType VARCHAR2(10) EXEC :staffAccountType := ‘PRIME’ – select 1, :employeeId, :begin, :end from dual; SELECT TRUNC(stafftrans0\_.TRANS\_DATE, ‘MONTH’) AS month, SUM(stafftrans0\_.TRANS\_AMOUNT) AS transaction\_total, stafftrans0\_.SOURCE\_CODE AS source\_code, stafftrans0\_.DESCR AS description FROM PS\_STAFF\_TRNSACTNS stafftrans0\_ WHERE (stafftrans0\_.EMPLID IN ( :employeeId, :spouseEmployeeId)) AND (stafftrans0\_.TRANS\_DATE BETWEEN :begin AND :end) AND stafftrans0\_.STF\_ACCT\_TYPE = :staffAccountType AND stafftrans0\_.TRANS\_AMOUNT \> 0 GROUP BY TRUNC(stafftrans0\_.TRANS\_DATE, ‘MONTH’), stafftrans0\_.SOURCE\_CODE, stafftrans0\_.DESCR; ---
**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\ **Post date:** [February 24, 2014, 8:26am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/12 "2014-02-24T08:26:48Z")
Looks like our email responses are not coming thru, so I’ll post this again manually… Hey Phyllis - what index is it using for you and what index were you trying to get it to use? I’m assuming either PSA or PSC? When I ran it, it’s using PSB for me, which is ok since I’m only using a few records, but could see how that could be a problem with a lot of records. ---
**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\ **Post date:** [February 24, 2014, 9:11am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/13 "2014-02-24T09:11:34Z")
I’m not sure if we’re seeing the same thing or not, but for me, it was always using the PSB index, which I would have expected it to use the PSA index. The only way I could get it to use the PSA index was to add the other fields to it (trans\_amount, source\_code, and descr). Sorry if this doesn’t help. ---
**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 24, 2014, 10:24am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/14 "2014-02-24T10:24:57Z")
I’ve had problems replying to the group as well today. Had to reply directly in toadworld. Actually, PSB is the slow index for this query and the one Oracle would choose when running with F5, PSC is fast and the one chosen when using an F9. We finally got around this by doing math on the date and changing the line to trans\_date + 0 between :start and :end. Which isn’t exactly the best solution long-term. I still would like to know why the different plan using the different methods. ---
**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 24, 2014, 10:53am UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/15 "2014-02-24T10:53:31Z")
it is really interesting how poster do not want to anticipate obeying one of the most important rule in almost any database: **Data does matter.** This has nothing with F5, F9 (for that try execute in pure sqlplus from Oracle) or with Toad (IMHO). Cheers and good luck in that kind of approach. ---
**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:08pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/16 "2014-02-24T13:08:22Z")
Evening all, long time no see! ☹ I have missed most of this thread due to a new (ish) contract and being very very busy. I now have to look after a lot of up to date databases, no more 7.3.4 in production for me! I get to my laptop about once or twice a month these days. Anyway, are you aware that there are (always?) two explain plans in Oracle? One of them takes place when you run the EXPLAIN PLAN FOR … statement, and the other is what Oracle actually did, and can be extracted from the dictionary - “somewhere”. (Note a lack of specifics there, sorry!) The EXPLAIN PLAN FOR statement gives you a plan that Oracle “might” use to run the statement, while the one you get afterwards is the one that it did use. I’m just wondering out loud, if this is what you are seeing? Apologies if this has been discussed somewhere down the line. I’ve got about three months of emails to clear out of my inbox. ☹ Cheers, Norm. [TeamAbsenT] ---
**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:12pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/17 "2014-02-24T13:12:22Z")
Hi Phyllis, On 21/02/14 20:23, phyllis.helton\_1150 wrote: > ... I'm actually trying to help one of our Java developers who is > trying to get his queries to return in a decent amount of time... > Sorry, can't resist - tell him not to use Java! 😉 > Cheers, > Norm. [TeamAbsenT] ---
**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:15pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/18 "2014-02-24T13:15:50Z")
I’ve blogged about this and how you ca use Toad to see both the expected vs. actual explain plans 🙂 [www.toadworld.com/…/default.aspx](http://www.toadworld.com/members/bert-scalzo/blogs/default.aspx#post-7866) ---
**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:17pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/19 "2014-02-24T13:17:22Z")
Evening all, > ... > Oh - and when I run this as a script even with a hint telling > Oracle which index to use, it ignores my index. > The CBO in Oracle will happily ignore hints any time it feels like it. A hint is just that, a hint, it isn't an order that the optimiser cannot ignore. > A 10053 trace will show you exactly what the optimizer did to work out the execution plan for the F5 and/or F9 version. Sadly, there are not too many people around who can read a 10053 trace, and I'm not one of them. Maybe Bert? > I've been trying to figure this out for way too long now. Any thoughts? > Hopefully the above will give you something to go on? > Cheers, > Norm. [TeamAbsenT] ---
**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:20pm UTC](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905/20 "2014-02-24T13:20:41Z")
Oracle will also ignore a hint without warning if you mistype the hint - which is darned easy to do 🙂 [Next page](https://forums.toadworld.com/t/different-explain-plan-for-f9-vs-f5/28905.md?page=2)