All Forums Viewpoint
Kishore_1 208 posts Joined 03/10
29 Jul 2014
Session CPU,IO Skew

How is session level CPU and IO skew calculated as dispalyed in Viewpoint for a particular session (in Query Monition) in the "Skew" tab for a particular session?Is it the skew factor of the currently executing query of the session.There are top 3 and bottom 3 Amp (along with Amp#)  and their corresponding CPU and IO consumption values.Is there any relationship between these and Session CPU /IO skew? 

stever 149 posts Joined 08/09
30 Jul 2014

In Query Monitor, the CPU and I/O skew is calculated based on the last sample period only.  On the Skew tab, all these data points are also for the last sample period.  Skew is calculated as 1 - AvgAMPCPU / MaxAMPCPU.  Since the Skew tab displays both the highest AMP CPU and the average AMP CPU, you can calculate the skew based on the values on this screen.
The sample period is the same as the session sampling rate set on your system.  If you have admin access to Viewpoint, you can view the session sampling rate in the Monitored Systems portlet (or the Teradata Systems portlet on Viewpoint 14.10 and earlier).  It's in the Monitor Rates screen of this portlet.
We realize calculating skew based on the last sample period is not necessarily a good indication of a particular request's skew.  However, for current versions of Teradata database this is the best realtime data to which we have access.  Look for some improvements in this functionality in future versions of Teradata database and Viewpoint!

Kishore_1 208 posts Joined 03/10
30 Jul 2014

Hi Stever,
Thanks for the info.
I  believe, for more real time update we can use the SYSLIB table function as in the following query:

     SEL UserName (TITLE 'Users', format 'X(20)'),

     SessionNo (TITLE 'Session', FORMAT 'Z(9)ZZZ'),

      (100 * (1 - (AvgAmpCPUSec / NULLIFZERO(HotAmp1CPU))))        (TITLE 'Amp CPU Skew %',FORMAT 'ZZ9'),

    (100 * (1 - (AvgAmpIOCnt / NULLIFZERO(HotAmp1IO))))        (TITLE 'Amp IOSkew %',FORMAT 'ZZ9')

     FROM TABLE (SYSLIB.MonitorSession(-1,'*',0)) AS t1

     WHERE AmpState='ACTIVE' AND HotAmp1CPU > 0 AND HotAmp1IO > 0

     ORDER BY 1,2;


stever 149 posts Joined 08/09
30 Jul 2014

Thanks, but Viewpoint is already using the PM/API MONITOR SESSION call which is equivalent to the SYSLIB.MonitorSession function you are referencing.  The problem is the same in both cases: the HotAmp1CPU is only the CPU on the hottest AMP during the last sample period.  So say for example the sample period is 30 seconds, but the query has already been running for 10 minutes.  You are only getting a picture of the skew for 1/20 of the query execution duration.  Without knowing the hottest AMP over the entire request, you can't calculate an accurate request skew metric.  The hottest AMP CPU for the entire request is available in DBQL, but that's not realtime monitoring.

Kishore_1 208 posts Joined 03/10
30 Jul 2014

Thank you so much Stever for clarifying!I got the point.

er.vipin 7 posts Joined 09/11
30 Jul 2015

How Heatmap portlet calcultes AMPCPUSkew and AMPIOSkew ? I would like to get similar information from Resusage table.
I could figure out that viewpoint is fetching data from ResusageSVPR, but could not find that query/method it is using.
Please help me to understand this.

You must sign in to leave a comment.