Toad 10 vs. SQL Optimizer runtimes very different

Toad for Oracle
1

I’m wondering if anyone has run into this issue. I have a query that
appears to be the problem in an Oracle Report that is kicked of via a
procedure called by DBMS_JOB. When I execute the query in SQLPlus or
Toad’s Editor it returns in about 10 seconds. When I execute it through
SQL Optimizer for Oracle, it ran in over 9 minutes. The report runs at
night and has been taking hours to complete. It used to run in just a
few minutes.

Does SQL Optimizer package the run using a job? There must be something
similar in how SQL Optimizer and the nightly report are running that is
causing the problem.

Thanks in advance for your help,

Steve

2

It runs the query just as toad does, via OCI.

There are options to handle the data differently, like retrieving all the data,
or retrieving none of the data which would obviously have a bit impact on
execution time. Toad for example only retrieves the first 500 rows when you
execute via F9 (although SQL*Plus would gets all of the data)

How are you executing in SQL Optimizer? Are you only testing a single re-write,
or are you testing all of the re-writes?

Are you using the Tuning Lab (Tune SQL) or the Batch Optimize interface?

We may want to take this offline or move to the SQL Optimizer community hosted
over on ToadWorld…

Jeff

3

Thanks Jeff. I wasn’t aware that SQL Optimizer community has a place to ask questions like this group. I’ll move this discussion it over there.

Steve