Shulou(Shulou.com)06/01 Report--

Buffer busy waits

One: B uffer busy waits description

Second: B uffer bus y waits solution

Third: B uffer busy waits reproduction process

Four: B uffer busy waits official documents

One: B uffer busy waits description

-refer to "Oracle Kernel Technology decryption"

The process searches the Buffer Cache for the buffer process (logical read process):

(1) the process calculates the Hash value according to the file number and block number to be accessed.

(2) find the HASH Bucket according to the hash value.

(3) search the linked list after Bucket to find which BH is the target BH (Buffer Header).

(4) find the target BH and extract the BA (Buffer Address) of the Buffer.

(5) Press BA to access Buffer.

Where:

Each HASH Bucket holds a link header that points to the Cache Buffers Cache linked list (CBC linked list).

BH (Buffer Header) records the specific file number, block number, BA,BUFFER Pin and other information.

Step (3) the linked list after searching for Bucket requires Latch protection.

Step (4) access to BA in BH requires Latch protection.

This Latch is Cache Buffer Chain Latch (CBC Latch for short).

Before accessing the CBC linked list, get the CBC Latch, find the corresponding BH, add Buffer Pin to the found BH, and modify the status of the lock (S-sharing, X-exclusive). After the modification, you can further access the Buffer.

When you start accessing Buffer, CBC Latch has been released, and access to Buffer is done under the protection of Buffer pin locks.

After accessing the Buffer, you need to modify the Buffer Pin lock, and you also need CBC Latch protection to modify the Buffer Pin before you can release the Buffer Pin lock.

The function of CBC Latch: protect linked list, protect BH

If you cannot apply for Buffer Pin immediately after finding BH, there will be Buffer busy waits waiting.

Http://bbs.chinaunix.net/thread-3635879-1-1.html

The reason why a process cannot obtain buffer header pin is that in order to ensure data consistency, a block can only be accessed by a process pin at a time, so when a process needs to access a block used by other processes in the buffer cache, the process will generate a buffer busy waits event for that block.

For example:

When session A tries to modify a row of Buffer data in memory and finds that session B is modifying the same Buffer data in memory, Buffer busy waits wait will occur in session A.

Http://www.itpub.net/thread-1801066-1-1.html

If you have a lot of reads and very few writes in your database, there will not be any contention because the buffer pin between reads is compatible and is in s mode.

If you write a lot in your database and read very little, there will be buffer busy waits waiting, but the cost of waiting is much lower than that of cbc latch. The spin mechanism of latch consumes cpu very much, while the management of buffer pin is essentially similar to the mechanism of enq lock. There is no spin mechanism, and there is no need for spin to consume a lot of cpu.

If your database is a read-write mixed scenario, then write will block read and generate buffer busy waits, but read will not block write and this wait will not occur.

Steps for Oracle to access or modify buffer:

1) calculate the bucket of the data block based on the address of the block

2) obtain the cbc latch that protects this bucket

3) look for the data blocks we need on the linked list, and then pin the buffer (read s, modify x)

4) release cbc latch

5) read / modify the contents of the data block

6) obtain cbc latch

7) unpin this buffer

8) release cbc latch

Finally, we can make a summary:

1) buffer busy waits is a wait generated on buffer block, which causes buffer busy waits to wait because n processes want to obtain the buffer pin of buffer block in an incompatible mode.

2) the management mode of buffer lock is very similar to that of enq lock, with first-in, first-out, queue to record the owner of the lock and waiting.

3) both writing and reading will produce buffer busy wiats waiting. Both sessions of writing and writing will produce buffer busy wiaits waiting, while for two sessions of reading and writing, only read session will be generated, because it cannot simply clone a memory block, and it is not safe to clone the memory blocks that are being written.

4) in order to solve the problem that the cbc latch is held for too long, oracle uses the session that visits the buffer block to get the cbc latch twice, and then adds buffer pin to the memory block to solve this problem.

Note: oracle does not take the mechanism of getting cbc latch twice for all memory blocks, such as index root, index branch, unique index leaf node, is an one-time acquisition mechanism.

Https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#BGGIBDJI

A session cannot pin the buffer in the buffer cache because another session has the buffer pinned.

Https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94465

This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently.

Second: B uffer busy waits solution

1: find the corresponding object and object type of buffer busy waits

(1) find the object number

SELECT row_wait_obj# FROM V$SESSION WHERE EVENT = 'buffer busy waits'

(2) find the corresponding object information through the object number

SELECT owner, object_name, subobject_name, object_type

FROM DBA_OBJECTS

WHERE data_object_id = & row_wait_obj

Or find the corresponding block number, file number and type through SID

Select event, sid, p1, p2, p3

From v$session_wait

Where sid in (69,75)

And event like'% buffer busy waits%'

-

P1: File ID

P2: Block ID

P3: Class ID

The p1, p2 parameters and dba_extents are jointly queried to get the segment name and segment type where block is located.

(3) find the corresponding SQL information

Select sql_text

From V$sqlarea

Where (address, hash_value) in

(select sql_address, sql_hash_value

From v$session

Where event like'% buffer busy waits%')

Or

Select sql_text

From v$sql t1, v$session t2, v$session_wait t3

Where t1.address = t2.sql_address

And t1.hash_value = t2.sql_hash_value

And t2.sid = t3.sid

And t3.event = 'buffer busy waits'

2: there are different solutions according to different object types

Http://bbs.chinaunix.net/thread-3635879-1-1.html

For different types of waiting blocks, we take different approaches:

1.data segment header

Processes usually visit data segment header frequently for two reasons

(1) obtain or modify process freelists information

Frequent access to process freelists information by processes leads to freelist contention. We can increase the corresponding segment object storage parameters freelist or freelist groups;. If the process frequently modifies freelist due to frequent data blocks entering and leaving the freelist, you can set a large gap between the pctfree value and the pctused value, so as to prevent data blocks from entering and leaving the freelist frequently.

(2) extended high water mark

Because the segment consumes very fast space, and the set next extent is too small, the high water mark is frequently extended. The solution is to increase the storage parameter next extent of the segment object or directly set extent size uniform when creating the tablespace.

2. Data block

One or some data blocks are read and written by multiple processes at the same time and become hot chunks. This problem can be solved by the following ways:

(1) reduce the concurrency of the program. If the parallel query is used in the program, reduce the parallel degree, so as to prevent multiple parallel slave from accessing the same data object at the same time, resulting in waiting to degrade performance.

(2) adjust the application so that it can read fewer data blocks to get the required data, reducing buffer gets and physical reads

(3) reduce the number of records in the same block so that records are distributed in more data blocks, which can be achieved in several ways: you can adjust the pctfree value of segment objects, you can rebuild segment into table spaces with smaller block size, and you can use alter table minimize records_per_block statements to reduce the number of records in each block.

(4) if the hotspot block object is an index similar to the self-increasing id field, the index can be converted into an inverted index to break up the data distribution and disperse the hotspot block.

3.undo segment header

The reason for undo segment header contention is that there is not enough undo segment in the system and enough undo segment needs to be added. According to the management method of undo segment, if you are in manual management mode, you need to modify rollback_segments initialization parameters to increase rollback segment. If you are in automatic management mode, you can reduce the value of transactions_per_rollback_segment initialization parameters to make oracle automatically increase the number of rollback segment.

4.undo block

Undo block contention is due to the fact that the data is read and written at the same time in the application, and the reading process needs to go to undo segment to obtain consistent data. The solution is to stagger the time for the application to modify data and query a large amount of data.

Summary: the buffer busy waits event is one of the more complex oracle waiting events, and there are many reasons for its formation. It needs to be diagnosed according to the p3 parameters compared with the cause code table provided by Oracle. After 10g, it is necessary to analyze the waiting time according to the block type of waiting combined with the specific SQL of waiting time, and take corresponding adjustment measures.

One: contention for data blocks with reason code 130 (class # 1)

1) waiting is concentrated on the data block, and the reason code is 130, which means that multiple sessions request the same data block concurrently, but the data block is not in the buffer memory and must be read from disk.

2) when multiple sessions request the same block of data that is not in the buffer cache, the ORACLE prevents each session from making the same operating system IWeiO call. Otherwise, this may significantly increase the number of system I _ pico, so ORACLE allows only one session to perform the actual I _ pico, while other sessions wait for blocks on the buffer busy waits, and sessions that perform I _ peg O wait on db file sequential read or db file scattered read wait events.

3) you can check the registration time of SESSION in the v$session view, and wait for the events db file sequential (scattered) read and buffer busy waits to wait for the same file number and block number.

4) solution: optimize SQL statements to minimize logical and physical reads

Two: contention for data blocks with reason code 220 (class # 1)

1) the wait is concentrated on the data block, and the reason code is 220, which means that multiple sessions execute DML (different rows in the same block) on the same object at the same time.

2) if the size of the data block is large (> = 16K), this phenomenon may be reinforced, because larger blocks generally contain more rows in each block.

3) ways to reduce wait in this case: reduce concurrency; reduce the number of rows in a block; and rebuild objects in another tablespace with a smaller block size.

4) specific method description:

Rebuild a table or index using a larger PCTFREE

Use the alter table minimize records_pre_block command to change the table to minimize the minimum number of rows per block

Starting with ORACLE9i, you can move or rebuild objects in another tablespace with a smaller block size.

Note: while these methods can minimize buffer busy waits problems, they will undoubtedly increase full table scan time and disk space utilization.

III: contention for data segment headers (class # 4)

1) if buffer busy waits wait events are mainly concentrated on the data segment header (that is, the table or index segment header, and not the UNDO segment header), this means that some tables or indexes in the database have high segment header activity.

Note: the process accesses the segment header for two main reasons, one is to obtain or modify FREELISTS information, and the other is to extend the high water mark (HWM).

2) ways to reduce waiting in this situation:

> for tables that use free tables for segment management, add the FREELISTS and FREELIST GROUPS of the confirmation object (Note: the increase of FREELIST GROUPS is also required)

> > make sure the gap between FCTFREE and PCTUSED is not too small, so that the block loop of FREELIST can be minimized.

> > the size of the next zone cannot be too small. When the zone expands at a high speed, the new area established needs to be modified in the mapping table in the middle of the section head. Consider moving objects to a reasonable, uniformly sized locally managed tablespace.

IV: revoke the contention of the paragraph header (class # 17)

1) if the buffer busy waits wait event is mainly focused on the undo segment header, it indicates that there are too few rollback segments in the database or their area size is too small, resulting in frequent updates to the segment header. If UNDO segments are managed by the database system using ORACLE9I, you don't need to deal with this because ORACLE adds additional UNDO segments as needed.

2) Private rollback segments can be created and enabled to reduce the number of transactions per rollback segment. The ROLLBACK_SEGMENTS parameter in the init.ora file needs to be modified.

3) if you use common rollback segments, you can reduce the initialization parameter transactions_per_rollback_segment, and ORACLE uses transactions/transactions_per_rollback_segment to obtain the minimum number of public rollback segments.

V: revoke contention for blocks (class # 18)

1) if the buffer busy waits wait event is mainly concentrated on the undo block, it indicates that there are multiple concurrent sessions that query updated data at the same time to ensure consistency.

2) this is a problem with the application, which does not exist when the application runs queries and DML at different times.

Third: B uffer busy waits reproduction process

-conn chenjch/a

-1 sid=69

Declare

J number

Begin

For i in 1..30000000 loop

Update t1 set id=id+0 where rowid='AAAR4jAAMAAAAh7AAA'

End loop

End

/

-2 sid=75

Begin

For i in 1..30000000 loop

Update t1 set id=id+0 where rowid='AAAR4jAAMAAAAh7AAB'

End loop

Commit

End

/

-View session wait events

Select event, sid, p1, p2, p3

From v$session_wait

Where sid in (69,75)

And event like'% buffer%'

Select event, sid, p1, p2, p3

From v$session_wait_history

Where sid in (69,75)

And event like'% buffer%'

-determine the type of block waiting for event access

Select * from v$waitstat where count > 0

-look at the sql statement that caused the 'buffer busy waits' event

Select sql_text

From V$sqlarea

Where (address, hash_value) in

(select sql_address, sql_hash_value

From v$session

Where event like'% buffer busy%')

-View the block type that caused the 'buffer busy waits' event

Select 'Segment Header' class

A.segment_type

A.segment_name

A.partition_name

From dba_segments a, v$session_wait b

Where a.header_file = b.p1

And a.header_block = b.p2

And b.event = 'buffer busy waits'

Union

Select 'Freelist Groups' class

A.segment_type

A.segment_name

A.partition_name

From dba_segments a, v$session_wait b

Where a.header_file = b.p1

And b.p2 between a.header_block + 1 and

(a.header_block + a.freelist_groups)

And a.freelist_groups > 1

And b.event = 'buffer busy waits'

Union

Select a.segment_type | | 'block' class

A.segment_type

A.segment_name

A.partition_name

From dba_extents a, v$session_wait b

Where a.file_id = b.p1

And b.p2 between a.block_id and a.block_id + a.blocks-1

And b.event = 'buffer busy waits'

And not exists (select 1

From dba_segments

Where header_file = b.p1

And header_block = b.p2)

-check the segment that caused the 'buffer busy waits' event

Select owner,segment_name,segment_type,block_id from dba_extents where file_id=6

-query sql with T1

Select sql_text from v$sqlarea where sql_text like'% T1%'

SELECT *

FROM v$segment_statistics s

WHERE s.statistic_name = 'buffer busy waits'

AND s.owner = 'CHENJCH'

SELECT row_wait_obj#

FROM V$SESSION

WHERE EVENT = 'buffer busy waits'

SELECT owner, object_name, subobject_name, object_type

FROM DBA_OBJECTS

WHERE data_object_id = & row_wait_obj

Four: B uffer busy waits official documents

Https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94465

Wait Event: buffer busy waits

General Area: Buffer cache, DBWR

Possible Causes:

Depends on buffer type. For example, waits for an index block may be caused by a primary key that is based on an ascending sequence.

Look for / Examine:

Examine V$SESSION while the problem is occurring to determine the type of block in contention.

10.3.1 buffer busy waits

This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. Query V$WAITSTAT for the wait statistics for each class of buffer. Common buffer classes that have buffer busy waits include data block, segmentheader, undo header, and undo block.

Check the following V$SESSION_WAIT parameter columns:

P1: File ID

P2: Block ID

P3: Class ID

10.3.1.1 Causes

To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for buffer busy waits. For example:

SELECT row_wait_obj#

FROM V$SESSION

WHERE EVENT = 'buffer busy waits'

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type

FROM DBA_OBJECTS

WHERE data_object_id = & row_wait_obj

10.3.1.2 Actions

The action required depends on the class of block contended for and the actual segment.

10.3.1.2.1 segment header

If the contention is on the segment header, then this is most likely free list contention.

Automatic segment-space management in locally managed tablespaces eliminates the need to specify the PCTUSED, FREELISTS, and FREELISTGROUPS parameters. If possible, switch from manual space management to automatic segment-space management (ASSM).

The following information is relevant if you are unable to use ASSM (for example, because the tablespace uses dictionary space management).

A free list is a list of free data blocks that usually includes blocks existing in several different extents within the segment. Free lists are composed of blocks in which free space has not yet reached PCTFREE or used space has shrunk below PCTUSED. Specify the number of process free lists with the FREELISTS parameter. The default value of FREELISTS is one. The maximum value depends on the data block size.

To find the current setting for free lists for that segment, run the following:

SELECT SEGMENT_NAME, FREELISTS

FROM DBA_SEGMENTS

WHERE SEGMENT_NAME = segment name

AND SEGMENT_TYPE = segment type

Set free lists, or increase the number of free lists. If adding more free lists does not alleviate the problem, then use free list groups (even in single instance this can make a difference). If using Oracle RAC, then ensure that each instance has its own free list group (s).

See Also:

Oracle Database Concepts for information about automatic segment-space management, free lists, PCTFREE, and PCTUSED

10.3.1.2.2 data block

If the contention is on tables or indexes (not the segment header):

Check for right-hand indexes. These are indexes that are inserted into at the same point by many processes. For example, those that use sequence number generators for the key values.

Consider using ASSM, global hash partitioned indexes, or increasing free lists to avoid multiple processes attempting to insert into the same block.

10.3.1.2.3 undo header

For contention on rollback segment header:

If you are not using automatic undo management, then add more rollback segments.

10.3.1.2.4 undo block

For contention on rollback segment block:

If you are not using automatic undo management, then consider making rollback segment sizes larger.

Https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#BGGIBDJI

Buffer busy waits

Wait until a buffer becomes available.

There are four reasons that a session cannot pin a buffer in the buffer cache, and a separate wait event exists for each reason:

1. Buffer busy waits: A session cannot pin the buffer in the buffer cache because another session has the buffer pinned.

2. Read by other session: A session cannot pin the buffer in the buffer cache because another session is reading the buffer from disk.

3. Gc buffer busy acquire: A session cannot pin the buffer in the buffer cache because another session is reading the buffer from the cache of another instance.

4. Gc buffer busy release: A session cannot pin the buffer in the buffer cache because another session on another instance is taking the buffer from this cache into its own cache so it can pin it.

Prior to release 10.1, all four reasons were covered by "buffer busy waits."

In release 10.1, the "gc buffer busy" wait event covered both the "gc buffer busy acquire" and "gc buffer busy release" wait events.

Wait Time: Normal wait time is 1 second. If the session was waiting for a buffer during the last wait, then the next wait will be 3 seconds.

Https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=347530962988293&id=34405.1&_afrWindowMode=0&_adf.ctrl-state=gvalc855z_4

WAITEVENT: "buffer busy waits" Reference Note (document ID 34405.1)

* Checked for relevance on 14, June 2012, June 2012

"buffer busy waits" Reference Note

This is a reference note for the wait event "buffer busy waits" which includes the following subsections:

Brief definition

Individual wait details (eg: For waits seen in)

Systemwide wait details (eg: For waits seen in)

Reducing waits / wait times

See Note:61998.1 for an introduction to Wait Events.

Definition:

Versions:7.0-10.2 Documentation: 9.0

This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy". The two main cases where this can occur are:

1. Another session is reading the block into the buffer

2. Another session holds the buffer in an incompatible mode to our request

Individual Waits:Parameters:

P1 = file# (Absolute File# in Oracle8 onwards)

P2 = block#

P3 = id (Reason Code) / Block Class# in 10g

File# (Absolute File# in Oracle8 onwards)

This is the file number of the data file that contains the block that the waiting session wants.

Block#

This is the block number in the above file# that the waiting session wants access to.

See Note:181306.1 to determine the tablespace, filename and object for this file#,block# pair.

Id (Reason Code)

The buffer busy wait event is called from different places in the Oracle code. Each place in the code uses a different "Reason Code". These codes can differ between versions thus:

Versions

Values used

7.1-8.0.6

Uses one set of ID codes (mostly > 1000)

8.1.5

8.1.5 + does not include a value for P3 when waiting

8.1.6-9.2

Uses a different set of ID codes (100,300)

10.1 +

Uses the block class

Buffer Busy Waits ID's and Meanings

Reason Code (Id)

Reason

= 10.1

0

0

N/a

A block is being read

1003

one hundred

N/a

We want to NEW the block but the block is currently being read by another session (most likely for undo).

1007

two hundred

N/a

We want to NEW the block but someone else has is using the current copy so we have to wait for them to finish.

1010

two hundred and thirty

N/a

Trying to get a buffer in CR/CRX mode, but a modification has started on the buffer that has not yet been completed.

1012

-

N/a

A modification is happening on a SCUR or XCUR buffer, but has not yet completed

1012 (dup.)

two hundred and thirty one

N/a

CR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed.

1013

one hundred and thirty

N/a

Block is being read by another session and no other suitable block image was found e.g. CR version, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefore it will read the CR version of the block. This should not have a negative impact on performance, and basically replaces a read from disk with a wait for another process to read it from disk, as the block needs to be read one way or another.

1014

one hundred and ten

N/a

We want the CURRENT block either shared or exclusive but the Block is being read into cache by another session, so we have to wait until their read () is completed.

1014 (duplicate)

one hundred and twenty

N/a

We want to get the block in current mode but someone else is currently reading it into the cache. Wait for them to complete the read. This occurs during buffer lookup.

1016

two hundred and ten

N/a

