Performance Tuning help

From: Gary Avery <Gary.P.Avery_at_Pharmacia.com>
Date: Fri, 31 Jan 2003 16:19:11 -0000
Message-ID: <3e3aa1ff$0$209$1406d58a@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
db file parallel write                              1,993        8,248
2.66
db file parallel read                               2,217        5,562
1.79
control file sequential read                       14,152        3,896

1.26

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. 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)

We run a multi-purpose application with all the datafiles stored on a single raid5 array (NT).

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.

regards

Gary Received on Fri Jan 31 2003 - 10:19:11 CST