Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: can anyone help me tune the database

Re: can anyone help me tune the database

From: joel garry <>
Date: 13 Sep 2006 14:56:57 -0700
Message-ID: <>

Charles Hooper wrote:
> wrote:
> (Selective snipping):
> > Top 5 Timed Events
> > ~~~~~~~~~~~~~~~~~~
> > % Total
> > Event Waits Time (s)
> > Ela Time
> > -------------------------------------------- ------------ -----------
> > --------
> > CPU time 83
> > 62.08
> > direct path write 16,504 43
> > 32.23
> > db file sequential read 927 3
> > 2.02
> > db file scattered read 962 2
> > 1.25
> > control file parallel write 401 1
> > 1.11
> > CPU Elapsd

> You have sort_area_size at 524,288 B and pga_aggregate_target at
> 39,845,888 B. This sets the minimum value of the sort_area_size to
> 512KB, but that value can float upward, I believe to 5% (check Jonathan
> Lewis' Cost-Based Oracle Fundamentals book for the correct percentage)
> of 38MB (which ic roughly 1.9MB). Increasing the value of
> sort_area_size to 5MB and also increasing the pga_aggregate_target may
> decrease the sorts to disk, but fixing the SQL statement that I
> identified above may have a larger impact.

I agree with Charles, both for the sort area too small and that fixing those SQL's might have an even larger impact.

I'd add, to a lesser degree, see metalink Note:50416.1. It adds the questions:

Are you using Async I/O? (with the necessary platform information included in the question).

Are your temp sort files in conflict (on the same controllers or devices) with other heavily used files, such as undo, archived logs or the data files being read/written to? Impact on all these might be improved with Charles' suggestions, by the way.

Also, are any messages being written to the alert log like "checkpoint not complete" or complaints about archiving? How often are your log switches during these end-time processes? Are your cpu's pegged?

If you have 9.2 OEM, look at the resource advisor for PGA. Click on Memory usage details and see how many multipass executions you have for your pga size. Set your pga to the minimum value that gets rid of them. 400M works for my hp-ux system, yours will probably be different. There's a V_$PGA_TARGET_ADVICE view if you don't have OEM.


-- is bogus.  "If you're trying to do trouble-shooting,
kicking the database to death is not a good way to go about it." -
Jonathan Lewis
But then again, sometimes a bit more pain might show what hurts more
Received on Wed Sep 13 2006 - 16:56:57 CDT

Original text of this message