Can some please explain / eloborate the DBQL Metrics and how they are interrelated like the below with some example with any record count
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
WHEN (AMPCPUTime / (HASHAMP()+1) ) =0 OR NumOfActiveAMPs = 1 THEN 0
ELSE MaxAmpCPUTimeNorm/(AMPCPUTimeNorm / (HASHAMP()+1) )
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
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?
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.
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.
could you please help me to understand how AMPCPUTIMENORM, MAXAMPCPUTIMENORM and MINAMPCPUTIMENORM is calculated. And how it differs compared to AMPCPUTIME.
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.