"Gary Avery" <Gary.P.Avery_at_Pharmacia.com> wrote in message
news:3e3aa1ff$0$209$1406d58a_at_newsread.pharmacia.com...
> Dear board,
>
> here is some of the output from a statspack report I ran (went on the
> Performace/Tuning course last week)
>
> Snap Length
> (Minutes)
> -----------
> 168.90
>
>
> Top 5 Wait Events
> ~~~~~~~~~~~~~~~~~ Wait %
> Total
> Event Waits Time (cs) Wt
> Time
> -------------------------------------------- ------------ ------------ ---
--
> --
> db file sequential read 524,735 235,947
> 76.07
> db file scattered read 157,554 48,837
> 15.75
<snip>
> If I read this right, then of the 168 minutes that the snap was taken the
> highest 2 waits accounted for 47 minutes of the snap. Now to me that seems
> an awful lot and I beleive I ought to be looking at what sql is being
> performed against the tables to see if there are some obvious indexes
> missing.
The single largest wait accounting for nearly 80% of the time waited is for
db file sequential read, which despite its (in my view highly misleading)
name is in fact generally associated with table access by rowid. (or in
opther words an indexed access path). I suspect that you will find that most
of your execution plans do use sensible access paths. (they may be
suboptimal sql but thats another story).
never the less as you say you have waited for a long time for disk access.
Why. Likely causes seem to me to include
1. Everything on a single raid5 array (especially if it is software raid).
If it then turns out you only have 3 disks as well this will be a big
factor, In short I suspect that your IO subsystem isn't up to the job.
2. Insufficient buffer cache. You may be reading from disk when you should
be reading from memory.
> Is this what i should be doing or are there other 'quicker' gains
> to be made elsewhere. (I have already up'd the db_file_multiblock_read
> count)
Bear in mind that there is in fact a 'correct' value for dbfmbrc which is
the number of blocks that the IO system can in fact read in one call, so if
you have a stripe size of 64k and a db block size of 8k then I'd set dbfmbrc
to 8 as 8*8=64. Increasing dbfmbrc makes FTS look cheaper (which may be a
good thing if you can read 8mb from disk at once) the idea behind it is that
it reflects the io capabilities of your system, so set it so that it does.
(Incidentally in 9i will work out what dbfmbrc should actiually be based on
experience and ignore your setting.)
>
> We run a multi-purpose application with all the datafiles stored on a
single
> raid5 array (NT).
see above. How many disks and hardware or software raid.
>
> Also has anyone done much with function based indexes? Would an index on
> UPPER(surname) gave a dramatic increase to selects...where surname like
> (upper(blah)). we tend to do a fair amount of those.
In a word yes, thats exactly what they are for.
You should also submit your statspack report to the excellent parser run by
Anjo Kolk at www.oraperf.com. Probably the best free resource there is for
statspack/utlstat.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Fri Jan 31 2003 - 12:56:45 CST
