in this article, the root block, branch block, leaf block, the table block are index root block, Index branch block, index leaf block, data table block abbreviation.
In addition, most of the ideas in this article come from the Master geomechanics "Oracle Kernel Technology Revelation", this blog post for personal impressions.
you first need to identify 4 points of knowledge about the CBC latch and the buffer pin:1. For both the root block and the branch block, the CBC latch is acquired in s mode without a buffer PIN2. For the leaf block, the CBC latch is obtained in x mode, and the BUFFER pin is obtained in the S mode3. For the table block, the CBC latch is obtained in x mode, the buffer pin of the SELECT statement is obtained in S mode, and the buffer pin of the DML statement is obtained in x mode4. The above three points are basically correct, but there is a special case where the INDEX unique scan is the case: 1) for root block, branch block, and leaf block, CBC latch are obtained in S mode, and no buffer PIN 2) for the table block, the CBC latch of the SELECT statement is obtained in S mode, and the CBC latch without the buffer PIN;DML statement is obtained in x mode, and the buffer PIN is obtained in x mode
Now let's consider one of the most complex situations: not taking the index unique scan but walking the index. This is a summary of how CBC latch and buffer pins are obtained:A root Block and branch block: Get CBC latch-->> in s mode read index data-->> release CBC LATCH B yushang: Get CBC latch-->> in x mode get buffer PIN in s mode, release CBC LATCH, read index data-->> get CBC LATCH in x mode, release buffer Pin-->> release CBC LATCH C block (SELECT): Get CBC latch-->> in x mode get buffer PIN in s mode, release CBC LATCH, read data-->> get CBC in x mode LATCH, Release buffer pin-->> release CBC LATCH D block (DML): Get CBC latch-->> in x mode get buffer PIN in x mode, release CBC LATCH, modify data-->> get CBC in x mode LATCH , Release buffer pin-->> release CBC LATCH
so for select and DML statements we can use these combinations to show the process of acquiring internal locks and latches:the process for 1.SELECT statements is: ABCthe process for the 2.DML statement is: ABD
now we simulate the internal latch contention in all cases: (we assume the worst case, the select and DML statements go the same index, check the same data)
One: Concurrency of the SELECT statement, that is, ABC concurrency ABCphase A is all S mode no contention occurs, the BC phase gets x-mode CBC latch time is extremely short (only to modify the state of the buffer pin), the BC phase of the buffer pin is the s mode, and does not cause contention. (Will be before 10G)Therefore, select concurrency does not cause buffer BUSY WAITS
Two: Concurrency of the SELECT statement and DML statement, that is, the ABC concurrency abdphase A is all S mode no contention occurs, B stage gets x mode CBC latch time is very short (only to modify the state of the buffer pin), and no contention occurs, then C and D phases:if C in front then D seems to happen to wait, but in fact, the construction of the CR block operation, and under the protection of the exclusive CBC latch the current buffer HEADER (BH) modified to CR block, the cloned block of BH modified to the current block of xcur, will not occur waiting. However, if the completion of the CR block and then there is a C or D concurrent, because the clone block is the Xcur block is added to the x mode buffer PIN, then there will be a wait, here is the buffer BUSY waits. if D is in front, then the same buffer BUSY WAITS appears, so that as long as the C or D after D, will cause buffer BUSY WAITS. There are some questions here, why later C concurrency will not occur consistent read, and then think of D has added the X-mode buffer pin, consistent reading is required to read the table block of the ITL slot information, where c even block of buffer pin is not obtained, talk about the consistency of reading. A true consistency reading should refer to the operation involving undo, since DML has found the lock on the buffer pin of S-mode to prove that the block is being read, without the need for consistent reading at all.
Third: The concurrency of DML statementsObviously, there is no obvious contention in the AB phase of ABD, and after the D stage a session acquires the X-mode buffer pin, the other must wait, and if another SELECT statement is made, it will be blocked, according to the mechanism of the transaction lock. Select is not blocked by DML, but this is not a transaction lock blocking but a wait for the buffer pin.
Summary:we know that in a database, either reading or modifying a block is extremely fast, so even in the case of concurrency above, because the buffer pin lock holding time for each block is extremely short, a single block can occur even if buffer BUSY WAITS Time is basically negligible. Buffer BUSY occurs unless a large number of blocks cause multiple blocks to occur in buffer BUSY WAITS waits, so if buffer BUSY WAITS occurs, only DML is associated, and because DML also causes index updates Waits optimize DML statements as much as possible. and if there is a CBC LATCH wait, it is more obvious, it must be too much, even with DML, as long as a SQL concurrency too much will lead to CBC LATCH wait, because both the block and the block are exclusive mode of the CBC LATCH, the solution is either to reduce the concurrency , either full buyou to the index UNIQUE SCAN, or by modifying the implicit parameters _db_block_hash_latches and _db_block_hash_buckets to increase the CBC The number of latch and hash buckets.
but it needs to be clear that:The wait for BUFFER BUSY waits and CBC latch is largely caused by concurrency, and it is not recommended to use the change of implied parameters to resolve the contention of CBC Latch. The contention DML statement for the buffer pin is also very small and can only attempt to create a unique index, so you should consider reducing concurrency from a business perspective.
Oracle Buffer Pool internal latch mechanism
