|
X$BH
|
In this issue, I will continue to examine how the status of the object
on the database buffer changes in RAC environment.
First, I perform UPDATE (Write) in NODE1.
**********************************[Write/Write contention 3]***
Perform UPDATE in NODE1.
SQL> update test set text='ccccc' where id1=2;
(NODE1 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL TCH
------------- --------------- ---------- -------- ---------- ----------
TEST scur 257 5E5B6000 0 1
TEST xcur 258 5E5A6000 1 1
TEST pi 258 5E5AC000 1 1
TEST_IDX scur 1170 5E5AE000 0 2
(NODE2 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL TCH
------------- --------------- ---------- -------- ---------- ----------
TEST scur 257 5E5B4000 0 1
TEST pi 258 5E5AC000 1 1
TEST_IDX scur 1170 5E5AE000 0 1
*************************************************************
|
The status of NODE2 is exactly same as the one of NODE1 at Write/Write contention in Vol.168.
Also, the status of NODE1 is exactly same as the one of NODE2 at Write/Write contention in Vol.168.
Take a look at the status of NODE1 above. Current block data is different from the one
of the past image. When other NODEs queries the block in the exclusive mode, lockdown
occurs and the copy of the current block (XCUR) is created.
Next, I perform UPDATE (Write) in NODE2.
**********************************[Write/Write contention 4]***
Perform UPDATE (Write) in NODE2
SQL> update test set text='ddddd' where id1=3;
(NODE1 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL TCH
------------- --------------- ---------- -------- ---------- ----------
TEST scur 257 5E5B6000 0 1
TEST cr 258 5E5A6000 2 1
TEST pi 258 5E5AC000 1 1
TEST_IDX scur 1170 5E5AE000 0 2
(NODE2 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL TCH
------------- --------------- ---------- -------- ---------- ----------
TEST scur 257 5E5B4000 0 1
TEST xcur 258 5E5AA000 1 1
TEST pi 258 5E5AC000 1 1
TEST_IDX scur 1170 5E5AE000 0 2
*************************************************************
|
Take a look at the block whose BA indicates 5E5A6000 in NODE1.
The current block is converted to CR block, and
the values of X_TO_NULL and TCH are counted up. Accordingly,
BA of the past image is updated but its X_TO_NULL and TCH
are not counted up.
Why TCH is not counted up?
Past image is used in failure recovery. Therefore, it is less likely
to be recycled. In this analysis, I let Write/Write contention occur.
If the past image resides on the database buffer, it will actually degrade
performance. When I analyzed Write/Write contention last time, performance slowed
down quite remarkably. This is why TCH is not counted up.
Now, I perform UPDATE (Write) in NODE1 again.
**********************************[Write/Write contention 5]***
Perform UPDATE (Write) in NODE1
SQL> update test set text='eeeee' where id1=2;
(NODE1 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL TCH
------------- --------------- ---------- -------- ---------- ----------
TEST scur 257 5E5B6000 0 1
TEST xcur 258 5E5A4000 2 1
TEST cr 258 5E5A6000 2 1
TEST pi 258 5E5AC000 1 1
TEST_IDX scur 1170 5E5AE000 0 3
(NODE2 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL TCH
------------- --------------- ---------- -------- ---------- ----------
TEST scur 257 5E5B4000 0 1
TEST cr 258 5E5AA000 2 1
TEST pi 258 5E5AC000 1 1
TEST_IDX scur 1170 5E5AE000 0 2
*************************************************************
|
Take a look at XCUR in NODE1. X_TO_NULL indicates 2. I have no idea
why it happens.
As I repeat the Write/Write contention, X_TO_NULL of XCUR continues
to increase its value.
I perform full scan instead of index scan when I perform UPDATE to see if
X_TO_NULL also indicates 2.
In fact, X_TO_NULL neither indicates 2 nor continues to increase its value.
However, Oracle document has the following description, which means that the value
of X_TO_NULL may not be accurate.
(Excerpts from Oracle document)
X_TO_NULL:
Number of times the PCM lock is converted from exclusive to NULL mode due to the
contention with other instances. This column is obsolete but remains to keep the
compatibility with older Oracle version.
I will conclude the analysis of X$BH by deleting all past images
on the database buffer.
*************************************************************
Let the checkpoint occur in NODE1
SQL> alter system checkpoint;
System changed.
Past Image (PI) -> CR block
*************************************************************
|
That's it for today.
Yoshihiro Uratsuji
|
|
