Standards for the impact CPU

Database
Enthusiast

Standards for the impact CPU

Hi All, 

 

We are on Teradata 16.20 and database has  216 amps and  size is 180 TB .

We are planning on  getting the standard numbers for the Impact CPU,IO skew,CPU Skew,UII,PJI .

The below are the queries I am following .Can someone suggest if they needs to be changed.

 

SyntaxEditor Code Snippet

 ,((a.firstresptime-a.firststeptime) DAY(4) TO SECOND) AS QryRespTime     , a.AMPCPUTime AS SumCPU , a.TotalIOCount AS SumIO   ,CASE WHEN a.AMPCPUTime < 1 OR (a.AMPCPUTime /  (HASHAMP()+1)) =0 THEN 0
         ELSE MaxAmpCPUTime/(a.AMPCPUTime /  (HASHAMP()+1))   END (DEC(8,2)) AS CPUSKW   ,CASE WHEN a.AMPCPUTime < 1 OR (a.TotalIOCount /  (HASHAMP()+1)) =0 THEN 0
         ELSE a.MaxAmpIO/(a.TotalIOCount /  (HASHAMP()+1))   END (DEC(8,2)) AS IOSKW   ,CASE WHEN a.AMPCPUTime < 1 OR a.TotalIOCount = 0 THEN 0  ELSE (a.AMPCPUTime *1000)/a.TotalIOCount  END  AS PJI   ,CASE WHEN a.AMPCPUTime < 1 OR a.AMPCPUTime = 0 THEN 0  ELSE a.TotalIOCount/(a.AMPCPUTime *1000) END  AS UII

I am collecting queries for impact CPU >5000 and UII>2 .Please let me know they need to be changed.

 

Thanks.

5 REPLIES 5
Teradata Employee

Re: Standards for the impact CPU

Better to use NumOfActiveAMPs rather than HASHAMP()+1.

As far as best thresholds for Impact CPU or UII or PJI ... those will be site-dependent, based on workload as well as system capacity.

 

If you find you are capturing too many queries or hardly any, revise the limits. You may even need different thresholds for different workloads.

Junior Supporter

Re: Standards for the impact CPU

Typically this is what we use :

SyntaxEditor Code Snippet

(   (CpuSkw > 1.25 /*2.0*/  
    OR  IOSkw > 1.25 /*2.0*/ )    /* Start with 2.0 as a threshold for CoExistence systems */
                OR UII > 3               /* UnNecessary IO Indicator - may offer an opportunity for Indexing */
                OR PJI > 3               /* Could use a higher relative threshold (6) for Older Node systems  */                ) 

And i think, you should not keep a threshold on Impact CPU. Rather than that you should sort it in descending order and take top 50. Many a times, the Impact CPU will go more than 100K. 

--Samir

Teradata Employee

Re: Standards for the impact CPU

 Use numofactiveamps instead of hashamp() + 1.   The hashamp approach overweights single row inserts, pushing customers to use load utilities even when the loads are too small to benefit.   You also lose visibility into the value of group amp operations.  You will have to make the change for TD16.20 MAPS in any case.

Highlighted
Enthusiast

Re: Standards for the impact CPU

How to find the active number of amps  for  active sessions and for the queries which are executed already .?

 

Teradata Employee

Re: Standards for the impact CPU

NumOfActiveAMPs is available in DBQL.