Reduce Contention

Performance impact: Large

Database contention can substantially impede application performance.

To reduce contention in your application:

• Choose the appropriate locking method. See Choose the Best Method of Locking.

• Distribute data strategically in multiple tables or databases.

If your application suffers a decrease in performance because of lock contention and a lack of concurrency, reducing contention is an important first step in improving performance.

The lock.locks_granted.immediate, lock.locks_granted.wait and lock.timeouts columns in the SYS.SYSTEMSTATS table provide some information on lock contention:

• lock.locks_granted.immediate counts how often a lock was available and was immediately granted at lock request time.

• lock.locks_granted.wait counts how often a lock request was granted after the requestor had to wait for the lock to become available.

• lock.timeouts counts how often a lock request was not granted because the requestor did not want to wait for the lock to become available.

If limited concurrency results in a lack of throughput, or if response time is an issue, an application can reduce the number of threads or processes making API (JDBC, ODBC, or OCI) calls. Using fewer threads requires some queuing and scheduling on the part of the application, which has to trade off some CPU time for a decrease in contention and wait time. The result is higher performance for low-concurrency applications that spend the bulk of their time in the database.