The session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock and so does not show up as waiting very long. In this case the statistic: "exchange deadlocks" is incremented and we yield the CPU for the "buffer deadlock" wait event.

1016 (duplicate)

two hundred and twenty

N/a

During buffer lookup for a CURRENT copy of a buffer we have found the buffer but someone holds it in an incompatible mode so we have to wait.

Wait Time:

Normal wait time is 1 second. If the session has been waiting for an exclusive buffer during the last wait then it waits 3 seconds this wait. The session will keep timing-out/waiting until it acquires the buffer.

Finding Blockers:

Finding the blocking process can be quite difficult as the information required is not externalised. If P3 (Reason Code) shows that the "buffer busy wait" is waiting for a block read to complete then the blocking session is likely to be waiting on an IO wait (eg: "db file sequential read" or "db file scattered read") for the same file# and block#.

If the wait is due to the buffer being held in an incompatible mode then it should be freed very soon. If not then it is advisable to contact Oracle Support and get 3 SYSTEMSTATE dumps at one minute intervals as the blocking session may be spinning. (Look for ACTIVE sessions with high CPU utilisation).

Systemwide Waits:

If the TIME spent waiting for buffers is significant then it is best to determine which segment/s is/are suffering from contention. The "Buffer busy wait statistics" section of the Bstat/estat or STATSPACK reports shows which block type/s are seeing the most contention. This information is derived from which can be queried in isolation:

SELECT time, count, class

FROM V$WAITSTAT

ORDER BY time,count

This shows the class of block with the most waits at the BOTTOM of the list.

Oracle Support may also request that the following query be run to show where the block is held from when a wait occurs:

SELECT kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT"

FROM x$kcbsw s, x$kcbwh w

WHERE s.indx=w.indx

And s. "OTHER_WAIT" > 0

ORDER BY 3

Note: "OTHER_WAIT is" OTHER WAIT "in Oracle8i (a space rather than an underscore)

Additional information regarding which files contain the blocks being waited for can be obtained from the internal thus:

SELECT count, file#, name

FROM x$kcbfwait, v$datafile

WHERE indx + 1 = file#

ORDER BY count

This shows the file/s with the most waits (at the BOTTOM of the list) so by combining the above of information we know what block type/s in which file/s are causing waits. The segments in each file can be seen using a query like:

SELECT distinct owner, segment_name, segment_type

FROM dba_extents

WHERE file_id= & FILE_ID

If there are a large number of segments of the type listed then monitoring may help isolate which object is causing the waits.

Eg: Repeatedly run the following statement and collect the output. After a period of time sort the results to see which file & blocks are showing contention:

SELECT p1 "File", p2 "Block", p3 "Reason"

FROM v$session_wait

WHERE event='buffer busy waits'

Note:

In the above query there is no reference to WAIT_TIME as you are not interested in whether a session is currently waiting or not, just what buffers are causing waits.

If a particular block or range of blocks keep showing waits you can try to isolate the object using the queries in Note:181306.1.

One can also look at:

Capturing session trace and noting the "buffer busy waits" may help-See Note:62160.1.

Reducing Waits / Wait times:

As buffer busy waits are due to contention for particular blocks then you cannot take any action until you know which blocks are being competed for and why. Eliminating the cause of the contention is the best option. Note that "buffer busy waits" for data blocks are often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index)-the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk-the other sessions (scanning the same index) quickly 'catch up' and want the block which is currently being read from disk-they wait for the buffer as someone is already reading the block in.

The following hints may be useful for particular types of contention-these are things that MAY reduce contention for particular situations:

Block Type

Possible Actions

Data blocks

Eliminate HOT blocks from the application. Check for repeatedly scanned / unselective indexes. Change PCTFREE and/or PCTUSED. Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes). Increase INITRANS. Reduce the number of rows per block.

Segment header

Increase of number of FREELISTs. Use FREELIST GROUPs (even in single instance this can make a difference).

Freelist blocks

Add more FREELISTS. In case of Parallel Server make sure that each instance has its own FREELIST GROUP (s).

Undo header

Add more rollback segments.

Related:

Bug can cause "buffer busy waits" and latch contention in 817and901 Note:176129.1

Tracing User sessions Note:62160.1

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.