Re: Assistance on performance tuning

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
Received on Fri Nov 22 2002 - 19:15:47 CST