|
Lock Down
|
In the last issue, we checked the number of times which caused the lockdowns per object.
(i.e. number of times the lockdown occurred in a specific instance by querying the block
from other instances in current mode.)
It was not actually the number of times the lockdown occurred in an instance but the number
of times which caused the lockdown to other instances.
As response usually slows down in the instance causing the lockdown, we should focus on
that instance to do performance tuning.
If a system runs an online batch in the Real Application Clusters environment, the number
of times which causes the lockdown will increase.
We already know that v$segment_statistics contains the information above.
However, there are some cases where performance slows down by accessing this table because of
overload.
v$segment_statistics
v$segment_statistics contains:
1. the statistics per object since the instance is started. In other words, there is huge
amount of data.
2. the statistics that is the accumulation of the data. Thus, it is necessary to determine
the finite difference.
I think we need to obtain the data from v$segment_statistics by time series and check the
finite difference by every time-line.
This may be possible in small-scale system only. In large-scale system (i.e. there is huge
amount of objects), the amount of data is too huge to obtain the data by time series.
Identify the object that causes the lockdown
If I could narrow down the object on some level, it may be possible to obtain the data
by time series.
I can simply check the object that meets the following condition
1. Object that is accessed on the database buffer
2. Object that is being updated (written)
All objects should be on the database buffer except for some processes such as direct load,
which means that it is possible to identify the object on the database buffer.
The following SQL statement can be used to obtain the target object.
SQL> |
1 select obj , o.name, count(*)
2 from x$bh x , obj$ o
3 WHERE bitand(flag,2097185) > 0
4 and x.obj = o.dataobj#
5 group by obj ,o.name
6* order by 3 desc
SQL> /
OBJ NAME COUNT(*)
---------- ------------------------------ ----------
49447 CUSTOMER 251
49454 STOCK 241
215 HISTGRM$ 134
215 C_OBJ#_INTCOL# 134
49459 HISTORY_I1 45
3955 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 39
49463 ORDERS_I2 32
49461 NEW_ORDER_I1 28
49824 NEW_ORDER_I2 22
49465 ORDER_LINE_I1 15
49462 ORDERS_I1 14
2 CLU$ 13
2 COL$ 13
2 IND$ 13
2 LOB$ 13
2 TAB$ 13
2 ICOL$ 13
.
.
.
----------------------------------------------------------
(Reference)
When object is being written, the following flag in x$bh turns to ON.
buffer_dirty [dirty FLAG=1 ]
being_written [being written FLAG=32 ]
block_written_once [written once FLAG=2097152]
The sum of all flags above is 2097185.
You can determine a type of flag by the following WHERE clause.
bitand(flag,2097185) > 0
----------------------------------------------------------
|
We can identify the object causing the lockdown by obtaining the object
information above from v$segment_statistics.
That's it for today.
Yoshihiro Uratsuji
|
|
