Coding TricksOracle multi-session serial access to the same block test
i. instructions
Oracle's data is placed inside the table, in the table's segment, which consists of extents, which consists of Blocks. Each block can hold more than one row.
OracleSGA consists of a DB buffer cache, an area consisting of default, keep, and recycle pools. By default, block is loaded into defaultpool, where Oracle performs all operations on the block, including modifications to the data, after which dbwr writes to disk. This is a more complex process, specifically referring to my blog:
http://www.cndba.cn/Dave/article/1398
relevant testing
2.1 create a dead loop 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 purpose of this process is to loop to access a block
2.2 view the file_id and block_id of this block
/* Formatted on 2011/7/4 19:45:56(QP5 v5.163.1008.3004) */
SELECT DBMS_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';
[email protected](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 turn on 2 sessions to invoke this process and implement continuous access to a block
[email protected](rac2)> select sid from v$sesstat where rownum=1;
SID
----------
147
[email protected](rac1)> select sid fromv$sesstat where rownum=1;
SID
----------
141
[email protected](rac2)> exec proc_test
-- keep going because it's a dead cycle---
[email protected](rac1)> exec proc_test
-- keep going because it's a dead cycle---
2.4 view waiting events
[email protected](rac1)> select event fromgv$session_wait where sid=147 and inst_id=2;
EVENT
----------------------------------------------------------------
latch: cache bufferschains
[email protected](rac1)> select event fromgv$session_wait where sid=141 and inst_id=1;
EVENT
----------------------------------------------------------------
latch: cache bufferschains
2.5 description
serial access to the data block was originally tested, but the above test simulates another case: the cache buffers chains caused by hot blocks.
in my blog there is a description of this latch wait:
Lock deadlock blocking Latch waits for details to be solved
http://blog.csdn.net/tianlesoftware/article/details/5822674
Very frequently accessed blocks are called Hot Blocks, and when many users go together to access a few blocks, it can lead to some Latch contention, the most common of which is:
（1）buffer busy waits
（2）cache buffer chain
The contention between the two Latchs occurs at different times when blocks of data are accessed.
When a session needs to access a block of memory, it first goes to a chain-like structure to find out if the block is in memory, and when the session accesses the list, it needs to get a Latch, and if it fails, it will result in a Laach cache buffer chain wait, causing the wait to be caused by too many sessions accessing the same block of data or too long for this list (if you read too much data in memory, The hash list that needs to manage the block is long, so that the session scans the list for longer, the time it takes to hold the cache buffer chain latch, and the chances of other sessions getting the Latch decrease and the wait increases).
when a session needs to access a block of data that is being read from disk to memory by another user, or when the block is being modified by another session, the current session waits, resulting in a buffer busy wait.
The direct cause of these Laach contentions is that too many sessions accessing the same blocks of data causing hot-fast problems, which can be caused by database settings or repeated SQL frequent access to some of the same blocks of data.
This picture was snuffed over in my buffer cache. Combined with this diagram, let's take a look at the whole process. Blocks are loaded into the buffer cache, and by default to default pool. Oracle is then managed through Hash Bucket and Hash Chain.
Divide the blocks into different Hash buckets, one for each Hash Bucket. Each Hash Chain holds the location of the block and the List information before and after it. The Hash Bucket is controlled by Latches, and when we want to find a block of data on a Hash Bucket that corresponds to the Hash Chain, we need to get the Latch first. If a lot of sessions go to access a block of data, a hot block is created, and when session A gets Latch, the other Sessions can't get it until Session A releases Itch. Therefore, a wait event for latch: cache buffers chains.
It's also because of this Latch that it's difficult to access a block of data in parallel.
Now let's see what Sessions does when he gets Laach. This contact x$bh dictionary. On blog:
http://www.cndba.cn/Dave/article/1398
The dictionary records each block in buffercache. Oracle needs to update this x$bh information when we get the latch. as:
（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 block's touch count, and generally the higher the value, the hotter the block, which we call a hot block.
note: data_object_id in the user/all/dba_objects are associated with obj in x$bh or objd in v$bh.
[email protected](rac1)> selectfile#,block#,status,objd from v$bh where block#=294838;
--block ID number, previously queried
FILE# BLOCK# STATUS OBJD
---------- ---------- ------- ----------
1 294838 scur 56204
xcur: indicates that the block is being excluded and is being monopolized by the current instance.
scur: indicates that the block
is being shared
by the current instance cr: represents consistent read free: indicates that the block is idle
read: indicates that the block
is being read from disk: indicates that the block is being written out
--view the most blocks of tch
[email protected](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.
-------------------------------------------------------------------------------------------------------
QQ:492913789
Email:[email protected]
Blog: http://www.cndba.cn/dave
DBA1 GROUP: 62697716 (FULL); DBA2 GROUP: 62697977 (FULL) DBA3 GROUP: 62697850 (FULL)
DBA SUPERGROUP: 63306533 (FULL); DBA4 GROUP: 83829929 DBA5 GROUP: 142216823
DBA6 GROUP: 158654907 CHAT GROUP: 40132017 CHAT GROUP 2: 69087192
--Adding groups is required to explain the relationship between Oracle table space and data files in the notes, otherwise the request will be rejected