in black and white
Main menu
Share a book About us Home
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 148 149 150 151 152 153 < 154 > 155 156 157 158 159 160 .. 218 >> Next

Determining the Cause of a Slowdown or Hang

You can use the following utilities and tools to help determine the cause of a slowdown. Some steps for immediate action are suggested in the next sections.

Tool Hint
AWS console Check the status of all hardware components.
BTEQ Log onto Teradata and try to select time and date.
syscheck See if any kernel attributes are reaching a saturation level.

Teradata RDBMS Database Administration

12 - 23 Chapter 12: Troubleshooting

Troubleshooting a Slow or Hung Job


Vproc Manager


Determine the current state of each AMP and of Teradata RDBMS. For example, is Teradata RDBMS in debugger mode?


Check for messages from hardware errors, also for error 2631 records indicating repeated locking contentions.

Teradata RDBMS log (on UNIX, /var/adm/streams) Look for messages indicating many mini-cylpacks, deadlocks, memory paging, and so on.
Update Space Update DBC To rule out lack of available disk space, perform this procedure:
DBC.DiskSpace view Step Action
1 Enter the Update Space utility (for all types of space and all databases) to update current user space values: UPDATE ALL SPACE FOR ALL DATABASE;
2 Enter the Update DBC utility to update the values for system user DBC. UPDATE DBC;
3 Query the DBC.DiskSpace view to find currently available space with this statement: SELECT MAX(CurrentPerm) ,SUM(CurrentPerm) FROM DBC.DiskSpace ; (For help, see "DiskSpace View" on page 3-14.)


Make sure an archive job did not leave active HUT locks on data tables.

Lock Display or Locking Logger

Investigate whether queries are blocked by a long-running job holding a non-shareable lock.

xcpustate (UNIX) Teradata Manager (Windows 2000)

Display current PDE parameters.

puma -p

Teradata Manager (Windows 2000)

Determine the number and process id of tasks, mailboxes, and monitors in use.

Kill processes or end tasks that are hung.

CheckTable LEVEL 3 (if you use PPIs)

Make sure rows are properly located. If errors are reported, see "Solving PPI and RI Validation Errors" on page 12-27.

Teradata RDBMS Database Administration 12 - 23 Chapter 12: Troubleshooting

Troubleshooting a Slow or Hung Job

Tool Hint
ResUsage reports Determine congested resources such as events that are I/O or CPU intensive, an AMP with a much higher number of last-done events, and so forth. Note: In general, NCR recommends using Resource Usage through the Performance Monitor facility on a regular basis. Resource Usage is able to organize the data in a useful way, while Performance Monitor can provide real-time data analysis.

Solving Lock, Partition Evaluation, or Resource Problems

You can resolve some problems quickly and fairly easily:

Issue Action

HUT locks Run ShowLocks to find any outstanding host utility (HUT) locks.

Deadlock If you run multi-tier applications with many network users logged on under the same userID, you can find the originator of a problem request by using one of the following to find the session:

Query Session Utility

Teradata Performance Monitor

DBC.SessionInfo view

Query the LogonSource column of SessionInfo to obtain the TDPID, user name, and executable name of the session.For instance, in the example under "DBC.SessionInfo View" on page 6-30, the first network session logged on via TDP IETTST by user ADMIN using BTEQ.

You need to terminate a blocking job with one of the several tools available, as follows:

IF . THEN immediately .
locks are listed on the client or a NetVault server, start an ARC session and submit the RELEASE LOCK command.
no locks are listed run the Lock Display Utility or, if you enabled the locklogger option, the dumplocklog command of the Locking Logger Utility, to check for transaction lock contentions. If the utility shows a bottleneck caused by an active session, go to "Deadlock" on page 12-25. If no active locking queues or deadlocks are reported, review other issues.

Teradata RDBMS Database Administration

12 - 23 Chapter 12: Troubleshooting

Troubleshooting a Slow or Hung Job

Issue Action
Deadlocks (continued) When you have identified the session and user, you can choose to do one of the following: Dynamically lower the priority of the heavy user with one of: - SQL: SET SESSION ACCOUNT='prioritystring' FOR [sessionID/requestID] - Performance Monitor: MODIFY USER ACCOUNT facility - PM/API: SET SESSION ACCOUNT Abort the heavy user manually with one of the following: - Performance Monitor ABORT SESSION facility - TDP LOGOFF command - Gateway KILL command
Transaction rollbacks with partitioned tables If transaction rollbacks are occurring because a partitioning expression is resulting in evaluation errors, do one of the following: Change the partitioning expression Delete the rows causing the problem Remove partitioning from the table Drop the table For more on troubleshooting partitioned tables, see "Solving PPI and RI Validation Errors" on page 12-27.
Previous << 1 .. 148 149 150 151 152 153 < 154 > 155 156 157 158 159 160 .. 218 >> Next