Slowness in Teradata system

Database
Enthusiast

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

5 REPLIES
Enthusiast

Re: Slowness in Teradata system

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.

Senior Apprentice

Re: Slowness in Teradata system

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.

Enthusiast

Re: Slowness in Teradata system

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.

Re: Slowness in Teradata system

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

Enthusiast

Re: Slowness in Teradata system

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)