Re: Need Statspack help

"Paul Widner" <paul_widner_at_hotmail.com> wrote in message news:<mNvdd.3$v02.24415_at_news.uswest.net>...
> "Paul Drake" <bdbafh_at_gmail.com> wrote in message
> news:910046b4.0410191944.785046d9_at_posting.google.com...
> > paul_widner_at_hotmail.com (PaulW) wrote in message
> news:<3fdda837.0410191359.515564bb_at_posting.google.com>...
> > > We have collected the following stats pack report, could someone give
> > > me some feedback on it? Any help would be great! Thanks, Paul
> >
> > tune the app sql.
> >
> > large amount of full table scans.
> > check v$sql_plan for full table scans, as well as v$session_longops.
> >
> > 350 physical reads/second (how many controllers, disks?)
> > 10 msec avg duration per wait on db file scattered read, sounds like a
> > longish queue on new disks - it should be half of that.
>
> They UNIX SysAdmin told me the drives are running on a SAN with 5 drives in
> a RAID 5 configuration. The SAN is connected via fiber channel. All DB
> operations are on this one volumn. Do you think this could be a problem?

Some people think that both online redo logs and archived redo logs should be on RAID-1, as they are by nature accessed in a sequential fashion, and they should be on different RAID-1's because of reading the redo's to archive. Others say spreading the disks you would have used in RAID-1 over the RAID-5, plus modern battery backup and write-caching removes much of any performance difference under normal circumstances.

And of course, RAID-5 is cheaper, so you wind up having to prove to your sysadmins that there is an actual benefit to RAID-1. And that can be difficult, since the difference may only be noticeable under transient heavy loads or during heavily sequential processing. The scattered reads may indicate sequential processing (ie, full table scans), but as everyone will note (like Paul D, above), those may go away with application tuning. Which is why so many advocate tuning the app first, it can be a big win in the real world. Then if you still see queue's that are too long, you can start trying to figure out hardware issues.

(lest anyone lynch me, my sympathies are with BAARF).

>
>
> >
> > also, apply the 9.2.0.5 patchset and whatever patchsets are applicable
> > for your platform as covered in Security Alert #68.
> >
> > -bdbafh

jg

--
@home.com is bogus.  Top ten places to retire (US):  Florence, Ore.;
Scottsdale, Ariz.; Charleston, S.C.; Melbourne-Palm Bay, Fla.; North
San Diego County.; Tucson, Ariz.; Medford-Ashland, Ore.; Lake
Winnipesaukee, N.H.; Daytona Beach, Fla.; Fayetteville, Ark.;
Bellingham, Wash.