All Forums Database
Kishore_1 208 posts Joined 03/10
05 Jan 2011
Performance Stats

Hi ,
I am using the following table to collect performance statistics on a query, but getting no rows.

SELECT CollectTimeStamp,
SessionID,
UserName,
DefaultDatabase as DatabaseName,
StartTime,
FirstRespTime,
(FirstRespTime - StartTime) hour to second(4) as FirstRespElapsedTime,
TotalIOCount,
AMPCPUTime+ParserCPUTime TotalCPUTime,
SpoolUsage/(1024*1024*1024) as Spool_GB,
100-(nullifzero(AMPCPUTime/HASHAMP())/(MaxAMPCPUTime) *100) "Skew Factor",
delaytime,
errorcode,
ErrorText,
QueryText
FROM dbc.dbqlogtbl
where
sessionid=123567
order by CollectTimeStamp desc ;

How to get the performance stats? Is there any threshold limit set for any query stats to be collected in dbc.dbqlogtbl ?
How to check that? How to get the stats , if the query is light?

Glass 225 posts Joined 04/10
06 Jan 2011

You'll need to begin dbql logging for any users/accounts you want to log.
to determine what you have now:
sel * from dbc.dbqlrules;
After logging has been enabled your query will return results, for another session of course.

SmarakDas 51 posts Joined 02/12
05 Nov 2012

In addition to the above Performance Metrics, other important factors which also needs to be considered are:
(a) ImpactCPU: MaxAMPCPUTime * (HASHAMP() + 1)
(b) Parallel Efficiency: AMPCPUTime / (EffectiveCPU + 1) * 100
(c) CPU Skew and IO Skew.

You must sign in to leave a comment.