Re: Database tunning

The recommended maximum size of the SGA is one third of the physical memory. From the figures you provide here it looks like you should experience paging on NT as your SGA is > 200 M. I would try to decrease db_block_buffers and shared_pool_size. I would probably double log_buffer, increase the redo log size to 10M and add a fourth redo log group.
log_simultaneous copies should equal two times number of processors. You probably have to processors.
Hth,

Sybrand Bakker, Oracle DBA

PS I found the script you are using very valuable. I use it too. You don't need to drown in the complete utlestat report and you have the key performance factors at one glance.

On Thu, 29 Jul 1999 08:57:41 +0200, "Maoz Mussel" <maoz_at_mindcti.com> wrote:

>
>Maoz Mussel wrote in message <7n6a6l$s1c$1_at_news.netvision.net.il>...
>>Hello DB experts,
>>
>>I run the checktuning script I found on
>>http://www.ipass.net/~davesisk/oont_download.htm,
>>and received the results below. I read some references regarding the
>>problematic settings,
>>and still have few questions:
>>1. I'm running the database on a 256MB of RAM NT machine. This is a server
>>that serves 1-10
>> clients simultaneously (all in the same account), where 1-3 of the
>>clients run a quite massive
>> batch transactions. Assuming this is a detected machine for DB
>processes,
>>what is the
>> maximum reasonable amount of memory I can dedicated to the Oracle
>>processes? (The current
>> settings are: db_block_buffers = 40000, shared_pool_size = 40000000,
>> log_checkpoint_interval = 10000000, db_block_size = 4096).
>>2. Since the "Redo Log space requests" statistic looks pretty bed, what is
>>the most effective
>> change according to the above settings?
>>3. What is a reasonable value should be set to ENQUEUE_RESOURCES according
>>to the results below?
>>
>>Any advice or suggestion will be appreciable,
>>Maoz
>>
>>
>>
>>Measurement Goal Value Action
>>------------------------------ ------ ----------- -------------------------
>-
>>-----------------------
>>Library cache hit percent >=0.99 1.00 OK
>>Dictionary cache hit percent >=0.90 0.96 OK
>>Buffer cache hit percent >=0.90 0.90 OK
>>Sort area hit percent >=0.90 0.99 OK
>>Redo Log space requests =0 2552 Increase the LOG_BUFFER
>in
>>INIT.ORA
>> Examine Redo Log size
>>" Examine number of Redo
>Log
>>groups
>>-- Current value: log_buffer = 1048576
>>-- Current value: redo log size = 4194304
>>-- Current value: # of redo log groups = 3
>>Enqueue waits =0 130 Increase the
>>ENQUEUE_RESOURCES in INIT.ORA
>>-- Current value: enqueue_resources = 265
>>Checkpoints not completed <=1 180 Increase
>>LOG_CHECKPOINT_INTERVAL in INIT.ORA
>>" Examine
>>LOG_CHECKPOINT_TIMEOUT in INIT.ORA
>>" Examine Redo Log size
>>-- Current value: log_checkpoint_interval = 10000000
>>-- Current value: log_checkpoint_timeout = 0
>>-- Current value: redo log size = 4194304
>>Rollback contention ratio <=0.01 0.00 OK
>>Redo allocation latch miss (W) <=0.01 0.00 OK
>>Redo allocation latch miss (I) <=0.01 0.00 OK
>>Redo copy latch miss (WTW) <=0.01 1.00 Increase
>>LOG_SIMULTANEOUS_COPIES 2xCPU in INIT.ORA
>>-- Current value: log_simultaneous_copies = 2
>>-- Current value: log_small_entry_max_size = 80
>>Redo copy latch miss (Immed) <=0.01 0.00 OK
>>Freelist contention ratio <=0.01 0.00 OK
>>
>>
>
>
Received on Thu Jul 29 1999 - 16:07:35 CDT