Oracle Database Blog: Experiments & Learnings

September 28, 2011

One Node in RAC is slow to respond !

Filed under: Oracle RDBMS — Saurabh Manroy @ 9:04 pm

I was about to leave for home when I received a page asking me to join for a performance problem.
a.) Inserts, updates, Drop, truncate,commit were dead slow on one of RAC nodes, while other nodes responded as expected.
b.) Select queries were working fine.

It was pre-production database and was in no-archivelog mode.

I am not able to share the exact numbers and stats because system was behind citrix and copying was not possible due to security reasons.

Following is analysis that I did.

a.) 10046 trace on slow node revealed log file sync wait.
b.) Performed ‘strace’ on LGWR: It was quick enough to post message back to User after a commit operation.

As expected: Doubt was on response of I/O subsystem.

Quick check on storage revealed lot of write latency on one of the disks.

V$IOSTAT_FILE revealed interesting figures for node with slow response time. For FILE_TYPE as ‘log file’ I saw huge numbers against ‘SMALL_WRITE_SERVICETIME’ and ‘LARGE_WRITE_SERVICETIME’ columns. V$logfile was queried to understand the location of logfiles for that thread.  Logfiles for this node were on different diskgroup (DG_DATA) than for rest of the nodes (FG_DATA). This led me to check v$asm_disk:

SQL> select path,write_time from v$asm_disk where group_number=(select group_number from v$asm_diskgroup where name='DG_DATA');

Output was mapped to the one provided by storage team. Bang!

New redolog groups were created for this thread on a different storage array and old redolog groups were dropped. Response time came back to normal.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: