Oracle multi-session serial access to the same block

1. Instructions

Oracle Data is placed in the table, and the table segment (segment) is composed of extents, and extents is composed of blocks. Each block can store multiple rows.

Oraclesga is composed of a DB buffer cache, which consists of default, keep, and recycle pool. By default, the block is loaded into the defaultpool. All Oracle operations on the data block are performed in this pool, including data modification. After modification, dbwr will be used to write data to the disk. This is a complicated process. For details, refer to my blog:

2. related tests

2.1 create an endless Stored Procedure

 

Create or replace procedure sys. proc_test

As

STR varchar2 (100 );

I number;

Begin

I: = 1;

While (true) loop

Selectobject_name into STR from T1 where object_name = 'rb _ test ';

If Mod (I, 1000) = 0 then

DBMS_OUTPUT.put_line(I );

End if;

I: = I + 1;

End loop;

END;

/

-- The role of this process is to cyclically access a block.

 

2.2 check the file_id and block_id of the block.

 

/* Formatted on 19:45:56 (QP5 v5.163.1008.3004 )*/

SELECTDBMS_ROWID.rowid_relative_fno(ROWID) REL_FNO,

DBMS_ROWID.rowid_block_number(ROWID) BLOCKNO,

DBMS_ROWID.rowid_row_number(ROWID) ROWNO

FROM t1

WHERE object_name = 'rb _ test ';

 

SYS @ anqing2 (rac2)> SELECTDBMS_ROWID.rowid_relative_fno (ROWID) REL_FNO,

2 DBMS_ROWID.rowid_block_number (ROWID) BLOCKNO,

3 DBMS_ROWID.rowid_row_number (ROWID) ROWNO

4 FROM t1

5 WHERE object_name = 'rb _ test ';

 

REL_FNO BLOCKNO ROWNO

------------------------------

1 294838 54

 

2.3 enable two sessions to call this process to continuously access a block

SYS @ anqing2 (rac2)> select sid from v $ sesstat where rownum = 1;

SID

----------

147

SYS @ anqing1 (rac1)> select sid fromv $ sesstat where rownum = 1;

 

SID

----------

141

SYS @ anqing2 (rac2)> exec proc_test

-- Continuous, because it is an endless loop ---

Sys @ anqing1 (Rac1)> exec proc_test

-- Continuous, because it is an endless loop ---

 

2.4 view wait events

Sys @ anqing1 (Rac1)> select event fromgv $ session_wait where Sid = 147 and inst_id = 2;

 

Event

----------------------------------------------------------------

Latch: cache bufferschains

 

Sys @ anqing1 (Rac1)> select event fromgv $ session_wait where Sid = 141 and inst_id = 1;

 

Event

----------------------------------------------------------------

Latch: cache bufferschains

 

 

2.5 description

I tested the serial access of data blocks. However, the test above simulates another situation: latch: cache buffers chains CAUSED BY HOT blocks.

Frequently accessed data blocks are called Hot blocks. When many users access several data blocks together, some latch contention occurs. The most common latch contention includes:

(1) buffer busy waits

(2) cache buffer chain

The competition between the two latchs takes place at different times of accessing data blocks.

When a session needs to access a memory block, it first needs to search for the block in the memory in a structure like a linked list, when a session accesses this linked list, it needs to obtain a latch. If the query fails, latch cache buffer chain waits, the reason for this wait is that there are too many sessions accessing the same data block or the list is too long (if too much data is read in the memory, the hash list for managing the data block will be very long, in this way, the time for the session scan list will increase, the time for holding the chache buffer chain latch will become longer, and the chance for other sessions to obtain this latch will decrease, and the waiting time will increase ).

When a session needs to access a data block, which is being read by another user from the disk to the memory or is being modified by another session, the current session needs to wait, and a buffer busy waits will be generated.

The direct cause of these Latch contention is that too many sessions access the same data block, resulting in hot and fast issues, the cause of the heat may be that the database settings or repeated SQL statements frequently access the same data block.

 

 

This figure is displayed on the blog of my buffer cache. Let's take a look at the entire process. The data block is loaded to the buffer cache, which is loaded to the default pool by default. Oracle uses Hash buckets and Hash chains for management.

Divides data blocks into different Hash buckets. Each Hash bucket corresponds to a Hash Chain. Each Hash Chain stores the location of the data block and the List information before and after it. The Hash Bucket is controlled by Latches. When we want to find the data block corresponding to the Hash Chain on a Hash bucket, we need to obtain this Latch first. If many sessions access A data block, A hot block is generated. When session A obtains the Latch, before session A releases the Latch, other sessions
The Latch cannot be obtained. Therefore, the latch: cache buffers chains. Wait event is generated.

This Latch also makes it difficult to access a data block in parallel.

 

Now let's take a look at the operations that the Session will perform after obtaining the Latch. Contact the x $ bh dictionary.

This dictionary records the situation of each block in buffercache. After we get latch, Oracle needs to update the x $ bh information. For example:

(1) state:

(2) tch: tch is the touch count. A hightouch count indicates that the buffer is used often. Therefore, it willprobably be at the head of the MRU list.

(3) tim: touch time.

(4) class: represents a value designated for the use of the block.

(5) flag: is a bit array.

 

The tch field in the x $ bh dictionary table represents the touch count of the block. Generally, the higher the value, the hotter the block. We call this block a hot block.

Note: data_object_id in user/all/dba_objects is associated with obj In x $ bh or objd in v $ bh.

 

SYS @ anqing1 (rac1)> selectfile #, block #, status, objd from v $ bh where block #= 294838;

-- Block ID, which was previously queried

 

FILE # BLOCK # STATUS OBJD

-------------------------------------

1 294838 scur 56204

 

Xcur: indicates that the block is exclusive to the current instance.
Scur: indicates that the block is being shared by the current instance.
Cr: Indicates consistent read.
Free: indicates that the block is idle.
Read: indicates that the block is being read from the disk.
Write: indicates that the block is being written.

 

-- View the most tch Blocks

Sys @ anqing2 (rac2)> select * from (selectfile #, dbablk, tch from x $ BH where OBJ = (select data_object_id from dba_objectswhere owner = 'sys 'and object_name = 'T1') order by tch DESC) Where rownum <10;

 

File # dbablk tch

------------------------------

1 74678 115

1 74911 115

6 144 115

6 377 115

1 294840 115

1 73602 115

1 75000 115

6 233 115

6 322 115

 

9 rows selected.