|
X$BH
|
In this issue, I will examine how the object status changes in Real Application Clusters.
Before I get into details, I will briefly talk about the Global Cache Service (GCS).
What is Global Cache Service?
When we want to share the data on database buffer among multiple nodes, we first need to check if
the target data block already exists on other instances or is already updated. Master node
contains such information and is determined by hashing.
Master node provides Global Cache Service. The Global Cache Service employs
various background processes such as Global Cache Service Processes (LMS) that handles
Global Cache Service messages and processes database buffer. In other words, Global
Cache Service Processes will be heavily occupied if the master node receives many queries
to share the data on the database buffer. In such environment, this problem will be resolved
by increasing the number of Global Cache Service Processes on the master node to improve
the performance.
In order to improve the performance in the Real Application Clusters, it is important
to reduce the number of queries to the master node and to handle queries in the local database buffer.
Query is processed in the shared current (SCUR) in the local database buffer.
Now, I will actually start my analysis. The following are possible contentions in the Real
Application Clusters.
1. Read/Read contention
2. Read/Write contention
3. Write/Read contention
4. Write/Write contention
Today, I will analyze the Read/Read contention.
The following is the testing environment.
*************************************************************
Environment (2 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
Table contains the following data.
ID1 ID2 TEXT
---------- ---------- --------------------
1 1 insight
2 2 insight
3 3 insight
*************************************************************
|
This is a typical case of Read/Read contention.
***********************************[Read/Read contention 1]***
Perform SELECT query in NODE1
SQL> select * from test;
(NODE1 status)
OBJECT_NAME STATE DBABLK BA
------------------------------ --------------- ---------- --------
TEST scur 257 5E5E4000
TEST scur 258 5E5DA000
SQL statement to check the status of the 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
, dbablk , ba
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 , dbablk , ba
order by dbablk;
*************************************************************
|
[Read/Read contention 1]
The following is the flow of issuing the SELECT query in NODE1.
1. Check if the database buffer on the node does not have the target block.
2. Query the Global Cache Service Processes on the master node if database buffer
of other nodes has the target block.
3. Check if the target block does not exist in any node.
4. Read the segment header block as the shared current (SCUR) from a disk
to the database buffer.
5. Read the data block as the shared current (SCUR).
This is the first time that I see the shared current (SCUR) in the Real Application
Clusters. The shared current (SCUR) literally indicates that multiple nodes
can share the current block.
Even though heavy SELECT queries are made to the blocks in all nodes,
the block will not be transferred among nodes because the local database buffer
contains the current block. Thus, there is no need to make a query to the master node.
***********************************[Read/Read contention 2]***
Perform SELECT query in NODE2
SQL> select * from test;
(NODE2 status)
OBJECT_NAME STATE DBABLK BA
------------------------------ --------------- ---------- --------
TEST scur 257 5B5CE000
TEST scur 258 5B5C8000
(NODE1 status)
OBJECT_NAME STATE DBABLK BA
------------------------------ --------------- ---------- --------
TEST scur 257 5E5E4000
TEST scur 258 5E5DA000
*************************************************************
|
[Read/Read contention 2]
First, make a query to the master node to check if the database buffer
of any node has the target block. As the database buffer of NODE1 already
has the block, SCUR block is transferred from NODE1 to NODE2 via inter-connection.
There is no disk I/O at all.
Next week, I will analyze the rest of the contentions.
That's it for today.
Yoshihiro Uratsuji
|
|
