Some tuning advise

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 somewhere)

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 Received on Mon Oct 22 2007 - 08:02:27 CDT