Avg user wait time for <user_task_ID>: <abs_duration> (<rel_duration>) ms, <number_of> waits, <appl_pid_and_node>
Waiting times occur when users do not get the required resources.
In a lock collision, the wait for a lock to be released is very long. This can be caused by a transaction that takes too long. Long wait times (task state vwait) can also occur when many applications want to lock the same object. If the locks are requested by other sessions, the database system attempts to execute locking tasks with higher priority within the database kernel.
For information on the current locks, see the system tables SYSINFO.TRANSACTIONS, DOMAIN.LOCKS and DOMAIN.LOCKWAITS of the database instance.
As waiting situation have an remarkable impact on performance you should find out the reason. If the user task is waiting to get a lock, f.e. there might be possibilities to make improvements on application level.
Check the following aspects of the application:
- Is it suitable for isolation level 0 (uncommitted)?
- Can the time between the lock request and the COMMIT statement be reduced?
- Can you avoid simultaneous locks on the same row by splitting tables
Relevant DB-Analyzer Files
Helpful with ...
|DBAN_LOCKS.csv||Information about lock statistics.|
|DBAN_TRANSACTIONS.csv||Information about timeouts caused by locks, lock list escalations.|
Helpful with ...
Expert Sessions (Link to trainings)
Content / Helpful with ...
|Session 27: SAP MaxDB Multi Tasking||Find general information about the concept of multi tasking.|
|Session 12: Analysis of SQL Locking Situations||In case of waiting situations because of SQL locks you find detailed information about the locking bebaviour.|