I have a question regarding tactical queries. I designed some tactical queries with index (USI) access to the rows. If i run this query on an unloaded system, response time is between 1-2 seconds. On a loaded system, run times go up to 30 seconds. As the client is not assigning my queries to a tactical workload, it's running together with many other queries in a high priority workload.
The strange thing is: In QryLog I can see always,independently from the system load, the run time being between 1-3 seconds (FIRSTRESPTIME-STARTTIME).
But, the manually measured run time is 30 second on a heavily loaded system. There are no system/workload delays or blocks happening. I wonder were the time is lost and if the information is available in any of the QryLog Tables / columns?
Thanks in advance
I checked for dictionary blocks, but the query was not blocked. But thanks for the hint, I will check if there was flow control during the execution of my query.
Work load management works on optimizer estimation before run. If your query is running a differnt workload rather than tactical then check the query explain plan and see if the estimation meets the tactical query ruleset.
Generally tactical rule set is based on single amp select or response time less than a second for select query.
Blocking or flow control will not change your workload.
one more thing u need to verify if the query started in tactical and got demoted to other workload because of exception
It is worth to verify that how much weight has been allocated for the tactical workload as it only suffer during busy system. Also, you need to consider no of concurrent sessions running into tactical workload during heavily loaded system and normal system.