Re: DB File Sequential Read Waits

From: rjpfal <ralbertson_at_comcast.net>
Date: 31 Dec 2003 06:12:13 -0800
Message-ID: <2932a99f.0312310612.57228367@posting.google.com>

"Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message news:<3ff1a45f$0$18752$afc38c87_at_news.optusnet.com.au>...
> "rjpfal" <ralbertson_at_comcast.net> wrote in message

Answers BELOW
>
>
>
> >
> > Redo logs and their mirrors are on /u07 and /u08 separate volumes.
>
>
> Are they really separate volumes? Read on.
>
>
> > Could you clarify on FA here is the disk layout:
> > /dev/vx/dsk/ora01dg/u01 8836096 4593114 4110434 53% /u01
> > /dev/vx/dsk/ora01dg/u02 53010432 35757224 17118480 68% /u02
> > /dev/vx/dsk/ora01dg/u03 70680576 52983600 17558776 76% /u03
> > /dev/vx/dsk/ora01dg/u04 26501120 7492696 18859928 29% /u04
> > /dev/vx/dsk/ora01dg/u05 11043840 3191368 7791136 30% /u05
> > /dev/vx/dsk/ora01dg/u06 11043840 2071456 8902296 19% /u06
> > /dev/vx/dsk/ora01dg/u07 33131520 89424 32783960 1% /u07
> > /dev/vx/dsk/ora01dg/u08 33131520 3942760 28960768 12% /u08
> > NOTE FS Block size is 8K along with oracle block size of 8K
>
> Hmmm, it looks like you got 89M on /u07 in use and a lot more in /u08.
> How come that's where your logs are? Isn't 89M a bit too small
> for logs? What else is on /u08 that might interfere with logs?

The space taken up on /u08 are old archive logs that had come across from the primary before I activated my current test environment. It was 1 of 2 standby's we had setup.

Here are the redo logs from /u07 a mirrored set exists in /u08 along with the old archives.

rchd-cct:/u07/oradata/CCTPROD>ls -ltr                              
total 143584                                                       
-rw-r-----   1 oracle   oinstall 10486272 Dec 30 10:05 redo07.log  
-rw-r-----   1 oracle   oinstall 10486272 Dec 30 10:05 redo05.log  
-rw-r-----   1 oracle   oinstall 10486272 Dec 30 10:05 redo04.log  
-rw-r-----   1 oracle   oinstall 10486272 Dec 30 10:05 redo03.log  
-rw-r-----   1 oracle   oinstall 10486272 Dec 30 10:05 redo02.log  

-rw-r----- 1 oracle oinstall 10486272 Dec 30 10:05 redo01.log -rw-r----- 1 oracle oinstall 10486272 Dec 31 01:13 redo06.log
>
> Still, the problem you have is overall I/O capacity, not just writes?
> So logs might not be THAT important.
>
> The other thing is: if it's separate volumes on the EMC box
> for each f/s, then how come the device name above is the same for all
> f/s? Is this a result of Veritas? I don't recall seeing this when I
> used EMCs. I wasn't using Veritas.

Here I am unforunately relying on the admins but yes it would be a result of VERITAS volume manager I beleive. The admins have provided a breakout of the individual 8G hyper volumes to be and how they are spread across many physical disk to account for the space required for my data and indexes.
>
> > Yes no bind variables being used at all. I am talking with developers
> > about this now.
>
> Yikes! That alone is a killer... You might need the parameter
> to take care of parses when bind variables not used, what was it
> again, anyone?
>
> > > I think You could have also benefited from Locally Managed
> > > Tablespaces.
> >
> > Good point will look into chance of converting.
>
> Definitely. But that alone won't explain the nearly 40ms I/O
> times. It should be much, much lower than that.

Yes this seems to be my bottleneck and with striped volumes I thought it would have been much less. However same test run againt production produced an average wait of 34ms so it is not that far off. Production is concatenated volumes.

I have been reviewing explain plans on all the SQL and everything is low cost, few rows being returned. i.e. indexes being used. Received on Wed Dec 31 2003 - 08:12:13 CST