|
Real Application Clusters
|
Last time, I queried V$SYSTEM_EVENT view to examine wait event and V$SEGMENT_STATISTICS
view to examine object information.
According to these views, wait events such as enqueue and buffer busy waits
were high in RAC, which was hardly recognized in a single instance.
In addition, wait events such as buffer busy waits and ITL waits occurred
many times in log-type index.
Why buffer busy waits and ITL waits occurred frequently in CASE02?
V$SEGMENT_STATISTICS lists wait events such as enqueue, buffer busy waits,
and buffer busy global cache in RAC.
*********************************************************
V$SYSTEM_EVENT
Contents in Vol.138
CASE INSTANCE NO EVENT WAIT WAIT time(sec)
=================================================================
01 1 log file sync 1,763,899 422,800
latch free 456,638 38,245
buffer busy waits 244,583 16,821
enqueue 122,109 12,221
-----------------------------------------------------------------
02 1 enqueue 2,367,703 410,303 << (1)
buffer busy waits 3,025,078 314,120 << (2)
buffer busy global
cache 1,736,016 206,586 << (3)
global cache busy 1,042,919 95,160
latch free 4,551,655 75,086
--------------------------------------------------------
2 enqueue 2,341,917 408,503
buffer busy waits 3,034,419 321,680
buffer busy global
cache 1,748,311 213,001
global cache busy 968,529 85,302
latch free 4,638,862 73,170
-----------------------------------------------------------------
*********************************************************
|
What is buffer busy global cache?
buffer busy global cache literally defines buffer busy regarding global
cache.
In an environment with a single instance, if the instance does not have datablock,
that instance reads the datablocks from a disk. In an environment with RAC,
on the other hand, other instances may have the datablock. Thus, the datablocks
can be transferred via interconnect.
What is the advantage of interconnect?
Major advantage of interconnect is to reduce disk I/O which has always been a bottleneck
in Oracle. RAC has database buffers for the number of instances.
However, even though RAC has large number of database buffers, it does not necessarily mean
every instance can use the database buffer at any time.
Query is not the only process that is performed in every instance. Thus, the instance
needs to verify if the target datablock exists in other instances or it is updated before
the datablock is transferred via interconnect.
Global Cache Service (GCS) is responsible for transferring datablocks between
instances. GCS actually uses a background process Global Cache Service Processes (LMS).
You can check this process by executing ps command in RAC.
********************************************************
$ ps -ef | grep ora_lms
bora920 17535 1 0 May16 ? 00:00:36 ora_lms0_RACDB2
bora920 17537 1 0 May16 ? 00:00:33 ora_lms1_RACDB2
bora920 15286 15109 0 18:11 pts/4 00:00:00 grep ora_lms
$
********************************************************
|
Normally, each instance has two LMS.
Oracle often uses lock mechanism to keep data consistency. GCS also uses
lock mechanism to keep data consistency between instances.
Following is the information regarding the datablock.
**********************************************************
SQL> select status,count(*) from v$bh group by status ;
STATUS COUNT(*)
--------------- ----------
cr 453
free 11612
pi 11
scur 1335
xcur 232
**********************************************************
|
Actually, the information above indicates the status of lock.
I will talk about this next week.
That's it for today.
Tadashi Yamashita
|
|
Ora! Ora! Oracle