DBQL Metrics

Database

DBQL Metrics

Hi ,

Can some please explain / eloborate the DBQL Metrics and how they are interrelated like the below with some example with any record count

TotalIOCount,

MaxAmpIO,

MaxAmpCPU,

ImpactCPU

ImpactIO,

CPUSKew,

IOSkew,

IOSkewWastage,

CPUSkewWastage

Thanks

8 REPLIES
Junior Contributor

Re: DBQL Metrics

Total = sum of all AMPs CPU/IO usage

Max = maximum CPU/IO usage across all indicidual AMPs

Skew = Comparing Max with Avg usage. On a parallel system the slowest AMP determines the speed, when the work is not evenly spread across AMPs it's slowing down the elapsed time and might withhold resources for other queries

CASE

   WHEN (AMPCPUTime / (HASHAMP()+1) ) =0 OR NumOfActiveAMPs = 1 THEN 0 

   ELSE MaxAmpCPUTimeNorm/(AMPCPUTimeNorm / (HASHAMP()+1) ) 

END

Impact = Max * number of AMPs in system, i.e. resource usage based on Skew

CASE WHEN NumOfActiveAMPs = 1 THEN MaxAmpCPUTime * (HASHAMP()+1

 

Wastage = don't know probably Impact - Total



Have a look at the definition to see the exact calculation used at your site.

Dieter

Re: DBQL Metrics

Hi Dieter,

Thanks for your response and explanations.

One more quick question. When we say I/O..is it the number of records it is processing. for Example when i say select * from order_header where customer_number = 12345 where customer_number neither PI nor any index. then Numofresultsrow will be 1 but TotalIO count will 542334. What exactly this TotalIO Count mean here?

Thanks

Junior Contributor

Re: DBQL Metrics

I/O is the number of logical disk I/Os not the number of records. The esitmated vs. actual number of records is found in QryLogSteps.

In your case the high count might indicate a Full Table Scan reading 542334 datablocks.

Dieter 

Re: DBQL Metrics

Thanks Dieter. It was really helpful

Teradata Employee

Re: DBQL Metrics

Hi,

Can you guys please explain , why in the calculation of PJI and UII we multiply AmpCpuTime by 1000

Thanks

Sandesh 

Enthusiast

Re: DBQL Metrics

because they are based onCPU Milliseconds per I/OException criteria. An anticipated range of appropriate CPU milliseconds per I/O values to set typically varies between 3 and 10. A typical query tendsto fall between 1 and 2. A legitimate small‐table product join query tends to fall between 2 and 3.High CPUqueries are generally > 3.

Re: DBQL Metrics

Hi dieter,

could you please help me to understand how AMPCPUTIMENORM, MAXAMPCPUTIMENORM and MINAMPCPUTIMENORM is calculated. And how it differs compared to AMPCPUTIME.

Enthusiast

Re: DBQL Metrics

I'm not Dieter... and i don't play him on TV, but to answer your question, The ...Norm columns are intended to be used in co-existence systems, multiple generations of hardware in one system and the performance stats are 'normalized' across generations.