R. Schierbeek wrote:
> "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
>
>
>
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 ;)
Regards,
Ian
Received on Mon Oct 22 2007 - 12:07:39 CDT
