Assistance on performance tuning

Hi,

I'd like some pointers on performance tuning an package i'm developing. I've tried quite a few things and although performance is improved, I can't see why it doesn't get even quicker ! The package loops through around 26000 records, doing various sums of transactions and other calculations - some of these use dynamic sql. To run for a month's worth of transactions it can take 2 hours.

I have:

i) Traced the session, ran tkprof, attempted to make all the queries use

     indexes
ii) Set sizing on tables and indexes (there were lots of extents - initially I

     hadn't spent much time sizing)
iii) Increased the shared pool and sort area size (these were very small)
iv) Tuned the processing (improved the efficiency of what it was doing)

All this, and trying a few variations of init.ora parameters seems to have done little - although it has improved things. The thing that's bugging me is - if there's:

i) spare CPU capacity (running at average 50% on two CPUs - not with PQ

     though),
ii) lots of spare memory (1GB, oracle only using 180MB) iii) I believe spare disk IO capacity (although I have a hunch it is Disk I/O

     related) - how do I check on 2000 ?

Environment:

Oracle 8.1.7 (don't think its Enterprise as Paralel Query not installed)
Windows 2000
2 x 1GhZ processors
Compaq disk, I believe its one large disk with C,D,E on it - Oracle datafiles are all on E:

What I don't understand is - why isn't Oracle using all the disk, CPU and memory it needs to make this thing quicker ?! I could say 'lets buy more disk' if I knew it was disk, same for CPU or memory but I can't pinpoint it to any of this. If it was my code that was the problem, wouldn't Oracle just use more CPU or Disk to run it ?

I've ran statspack as best as I knew how and nothing jumped out at me.

Help much appreciated. Clever, smart comments about how limited by Oracle DBA knowledge are are not !

Regards,

Paul Received on Tue Nov 19 2002 - 12:08:24 CST