Oracle parameters

[Kenn Chen]

Currently, i have upgrade my memory additional 256 MB. But, i did not chnage my oracle parameters (eg. SGA), so i cannot see any imptovement in term of response time. Could advice which are the parameters need to be changed. Thanks.

[Steven Sim Kok Leong]

Hi,

One HPUX kernel parameter you would want to change is shmmax which limits the amount of shared memory you can use for your Oracle SGA.

Hope this helps. Regards.

Steven Sim Kok Leong
Brainbench MVP for Unix Admin
http://www.brainbench.com

[Volker Borowski]

Hi Chen,

usally db_block_buffers and shared_pool_size are the first ones to be increased.
Anything else depends on your application and the distribution of your data.
If the application is doing a lot of sorts, it might be good to increase the sort_area_size.
If you use MTS, it might be an additional option to increase the number of server-processes.

First guess
Volker

[Kenn Chen]

Why shmmax parameters is always equal to size of phyical memory, eg server RAM=256, then shmmax=268435456. Is it recemmoneded from Oracle ?

[Printaporn_1]

Hi,

It's not mean that shmmax = phymen
but shmmax*shmmni have to big enough for oracle share memory region.
there are some document in oracle metalink state the relationship.
with oracle paramater and hp-ux share memory paramater.

[Andreas D. Skjervold · Accepted solution]

Hi
SHMMAX is the maximum ALLOWABLE segment size, but it is allowed to have multiple segments!
If this value is smaller than your physical memory and futher more if its smaller than your SGA size, the only result is that you'll end up with a SGA consisting of 2 or more shared-memory segments. This might inflict on the performance, (even though I haven't noticed any problems on one of my systems running with a lo value for SHMMAX ('cause of Sybase on the same machine))

The important value is DB_BLOCK_BUFFERS in your init.ora. The more active data that can fit into your buffer cache the faster your db will run.

The value for shared pool should'n make such a difference as the code you're executing today should fit in the library cache already, but if it does'nt; this is important as well.

regards
Andreas