All Forums Database
samurai07 4 posts Joined 03/13
22 Jun 2013
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

dnoeth 4628 posts Joined 11/04
23 Jun 2013

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
 

Dieter

samurai07 4 posts Joined 03/13
23 Jun 2013

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

dnoeth 4628 posts Joined 11/04
25 Jun 2013

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 

Dieter

samurai07 4 posts Joined 03/13
27 Jun 2013

Thanks Dieter. It was really helpful

sg186048 5 posts Joined 09/12
17 Jul 2013

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

abhijitvyas 51 posts Joined 08/06
17 Jul 2013

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.

Arunkumargyara 3 posts Joined 03/16
24 Jul 2016

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

Arun

VandeBergB 182 posts Joined 09/06
25 Jul 2016

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.
 

Some drink from the fountain of knowledge, others just gargle.

You must sign in to leave a comment.