When a user issue the query in a database, oracle server processes will read the database blocks from disk to database buffer cache. When two or more session issue the same query/related query (access the same database blocks), the first session will read the data from database buffer cache while other sessions are in wait.
The resolution of a “buffer busy wait” events is one of the most confounding problems with Oracle. In an I/O-bound Oracle system, buffer busy waits are common, as evidenced by any system with read (sequential/scattered) waits in the top-five waits.
We simply say, several concurrent sessions will read the same blocks/same table or same index block.
How can we find the block contention?
AWR/Statspack report top 5 wait event shows the read by other session or Buffer busy wait event and also we can see wait event section.
SELECT p1 “file#”, p2 “block#”, p3 “class#”
WHERE event = ‘read by other session’;
SELECT p1 “file#”, p2 “block#”, p3 “wait class#”
WHERE event = ‘buffer busy waits’;
Also using v$segment_statistics or v$system_event we can see the buffer busy wait event.
How can we tune the Read by other session wait event?
Number of concurrent session’s access single block in an object is known as hot object.
Using AWR report “Segment statistics” section shows the HOT objects list.
Or using below query we find the hot objects.
SELECT relative_fno, owner, segment_name, segment_type
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks – 1;
Why buffer busy wait/read by other session event happen? How we reduce the buffer busy waits?
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.
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.
Tune inefficient queries
Review the execution plan and make sure the plan chosen by Oracle is that read the fewest blocks possible. Optimize the SQL statement to reduce the number of physical and logical reads.
Adjust PCTFREE and PCTUSED
Adjusting the PCTFREE value downward for an object will reduce the number of rows physically stored in a block. Adjusting the PCTUSED value for an object keeps that object from getting prematurely put back on the freelist. Increase the number of FREELISTS and FREELIST GROUPS. Depending on the type of contention, adjusting these values could help distribute data among more blocks and reduce the hot block problem. Be careful to optimize these parameters so blocks do move in and out of the freelist too frequently.
Reduce the Block Size
This is very similar to adjusting the PCTFREE and PCTUSED parameters in that the goal is to reduce the amount of data stored within one block. In Oracle 9i and higher this can be achieved by storing the hot object in a tablespace with a smaller block size. In databases prior to Oracle 9i the entire database must be rebuilt with a smaller block size.
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.
Several weeks ago I received a phone call from one of my customer relating an instance wide performance problem (he said that from time to time the application hangs during 15-20 minutes). Below is the first AWR report I have been sent for investigation.
All this seems now very coherent. There is no TX-row lock contention wait event in this new report. There is more than 67% of the response time have been spent on the read by other session wait event while the remaining 30% has been allocated to the db file sequential read wait event.
A “read by other session” is a wait event that occurs when one session wants to read a block another session is reading from disk and loading into the memory (buffer cache). In short, I want to read a block from disk and someone else is reading it for me; hence I have to wait. But in my actual situation I have many sessions trying to read the same block from the disk. This means that the object those sessions are trying to read can’t be kept in the buffer cache or the cache is filled by other process so that our hot object can’t stay in the buffer cache generating this “read by other session wait” event and consequently more physical I/O.
So what else then?
Generally, there is a correlation between physical I/O, read by other session and “Sql Ordered by read” part of the AWR. This is why I’ve drilled down to this part to observe this:
Ah we’ve narrowed the problem now up to the above query.
So what else then?
Since we’ve the SQL responsible of the important part of physical I/O, let’s drill down to the ‘Segment per physical read’ part of the AWR in order to localize the ‘hot’ object:
Oh yes; there is an index there (SYS_C0010893 ) which is consuming 93% of the whole physical I/O.
Finally, I asked to have the explain plan of the above SQL query which I have reproduced here below (explain plan has been taken with the help of DBMS_DISPLAY_AWR package):
Where I see the presence of this hot SYS_C0010893 index.
Oh I will not finish without displaying the buffer pool advisory part of the problematic AWR:
Knowing the limitation I have that is (a) I can’t change the query (b) I can’t create a new index and as far as it is a problem of physical I/O perpetuated by a “read by other sessions” wait event, I have suggested the client to increase the buffer cache size from 776MB to 1224MB.
When a session waits on the “read by other session” event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache. If this happens too often the performance of the query or the entire database can suffer. Typically this is caused by contention for “hot” blocks or objects so it is imperative to find out which data is being contended for. Once that is known, there are several alternative methods for solving the issue.
When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait. In previous versions this wait was classified under the “buffer busy waits” event. However, in Oracle 10.1 and higher this 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 contentions
When a session is waiting on the “read by other session” event, an entry will be seen in the v$session_wait system view, which will give 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;
Depending on the Oracle database environment and specific performance situation the following variety of methods can be used to eliminate contention:
Tune inefficient queries – This is one of those events you need to “catch in the act” through the v$session_wait view as prescribed above. Then, since this is a disk operating system issue, take the associated system process identifier (c.spid) and see what information you can obtain from the operating system.
Redistribute data from the hot blocks – Deleting and reinserting the hot rows will often move them to a new data block. This will help decrease contention for the hot block and increase performance. More information about the data residing within the hot blocks can be retrieved with queries similar to the following:
SELECT data_object_id FROM dba_objects WHERE owner='&owner' AND object_name='&object'; SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,0) start_rowid FROM dual; --rowid for the first row in the block SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,500) end_rowid FROM dual; --rowid for the 500th row in the block SELECT <column_list> FROM <owner>.<segment_name> WHERE rowid BETWEEN <start_rowid> AND <end_rowid>
Adjust PCTFREE and PCTUSED – Adjusting the PCTFREE value downward for an object will reduce the number of rows physically stored in a block. Adjusting the PCTUSED value for an object keeps that object from getting prematurely put back on the freelist.
Depending on the type of contention, adjusting these values could help distribute data among more blocks and reduce the hot block problem. Be careful to optimize these parameters so blocks do move in and out of the freelist too frequently.
Reduce the Block Size – This is very similar to adjusting the PCTFREE and PCTUSED parameters in that the goal is to reduce the amount of data stored within one block. In Oracle 9i and higher this can be achieved by storing the hot object in a tablespace with a smaller block size. In databases prior to Oracle 9i the entire database must be rebuilt with a smaller block size.
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.