oraclex$bh说明.doc

X$BH
Hello everyone. When we covered Oracle9i in previous issues, we mainly focused on X$BH to further understand the structure of database buffer. In this issue, I will start a new topic focusing on X$BH.

First of all, I check if there is any change in the status of the object on database buffer when object is being searched or updated in a single instance environment. Next, I check the status of the object in RAC environment.

Hopefully, I will talk about SGA management in Oracle10g.

Now, I briefly review the X$BH.

What is X$BH?
X$BH is a source table of dynamic performance view where you can query as SYS user. X$BH helps you examine the type and the status of the object in database buffer.

Following query will return a list of tables in database buffer.
*************************************************************
select
o.object_name, blsiz , count(*) blocks
from x$bh b , dba_objects o
where b.obj = o.data_object_id
and b.ts# > 0
group by o.object_name, blsiz
order by blocks desc

OBJECT_NAME BLSIZ BLOCKS
------------------------------ ---------- ----------
CUSTOMER 8192 920
CUSTOMER_BAD_IDX 8192 23
ITEM 8192 11
STOCK 8192 11
STOCK_PKEY 8192 11
ITEM_PKEY 8192 9
NEW_ORDER_PKEY 8192 6
ORDER_LINE_PKEY 8192 5
ORDER_PKEY 8192 4
.
.
.
*************************************************************

For example, the list above indicates that the table CUSTOMER occupies the database buffer in large quantity. In this case, SQL statements referring to the table CUSTOMER can be a tuning target.

Why? Because this may prove that index is not performed efficiently and full scan or inefficient range scan is performed. If the table CUSTOMER is extremely larger than other tables, this is not a problem.

Next, I focus on a single object and view STATE column.
Following query will return the status of each object. (Query is narrowed down to a table TEST.)
*************************************************************
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
order by blocks desc

OBJECT_NAME STATE BLSIZ BLOCKS
------------------------------ ----- ---------- ----------
TEST xcur 8192 23
TEST cr 8192 5
*************************************************************

STATE column indicates the following:

FREE: not currently in use
XCUR: exclusive
SCUR: shared current
CR: CR block
READ: being read from disk
MREC: in media recovery mode
IREC: in instance recovery mode
WRITE: writing to disk
PI: past image block involved in cache fusion block transfer

I will explain each of the categories in future issues.

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.objectname, 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.

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