Nov 17, 2014

Latches and Locks in Oracle

Latches in Oracle

There are lots of concepts about latches and locks in oracle. Few documents I read and and investigated in many production environments. 

About Latch in Oracle:
Latches are serialization mechanisms that protect areas of Oracle’s shared memory (the SGA).  In simple terms latches prevent two processes from simultaneously updating - and possibly corrupting - the same area of the SGA.. It is low-level serialization mechanism.
For example, the data buffer latches (sometimes called LRU latches) ensure that Oracle processes are 'serialized', such that only one process may alter the data buffer address chain.  This twiddling of RAM addresses happens very fast (RAM speed is expressed in nanoseconds), yet busy Oracle databases may experience waits on these events.
In-other way we can say, Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial execution of kernel code.  The LRU (least recently used) latches are used when seeking, adding, or removing a buffer from the buffer cache, an action that can only be done by one process at a time.
Contention on an LRU latch usually means that there is a RAM data block that is in high demand.  If a latch is not available a 'latch free miss' statistics is recorded.

When Latch will occur:
Oracle sessions need to update or read from the SGA for almost all database operations.  For instance:

  1. When a session reads a block from disk, it must modify a free block in the buffer cache and adjust the buffer cache LRU (Least Recently Used) chain.
  2. When a session reads a block from the SGA, it will modify the LRU chain.
  3. When a new SQL statement is parsed, it will be added to the library cache within the SGA.
  4. As modifications are made to blocks, entries are placed in the redo buffer.
  5.  The database writer periodically writes buffers from the cache to disk (and must update their status from “dirty” to “clean”).
  6.  The redo log writer writes entries from the redo buffer to the redo logs.
  7.  Latches prevent any of these operations from colliding and possibly corrupting the SGA. 

How Latches will work:
Because the duration of operations against memory is very small (typically in the order of nanoseconds) and the frequency of latch requests very high, the latching mechanism needs to be very light-weight.   On most systems, a single machine instruction called “test and set” is used to see if the latch is taken (by looking at a specific memory address) and if not, acquire it (by changing the value in the memory address).

If the latch is already in use, Oracle can assume that it will not be in use for long, so rather than go into a passive wait (e.g., relinquish the CPU and go to sleep) Oracle will retry the operation a number of times before giving up.  This algorithm is called acquiring a spin lock and the number of “spins” before sleeping is controlled by the Oracle initialization parameter “_spin_count”.

The first time the session fails to acquire the latch by spinning it will attempt to awaken after a millisecond or so.  Subsequent waits will increase in duration and in extreme circumstances may reach 100s of milliseconds.   In a system suffering from intense contention for latches, these waits will have a severe impact on response time and throughput.

Root causes of Latch contention:
The latches that most frequently affect performance are those protecting the buffer cache, areas of the shared pool and the redo buffer.

  • Library cache and shared pool latches:  These latches protect the library cache in which sharable SQL is stored.  In a well defined application there should be little or no contention for these latches, but in an application that uses literals instead of bind variables (for instance “WHERE surname=’HARRISON’” rather that “WHERE surname=:surname”, library cache contention is common.
  • Cache buffers chain latches: These latches are held when sessions read or write to buffers in the buffer cache. There are typically a very large number of these latches each of which protects only a handful of blocks. Contention on these latches is typically caused by concurrent access to a very “hot” block and the most common type of such a hot block is an index root or branch block (since any index based query must access the root block).
  • Redo copy/redo allocation latches:  These latches protect the redo log buffer, which buffers entries made to the redo log.   These latches were a significant problem in earlier versions of Oracle, but are rarely encountered today. 


Detecting/ Finding latch Contention:
Oracle’s wait interface makes it relatively easy to detect latch contention and – from 10g onwards – to accurately identify the specific latch involved.   In 10 and 11g, each latch has it’s own wait category if waits on the specific latch become significant then we can deduce a latch contention problem.
See more : Click here & here

Latch and Concurrency:
An increase in latching means a decrease in concurrency. For example, excessive hard parse operations create contention for the library cache latch.  Latches are a type of lightweight lock. Locks are serialization devices. Serialization devices inhibit concurrency.  To build applications that have the potential to scale, ones that can service 1 user as well as 1,000 or 10,000 users, the less latching we incur in our approaches, the better off will be.
·         You have to choose always an approach that takes longer to run on the wall clock but that uses 10 percent of the latches. We know that the approach that uses fewer latches will scale substantially better than the approach that uses more latches.  Latch contention increases statement execution time and decreases concurrency.

Latch and Queuing:
Unlike enqueue latches such as row locks, latches do not permit sessions to queue. When a latch becomes available, the first session to request the latch obtains exclusive access to it.
Latch spinning occurs when a process repeatedly requests a latch in a loop, whereas 
Latch sleeping occurs when a process releases the CPU before renewing the latch request.
Typically, an Oracle process acquires a latch for an extremely short time while manipulating or looking at a data structure. For example, while processing a salary update of a single employee, the database may obtain and release thousands of latches.

Data Dictionary:
The V$LATCH view contains detailed latch usage statistics for each latch, including the number of times each latch was requested and waited for.
See more:  Click here & here also

User Action:
Determine which latch is causing the highest amount of contention.
To find the problem latches since database startup, run the following query:

SELECT n.name, l.sleeps
  FROM v$latch l, v$latchname n
  WHERE n.latch#=l.latch# and l.sleeps > 0 order by l.sleeps;

To see latches that are currently a problem on the database run:

SELECT n.name, SUM(w.p3) Sleeps
  FROM V$SESSION_WAIT w, V$LATCHNAME n
 WHERE w.event = `latch free'
   AND w.p2 = n.latch#
 GROUP BY n.name;

Take action based on the latch with the highest number of sleeps.

Q&A:
How are latches different from locks, and how does a DBA learn about Oracle latch management?

Ans: Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial execution of kernel code.  The LRU (least recently used) latches are used when seeking, adding, or removing a buffer from the buffer cache, an action that can only be done by one process at a time.

Difference between Latch and Lock?
Ans: Latches occur and removed automatically internally and fully managed by Oracle database. i.e., A latch is a low-level internal lock used by Oracle to protect memory structures. It may tell degrade of performance. Lock may created due to various reason. One of the major root cause is bad application code and user’s mistake. Read this document to clear the idea more.




Nov 3, 2014

Alerts and notifications setup & configuration using Oracle 11g OEM

OEM 11g alerts and notifications setup & configuration
Enterprise Manager monitoring functionality provides:
·         In-depth monitoring with Oracle-recommended metrics and thresholds.
·         Access to real-time performance charts.
·         Collection, storage, and aggregation of metric data in the Management Repository. This allows you to perform strategic tasks such as trend analysis and reporting.
·         E-mail notification for detected critical alerts.
Enterprise Manager can monitor a wide variety of components (such as databases, hosts, and routers) within your IT infrastructure.
Some examples of monitored metrics are:
·         Archive Area Used (Database)
·         Component Memory Usage (Application Server)
·         Segments Approaching Maximum Extents Count (Database)
·         Network Interface Total I/O Rate (Host)
Some metrics have associated predefined limiting parameters called thresholds that cause alerts to be triggered when collected metric values exceed these limits. Enterprise Manager allows you to set metric threshold values for two levels of alert severity:
·         Warning - Attention is required in a particular area, but the area is still functional.
·         Critical - Immediate action is required in a particular area. The area is either not functional or indicative of imminent problems.
Hence, thresholds are boundary values against which monitored metric values are compared. For example, for each disk device associated with the Disk Utilization (%) metric, you might define a warning threshold at 80% disk space used and critical threshold at 95%.

To setup SMTP server & sender mail ID:


OEM Home Page à Setup à Notification Methods à Enter IP address of SMTP server ( if 25 port is used, no need to mention port number , set other boxes as per your requirement, ignore username of smtp server if no user given à Test Mail server ( verify you are getting mails or not) if succeeded à Apply (you will have confirmation window)



(Fig-1: OEM Home page )

(Fig-2: Notification Methods )


(Fig-3 : Notification Method setup window )


(Fig-4 : Notification Method Test window )

(Fig-5 : Notification Method Confirmation window )

This is the end of setup process.

Setup alert notification :

OEM Home page à  Preference à in general screen, add mail ID along with sys password à perform a test ( check should be succeeded) à Apply ( see the message “Your preferences data has been updated.”)

(Fig : Adding mail IDs for notification)

In the above window password change is not mandatory. It is one of the option in the same window. Here you can test whether you are getting alert mails or not.

Add rules to notification:
OEM Home page à  Preference à rules ( notification) à Check default set rules from subscribe (send email) column à Apply à follow the message “Update succeeded”


Edit Notification default rules:

E.g., Edit Notification Rule: Database Availability and Critical States:

OEM Home page à  Preference à Rules à select/ click the name of the rule which you want to customize à select “Database Availability and Critical States” à Edit à There are 6 tabs available. These are : General , Availability, Metrics, policies, Jobs, Methods; select one by one to set customized rule à select metrics à e.g., select/check “Data Block Corruption Status” à select “Edit” à Choose your options à select “continue” 

Various windows/ Tables while edit the rules for notification:


(Fig-1: General tab for edit notification window)

(Fig-2:  Availability tab for edit notification window)

(Fig-3: Metrics tab for edit notification window)

(Fig-4: Metrics tab for edit notification window)


(Fig-5: While edit metrics values from metrics tab for edit notification window)

I this way any metrics values can be edited for notification. There are various ways to add 'n' number of notifications. I will add more windows for various kind of notification setup .

Thanks..Cheers !!!



Translate >>