Toad World® Forums

Optimizing queries in two sessions problem (obey previous best time query)

Hi,
SQL Optimizer 9.2.0.4159 x64 on Windows 7 x64 (this is enough for problem I face).
I have situations that some queries last for several hours, so all candidate are not possible to be run in one session in one day, because db is refresh on daily level (dolly database clone).
So when I stop the test, I have original sql value and maybe some better candidates.

I save all and load tomorrow for continuing.

When I select several queries (that were not run) and choose “Run selected” all testing is not monitoring previous “Best time” so queries runs forever, regardless they go far beyond time of best solution (Org or candidate…never mind).
But when in that second day some query run in “acceptable time” (near or even better that current) then again all other testing queries follow that time.

Next day situation is the same if I have to stop the session.

So mine question is:
Is this a bug?
How to overcome that in next session on not executed or repeatedly executed candidates, they follow timings from the best time.

Of course it is not acceptable to include the best candidate with them again, because this is just waste of time. But even in this scenario, SQL Optimizer will choose whic candidate will run first so.
Thx for your help in front.

Brg
Damir Vadas

Hi,

Hope these steps meet your requirement:

  1. Test Run - Test Run Selected.

  2. When the “Test Run Settings” dialog box popup, click “Customize test Run Settings” (Left-Bottom).

  3. Change to “Order and Termination” tab.

  4. Select “User-defined time (hh:mm:ss):”

  5. Set a time that you want SQL optimizer to stop testing a SQL

Regards,

Ken

Hi,

Yes this is a good workaround for now.

But is this behavior a feature or a bug?
(if you come from Quest team)
:slight_smile:

Brg
Damir

Hi ,

This behavior is by design. We will consider add a dialog box to ask user want to terminate a SQL by the best run time in history or by a user defined time.

Regards,

Ken