Re: Assistance on performance tuning

Paul Donovan wrote:
>
> 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

Try using a level 8 10046 trace, ie

alter session set events = '10046 trace name context forever, level 8' (then your plsql)

You get all the waits for as well the load which should assist.

If waits are gobbledeegook to you, head over to www.oraperf.com and download the YAPP paper.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."