|
Lock Down
|
Hello, everyone. In this issue, I will start a new topic, lockdown.
When write-write contention occurs in Oracle 9i Real Application Clusters,
write lock is released from a node. We call this a lockdown.
If the write lock is released in the following conditions, it may cause performance
degradation.
1. There are many releases of the write lock of the same object among nodes.
2. The object above is an index.
3. A count of a specific object rises suddenly in certain time frame.
Now, I check the number of locks.
SQL> l
1 select count(*) cnt,lru_flag,sum(x_to_null)
2 from sys.x_$bh
3* group by lru_flag
SQL> /
CNT LRU_FLAG SUM(X_TO_NULL)
---------- ---------- --------------
4047 0 0
7694 2 0
9733 4 0
5022 6 0
8586 8 2300
|
LRU_FLAG indicates the status of a block.
LRU_FLAG=8, for example, is a hot block that is accessed frequently.
The status above indicates:
1. There are 8586 hot blocks.
2. Write lock is released for 2300 times in the hot block.
3. Write lock is not released in other blocks.
The number 2300 here, is an accumulation of lockdowns occurring in some blocks of an object
on database buffer.
Now, I count the number of lock in an object.
SQL> select count(*) cnt,lru_flag,sum(x_to_null),obj
2 from sys.x_$bh
3 group by lru_flag,obj;
CNT LRU_FLAG SUM(X_TO_NULL) OBJ
---------- ---------- -------------- ----------
350 0 0 2
1 0 0 3
3 0 0 6
33 0 0 8
3 0 0 10
1 0 0 17
58 0 0 18
continues...
....
219 8 1037 7222
554 8 223 7223
135 8 44 7224
175 8 228 7225
1 8 0 7226
15 8 0 7227
133 8 0 7228
21 8 0 7229
1 8 0 7230
1 8 0 7231
2 8 0 7292
1 8 0 7372
16 8 0 7373
6 8 0 7374
2 8 0 7375
25 8 0 7377
|
With this result above, I can identify objects where write lock is released frequently.
Object name can be identified by executing the following SQL statement.
select
o.obj#
,o.name
,o.subname
,o.dataobj#
,o.type#
,o.owner#
,u.name
,i.bo#
from sys.obj$ o, sys.ind$ i , sys.user$ u
where u.user#=o.owner#
and o.obj#=i.obj#(+)
and o.obj# = xxxx
|
The important matter is that the number of lockdowns is an accumulation of
lockdowns which have been occurring up to current time. It does not necessarily mean that
the lockdown is occurring for 2300 times at present.
Again, as I explained in the beginning, if the write lock is released in the following
conditions, it may cause performance degradation.
1. There are many releases of the write lock of the same object among nodes.
2. The object above is an index.
3. A count of a specific object rises suddenly in certain time frame.
In addition to those conditions, I state the following:
4. If the number of lockdowns increases for a certain period in 1. and 2. above,
that is a key to find where the problem is.
5. If the object above is an index, it will be a serious problem.
6. If you don't see any increase in lockdowns for a certain period, you can ignore it.
7. In that case, you can simply focus on an object where the count suddenly rises.
That's it for today.
Yoshihiro Uratsuji
|
|
