Re: Some tuning advise

"Eric von Horst" <z80vsvic20_at_hotmail.com> wrote
> Hi,
> I need some general advise on tuning.
> I am responsible for an third-party application that uses an Oracle
> 9.2.0 database on 2 CPU HP-UX11.11 with 4Gb mem
> Apparently this database has not been tuned/adapted in any way by the vendor.
> (a long time ago I was an Oracle DBA but I left the Oracle plane at
> Oracle 7.3)
>
> Anyway, the application is an event system that receives about 5000 to
> 10.000 events per day (not an enormous amount for an Oracle db).
> The events in the db can be viewed by a dedicated client that runs on
> a Pc. This client starts a connection to the DB and keeps the
> connection open. I did some testing an no additional Oracle processes
> were created by the clients once they were running.
>
> The DB_BLOCK_BUFFERS = 2000 and SHARED_POOL_SIZE = 24000000,
> LOG_BUFFER = 65536
>
> What I would like to do is to increase the number of db writers to 1 per CPU (thus 2).
> Also, the SORT_AREA_SIZE is still set to the default value (64k). I
> would set it to 5Mb (a rough estimate is that one event is about 1024
> bytes so 5Mb should allow to sort on about 1 day of events) I have
> only 10 clients and the system can handle the increase in memory
> The SORT_AREA_RETAINED_SIZE = SORT_AREA_SIZE since the client keeps
> the connection open and thus there is no reason to free the sort
> memory after the sorting operation.
> The HASH_AREA_SIZE = 1.5*SORT_AREA_SIZE (read this in a DBA forum)
>
> Any other tips or advise? I don't expect miracles in performance gain
> but I convinced the performance can be better since the config of the
> default is db is really basic)
>
> With kind regards,
> Eric

Hello Eric,
Since Oracle 9 the SORT_AREA_SIZE parameters are obsolete; if (and only if) PGA_AGGREGATE_TARGET is set. This is a smarter global parameter and worth setting if it's not already. Same goes for HASH_area_size.
The pga-target is for all sessions summed( Aggregated) so 1 Gbyte is a normal value for a Linux system of maybe 10 Gbyte RAM. Ymmv though.

Cheers,

   Roelof Schierbeek Received on Mon Oct 22 2007 - 11:46:04 CDT