Different Explain Plan for F9 vs F5??

Toad for Oracle & DevOps Toolkit
1

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 :phone: 407-515-4452

phyllis.helton@cru.org

2

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.

3

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?

4

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)

5

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. :slight_smile: Or that the index hint wasn't being ignored in the script so we could rely on that.

6

Yeah it’s weird that the hint is ignored completely. I’m not sure what’s going on with that.

7

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.

8

Thanks Dennis! You may not want me to send the data - there are 47 million rows in the table. :slight_smile:

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;

/

9

I’m not seeing the script you mentioned for some reason :slight_smile:

10

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.

11

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;

12

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.

13

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.

14

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.

15

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.

16

Evening all,
long time no see! :frowning:
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. :frowning:
Cheers,
Norm. [TeamAbsenT]

17

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! :wink:
Cheers,
Norm. [TeamAbsenT]

18

I’ve blogged about this and how you ca use Toad to see both the expected vs. actual explain plans :slight_smile:

www.toadworld.com/…/default.aspx

19

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]

20

Oracle will also ignore a hint without warning if you mistype the hint - which is darned easy to do :slight_smile: