|
X$BH
|
Last time, I viewed STATE column in a table to keep track of the
status of an object on a buffer. This time, I will analyze how the status of
the object on a buffer will change when I query (SELECT, INSERT and UPDATE)
in a single server and in a single session.
Following are the testing environment and the table configuration:
*************************************************************
(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
*************************************************************
|
First, I issue a simple SELECT statement to see what happens.
*************************************************************
(Execute SELECT statement)
SQL> select * from test ;
(Capture the 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
, blsiz , count(*) blocks
from x$bh b , dba_objects o
where b.obj = o.data_object_id
and b.ts# > 0
and o.object_name = 'TEST'
group by o.object_name, state, blsiz
(Result of the query)
OBJECT_NAME STATE BLSIZ BLOCKS
------------------------------ ----- ---------- ----------
TEST xcur 8192 6
*************************************************************
|
The status of the block is XCUR, which indicates a current mode that is not
shared with other instance. In other words, only the latest block image is
on the database buffer. As I don't use RAC environment to analyze the object status,
SCUR shouldn't appear in STATE column.
I execute INSERT statement in the same session. The result comes out to be the same as the one of
SELECT statement because there is no access from other instances. Besides, it doesn't
have a consistent read.
However, I get a different result when I execute UPDATE statement. CR (consistent read)
block is created.
*************************************************************
(Execute UPDATE statement)
SQL> update test set id1 = 1 where id2 =2;
(Result of query)
OBJECT_NAME STATE BLSIZ BLOCKS
------------------------------ ----- ---------- ----------
TEST xcur 8192 6
TEST cr 8192 1
*************************************************************
|
What is CR block?
CR block is read-only image block that is created to ensure consistent read.
I wonder why CR block is created only when I execute UPDATE statement. I have a single session
environment and CR block doesn't have to be created for updating process. I will
further analyze this topic in future issues.
Next, I get dump data on database buffer and view data of XCUR and CR blocks.
I get buffer address from X$BH to compare dump data with data on database
buffer. For more details about getting dump of database buffer, refer to Vol.59.
*************************************************************
(Capture the status of database buffer)
* I get block number (dbarfil, dbablk) and block address.
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
, dbarfil
, dbablk
, ba
from x$bh b , dba_objects o
where b.obj = o.data_object_id
and b.ts# > 0
and o.object_name = 'TEST'
group by o.object_name, state, blsiz
, ba, dbarfil, dbablk
(Result of the query)
OBJECT_NAME STATE DBARFIL DBABLK BA
------------- ----- ---------- ---------- --------
TEST xcur 9 18720 54DD4000
TEST xcur 9 18719 54DD6000
TEST xcur 9 18718 54DD8000
TEST xcur 9 18717 54DDA000
TEST xcur 9 18715 54DE2000
TEST xcur 9 18716 54C1E000
TEST cr 9 18716 54DDC000
As datafile (BARFIL) and block number (DBABLK) are the same, the last two rows are blocks
to be updated.
(Get a dump)
SQL> alter session set events 'immediate trace name buffers level 6';
(XCUR block)
updated data: (column1, column2, column3) = (1,2,'insight)
tl: 17 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02 <----------------------represents 1
col 1: [ 2] c1 03 <----------------------represents 2
col 2: [ 7] 69 6e 73 69 67 68 74 <-------represents insight
tab 0, row 2, @0x1f65
(CR block)
updated data: (column1, column2, column3) = (2,2,'insight)
tl: 17 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 03 <----------------------represents 2
col 1: [ 2] c1 03 <----------------------represents 2
col 2: [ 7] 69 6e 73 69 67 68 74 <-------represents insight
*************************************************************
|
Dump data indicates that current block (XCUR) is the latest (updated) data and
that CR is the data that is not yet updated. If I repeat querying, CR blocks
keeps being created.
*************************************************************
OBJECT_NAME STATE DBARFIL DBABLK BA
--------------- ----- ---------- ---------- --------
.
TEST xcur 9 18716 54C5E000 <- DBA of XCUR
TEST cr 9 18716 55BA4000
OBJECT_NAME STATE DBARFIL DBABLK BA
--------------- ----- ---------- ---------- --------
.
TEST xcur 9 18716 54C54000 <- DBA of XCUR
TEST cr 9 18716 54C5E000 <- changes to CR
TEST cr 9 18716 55BA4000
OBJECT_NAME STATE DBARFIL DBABLK BA
--------------- ----- ---------- ---------- --------
.
TEST xcur 9 18716 54C50000
TEST cr 9 18716 54C54000 <- changes to CR
TEST cr 9 18716 54C5E000
TEST cr 9 18716 55BA4000
*************************************************************
|
Every time I query, a current block (XCUR) changes to a CR block and
new current block is created.
If CR blocks increase continuously in an environment where a single
object is being updated frequently, CR blocks will consume database
buffer in large quantity, and consequently, will cause performance slowdown.
The maximum number of blocks is limited by hidden parameter. By default,
parameter is set to 6.
---------
_db_block_max_cr_dba = 6
---------
This time, the CR blocks are actually created after UPDATE statement is executed,
but in fact, there are some cases where CR blocks are not created. I will talk about
this topic next week.
Yoshihiro Uratsuji
|
|
