Oracle

November 22, 2010

IO issue

Filed under: Uncategorized — srivenu @ 10:27 am

Sometime at the end of October i was asked to look into the performance issue in one of the new databases. It was Oracle 10.2.0.3.0 on Sun OS 5.10. They were presently processing around 175 records/sec to 260 records/sec. They were worried as the performance was highly erratic and they were barely meeting the SLA and they expected an increase in the arrival rate (A term i took from craig shallahamer’s book). Since it was the month-end, they needed some immediate relief.
First glance of the session waits, showed numerous sessions wating on “free buffer waits” and some on “db file sequential read”. System Event stats also showed a huge time accumulation on “db file parallel write”.
It seems the db has been setup as per the application vendors specification. It was configured with 10GB of Buffer cache. The instance had 10 DBWR processes as specified by the vendor (More about this in another post).

I trussed the DBWR0 process and see some abnormal values for file 260 (the first datafile in UNDO tablespace)

 260: S_IFREG mode:0640 dev:329,9003 ino:9 uid:105 gid:501 size:68719460352
      O_RDWR|O_DSYNC|O_LARGEFILE FD_CLOEXEC
      /disk2/oradata/undotbs01.dbf

……..Part of the truss output………..

/193:	6530916.0239	 0.0013	pwrite(416, " ", 16384, 0x)   = 16384
/193:	6530916.0251	 0.0012	kaio(AIONOTIFY, 4406879552)   = 0
/1:	6530916.0251	 0.0023	kaio(AIOWAIT, 0x )            = 1
/204:	6530916.0762	53.8136	pwrite(260, " ", 16384, 0x)   = 16384
/204:	6530916.0782	 0.0020	kaio(AIONOTIFY, 4406837216)   = 0
/1:	6530916.0782	 0.0531	kaio(AIOWAIT, 0x )            = 1
/202:	6530916.0874	53.8139	pwrite(260, "  '", 16384, 0x) = 16384
/202:	6530916.0896	 0.0022	kaio(AIONOTIFY, 4406612544)   = 0
/1:	6530916.0896	 0.0114	kaio(AIOWAIT, 0x )            = 1
/203:	6530916.1181	53.8200	pwrite(260, " ", 16384, 0x)   = 16384
/203:	6530916.1205	 0.0024	kaio(AIONOTIFY, 4406417440)   = 0
/1:	6530916.1205	 0.0309	kaio(AIOWAIT, 0x)             = 1
/205:	6530916.1289	53.8266	pwrite(260, " ", 49152, 0x)   = 49152
/205:	6530916.1325	 0.0036	kaio(AIONOTIFY, 4406938016)   = 0
/1:	6530916.1325	 0.0120	kaio(AIOWAIT, 0x )            = 1
/206:	6530916.1526	53.7966	pwrite(260, " ", 16384, 0x)   = 16384
/206:	6530916.1552	 0.0026	kaio(AIONOTIFY, 4406243616)   = 0
/1:	6530916.1552	 0.0227	kaio(AIOWAIT, 0x)             = 1
/207:	6530916.1848	53.7976	pwrite(260, " ", 16384, 0x)   = 16384
/207:	6530916.1877	 0.0029	kaio(AIONOTIFY, 4407051808)   = 0
/1:	6530916.1878	 0.0326	kaio(AIOWAIT, 0x)             = 1
/220:	6530916.2522	53.8301	pwrite(260, " ", 16384, 0x)   = 16384

I also verified the IO stats recorded in V$FILESTAT.

                                                                                         
                                                Total           I/O   write
                                                write          Time     I/O
                                 Phy             Time        (milli    Time
Datafile          File#       writes           (secs)         secs)  (secs)
----------------- ----- ------------ ---------------- ------------- -------
undotbs01.dbf         2   21,632,569    1,017,710,807     47,045.31   1,164
undotbs05.dbf         6    3,536,387       75,307,326     21,294.99     878
undotbs06.dbf         7    3,532,369       74,229,652     21,014.13     838
undotbs02.dbf         3    3,652,466       72,533,656     19,858.82   1,023
undotbs04.dbf         5    3,537,340       72,053,123     20,369.30   1,028
undotbs03.dbf         4    3,597,883       70,619,202     19,627.99   1,019
.....................
ALARM_COMMENT.dbf   527        1,299                8          6.28       0
ALARM.dbf           526        1,299                8          6.07       0
SUMMARY25_01.dbf    479        1,299                7          5.65       0

After calculating iops on UNDO asked the storage admins to move the existing UNDO from RAID 5 to RAID 10. After making this change, they were able to consistently achieve an application throughput of 400 recs/sec.
I Will be analyzing the io system on that server in a month or so.

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: