|
X$BH
|
In this issue, I will continue to examine how the status of the object
on the database buffer changes in RAC environment.
Last time, I examined the following contentions:
(1)Read/Read contention
(2)Read/Write contention
(3)Write/Read contention
When I examined the Read/Write contention (i.e. I perform Read query in NODE1 and Write query in NODE2)
last time, the shared current (SCUR) was converted to the consistent read (CR).
This time, I will examine the status of the block as I let the lockdown occur
at Write/Write contention.
I mainly focus on the following two points.
1. The lockdown occurring at Write/Write contention is remarkably heavy process.
2. In addition to the occurrence of the lockdown, past image (PI) is created when Write/Write
contention occurs.
It is very important not to leave this block on the database buffer.
(I will further talk about the past image later.)
Now, I will identify the cause of the occurrence of heavy lockdown and analyze
the overhead such as the creation of past image.
The following is the testing environment.
*************************************************************
Testing environment (two nodes)
Linux 2.4.9-e.9.30ml
Oracle9i Release 9.2.0.3.0
Table configuration
SQL> desc TEST
Name Type
--------- ------------------
ID1 NUMBER
ID2 NUMBER
TEXT VARCHAR2(2000)
Grant INDEX (TEST_IDX) to ID1.
The table contains the following data.
ID1 ID2 TEXT
---------- ---------- --------------------
1 1 insight
2 2 insight
3 3 insight
*************************************************************
|
First, I perform UPDATE (Write) in NODE1.
**********************************[Write/Write contention 1]***
Perform UPDATE (Write) in NODE1.
SQL> update test set text='aaaaa' where id1=2;
(NODE1 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL TCH
------------- --------------- ---------- -------- ---------- ----------
TEST scur 257 5E5B6000 0 1
TEST xcur 258 5E5AC000 0 1
TEST_IDX scur 1170 5E5AE000 0 1
*************************************************************
|
I got the same result as the one we got last time (i.e. UPDATE (Write) at
Write/Read contention).
Next, I perform UPDATE (Write) in NODE2.
**********************************[Write/Write contention 2]***
Perform UPDATE (Write) in NODE2.
SQL> update test set text='bbbbb' where id1=3;
(NODE1 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL TCH
------------- --------------- ---------- -------- ---------- ----------
TEST scur 257 5E5B6000 0 1
TEST pi 258 5E5AC000 1 1
TEST_IDX scur 1170 5E5AE000 0 1
(NODE2 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL TCH
------------- --------------- ---------- -------- ---------- ----------
TEST scur 257 5E5B4000 0 1
TEST xcur 258 5E5AC000 0 1
TEST_IDX scur 1170 5E5AE000 0 1
*************************************************************
|
Past image (PI) is created only when Write/Write contention occurs.
Take a look at the NODE1 status above. Past image is created. When the Write/Write contention
occurs, this block needs to transfer the privilege of the exclusive current to other NODES.
To do this, the status of the block is converted from the exclusive current to CR.
This is the second lockdown.
When the Read/Write contention occurred last time, the status of the block was converted
from the shared current (SCUR) to CR. This time, on the other hand, the status
is converted from the exclusive current (XCUR) to CR. This is why I call it the second
lockdown.
When the status is converted from the exclusive current to CR, the past image
containing the latest block information is created. If a problem occurs in the NODE (in this case,
NODE2) to which the privilege of the current block is transferred, the page image
will enable the prompt recovery by applying the REDO log of NODE2 to the past image
in the source NODE.
What happens if the source NODE does not have the past image?
REDO log is applied to the data that is most recently written to the disk.
In other words, if the data updated in the source NODE is not written to the disk,
it is necessary to apply two REDO logs of the source NODE and the other NODE.
It is very clear now that the past image takes an important role in enabling
the prompt recovery.
Take a look at the result of the analysis again. X_TO_NULL of the past image
increases to 1 at the lockdown.
In NODE2, ROWID to be updated is identified by the shared current (in this case,
TEST_IDX), and the target block is obtained from NODE1 via interconnection.
The shared current is updated and the entire UPDATE process is completed.
I will continue the analysis in the next issue.
That's it for today.
Yoshihiro Uratsuji
|
|
