Is there SQL I can submit to find what percentage of the resources are currently being used by the top consuming queries ? For example, I assuming currently a query can get 100% of the resources. how do I find the top consuming SQL queries using 50%, 75% , etc ?
We are currently on TD13.10
You can easily sort the Query Portlet in Viewpoint by CPU-Delta.
If you want to write a query do that on your own there's the SQL PMon API, e.g.
SELECT 100 * AvgAmpCPUSec / SUM(AvgAmpCPUSec) OVER () AS pct
FROM TABLE (syslib.MonitorSession(-1,'*',0)) AS dt
WHERE AvgAmpCPUSec > 0
ORDER BY pct DESC
PMon data is always snapshot data.
You might find historical data within DBQL, but Viewpoint should be the easiest way to find resource intensive queries using the Query Spotlight portlet.
Related to this thread I have a question:
How to find out the max. CPU Secs that is available on a system.
The situation is like follows:
We have 2 clients sharing the same system using COD: 87,5% for one, rest for the other. SLES 10.0 (soft limit).
Now the clients like to know whether they were using more or less CPU than defined in COD (for capacity planning).
Currently, I'm using AMPCPUTIME and USERNAME in DBQLogTbl to find out who has consumed how much CPU Secs.
However there is no data about the max. available CPU Secs on the system respectively how much CPU ist for 87,5% and how much for the rest.
So it's not possible to get information on Over- or Under-Usage at each client.
Would you please help me in this topic.
This is from the Resource Usage Macros and Tables manual:
Thanks Dieter. But ResUsage-Tables deliver no information on User/WD. So I still can not find out how much CPU was consumed by a client. Is there any way to build a relation between ResUsage and DBQLogTbl.
...Now I think my problem could be solved by trying to answer the question:
Does AMPCPUTime on DBQLogTbl correspond to CPUUServ, CPUUExec or the sum of both?