1748209 Members
2814 Online
108759 Solutions
New Discussion юеВ

SGA & Oracle parameters

 
SOLVED
Go to solution
Kenn Chen
Advisor

SGA & Oracle parameters

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 ?
Cyber Zen
5 REPLIES 5
Steven Sim Kok Leong
Honored Contributor

Re: SGA & Oracle parameters

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
Andreas D. Skjervold
Honored Contributor
Solution

Re: SGA & Oracle parameters

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
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Ed Ulfers
Frequent Advisor

Re: SGA & Oracle parameters

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
Put a smile on your users face, offer them a kiss (Hershey's Kiss).
Chad Molina
Occasional Advisor

Re: SGA & Oracle parameters

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
If ya feel froggy ... start hopping
Greta Blamire
Frequent Advisor

Re: SGA & Oracle parameters

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.
If you can't face the facts, change them!