|
X$BH
|
Hello everyone. In this issue, I will examine how CR block is created
in multiple sessions.
The following is my estimation of the process for analyzing CR blocks in
multiple sessions.
Step 1. Perform DELETE to the table TEST from session A. (Commit is not performed at this moment.)
Step 2. Access the table TEST from session B.
Step 3. CR block is created to ensure consistent read.
Step 1. is a mandatory process. This time, I perform SELECT, INSERT, and UPDATE
queries in Step 2. In Step 3., I analyze the change in the status of the object
on database buffer. When I perform SELECT and UPDATE queries, I will also examine
full scan and index scan.
The following is a testing environment:
*********************************
(Environment)
Linux 2.4.2-2
Oracle9i EE Release 9.2.0.1.0
(Table configuration)
SQL> desc test
Name Type
--------- ------------------
ID1 NUMBER
ID2 NUMBER
TEXT VARCHAR2(2000)
*Grant INDEX (TEST_IDX) to ID1.
Table contains following data.
ID1 ID2 TEXT
---------- ---------- --------------------
1 1 insight
2 2 insight
3 3 insight
*********************************
|
I will examine the status of the object in the following order.
1. SELECT at full scan
2. SELECT at index scan
3. INSERT
4. UPDATE at index scan
5. UPDATE at full scan
I will start with 1.
[1. Performing SELECT at full scan]
*************************************************************
(1)Perform DELETE query at session A (ID1 deletes data 2.)
SQL> delete from test where id1=2;
A row deleted
The status of the object on database buffer
SQL> select
o.object_name
,decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec'
,6,'irec',7,'write',8,'pi') STATE
,dbablk
from x$bh b , dba_objects o
where b.obj = o.data_object_id
and o.object_name like 'TEST%'
group by o.object_name, state, dbablk;
OBJECT_NAME STATE DBABLK
-------------------- ----- ----------
TEST xcur 63793 <-segment header of the table
TEST xcur 63794 <-data block of the table
TEST_IDX xcur 105642 <-leaf block of the index
(2)In session B, perform SELECT query at full scan
SQL> select /*+ full(test) */ * from test where id1=2;
[Full scan is specified in hint clause]
The status of the object on database buffer
OBJECT_NAME STATE DBABLK
-------------------- ----- ----------
TEST xcur 63793
TEST xcur 63794
TEST cr 63794 <-created for consistent read
TEST_IDX xcur 105642
*************************************************************
|
Session A has a table lock. CR block is created for this block.
Next, I perform SELECT query at index scan.
[2. Performing SELECT at index scan]
*************************************************************
(1)In session B, perform SELECT query at index scan
*Data has already been deleted in session A
SQL≫ select * from test where id1=2 <-ID1 is indexed
The status of the object on database buffer
OBJECT_NAME STATE DBABLK
-------------------- ----- ----------
TEST cr 18720 <-created for consistent read
TEST xcur 18720
TEST_IDX cr 18724 <-created for consistent read
TEST_IDX xcur 18724
*************************************************************
|
The state of an index TEST_IDX is changed to CR.
CR block is created in the index, index scan is performed
to the table, and CR block is created in the table.
I don't think this is an efficient process in regard to performance.
Next, by performing INSERT query, I insert a single row.
[3. Performing INSERT]
*************************************************************
(1)In session B, perform INSERT query
*Data has already been deleted in session A.
SQL> insert into test values(2,2,'insight');
The status of the object on database buffer
OBJECT_NAME STATE DBABLK
-------------------- ----- ----------
TEST xcur 70913 <-segment header of the table
TEST xcur 70914 <-data block of the table
TEST_IDX xcur 96394 <-accessed at delete process
*************************************************************
|
CR block is not created. In the process, it first checks
a freelist and inserts the data in free space. This means that the process accesses
to two blocks, header block and data block of the table.
I will cover the rest of the steps next week.
Yoshihiro Uratsuji
|
|
