|
Real Application Clusters
|
In the last issue, I performed index tuning and as a result, scalability
improved drastically. I'm glad I finally found the proper tuning method.
V$SEGMENT_STATISTICS view proved that ITL Waits and
Buffer Busy Waits also decreased.
I checked V$SYSTEM_EVENT view to examine wait status of entire system and found out
waits such as enqueue, buffer busy waits, buffer busy global cache
decreased.
In previous issues, I briefly talked about Global Cache Service (GCS) that
was unique to RAC. GCS operates to maintain data consistency among instances.
Now, I check if index tuning affected GCS.
((V$SYSSTAT))
**********************************************************
(Before tuning)
CASE01 CASE02 (instance 1)
----------------------------------------------------------
global cache convert time(*1) 2.3 22.9 (ms)
global lock get time(*2) 1.3 38.6 (ms)
(After tuning)
CASE03 (instance 1)
----------------------------------------------------------
global cache convert time(*1) 39.3 (ms)
global lock get time(*2) 10.4 (ms)
*1 global cache convert time/global cache converts
*2 global lock get time/(global lock async gets+global lock sync gets)
**********************************************************
|
I can see a slight difference in results.
In addition to GCS, there is another important service
for RAC, that is, Global Enqueue Service (GES).
global lock get time is statistics related to GES.
GES coordinates local and global enqueues among instances.
For example, while instance A is updating data, instance A
does not want other instances to drop a corresponding table. It is thus necessary
to prevent DDL from being executed when other instances have table lock.
GES is actually managed by Global Enqueue Service Daemon (LMD).
The result of tuning indicates increase in global cache convert time
and decrease in global lock get time.
I further examine the result more carefully.
Both global cache convert time and global lock get time are time required per
request. Now, I view V$SYSSTAT to examine the number of requests.
*******************************************************************
instance 1 tuning (before 10ms) (after 10ms)
-------------------------------------------------------------------
global cache convert time 969,075 1,604,022
global cache converts 423,314 408,262
global lock async gets 4,828 12,600
global lock sync gets 10,733,630 17,517,200
global lock get time 41,433,031 18,312,454
instance 2
-------------------------------------------------------------------
global cache convert time 920,928 1,386,120
global cache converts 392,841 394,132
global lock async gets 4,822 12,112
global lock sync gets 10,487,512 21,365,590
global lock get time 41,316,116 21,027,412
*******************************************************************
|
I don't see any difference in the number of global cache converts.
But, I can say global cache converts decreases because the number of transactions
is doubled. Index tuning improves global cache converts but it takes
time to process transaction.
global lock async gets and global lock sync gets increase
as the number of transactions increases. However, process time decreases to the half.
Now, I get the point.
There is a difference in number of requests between global cache converts
and global lock sync gets.
Take a look at the following results. Difference is quite obvious.
********************************************************************
instance 1 tuning (before 10ms(%)) (after 10ms(%))
-------------------------------------------------------------------
global cache convert time 969,075( 2.1) 1,604,022( 6.4)
global cache cr block build time 2,296( 0.0) 1,950( 0.0)
global cache cr block flush time 22,658( 0.0) 679,202( 2.7)
global cache cr block send time 110,612( 0.1) 99,074( 0.4)
global cache cr block receive tim 521,939( 1.1) 1,531,250( 6.2)
global cache current block flush 86,058( 0.2) 297,198( 1.2)
global cache current block pin ti 382,498( 0.8) 300,052( 1.2)
global cache current block send t 61,183( 0.1) 130,462( 0.5)
global cache current block receiv 2,417,464( 5.2) 1,539,686( 6.2)
global cache get time 182,562( 0.4) 410,640( 1.7)
global lock convert time 357( 0.0) 5,726( 0.0)
global lock get time 41,433,031(89.7) 18,312,454(73.5)
===================================================================
********************************************************************
|
Most of waits are global lock get time related. At the same time,
global lock get time decreases the most after index tuning. Both
instance 1 and instance 2 have almost the same result.
I now check enqueues.
*******************************************************************
(before tuning)
instance 1
EQ_TYPE TOTAL_REQ TOTAL_WAITS WAITTIME (sec)
--------------------------------------------------------
TX 4,682,405 1,892,191 419,433
HW 30,123 27,898 3,011
SQ 283 172 14
CF 7,634 292 11
TT 1,085 816 10
TA 324 320 4
US 335 123 0
(skipped)
(after tuning)
instance 1
EQ_TYPE TOTAL_REQ TOTAL_WAITS WAITTIME (sec)
--------------------------------------------------------
HW 155,472 153,202 117,494
TX 5,469,420 250,862 54,910
SQ 3,482 2,904 470
PS 76 36 8
US 1,958 1,076 6
TA 454 24 0
(skipped)
*******************************************************************
|
Enqueue wait time exactly matches process time required for GES.
Most of waits in GES are enqueue wait.
Result of tuning proves that TX enqueue decreases.
Our objective is to prevent index from being transferred among instances.
I have thought GCS may be affected. GCS actually
reduces its process time because enqueue wait is resolved.
That's it for today.
Tadashi Yamashita
|
|
Ora! Ora! Oracle