|
Real Application Clusters
|
Hello everyone. I continue to talk about the topic from the last time.
Oracle uses lock mechanism to keep data consistency. I queried V$BH view
to check the status of database buffer lock. Following is the result of the view.
**********************************************************
SQL> select status,count(*) from v$bh group by status ;
STATUS COUNT(*)
--------------- ----------
cr 453
free 11612
pi 11
scur 1335
xcur 232
**********************************************************
|
I briefly explain the result above.
Number of data blocks that occupy database buffer: 13643 blocks
Read consistency block (cr): 453 blocks
Free block (free): 11612 blocks
PI block (pi): 11
Shared current (scur): 1335 blocks
Exclusive current (xcur): 232 blocks
SCUR and XCUR actually indicate that block has been locked.
Shared current (SCUR)
Status that is obtained when scan is made in any instance.
If the data block is in SCUR mode, same block may exist in multiple instances.
Exclusive current (XCUR)
If data block is read to a buffer for update purposes, the data block is in XCUR mode.
Other instances therefore cannot update this data block.
In addition, when the data block is read in XCUR mode, this data block may have already
been read by other instances. In this case, it is necessary to notify these instances
that the data block will be updated.
Data block in instances that have already acquired lock is no longer in XCUR mode.
If the data block in a source instance is read in SCUR mode, this data block will
be kept in the buffer as consistent read (CR).
On the other hand, if the data block in the source instance is read in XCUR mode,
the data block will be PI block.
RAC allows all instances to acquire lock at data block level and therefore
data is kept consistent.
GCS causes buffer busy global cache wait?
As I covered last time, Global Cache Service (GCS) is responsible for
transferring data blocks between instances. GCS takes an important role
in RAC.
Is there any way to check the operating status of GCS?
Global, for example, is a keyword for viewing the statistics related to processes
such as lock between instances in RAC.
You may see the word global a lot in V$SYSSTAT, V$SESSTAT, and
V$STATNAME. global cache... indicates the statistics related to GCS.
global lock... is an event that may affect RAC.
**********************************************************
SQL> select distinct name from v$sysstat where name like 'global%'
NAME
------------------------------------------------
global cache blocks corrupt
global cache blocks lost
global cache claim blocks lost
global cache convert time
global cache convert timeouts
global cache converts
global cache cr block build time
global cache cr block flush time
global cache cr block receive time
global cache cr block send time
global cache cr blocks received
global cache cr blocks served
global cache current block flush time
global cache current block pin time
global cache current block receive time
global cache current block send time
global cache current blocks received
global cache current blocks served
global cache defers
global cache freelist waits
global cache get time
global cache gets
global cache prepare failures
global cache skip prepare failures
global lock async converts
global lock async gets
global lock convert time
global lock get time
global lock releases
global lock sync converts
global lock sync gets
31 rows selected
SQL>
**********************************************************
|
I compare the results regarding this event between CASE01 and CASE02.
**********************************************************
CASE01 CASE02 (instance 1)
----------------------------------------------------------
global cache convert time(1) 2.3 22.9 (ms)
global lock get time(2) 1.3 38.6 (ms)
*1 global cache convert time/global cache converts
*2 global lock get time/(global lock async gets+global lock sync gets)
**********************************************************
|
When compared with CASE01, CASE02 takes longer than CASE01.
CASE01 is performed in a single instance environment, therefore,
it is not necessary to keep the data consistent between instances.
In CASE02, consistency may cause overhead.
That's it for today.
Tadashi Yamashita
|
|
Ora! Ora! Oracle