15.00 - Troubleshooting Performance Problems - MultiLoad

Teradata MultiLoad Reference

Programming Reference

Troubleshooting Performance Problems

Use the following procedure to determine the cause of Teradata MultiLoad performance problems that do not produce a specific error indication.

1 Determine which phase of the Teradata MultiLoad job is causing the poor performance.

If it is during the acquisition phase, when data is acquired from the client system, the problem may be with the client system. If it is during the application phase, when data is applied to the target tables, the problem is not likely to be with the client system.

The Teradata MultiLoad job output lists the job phases and other useful information. Save these listings for evaluation later.

2 Use the Teradata Database QrySessn utility to monitor the progress of the Teradata MultiLoad job.

3 Check for locks on the Teradata MultiLoad target tables:

  • Use the Teradata Database Show Locks utility to check for Hut locks.
  • Use the Query Session utility or other utilities that use the performance monitor feature of Teradata Database (Amon, or Pmon) to check for the “blocked” status, indicating transaction locks.
  • 4 Check the DBC.Resusage table for problem areas, such as data bus or CPU capacities at or near 100 percent for one or more processors.

    5 Determine whether the target tables have NUSIs. NUSIs degrade Teradata MultiLoad performance because the utility builds a separate NUSI change row to be applied to each NUSI subtable after all of the rows have been applied to the primary table.

    6 Check the size of the error tables. Write operations to the fallback error tables are performed at normal SQL speed, which is much slower than normal Teradata MultiLoad tasks.

    7 Verify that the primary index is unique. NUPIs can cause severe Teradata MultiLoad performance problems.