otak-otak | favorite evening food

November 10, 2011

HW – Contention evil

Filed under: Troubleshoot Tuning — ora62 @ 1:26 pm

Suddenly my old friend call me on 10PM, he asked about HW contention and row cache object +¬†free global transaction table entry which makes their enterprise system “stuck”.

From AWR, the insert statement on a BLOB column can reach 101 seconds, hence makes database call from application server to database slower. Log in to the system and looking for v$session_wait, found a session who execute ‘rollback force’ and hung, I decided to kill this problematic session.

By querying v$session_wait sampled every 3 seconds, found HW – contention from the P3, decode to the file number and block number:

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(771921779) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(771921779) BLOCK#
from dual;

What is the segments which contending on HW:

select owner, segment_type, segment_name
from dba_extents
where file_id = 184
and 169843 between block_id and block_id + blocks - 1; 

Internal application server engine saves state of every transaction to BLOB, and this above query result point to that BLOB. Insertion to this column will wait and finish after 101 seconds. 128 Hash partition on this segments can help at least for a while, and luckily there is no such documentation on the NET about how the best way to partition this table, this table has a column using unix date.

Create a free website or blog at WordPress.com.