Oracle multi-session serial access to the same block test

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.

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

1 2 Next Page