Find usage

Database
Enthusiast

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

14 REPLIES
Junior Contributor

Re: Find usage

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
Enthusiast

Re: Find usage

Thank you so much!

Enthusiast

Re: Find usage

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

Junior Contributor

Re: Find usage

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.

Teradata Employee

Re: Find usage

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!

Teradata Employee

Re: Find usage

ps: We're running TD14.00.

Junior Contributor

Re: Find usage

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.

Teradata Employee

Re: Find usage

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. 

Teradata Employee

Re: Find usage

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