In the optimize sql - batch run - order and termination settings is an option called cancellation delay. My question is, does this setting cause a slower than optimum query to continue running for x number of seconds before terminating the query or does it immediately terminate the disqualified query and wait for x number of seconds before executing the next rewritten candidate query ?? The reason I ask is that one of the drawbacks to tuning any query is the fact that after the first execution most of the data needed to return the results have been cached, but if this cancellation delay is a pause before the next query starts, I might be able to delay the execution of the next query by say 5 or 10 minutes, allowing other database activity to replace my cached data with it’s own, thus making each execution of my next query and getting a truer picture of the queries efficiency.
It is the first one, i.e. “cause a slower than optimum query to continue running for x number of seconds before terminating the query”.
As for what you would like to do in clearing out the data cache, this option would not help. However, we do have some enhancement requests around this area and we are considering to provide a few new options to do this. If you have any suggestions on this topic, I would really like to hear from you. Please don’t hesitate to post it here or send me an email.
If that is the case, what advantage do I see by extending the time like 5 minutes before killing an invalid query ??
There are 2 ways to measure the run time of a SQL in SQL Optimizer: Run on Server, and Run on Client. When Run on Server (which is the default) is used, a script is sent to the server and this script will take the start time, run the SQL, and then take the end time to calculate the elapsed time. Let’s say if you have a slow network and it takes 1 second for a script/SQL to travel between your PC and the database. Then if your SQL run in 5 seconds, you will need 1 + 5 + 1 = 7 seconds for the test run process to complete (1 second to send the script, 5 seconds to run it, another 1 second for the script to send back the result). So after 7 seocnds, you will see your SQL tested and the run time of it is 5 seconds. When SQL Optimize tried to test run another alternative, it cannot cancel the test run after 5 seconds or it will not have given the SQL a 5 seconds chance to run. The Cancelation Delay helps in this scenario to avoid terminating a SQL earlier than it should. You should adjust the value to larger if your network is slow or there is a high fluctuation in the performance.
In Run on Client, the start time is taken first, then Opitmizer will run the SQL directly and get back all data. At the end it will take the end time to calculate the elapsed time. So you can see that the network time is included in the measurement. In such case, the Cancelation Delay may not be useful. However, the Cancelation Delay does provide another function that is useful for both execution method.
This other function is to allow users to see SQL with similar performance. Let’s say if you have the Cencelation Dalay set to 2 seconds and use the original SQL (which run in 5 seconds) as the termination criteria. Then you should still see any SQL that run faster than 7 seconds finish test run. If you don’t have the Cancelation Delay, you will only find those SQL that run faster than 5 seconds. Seeing other SQL that run a little bit longer could be useful for different reasons, e.g. test run hose SQL again using a different set of bind values, review their run time statistics to pick an altenatives with similar performance but better sonsumption in a particular resource, etc.
I hope the above gives you more information on the Cancelation Delay and will help you decide what value is best for your usage. However, in general, I wouldn’t recommend to set it to a value too large like 5 minutes.
That was a very good concise answer and I do see the logic behind the concept. This actually plays an important part to another suggestion I made, to allow multiple values to a bind variable for just that situation where query performance varies based on the value. I do think another option to add a pause between executions to help minimize caching data would also be helpful. Thank You
I did see your other post with the suggestion about testing multiple bind values. It is definitely something we will consider in the future.
Regarding the other option to pause between executions, we may need to think it through first. On one hand, introducing the pause will lengthen the time to batch run. On the other hand, it may not be that reliable to use pause to clear data cache. For example, if you have a dedicated testing environment, there may not be other SQL running at all and you can’t control if the cached data is really flushed out even if there is a long pause between each run.