It makes it difficult to get an accurate comparison of the original query performance statistics with alternate scenarios, because of the data buffer caching issue. The batch run always executes the original query first. After it weeds out the most inefficient scenarios, there may be that we have still 33 scenarios that supposedly are faster than the original. If I remove all the failed scenarios I can execute again the batch run to whittle down the alternate list; except every time I execute the batch run, the original query gets run first. My suggestion would be to have an option to not execute the original again or execute all of the scenarios in the order of the fastest query first.
Hi Richs,
I am not sure if I understand your question completely but I will try to answer that. If you find my answer not relating, please elaborate your question for me.
You don’t always have to execute the Original in a Batch Run. Original can be skipped if you (i) selected the correct Batch Run button, and (ii) if the Batch Run Options are set right.
Select the correct Batch Run button
First of all, there are different types of Batch Run you can perform in Optimizer and they are grouped under the Batch Run button. If you click on the little triangle beside the Batch Run button, you will see the list of different Batch Runs for you to choose.
The “Batch Run All” (which is also the default action if you click the “Batch Run” button) executes all SQL so the Original and alternatives will all be executed. This is not the one you want. The one you may be looking for is the “Batch Run Selected” button. It executes only those SQL you have selected so you can skip the Original by not selecting it.
Set the right options
The Original may still be executed even if you choose “Batch Run Selected” when any one of these options is selected:
-
Under “Run Time Retrieval Method”,
“Run all SQL twice if original SQL runs faster than (seconds)” -
Under “SQL Termination Criteria”,
“This percentage of the original SQL run time”
If one of these options is selected, Original will still be executed when it was not executed before because these options mandate the Original to have run time to proceed. But note that if Original has been executed before, “Batch Run Selected” shouldn’t execute Original again even when these options are used.
I hope the above answered your questions.
And as a side note, since you mentioned the data caching issue, the next release of Optimizer will add a new Batch Run option to clear buffer cache before executing each SQL. This new feature is already in the current beta. If you have not downloaded the latest beta, you may want to try that.
Thanks,
Alex
Still Not working.
-
the alternative batch run options to run selected, as mentioned, are there … but are grayed out. I only have two choices available: BATCH RUN ALL and BATCH RUN MULTIPLE ALL.
-
The batch run options I have chosen is:
run on server
run all sql once
execution order ( either choice ) same results
terminate if runs longer than run times of fastest sql
cancellation delay 5 secs
best alternative based on elapsed time
The version of SQL Optimizer is 8.0.0.1551 and is part of the TOAD for Oracle DBA Suite.
Any clues ??
Hi Richs,
Based on your information, I have installed the QSOO 8.0.0.1551 which is part of the Toad for Oracel DBA suite. I am using the options which you mention in the previous notes for replicated the issue which you reported.
The Batch Run Selected or the Batch Run Multiple Selected options will be grayed out if there is no SQL (original) or Alternatives SQL selected under the alternatives section. One of the check box under the alternatives section should be checked then the options for "Batch Run Selected or the Batch Run Multiple Selected options will be enabled.
Just a reminder that the original sql may still be executed even if you choose “Batch Run Selected” when any one of these options is selected: - Under “Run Time Retrieval Method”, “Run all SQL twice if original SQL runs faster than (seconds)” - Under “SQL Termination Criteria”, "This percentage of the original SQL run time "If one of these options is selected, Original will still be executed when it was not executed before because these options mandate the Original to have run time to proceed. But note that if Original has been executed before, “Batch Run Selected” shouldn’t execute Original again even when these options are used.Please try and let us know if you still encounter the problems.
Kiristy