Questions and Answers

Latches

?

V$LATCH query performance

1 July 1999

The following query is taking over 7 seconds to return on a customer database (7.3.3 on Sun Solaris). On a Win NT development server it has sub-second response.

select sum(misses) from v$latch;

Any known problems with V$LATCH that could account for this?

 
? The difference is probably due to the number of db_block_buffers (or other parameters that affect the number of child latches). Note that V$LATCH does a GROUP BY which can require a disk sort. Have you tried going directly to X$KSLLT?
 
? The query against X$KSLLT directly is much faster. However, it still takes more than 1 second, which seems somewhat high. Are there "latching" issues associated with querying X$KSLLT that increase with the "busyness" of the system?
 
? The latch statistics for each latch are updated under the protection of the latch itself, but they are not protected by latches for the purposes of queries. (However, V$LATCH_MISSES is not protected by a latch, even for updates). The only latching that would affect your query against X$KSLLT would be the gets against the session idle bit, library cache and shared pool latches that would be required for the execution of any query.
 
 
?

session switching and session allocation latches

6 October 1999

We are currently experiencing problems with waits for the session switching and session allocation latches on our 7.3.3 database. I think this is due to the fact we are running XA. Is there anything we can change database wise to reduce the waits for these latches? Is there any way of increasing the number of these latches, or reducing the time that they are held for?
 
? There is no way of getting multiple latches for either of these. The session switching latch is what it says. The session allocation latch covers a multitude of sins.

There are 2 possibilities:

  1. This is a secondary problem due to other waits while holding these latches, and these sleeps will go away if we can solve the real problem. You can use the APT scripts latch_sleeps.sql and system_times.sql to check this out.
  2. Application level tuning is needed to reduce the number of calls generally, and the number of session switches in particular.
 
?

Spins

31 December 1999

I've read that Oracle will spin on latches. Is it safe to assume that when they say spin, they mean sleep() then check?
 
? There is an in-depth explanation of latches in my book. The short answer is that a spin means an active wait, burning CPU, with intermittent attempts to get the latch. If the spin cycle is unsuccessful, then the process will sleep before waking up to spin again if necessary.
 
 
?

_latch_wait_posting

28 January 2000

I have a database with more than 5000 concurrent sessions, 200M shared pool and bad contention for the shared pool latch. Would changing the _latch_wait_posting parameter give us some relief, or avoid the total freezes?
 
? I have not yet got any significant performance gain from changing the _latch_wait_posting parameter, and it would be pointless to change it for shared pool latch contention anyway, because that is a "long" latch for which latch wait posting is enabled by default. What you need is some basic shared pool tuning.
 
 
?

Single CPU latch contention?

11 March 2000

Is it possible for there to be latch contention on a machine with a single CPU? On Oracle's performance training course they said it was not possible. Surely if a process yields the CPU whilst holding a latch and another processes need the latch there could be contention even with a single CPU?
 
? Yes, latch contention is possible on single CPU boxes.
 
 
?

enqueue hash chains latch waits

24 March 2000

I have a question from your book. On page 41 you say that if we've increased the enqueue_resources parameter, it may be necessary to increase the _enqueue_hash parameter because the size of the hash table is derived from the value of processes. I'm seeing enqueue hash chains latch waits, and we've increased enqueue_resources to 20,000. I'm thinking we should bump up the _enqueue_hash parameter, but I don't know what the calculation should be. I already know that the underlying problem is the result of too many requests for a higher level lock on a table called VBDATA. This is an SAP site, and we've got SAP looking at this problem too, but I figured that this might buy us a little more breathing room when the tidal wave of enqueue requests hits.
 
? You have answered your own question by saying that the enqueue hash chains latch waits are a symptom of a higher level problem. You will no doubt find if you check V$LATCH_CHILDREN that the distribution of sleeps against the child latches is not even. If so, increasing the number of latches is highly unlikely to help. However, a small adjustment to _enqueue_hash to make it a prime number may help, but you should really address the root of the problem.
 
 
Copyright © Ixora Pty Ltd Send Email Home