Oracle

April 17, 2011

Assumption begets Failure

Filed under: Uncategorized — srivenu @ 8:28 am

This is the cardinal principle in debugging.

I was debugging a performance issue last week in one of our RAC databases. It was a typical OLTP application with hundreds of tps and a few high concurrenty tables. The tables involved were daily partitioned.

A quick scan of the system showed several hundreds of sessions waiting on “enq: TX – index contention” and several tens on “db file sequential read”, “buffer busy waits”. I was also immediately informed that previous night some of these tables have been re-orged and indexes rebuilt.

I noticed that the new index and table partitions were located in non-ASSM tablespaces. I assumed that the “enq: TX – index contention” was being caused due to branch or root block split. Despite my experience, In the haste to bring things to normal, instead of trying to analyze the issue in detail and applying a scientific approach, I immediately set on the task on working on my assumption. I wasted precious time trying to re-org the new index & table partitions.

That effort did not improve the situation and a trace of the oracle session and the unix process (truss) clearly showed abnormal elapsed times on the reads of some blocks of data. Some of the “db file sequential read” calls were showing even 90 seconds!. The truss showed that the process was hanging for long on the open system call.

truss -dD 

/1:     36348557.9213   43.3012 open("/dev/rdsk/emcpower20g", O_RDWR|O_DSYNC)   = 13

I would rank think as my worst debugging moments.

Moral of the story – Never assume things. Even in a deep crisis situation, take a deap breath and formulate a plan before taking the plunge.

Advertisements

1 Comment »

  1. At last,whats the action taken on the villainous behaviour!

    Comment by Phani Kumar Pendurthi — May 31, 2011 @ 2:05 am | Reply


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: