Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pradeep.

Asked: February 20, 2017 - 1:49 pm UTC

Last updated: February 22, 2017 - 1:01 am UTC

Version: 11GR2

Viewed 10K+ times! This question is

You Asked

Dear Team,

Can you please let me know few demonstration of latches and mutex types.

one more thing can we take any preliminary action to avoid latches and mutex in future.

want to know exact logic behind this.

Thank's
Pradeep

and Connor said...

Both latches and mutexes are memory structures designed to provide serialized access to a common resource.

Here's a terrible metaphor, but it does explain the concept :-)

Why do we have a (real physical) latch on the door of a toilet ? It's to avoid an embarrassing situation of someone walking in on someone else.

It doesnt really matter if the person who was in there was doing nothing (eg a 'read') or actually making use of the facilities (eg a 'write') - in either case, they want some assurances that no-one is going to burst in on them.

So they use a latch.

Same with Oracle - I might only be reading memory, but I need to protect what I'm reading because someone might "burst in" and want to change that memory whilst I'm reading it.

A (oracle) latch is typically a structure of a couple of hundred bytes which can be set atomically, so itself does not need to be protected. A single latch might protect several memory structures. Mutexes are similar but a typically a smaller, less complicated structures - which means they can be faster, and you can have more of them, which in turn should give more granular access to structures, thus yielding more concurrency.

Probably the most common latching/mutex issues seen in databases are around the shared pool and library cache, and the most common cause of that is parsing, or excessive call frequency (eg calling a plsql function tightly in a loop).

If you want to know internals, Tanel has a nice set of slides on it

http://blog.tanelpoder.com/files/Oracle_Latch_And_Mutex_Contention_Troubleshooting.pdf

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Latches and mutex

Pradeep prajapati, February 21, 2017 - 3:43 am UTC

Dear Sir,

Can you please specify is it possible mutex take place before latch.

If you have any demonstration for same then it will appreciable sir.

Thank's
Pradeep
Connor McDonald
February 22, 2017 - 1:01 am UTC

It doesnt really work like that, ie, there is no explicit relationship between a latch and a mutex. Latches get used for some things, mutexes get used for other things.

It would not suprise me (although I have no evidence) if latches slowly disappear over time, and all get replaced by mutexes.