Toad World® Forums

Query much slower in SQL Optimizer than in TOAD


#1

Hi everyone,

I have a problem regarding elapsed time and explain plan.

When I’m trying to tune my select statement with the SQL Optimizer i get an explain plan with the cost of 560 and an elapsed time of more than 1:30 h. After that i executed the same statement in Toad and it took only 6 sec! So I executed the statement again in the SQL Optimizer to be sure that its not because of the cache. It took again more than an hour. So I investigated the session browser while running the query with the SQL Optimizer to see the actual explain plan of it and there it showed an completely different explain plan with costs of over 88000!!!
Maybe also helpful information: many of the produced alternatives also need only some seconds to be executed.

can anybody???

thx,

Cedric


#2

Are there any bind variables in your SQL ? What is the actual execution time in your application ? If it is possible, give your SQL and query plans in both tools.

Thanks,

Richard


#3

See attachments for the explain plans.
There are only system generated bindvariables in it.


#4

See attachments for the explain plans.
There are only system generated bindvariables in it.


#5

It looks like your SQL is bind sensitive, the speed may be different on how it is executed. Our SQL Optimizer is using DBMS_SQL package to execute SQL statements, so,it is not bind sensitive. You can select the best one from our alternatives to replace your original SQL. If you want to have a steady speed SQL alternative, pick the SQL alternative with Hints is recommended.

Richard


#6

sum(sb.net_price) net_amount,

bds_ohead oh,

bds_address a

and sb.revenue_date > trunc(sysdate, ‘MM’)

and sb.ohead_no = oh.order_no

group by a.address_name

and oh.client_no = a.client_no

union all

0,

bds_return_head rh,

bds_return_line rl

and rh.received_date < trunc(sysdate - 1)

and rh.suborg_code = ‘AE’

group by a.address_name

and rh.client_no = a.client_no

SQL Statement:

select a.address_name, 0 from bds_sb sb, where sb.suborg_code = ‘AE’ and sb.revenue_date < trunc(sysdate - 1) and oh.address_no = a.address_no select a.address_name, sum(rl.received_qty * rl.return_price) from bds_address a, where rh.received_date > trunc(sysdate, ‘MM’) and rh.crhead_no = rl.crhead_no and rh.address_no = a.address_no


#7

thank you for your answer but now I read something about bind sensitivity and it looks like that this function is only availiable in oracle 11g, but I’m using 10g db’s so is it also possible???

The next thing is, that not only executing the query in Toad is much fast, also the alternatives do so.(original on more than 30 mins, alternatives some ms)

so its quite hard to say which alternative is the best is there another possible reason for my problem???

thx,

Cedric


#8

Since we are using DBMS_SQL to execute SQL statement like a cursor to get first record and all records execution time. Normally, the actual plan and estimate plan is the same, but in some rare situations, there may be different query plans.(The same problem may happen in your system while different programs execute the same SQL), So, you have to test it after you paste the best alternative to your source program. If you want a stable performance alternative, use a SQL with hint will normally guarantee the speed across the broad.

Richard


#9
  1. Which db version are you atacking

  2. what is the actual plan from SGA (not one which is shown in Toad or sqlplus)?


#10

Be careful, Toad has for over two years now (and it’s been blogged about several times) that toad can show either the proposed plan or the actual (executed
or cached) plan – it’s up to you as a simple setting ….

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Tuesday, September 17, 2013 2:11 PM

To: sqloptoracle@toadworld.com

Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] Query much slower in SQL Optimizer than in TOAD

RE: Query much slower in SQL Optimizer
than in TOAD

Reply by damir.vadas_531

  1. Which db version are you atacking

  2. what is the actual plan from SGA (not one which is shown in Toad or sqlplus)?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Optimizer for Oracle - General
notifications altogether.

SQL Optimizer for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#11

Here’s the most recent blog that covers this topic – near end …

http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2013/09/09/explain-plans-can-be-deceiving.aspx

From: Bert Scalzo

Sent: Tuesday, September 17, 2013 2:26 PM

To: sqloptoracle@toadworld.com

Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] Query much slower in SQL Optimizer than in TOAD

Be careful, Toad has for over two years now (and it’s been blogged about several times) that toad can show either the proposed plan or the actual (executed
or cached) plan – it’s up to you as a simple setting ….