topic Re: SGA & Oracle parameters in Operating System - HP-UX

SGA & Oracle parameters

Kenn Chen — Fri, 30 Mar 2001 02:25:05 GMT

Below is my current server setting.
Physical memory = 512 MB
shnmax = 536870912
SGA = 72113968
db_block_size = 2048
db_block_buffers = 20480
The system max memory usage still reached to 55% only, cannot reach to 80%. WHY ? How to change the SGA value ?

Re: SGA & Oracle parameters

Steven Sim Kok Leong — Fri, 30 Mar 2001 03:35:39 GMT

Hi,

In your OS kernel parameter settings, what is your dbc_max_pct value? For more efficient memory allocation, it should be set to a very small value < 15% since Oracle is already using its own buffer cache in the SGA.

Hope this helps. Regards.

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

Re: SGA & Oracle parameters

Andreas D. Skjervold — Fri, 30 Mar 2001 06:53:46 GMT

Hi!
To increase your SGA size, increase the Oracle parameters (found in init.ora (init.ora)):
-db_block_buffers (number of db_blocks)
-shared_pool_size (bytes)
-log_buffer (number of db_blocks)

With db_block_buffers as the important parameter; the more of your database you can fit into the buffer cache the better.
Be adviced not to increase the SGA size to much as Oracle will begin swaping shared memory to disk, and then you're in trouble.

To make the changes active you have to restart the database.

Andreas

Re: SGA & Oracle parameters

Ed Ulfers — Fri, 30 Mar 2001 15:25:05 GMT

Hi,

I think what you fail to realize is that Oracle only allocate/utilze memory as the new pages are needed, unless configured otherwise. This is why memory usage grows over time as a database becomes active.

To get Oracle to access all memory pages on startup (so you can see UNIX allocating them), there is the Oracle parameter Pre_Page_SGA that needs to set as true in the init.ora file.

To me, I am more interested in what the database memory usage is up to, rather than the max expexted. You may want to talk to your DBA for better Oracle memory allocation for its' various pools.

(Also, I never use less than 4K block size on an HP. HP memory page size is 4K. Anything less is inefficient and wastefull.)

Hope this helps,

-- ED Ulfers, UNIX/Oracle Administrator

Re: SGA & Oracle parameters

Chad Molina — Fri, 30 Mar 2001 16:22:21 GMT

Chen,

I agree with Ed that your db_block_size in Oracle should be increased to at least 4K if you are able to. The Oracle SGA size is computed by a formula based on a few parameters in the initSID.ora file. The most important of these parameters is DB_BLOCK_SIZE, DB_BLOCK_BUFFERS, and SHARED_POOL_SIZE.

So in your instance a very rough SGA size would be
DB_BLOCK_SIZE x DB_BLOCK_BUFFERS + SHARED_POOL_SIZE ~= SGA
2048 x 20480 + SHARED_POOL_SIZE = Your SGA

If you want your SGA to be bigger just increase the DB_BLOCK_BUFFERs and Oracle will be able to store more data in its Block Buffer Cache and improve performance for you.

Good luck,
Chad

Re: SGA & Oracle parameters

Greta Blamire — Tue, 03 Apr 2001 20:08:48 GMT

A warning: changing your block size requires exporting and importing your database. We went from 4k to 8k, it was a hassle doing the reload, but definately a performance boost and worth it.