What is "Buffer Busy Wait " - - Tuning | Learn DBA : A Life Long Learning Experience

Thursday 25 August 2016

What is "Buffer Busy Wait " - - Tuning

“When several concurrent sessions will read the same blocks/same table or same index block.”

Definition : When two or more session issue the same query/related query (that access the same database blocks), the few sessions will read the data from database buffer cache (based in inittrans and maxtrans in block level) while other sessions are in wait. In that case , Buffer Busy Wait – Event occurs.

How to find Buffer Busy Waits ?

Using the AWR report top-5 wait-event shows the read by other session or Buffer busy wait event.

Or Using v$segment_statistics or v$system_event also, we can see the buffer busy wait event.

What are Hot Objects/Blocks ?

When Number of concurrent session’s access single block in an object then they are called as hot object.

How to find these Hot Objects ?

Using AWR report “Segment statistics” section shows the HOT objects list.

Or also using below query, we find the hot objects. :

SELECT relative_fno, owner, segment_name, segment_type

FROM dba_extents

WHERE file_id = &file

AND &block BETWEEN block_id AND block_id + blocks - 1;

How we reduce the buffer busy waits?

Method 1:

Increasing INITRANS value method:

First we should know how concurrent sessions accessing a single block in an object?

Each database-block having 3 layers.

1. Cache layer

2. Transaction layer

3. Data layer

Transaction layer is playing vital role for block contention.

Each block will have ITL (INTERESTED TRANSACTION LIST) slots. This ITL slots is required for any sessions that’s need to modify a block in an object.

INITRANS value for table having segment 1 & INITRANS for index segment having 2.

MAXTRANS value default is 255.

If there is no free ITL slot in a blocks, then transaction will waiting for serially for a free ITL slot.

By increasing INITRANS value to avoid the serial transaction waiting. Concurrently number of session will perform the DML operation in single block.

Each ITL requires approximately 23 bytes in the block header.

Method 2 : Increasing PCTFREE method:

Suppose a single 8 KB block contains 1000 rows. We reducing the rows in a block can easily reduce the buffer busy wait.

PCTFREE space is used for future updates only. We have an 8 KB data block. Default PCTFREE value is 10%. If we increased the PCTFREE value is 20% automatically number of rows inserted in a block is automatically reduced.

Method 3: Reducing database block size method:

It’s similar to PCTFREE method. Suppose a single 8 KB block contains 1000 rows. Using db multiblock size future we used 4 KB data block. Now 1000 rows should be stored two 4 KB blocks.

Our goal is to reduce the number of records stored in a block.

Tune the inefficient queries:

Reduce the number of blocks accessing for an objects in buffer cache. By tuning the query to minimize the number of blocks reads from disk to database buffer cache.

Example: I have a one huge table & it contains 10000 blocks. There is no index for this table.If we doing any operation against this table, it’s going full table scan & accessing all the blocks in a table (server process reads the 10000 blocks from disk to database buffer cache). We can put proper index for this table & avoid the full table scan.