ANBOB™

About “Read by other session wait event”

This wait event occurs when we are trying to access a buffer in the buffer cache but we find that the buffer is currently being read from disk by another user so we need to wait for that to complete before we can access it.

In previous versions, this wait was classified under the “buffer busy waits” event. However, in Oracle 10.1 and higher, the wait time is now broken out into the “read by other session” wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

Finding the contention

When a session is waiting on this event, an entry will be seen in the v$session_wait system view giving more information on the blocks being waited for:

SELECT p1 "file#", p2 "block#", p3 "class#" 
 FROM v$session_wait
 WHERE event = 'read by other session';

If information collected from the above query repeatedly shows that the same block, (or range of blocks), is experiencing waits, this indicates a “hot” block or object. The following query will give the name and type of the object:

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;

Hot Objects/Blocks:

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

Eliminating contention

Increasing INITRANS value method:

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

Each db 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.

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.

Note: PCTFREE is still required even with ASSM because it determines how rows will be packed into blocks, while freelists, pctused and pctincrease are ignored with ASSM because they are used for transaction/block management.

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.

Optimize indexes
a low cardinality index has a relatively small number of unique values, e.g. a column containing state data with only 50 values. Similar to inefficient queries, the use of a low cardinality index could cause excessive number of blocks to be read into the buffer cache and cause premature aging out of “good” blocks.

打赏

微信扫一扫，打赏作者吧～