DPA currently polls the database and gathers information about long running queries. Queries that run quickly but then hold locks are not identified as problems, and the SQL text of the queries is not available. The result is that the SQL for a victim of a deadlock situation or idle blocker situation can be seen in the tool, but the offending statement is often not available and is a hard to find mystery.
This feature would create a new data store of locked tables and indexes and would gather information on the time that the lock was held, what the connection id was, and what statements were run during the time that the lock was held. In the case of the idle blocker situation, when it is noticed that the lock is being held longer than a second, and there is currently no query executing on the connection, the DB can be queried to determine the last query executed on the connection that is holding the lock. The additional information of the resource being locked (the particular table, index) will be very helpful in understanding the contention, in addition to the SQL last run, which may or may not be the SQL that started holding the lock. However, having the last SQL run will at least help nail down the application function that is responsible for the idle blocker.
Looking at long held locks across multiple connections can help further with resolving deadlock situations. This would be similar to the "SQL Statements executed while holding the lock" display that is currently in the product.
