Re: Some tuning advise

On Oct 22, 8:02 am, Eric von Horst <z80vsvi..._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
> 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

Posting the full Oracle version is quite helpful, as 9.2.0 says nothing, really.

Have you run Statspack reports on this database to at least get a feel for how it's responding? Have you traced sessions to see if, in fact, there are areas needing attention?

Until you have a basic picture of how this system responds to the load you can't expect anyone to know what to tell you.

Run Statspack and post a report, or enable tracing and post the tkprof formatted output so we can see what this system is doing and, hopefully, provide some useful insight.

David Fitzjarrell Received on Mon Oct 22 2007 - 10:10:16 CDT