|
X$BH
|
In this issue, I will continue the analysis of the Read/Write contention.
**********************************[Read/Write contention1]***
Perform SELECT query in NODE1.
SQL> select * from test;
(NODE1 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL
------------- --------------- ---------- -------- ----------
TEST scur 257 5E5DA000 0
TEST scur 258 5E5D6000 0
*************************************************************
|
[Read/Write contention 1]
The status of SELECT (Read) of NODE1 is same as the one of [Read/Read contention]
I covered last time.
**********************************[Read/Write contention2]***
Perform UDPATE query in NODE2
SQL> update test set id2=1 where id2=2;
(NODE2 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL
------------- --------------- ---------- -------- ----------
TEST scur 257 5E3A4000 0
TEST xcur 258 5E3A0000 0
TEST cr 258 5E3A2000 0
(NODE1 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL
------------- --------------- ---------- -------- ----------
TEST scur 257 5E5DA000 0
TEST cr 258 5E5D6000 1
*************************************************************
|
[Read/Write contention 2]
Next, I perform UPDATE (Write) from NODE 2. The status has changed as follows.
NODE1 | NODE2
|
[Transfer]
SCUR ----> XCUR
|
CR |
[Lockdown]
|
First, the status of the data block in NODE1 is changed from SCUR to CR (i.e. lockdown).
Next, the segment header and XCUR data block are transferred from NODE1 to NODE2.
Lastly, after CR block is created in NODE2, I perform UPDATE query.
What is lockdown?
I add X_TO_NULL to the result above. The value of the column increases
when the status of the block changes from the current mode to another status.
In the example above, the status changes from the shared current (SCUR) to the consistent
read mode. Thus, the value in X_TO_NULL column increases by 1.
This entire process is called lockdown.
However, Oracle document has the following description, which means that
the value in this column 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
version.
Next, I analyze the Write/Read contention.
**********************************[Write/Read contention1]***
Perform UPDATE (Write) query in NODE1.
SQL> update test set id2=1 where id2=2;
(NODE1 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL
------------- --------------- ---------- -------- ----------
TEST scur 257 5E5CA000 0
TEST xcur 258 5E5C0000 0
TEST cr 258 5E5C2000 0
*************************************************************
|
[Write/Read contention 1]
CR block is created when UPDATE query is performed at full table scan.
Since the segment header (SCUR) is not updated, the status remains as the shared current.
On the other hand, data block is updated and therefore, the status is changed to the exclusive
mode (XCUR).
Now, I perform SELECT (Read) in NODE2.
**********************************[Write/Read contention2]***
Perform SELECT (Read) query in NODE2.
SQL> select * from test;
(NODE2 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL
------------- --------------- ---------- -------- ----------
TEST scur 257 5B5CE000 0
TEST cr 258 5B5C8000 0
Segment header is SCUR at Read/Read contention.
cr receives data from xcur and is used temporarily.
(NODE1 status)
OBJECT_NAME STATE DBABLK BA X_TO_NULL
------------- --------------- ---------- -------- ----------
TEST scur 257 5E5CA000 0
TEST xcur 258 5E5C0000 0
TEST cr 258 5E5BE000 0
TEST cr 258 5E5C2000 0
*************************************************************
|
[Write/Read contention 2]
Take a look at the SCUR block in both nodes. As the segment header is not updated,
the status remains as the shared current in both nodes.
You may see the CR block in both nodes above. CR block is first created in NODE1 and
then is transferred to NODE2. Every time the SELECT query is performed in NODE2, CR block
is created and transferred repeatedly as follows.
(1)CR block is created in NODE1
(2)CR block is transferred from NODE1 to NODE2
[Response slows down when the contention occurs]
Lastly, I talk about EPS (Executions Per Second) when the contention occurs.
I make an access to a single block to analyze, which means that the probability of
the occurrence of the contention is 100%. The following result may not be accurate
but can be a guideline for evaluating the degree of performance slowdown.
The number of transactions is one session in each node.
EPS (Executions Per Second): indicates the number of times the SQL statement
is executed per second and is used as a guideline for the performance evaluation.
Perform following process to the same block.
[Read] [Write]
^^^^^^ ^^^^^^^
select update <-
| |
commit ->
|
EPS value is as follows.
******************************************
NODE1 EPS
---------------------------------------
Read(SELECT) 10000
Write(UPDATE) 416
******************************************
|
The following indicates the EPS when the contention occurs.
******************************************
NODE1 / NODE2 EPS(NODE1) EPS(NODE2)
-------------------------------------------------
Read/Read 10000[1.00] 10000[1.00]
Write/Read 250[0.60] 714[0.07]
Write/Write 175[0.40] 192[0.46]
******************************************
|
Response does not slow down at all at Read/Read contention.
The response seems to slow down when the contention is Write-related.
I assume that the response slows down at lockdown and when the CR block
is transferred among nodes.
That's it for today.
Yoshihiro Uratsuji
|
|
