Re: Assistance on performance tuning

Steve M wrote:
>
> Here's my 2 cents worth:
>
> Give Oracle as much memory as possible (increase DB_BLOCK_BUFFERS)
>
> and
>
> use hints if you are sure you know "better" than Oracle's optimizer
> (usually).
>
> "Tune" your indexes carefully. Creating indexes for "select" statements are
> different that for inserts and updates. (which is more important?)
>
> If you are doing mostly selects and you don't care too much about updates,
> use many composite indexes; if updates are many and selects are few,
> eliminate composite indexes where fields appear in more than one index
> (multiple indexes may have to be "updated" when records change)
>
> Examine buffer gets associated with each query. Do whatever you can to
> reduce 'em.
>
> If massive disk reads are involved (as would be the case with small number
> of db_block_buffers and full-table-scans), rebuild tables to use fewer
> extents
> so that disk reads will be contiguous. If massive updates are involved,
> make sure you have sufficient redo logs and that they are large enough.
> always (always) spread tablespaces across multiple disks and controllers.
>
> There are some initialization parameters associated with tricking the
> optimizer to favor,
> say, indexes, over table-scans. Change these sparingly and with great care.
>
> I have a sun box with 8i. We saw 100% cpu utilization with 2G ram.
> We upped it to 4G ram. and gave db_block_buffers 3G.
> CPU utilization dropped to 20% (identical test scenarios).
>
> some of this may help.
> good luck.
>
> "Paul Donovan" <pdonovan_at_clara.net> wrote in message
> news:bfdced1d.0211191008.34b5c64e_at_posting.google.com...
> > 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

"rebuild tables to use fewer extents so that disk reads will be contiguous."

To quote John McEnroe: "You cannot be serious"

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

http://www.oracledba.co.uk

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