Re: DB File Sequential Read Waits

From: rjpfal <ralbertson_at_comcast.net>
Date: 30 Dec 2003 07:54:16 -0800
Message-ID: <2932a99f.0312300754.5af48627@posting.google.com>

srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0312292314.71fc02e6_at_posting.google.com>...

Answers below:

> ripfal,
> What's the EMC model that you have ? is it a symmetrix ?

Yes it is a Symmetrix

> Are you using any volume manager on top of EMC ?

Veritas Volume Manager is being used on top of EMC.

> In DB File Sequential Read Waits the thing to look for is the average
> wait. In your case it seems a bit high at 39ms. I have seen an average
> of upto 8 ms on some boxes.

This is what I thought also. I am trying to reduce IO and improve data availability in the buffers to avoid the io to disk.

> Also It is always recommended to use striped metas for your data and
> index discs. You can use concatenated metas for your archive log
> destinations.

It is striped for both indexes and disk. Log files are also on striped volume. It is using Oracle mirrored redo logs and also production is writing to a standby. Test is not using a stanby, totally isolated.

> Where did you place your redo logs ? a log file sync of 13 ms in your
> case is also a bit on the higher side.

Redo logs and their mirrors are on /u07 and /u08 separate volumes.

> Whats the total hard disk capacity that you put on your FA port ?
> That will also have an impact on the I/O.

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

/u01 is oracle app and ctrl1 and system
/u02 and data and ctrl2
/u03 is indexes and ctrl3
/u04 is BLOBS
/u05 is ROLLBACK
/u06 is TEMP
/u07 and /u08 are REDO

> What SAN switch do you use and have you applied all patches for it ?

Will have to ask admins for the info.

>
> As far as the database is concerned,
> It would have been good if you had enclosed a statspack report for a
> 20 minute duration. Are you running any long batch jobs in your
> instance ?

No batch jobs at all are being run for the test. Just random queries of account information as if simulating use by the end user.

> During the period of collection, i think several long running
> operations have ended.
> Your hard parse ratio is horrible.

Yes no bind variables being used at all. I am talking with developers about this now.

> Did you do any customization ? I think your developers are not using
> bind variables.

None working on solution. Yes there is customization but even core code has no bind variables.

> I think You could have also benefited from Locally Managed
> Tablespaces.

Good point will look into chance of converting.

> How many copies of control files do you have and where did you place
> them ?

3 on /u01, /u02 and /u03
>
> By the way what was going on in the instance while you collected the
> stats?

Just radom queries against primary customer account tables as derived from production, using a 3rd party tool to simulate production. Also I am collecting IO stats every minute.

> I think you can set your sort_area_retained_size also to 1M (the same
> as your sort_area_size).
>
> regards
> Srivenu

I have placed the primary table in both the KEEP and RECYCLE pools and re-ran tests with no improvement seen. Received on Tue Dec 30 2003 - 09:54:16 CST