Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Some tuning advise

Re: Some tuning advise

From: Ian M <>
Date: Mon, 22 Oct 2007 19:07:39 +0200
Message-ID: <471cd853$0$230$>

R. Schierbeek wrote:

> "Eric von Horst" <> 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

Hi Eric,

Your setting of db_block_buffers cries foul but without knowing more about the database and the environment it is hard to identify the best value. I would add another 0 out of hand i.e. db_block_buffers = 20000
(just keep an eye on your glance output) db_block_buffers has also been updated oracle is pointing everyone towards db_cache_size but the basic rules are not that different.

I would recommend taking statspack snapshots every 30 minutes on the periods you consider "slow" this is normally the best approach to start tuning.

Outside of that welcome back to Oracle ;)

Ian Received on Mon Oct 22 2007 - 12:07:39 CDT

Original text of this message