Very nice article!
Anyway, how about a system with high Node CPU Skew? Which metrics do we need to check to be able to identify suspect queries?
I appreciate your response.
Thanks Cesar, That's a good question. One way I could think off is by identifying if there is a Node CPU Skew. This can be done by looking at System Health portlet, if you see high Node CPU Skew you can go to Node Resource portlet to find the Node that is skewed and then drill down to skewed node to see the Vprocs that is skewed. In Viewpoint 14.10 we have HOT AMP report in query monitor, the AMP that you see skewed in Node Resource portlet should be listed in HOT AMP report. You can drill down to the AMP and see the session that are logged on to the AMP and find the session that is skewed.
I don't have a straight forward answer to resolve slowness issue. Neither I think there is any rule of thumb. System slowness can be for multiple reasons, few that I can think of..
1. One Query or set of queries consuming lot of resources
2. Several queries getting blocked (This is not true in your case)
3. Node parallel efficiency is impacted (Node resource portlet can help understanding this)
4. Bad TASM rules
5. System is in flow control.. Out of AWTs..
What I see is that you have only 26 active sessions but 308 sessions in delay queue.. You may start with checking if I am completly utilizing my system resource, using system health portlet. If not, then revisit your TASM rules so see why you have 308 queries in delay queue. If yes, then what are the active queries doing, are there any bad long running queries hogging up all the resources (if system health shows high CPU utilization check for High impact CPU, High CPU Use..)... Other than that I can't say much without looking at your system.. Sorry I was not of much help here..
Thanks Shrity so much.
Would u plz tell me what is requests (count) in Session Info in query monitor overview tab. Is it also related to slowness issue? Means culprit queries can have high requests count?
Requests (count) in Session Info in query monitor is number of requests (Queries) submitted by the session. If a session is consuming high resource and request count is high you know why resource consumption by that session is high, but if a session is consuming high resource and request count is low 2 or 3 you may want to look at those queries.
Is there a way to display the query based on SQL Text. There are some adhoc queries run by users which we want to kill as soon as they come to the system. We can't track DBQL because it only registered in DBQL after the completed execution. Is there a way in viewpoint we can highlight them based on sQL text pattern match instead going through each SQL.
There is no way you can highlight query based on SQL txt. If you could attach a queryband to the query then you can either filter the query using workload management rule or sort it in the datagrid of query monitor portlet.