|
X$BH
|
Last time, I viewed STATE column of X$BH in a single server and in
a single session and examined the following points:
CR block is not created by SELECT or INSERT queries.
CR block is created by UPDATE queries.
There are some cases where CR block is not created by UPDATE queries, though.
I will analyze these cases in this issue. DELETE queries operate almost the same
as UPDATE queries.
The following is the 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 executed the following statement last time.
SQL> update test set id1 = 1 where id2 =2;
WHERE clause specifies the row ID2. As ID2 is not indexed, full scan
will be performed to update.
The following query specifies ID1 in WHERE clause. As ID1 is indexed,
index scan will be performed to update.
*************************************************************
(UPDATE query)
SQL> update test set id1 = 1 where id1 =2;
*ID1 is specified in WHERE clause so that index scan
is performed.
(Status of 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
, count(*) BLOCK
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
OBJECT_NAME STATE BLOCKS
-------------------- ----- ----------
TEST xcur 2
TEST_IDX xcur 1
*************************************************************
|
CR block is not created, as you see. Regarding index, there is only one block
on database buffer.
When I previously performed full scan under the condition where there was
no data to be updated, CR block was not created.
*************************************************************
Status of database buffer when there is no target data
to be updated at full scan.
SQL> update test set id1=3 where id2=5;
*ID2 is not indexed. There is no such data as ID2=5.
0 row updated
OBJECT_NAME STATE DBARFIL DBABLK BA
-------------------- ----- ---------- ---------- --------
TEST xcur 1 70913 54D28000
TEST xcur 1 70914 54D20000
*************************************************************
|
CR blocks are created in the following cases.
********************************************
CR block is created (in a single session)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UPDATE SELECT INSERT
---------- -------- --------- --------
Full scan Yes No --
Index scan No No --
********************************************
|
In a single session environment, CR blocks are created only by UPDATE query
at full scan.
Why CR blocks are created under this condition?
CR block itself is a read consistent block, but I wonder why CR block is created
in a single session environment. I assume that CR block is created
to create rollback information or to satisfy lock function.
CR block is not created at index scan because it is possible to identify
ROWID of target data from the index. As UPDATE process is performed by each row, CR block
is not necessary.
Lastly, I will examine a mechanism of current block updating, which
is quite different from the one of full scan and of index scan.
The following table indicates the changes in the buffer address of a current block
at index scan.
*************************************************************
UPDATE at index scan:
[Before UPDATE]
OBJECT_NAME STATE DBABLK BA
-------------------- ----- -------- --------
TEST xcur 18716 54D0E000 <-target block
TEST_IDX xcur 18724 54D04000
[After UPDATE]
OBJECT_NAME STATE DBABLK BA
-------------------- ----- -------- --------
TEST xcur 18716 54D0E000 <-target block
TEST_IDX xcur 18724 54D04000 *No changes
*************************************************************
|
I don't see any changes in the buffer address. This means that the data in the current
block is updated directly.
The following table indicates the changes in the buffer address of an XCUR block
at full scan.
*************************************************************
UPDATE at full scan:
[Before UPDATE]
OBJECT_NAME STATE DBABLK BA
-------------------- ----- -------- --------
TEST xcur 18717 54C3A000 <-target block
[After UPDATE]
OBJECT_NAME STATE DBABLK BA
-------------------- ----- -------- --------
TEST xcur 18717 551F2000 <-target block
TEST cr 18717 54C3A000 *BA is changed
|
BA of current block before being updated
*************************************************************
|
I see changes in the buffer address. In addition, the current block
is changed to CR block after updating.
The process is performed as follows:
1. A current block (XCUR) in 54C3A000 is copied to 551F2000.
2. The current block is changed to CR block.
3. Update process is performed to XCUR.
Yoshihiro Uratsuji
|
|
