All Forums Database
oshun 43 posts Joined 07/10
27 Sep 2015
Tactical Workload

Hi,
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
Roland
 

visit my private blog at http://www.dwhpro.com
CarlosAL 512 posts Joined 04/08
28 Sep 2015

Hi.
Parsing and/or dictionary blocks? (FirstStepTime-StartTime)
Flow control?
Cheers.
Carlos.

oshun 43 posts Joined 07/10
29 Sep 2015

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.

visit my private blog at http://www.dwhpro.com

talk2soumya 28 posts Joined 11/11
29 Sep 2015

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

pinaldba 4 posts Joined 09/10
30 Sep 2015

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. 

You must sign in to leave a comment.