|
Real Application Clusters
|
Last time, I changed index configuration and also let index skip scan be executed
instead of index full scan.
I performed registration and scan process, and changed index configuration.
Let's check how performance has improved.
********************************************************
(Before tuning)
CASE INSTANCE NO SESSIONs EPS TPS
=======================================================
01 1 237 16,083 732.3
-------------------------------------------------------
02 1 256 5,453
2 256 5,362
----------------------------------------------
Total 512 10,815 489.4
-------------------------------------------------------
Scalability = 0.67
********************************************************
|
Compared with a single instance, scalability dropped by 0.67.
Let's see the result below.
********************************************************
(After tuning)
CASE INSTANCE NO SESSIONs EPS TPS
=======================================================
01 1 237 16,083 732.3
-------------------------------------------------------
03 1 256 10,746
2 256 13,365
----------------------------------------------
Total 512 24,111 1079.4
-------------------------------------------------------
Scalability = 1.47
********************************************************
|
Index tuning made performance improve more than twice. Actually, there was another index
similar to this index. So I performed tuning to two indexes.
Now, I want to know if there is any difference in the statistics.
Following are statistics of an instance in RAC.
First, take a look at WAIT column of each object.
((ITL Waits))
********************************************************
(Before tuning)
OBJECT TYPE WAIT
---------------------------------------
IDX_LOGTBL1 INDEX 1,532 << (1)
IDX_LOGTBL2 INDEX 1,210 << (2)
PK_SESINFO INDEX 33
IDX_LOGTBL3 INDEX 1
PK_MSINFO INDEX 1
---------------------------------------
(After tuning)
OBJECT TYPE WAIT
---------------------------------------
PK_SESINFO INDEX 696
PK_MSINFO INDEX 54
PK_MSINFO_MA INDEX 38
IDX_MSINFO INDEX 6
---------------------------------------
********************************************************
|
((Buffer Busy Waits))
********************************************************
(Before tuning)
OBJECT TYPE WAIT
---------------------------------------
IDX_LOGTBL1 INDEX 2,445,422
IDX_LOGTBL2 INDEX 2,074,474
LOGTBL2 TABLE 76,123 << (3)
LOGTBL1 TABLE 37,154 << (4)
PK_SESINFO INDEX 32,102
---------------------------------------
(After tuning)
OBJECT TYPE WAIT
---------------------------------------
PK_MSINFO INDEX 336,430
PK_SESINFO INDEX 322,564
PK_MSINFO_MA INDEX 117,048
LOGTBL2 TABLE 106,772 << (3)
LOGTBL1 TABLE 57,724 << (4)
---------------------------------------
********************************************************
|
I don't see any wait event regarding log-related index any more. ((1) and (2))
Buffer Busy Waits of log-related table has slightly increased.
I assume that Buffer Busy Waits has increased as the number of
transactions increases. ((3) and (4))
Our current objective is to reduce contention among instances
regarding index where buffer busy waits occur.
We have pretty much accomplished our objective.
Now, let's check event statistics.
((V$SYSTEM_EVENT information))
********************************************************
(Before tuning)
INSTANCE NO EVENT WAIT WAIT (sec)
-----------------------------------------------------------------
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
-----------------------------------------------------------------
(After tuning)
INSTANCE NO EVENT WAIT WAIT (sec)
-----------------------------------------------------------------
2 log file sync 3,146,732 629,080
enqueue 802,320 203,810
buffer busy waits 715,314 71,804
buffer busy global cache 351,148 51,782
latch free 892,286 50,836
********************************************************
|
Total wait time has slightly decreased. waits regarding enqueue and buffer busy...
have also decreased drastically.
On the other hand, log file sync hits the worst wait event. I assume that this event
occurs in two distinct cases where commit is not issued so often or commit is issued frequently.
The latter case must have heavy transactions.
The statistics proves that the buffer wait has decreased dynamically.
After you perform index tuning, check wait events. If you don't see any increases in WAIT,
your tuning is quite successful.
Actually, the statistics indicates the increase in log file sync but this is not
a serious matter.
In Vol.140, I talked about Global Cache Service (GCS) that maintained data consistency
among instances in RAC. I wonder if there is any difference in the statistics.
I will talk about this next week.
That's it for today.
Tadashi Yamashita
|
|
Ora! Ora! Oracle