|
Lock Down
|
When large amount of physical read causing buffer busy waits occur,
the number of hot objects (i.e. the status of the block is LRU_FLAG=8) increases suddenly.
The following is an example from the last time.
SQL> |
1 select count(*) cnt,lru_flag,sum(x_to_null)
2 from 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
|
The reason why only hot object exists in the example above is that as full scan is heavily
performed in a single node, cold objects are taken one after another.
As a result, hot object related to lockdown only remains.
The following is the status before the full scan is performed.
CNT LRU_FLAG SUM(X_TO_NULL)
---------- ---------- --------------
13042 0 4212
2694 2 989
12 8 22
|
There are few hot objects.
The above example proves that the occurrence of lockdown has nothing to do with
the status of LRU_FLAG.
However, if buffer_hit_ratio is 50%, the status of the blocks is shifted to LRU_FLAG=8,
and consequently, X_TO_NULL indicates the true accumulation of lockdowns.
In the contrary, when buffer hit rate is 98%, there is little point in cheking
the accumulation of lockdowns.
Even though buffer hit ratio is 98%, it will mean something if you check the
accumulation of lockdowns by sorting in multiple nodes.
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.
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.
8. It is not a big deal because the number of lockdowns only indicates its accumulation.
9. Even though there are few lockdowns, it is easy to find an object where the count suddenly rises.
That's it for today.
Yoshihiro Uratsuji
|
|
