Shulou(Shulou.com)05/31 Report--

In this article, the editor introduces in detail "how to solve the buffer busy wait waiting event of ORACLE". The content is detailed, the steps are clear, and the details are handled properly. I hope that this article "how to solve the buffer busy wait waiting event of ORACLE" can help you solve your doubts.

1. The process of ORACLE accessing Data buffer block:

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

The cause of the 2.buffer busy wait wait event:

(1)。 Read it.

When a session reads a block, the block does not exist in the cache and needs to be read from the disk to the cache. When other session wants to read the block, the buffer busy wait wait event occurs.

(2)。 Write

When a session modifies a block, the block is first buffer pin (modify x), so other session waits for the last session unpin buffer when making changes to the block.

(3)。 Read and write

1) when the reading process finds that the memory block is being modified (if there is an x-mode buffer pin, it means it is being modified), it can only wait, it cannot clone the block, because at this time the memory block is in the process of changing ing, at this time clone is not safe. Many people say that read and write in oracle do not block each other, and oracle can clone memory blocks to separate the competition between read and write. In fact, it depends on the situation, when reading, it is found that the memory block is being written, it cannot be clone, because it is not safe. At this time, the reading process can only wait for buffer busy waits.

2) when the writing process finds that the memory block is being read, the read does not block writing, because ORACLE can easily clone a xcur data block and then write on the clone block. At this time, clone is safe, because the process of reading the memory block will not modify the data block, ensuring the security of clone.

Common causes of 3.buffer busy wait

(1)。 When poor-performing QUERY accesses the same block concurrently, a large number of physical reads are generated.

(2)。 The freelist setting is too small, resulting in frequent scanning of freelist when concurrent insert table occurs, resulting in contention.

(3)。 A large number of session modify the same index block concurrently

4. Common solutions

(1)。 Tuning sql reduces physical reading

(2)。 Delete some hot row and re-insert to other block

(3)。 If the table is small, consider cache its data to keep data buffer

(4)。 Reduce the use of low cardinality index. Avoid index block contention.

(5)。 Add extents size. Avoid extent map contention caused by frequent space allocation by oracle.

5. The types of block that generate waiting and its solution

Block TypePossible Actionsdata blocksEliminate 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 headerIncrease of number of FREELISTs. Use FREELIST GROUPs (even in single instance this can make a difference). Freelist blocksAdd more FREELISTS. In case of Parallel Server make sure that each instance has its own FREELIST GROUP (s). Undo headerAdd more rollback segments.

6. Related command

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

FROM v$session_wait

WHERE event='enq: TX-row lock contention'

Select objd, file#,block#,class#,ts#,cachehint,status,dirty from v$bh where file#=546 and block#=1289912

Select * from dba_objects where object_id = 124269

Select current_obj#,obj.object_name,count (*) from dba_hist_active_sess_history, dba_objects obj

Where event='buffer busy waits'

And sample_time > = to_date ('2017-07-09 00 hh34:mi:ss' 00mm Murray dd hh34:mi:ss')

And sample_time

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.