All Forums Database
Teradata_User1 13 posts Joined 04/14
06 May 2014
Slowness in Teradata system

Hi,
We are facing slowness in teradata system. Also the teradata system performance is degraded because of slowness.
Kindly let me know what all parameters we need to check from DBA prospectives. Also let me know is there any perticular query to identify the root cause for slowness issue. Let me know the procedure to improve the performance of the system.
Urgent help required.
 
Regards,
Mahendra
 

Raja_KT 1246 posts Joined 07/09
07 May 2014

Since it is urgent, a quick thought if they help you.

Loads maybe high, number of users may increase.Check locks it may be while doing backup, who knows, hut locks(showlocks util) , blocked sessions,, crashdumps,check skewness,packdisk an  expensive overhead operation , not recommended  ,MiniCylpacks running.
Check DBQL, resource usage tables if you enable to trace the culprits.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
07 May 2014

Hi Mahendra,
what's your TD release?
Do you have Viewpoint? Then simply use this to find the session(s) consuming most resources or blocking others.
If there's no Viewpoint you can do the same using PMon.
After the fact investigation needs DBQL data.

Dieter

Teradata_User1 13 posts Joined 04/14
07 May 2014

Thanks for your valuable suggestions.
Currently we are using Teradata 13.10 version. We have viewpoint also. 
Kindly let me know any specific script/query to know the current system utilization in teradata. Or any specific table name so that I will get to know which system parameter is degrading the system performance.
 
Regards,
Mahendra.

rajchatt84 1 post Joined 11/10
08 May 2014

Though Slowness can happen for lots of reason. The below query might help you
 
/************************************************************************/
/* DBC.AMPUSAGE - Get CPU Usage & Logical Disk I/Os by Users*/
/* with more than 100,000 cpu seconds */
/************************************************************************/
.export file ampusage.out
.set defaults
.set width 110
 
SELECT A.accountName (Format 'x(18)') (Title 'AMPUsage//Acct Name')
, A.username (Format 'x(22)') (Title 'User Name')
, SUM(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'CPUtime')
, SUM(A.DiskIO) (Format 'zzz,zzz,zzz,zz9') (Title 'DiskIO')
, AVG(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'AvgCPUtime')
, MAX(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'MAXCPUtime')
, MAX(A.CPUTime)/nullifzero(AVG(A.CPUTime) )(Format 'zz9.99') (Title 'CPU//Skew//Effect')
FROM DBC.AMPUsage A
GROUP BY 1, 2
HAVING SUM(CPUTime) > 1e5
ORDER BY 3 desc, 1, 2;
 
.set defaults
.set width 80

regards,
Raj

krishaneesh 140 posts Joined 04/13
09 May 2014

if the health of the system is normal and the cpu utilization is not too much, can you check if there are any hardware array disk failures on the system. is your system an appliance version or enterprise version with no GHS(global hot spare)

You must sign in to leave a comment.