All Forums Database
JustMe 76 posts Joined 03/07
17 Jan 2014
Find usage

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

dnoeth 4628 posts Joined 11/04
17 Jan 2014

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
   ,other columns
FROM TABLE (syslib.MonitorSession(-1,'*',0)) AS dt
WHERE AvgAmpCPUSec > 0
ORDER BY pct DESC

 

Dieter

JustMe 76 posts Joined 03/07
17 Jan 2014

Thank you so much!

JustMe 76 posts Joined 03/07
17 Jan 2014

How can I adjust this to show a period of time instead of just the current date?

dnoeth 4628 posts Joined 11/04
17 Jan 2014

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.

Dieter

hyma 33 posts Joined 07/11
20 Jan 2014

Hi Dieter,
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.
Thanks!

hyma 33 posts Joined 07/11
20 Jan 2014

ps: We're running TD14.00.

dnoeth 4628 posts Joined 11/04
20 Jan 2014

Check dbc.ResUsageScpu/ResScpuView. 
This is from the Resource Usage Macros and Tables manual:

The CPU utilization columns are aggregates representing all CPUs on the node. CPU

utilization by user code is further subdivided by the vproc tables.

 

• CPU idle time = CPUIdle + CPUIoWait

• CPU busy time = CPUUServ + CPUUExec

 

Theoretically, the values of these four columns, for any given interval, account for total CPU

time on the node. That is, these columns should total to 100 * Secs * number of CPUs on the

node, since each CPU is always in exactly one of these four states. In practice, there is

occasionally a very small plus or minus difference from this theoretical total.

Dieter

hyma 33 posts Joined 07/11
20 Jan 2014

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. 

hyma 33 posts Joined 07/11
21 Jan 2014

...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?   

dnoeth 4628 posts Joined 11/04
21 Jan 2014

You'll find similar CPU info in several other ResUsage tables.
If your calculation is based on WDs you should check dbc.ResSpsView.
DBQL is not really reliable for consumed CPU seconds, not all queries might be logged and the last step of an aborted query will be missing (e.g. running a cross join for an hour before aborting).
AmpUsage is more reliable and easier to query.

Dieter

hyma 33 posts Joined 07/11
21 Jan 2014

In this case, having WDs defined, I'll use dbc.ResSpsView.
It remains following questions:
1. To calculate CPU time that a client has consumed (=User CPU), which metric in ResSpsView should I take: CPUUServ, CPUUExec or sum of both? 
2. In ResSpsView we have no CPUIdle, CPUIoWait like in dbc.ResUsageScpu. Which metrics in ResSpsView should be chosen to calculate the max. CPU time? 
 
2. Same issue but for IOs: which tables/views should be used to get consumed IOs/client compared to max.IOs? 
Thank you again for your help.

dnoeth 4628 posts Joined 11/04
21 Jan 2014

#1: probably CpuTime or CptPct
#2: of course there's no idle, etc., to which WD this should be assigned.
The max available CPU time will not change unless you add new nodes or upgarde the CPU. So simply calculate it once.
#3: similar to #2, calculate the maximum available IO once.

Dieter

hyma 33 posts Joined 07/11
21 Jan 2014

I got now CPU Usage.
For IO, I think to read WDId, IOCOmpleted and IOCompletedKB from ResSpsView as effective values. But for the max. value for IO? Do you think which table I must use to get this information? Thanks.

hyma 33 posts Joined 07/11
22 Jan 2014

I got now some metrics in ResSpsView for calculating IOs and the max. IO specified in the system order. Thanks again.

You must sign in to leave a comment.