The Oracle latch free wait event occurs when a session needs a latch, tries to get the latch, but fails because someone else has it.
Possible causes of ‘Latch Free’ wait.
– If its due to Shared pool or Library cache latching, then the problem is due to excessive hard-parsing. (Most likely because of lack of bind variables)
– Another possible cause may be due to ‘cache buffer chains latching’.
Reference : https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=454764592204737&id=34576.1&_afrWindowMode=0&_adf.ctrl-state=xsq7uenau_62
The “cache buffer chain” latch wait is normal, but high values are associated with high simultaneous buffer access, similar to a freelist shortage on an index or table segment header.
To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.
Usually latch contention for these buffer caches is due to poor disk I/O configuration. Reducing contention with these latches involves tuning the logical I/O for the associated SQL statements as well as the disk subsystem.
Another factor for latch contention with buffers chain latches could possibly be hot block contention.
Oracle Metalink Note # 163424.1 has some useful tips on tuning and identifying hot blocks within the Oracle database environment.
Latch requests come in two flavors, willing to wait and no-wait modes. In willing to wait mode, when a latch cannot be acquired, the acquiring session will go into a spin mode, attempting to acquire the latch over and over a specified number of times. After the number of spins has reached a specific threshold, the session will sleep for a specified period of time, wake up and try the latch again. Spin mode is bad, but sleep mode is worse!
Very interesting articles :
This will happen if:
All buffer gets have been suspended. This could happen when a file was read-only and is now read-write. All the existing buffers need to be invalidated since they are not linked to lock elements (needed when mounted parallel (shared)). So cache buffers are not assigned to data block addresses until the invalidation is finished.
The session moved some dirty buffers to the dirty queue and now this dirty queue is full. The dirty queue needs to be written first. The session will wait on this event and try again to find a free buffer
This also happens after inspecting ‘free buffer inspected’ buffers. If no free buffer is found, Oracle waits for one second, and then tries to get the buffer again (depends on the context). For more information, see free buffer inspected.
- This could also happen when you need a buffer from the SGA for a block in CR, READING or any of the recovery modes. Basically you will post the DBWR to make some free buffers.
Wait time 1 Second.
The free buffer waits event has three parameters: file#, block#, and set ID. In Oracle Database 10g, this wait event falls under the Configuration wait class. Keep the following key thoughts in mind when dealing with the free buffer waits event.
Before a block is read into the buffer cache, an Oracle process must find and get a free buffer for the block. Sessions wait on the free buffer waits event when they are unable to find a free buffer on the LRU list or when all buffer gets are suspended.
The DBWR process is responsible for making clean buffers on the LRU lists.
Common Causes, Diagnosis, and Actions
A foreground process needing a free buffer scans the LRU list up to a predetermined threshold, usually a percentage of the LRU list. In Oracle9i Database, the threshold is 40 percent. This value is described in the X$KVIT (kernel performance information transitory instance parameters) view as “Max percentage of LRU list foreground can scan for free.” If a free buffer is not found when the threshold is met, the foreground process posts the DBWR process to make available clean buffers. While the DBWR process is at work, the Oracle session waits on the free buffer waits event.
Oracle keeps a count of every free buffer request. The statistic name in the V$SYSSTAT view is free buffer requested. Oracle also keeps a count of every free buffer request that fails. This is given by the TOTAL_WAITS statistic of the free buffer waits event. Free buffer requests are technically buffer gets, if you will, and free buffer requests that fail can be considered as buffer misses. Yet another V$SYSSTAT statistic free buffer inspected tells you how many buffers Oracle processes have to look at to get the requested free buffers. If the free buffer inspected value is significantly higher than the free buffer requested, that means processes are scanning further up the LRU list to get a usable buffer. The following queries list the systemwide free buffer requested, free buffer inspected, and free buffer waits statistics:
This the file in which Oracle is trying to get a free block. The name of the file can be determined with the following SQL statement:
where file# = file#;
This is the block# in the file that Oracle is trying to read into a buffer that currently is not available. With the following SQL statement one can determine to which object the block belongs:
select name, kind
where file# = file#
and lowb <= block#
and highb >= block#;
The DBWR is not writing enough buffers to disk. Make sure that the I/O load is evenly distributed across all disks, use O/S monitor tools or look at v$filestat:
select name, phyrds, phywrts
from v$filestat a, v$datafile b
where a.file# = b.file#
Also look for files that have full table scans:
select name, phyrds, phyblkrd, phywrts
from v$filestat a, v$datafile b
where a.file# = b.file#
and phyrds != phyblkrd
Check your application to make sure that is what you intended and that you don’t miss an index. Always try to use the O/S striping software to distribute database files over as many disks as one can.
Disk Sorts are known to cause a flood of dirty buffers that will need to be written out. It is important to stripe the datafiles belonging to the TEMP tablespace over many different disks. Also during a checkpoint the SORT blocks are not checkpointed and they are thus only written in the normal write batch. If the write batch is full with SORT blocks, the other dirty blocks can’t be written and foregrounds will have to wait for free buffers.
If a session spends a lot of time on the free buffer waits event, it is usually due to one or a combination of the following five reasons:
- Inefficient SQL statements
- Not enough DBWR processes
- Slow I/O subsystem
- Delayed block cleanouts
- Small buffer cache