Oracle : Free Buffer Wait

Share via:

Post Views: 0

Dear Readers,

In this article, we will see the following Free Buffer Wait .

Buffer Cache Management :
👉 Locating free blocks (Data to write)
👉 Finding data blocks (Data to read)
👉 Managing LRU lists (Flushing least recently used blocks)
👉 Cleaning dirty buffers

Depends on 2nd scenario from above listed scenarios :
Free buffer wait event mainly happens when there are no free blocks because of DBWR has not cleared dirty buffers from the cache.
Until unless dirty buffers cleans dirty buffers from cache (by writing the data of dirty buffers to the disk) we can face this wait event

One more reason for Free buffer wait event is number of buffers that can be pinned for each session.
Whenever the foreground process needs free buffer it scans LRU list until it reaches threshold value.
Once, it reaches threshold value and not found free buffer foreground process intimates DBWR to clean dirty buffers in the meantime DBWR is busy with some work leads to this wait event.

Oracle internally maintains algorithms to generate LRU list that can be hold by LRU chains which will point to exact data block

Instead of maintaining multiple blocks in single set of chain, Maintain multiple sets to avoid LRU latches.
_db_block_lru_latches
X$KCBWDS — Set Descriptor

Oracle internally calculates latch count based on CPUs
Tune by:-
👉 Increasing data blocks
👉 Try to tune DBWR by increasing DBWR processes
👉 Increase the size of buffer cache

Oracle internally calculates Buffer hits and misses of the request :

SELECT EVENT, TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,WAIT_CLASS FROM V$SYSTEM_EVENT WHERE EVENT='FREE BUFFER WAITS';

1	SELECT EVENT, TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,WAIT_CLASS FROM V$SYSTEM_EVENT WHERE EVENT='FREE BUFFER WAITS';

SELECT * FROM V$LATCH NAME SELECT * FROM V$EVENT_NAME WHERE NAME='%LATCH%';

PARAMETER1 PARAMETER2 PARAMETER3
------------------- ------------------ ----------------------
ADDRESS NUMBER TRIES

SELECT * FROM V$LATCH NAME SELECT * FROM V$EVENT_NAME WHERE NAME='%LATCH%';

PARAMETER1 PARAMETER2 PARAMETER3

------------------- ------------------ ----------------------

ADDRESS NUMBER TRIES

SELECT P2, COUNT(*) FROM V$ACTIVE_SESSION_HISTORY WHERE EVENT='%LATCH%' GROUP BY P2;
P2 COUNT(*)
---------- ------------------
150 4556

SELECT P2, COUNT(*) FROM V$ACTIVE_SESSION_HISTORY WHERE EVENT='%LATCH%' GROUP BY P2;

P2 COUNT(*)

---------- ------------------

150 4556

SELECT LATCH#, NAME FROM V$LATCHNAME WHERE LATCH#=150;
LATCH# NAME
----------- -------------
150 LRU LATCH

SELECT LATCH#, NAME FROM V$LATCHNAME WHERE LATCH#=150;

LATCH# NAME

----------- -------------

150 LRU LATCH

Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS