Save big on AI tools today!

Oracle Database Contention 1

This blog describes contentions in Oracle Database System.

Angudi Tech

4/27/20261 min read

For any application, it is essential to possess an efficient database system capable of storing data and processing requests at high speed. Sessions in the Oracle database spend the majority of their time waiting for resources to be accessible. However, all these resources within the database are shared among one another, and their availability is entirely contingent upon the management of each resource.

These conflicts occur at the memory, storage, or system level. When contention within the database is manageable, it can be addressed. However, when it arises from external resources of the database, it becomes challenging to resolve.

There are different types of contentions for the database,

1) Contention while locking on row level, table level, index level, etc.

2) Contention while holding latch in SGA, PGA or UGA.

3) Physical data block level.

Is every lock necessarily detrimental? Who should be concerned about contentions within the database?

Well, the Oracle database upholds ACID (Atomicity, Consistency, Isolation and Durability) properties for all sessions that connect to it, and to achieve this, it must enforce specific regulations regarding the acquisition of locks on database resources. Hence not all locks are bad.

The question then arises: how can one determine which locks are effective and which are ineffective?

Due to poor application design, incorrect table or index structures, and similar issues, locks can unnecessarily hold resources, causing other users to wait for access to those resources. This results in a backlog of requests for subsequent connections and an extended queue of requests. It is a well designed application when ACID property is well maintained along with performance.

We will continue the discussion in next blog.....