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:

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.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: