|
Lock Down
|
Last time, I viewed v$segment_statistics to check the statistics of an object
that had been updated. Write contention in Real Application Clusters environment required
heavy load. By viewing v$segment_statistics, it was possible to identify the object
causing the problem.
Now, I actually let write contention occur and obtain the data.
(Oracle 10g environment)
Linux 2.4.9-e.24enterprise
Oracle10g EE Release 10.1.0.2.0
I issue the following SQL statement to obtain easy-to-understand result.
In fact, I'm supposed to issue the SQL statement that does not require heavy load though.
SQL> |
1 select object_name, statistic_name , value
2 from v$segment_statistics
3 where statistic_name like 'gc current blocks received'
4 and value > 0
5 and obj# in
6 (select obj |
7 from x$bh x , obj$ o |
8 where bitand(flag,2097185) > 0 |--
9 and x.obj = o.dataobj# | |
10 group by obj | |
11 ) |
12* order by 3 desc |
SQL> / Updated object ----
OBJECT_NAME STATISTIC_NAME VALUE
-------------- ------------------------------ ----------
ORDERS_I1 gc current blocks received 286201
ORDERS_I2 gc current blocks received 286176
NEW_ORDER_I1 gc current blocks received 276446
ORDER_LINE_I1 gc current blocks received 267995
NEW_ORDER_I2 gc current blocks received 229234
HISTORY_I1 gc current blocks received 131412
CUSTOMER gc current blocks received 114743
DISTRICT gc current blocks received 86628
WAREHOUSE gc current blocks received 73724
HISTORY gc current blocks received 69672
NEW_ORDER gc current blocks received 57633
.
.
|
Large number of lockdowns occur in ORDERS (i.e. ORDERS_I1, ORDERS_I2),
which is not a good sign actually.
I can identify the object where write contention occurs by obtaining the
data by time series.
But, there is a problem to do that. Once the data are removed from the buffer,
it is not possible to obtain the data. In other words, it is not possible to
obtain all the statistics.
If you want to avoid this problem, create a table "list of objects with heavy writes"
as master and obtain the statistics on the objects in this master only.
After identifying the object where write contention occurs, we can move to the tuning step.
That's it for today.
Yoshihiro Uratsuji
|
|
