Slow-Running Transactions

As a database administrator, users will often ask you to investigate why a particular transaction or set of transactions is running slowly. There are many factors to take into account when tracking down these types of problems.

Analysis

Your research can take a long time and you should therefore make sure you have as much background information as possible before starting.

Ask the users for the following information:

·        Has the transaction always been slow, or has it only just started to slow down?

·        Is the problem in a new program or transaction?

·        Is the slowdown only during peak periods or is it fairly constant?

·        Have there been recent changes in workload?

·        Is just this one transaction slow or are other transactions or applications also now performing poorly?

Attempt to localize the performance bottleneck using the information you obtained.

If you feel this issue may be isolated to one particular transaction, program or application, you may also need assistance from the application developers. These developers understand the process flow of the application, and can help you to change and test statements in the program.

Analysis Options

Use the following analysis options:

·        Have the system display the transactions (Transactions).

·        Find out whether any exclusive wait situations exist (Wait Situations (Exclusive)).

·        Check for missing indices in the tables used by the transaction (Index Analysis).

·        Check the cache hit rates or speed of table searches. To do this, analyze the bottlenecks, for example.

·        Use the command monitor to perform runtime analyses of any long–running SQL statements.