Main Memory - 16GB
We are running Oracle 9i on SUN system, which has a total of 16GB main memory.
Is there any restirction in allocating the main memory to Oracle Database like 2GB/3GB in WINDOWS?
If not for what parameters generally I can allocate the excess main memory?
If your Oracle is 32bit, probably memory is limited to <4Gb.
If Oracle is 64bit, your memory allocation can exceed 4GB.
You may have to check metalink to confirm this.
What type of datafiles are you using?
If you are using normal filesystems, then there might be some memory overhead allocated from the OS level.
If you are using raw datafiles, then dont have to worry about this OS memory overhead.
You have to know how much physical memory is available on the server, before you know how much to allocate to Oracle that's all.
Generally, in UNIX, the kernel will take up 20% of physical memory.
If you are using normal filesystem (e.g jfs) then you probably need to take note of the filesystem buffer cache overhead which eats into your physical memory.
Allocating excess phycial memory doesnt usually improve your database server. It depends what sort of operations is running on the DB.
All these parameters below affect your SGA sizing:
DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS
Changing these parameters usually depend on what your DB is(OLTP or DW).
Also, take note of sort_area_size, hash_area_size which determines the amount of memory allocated for PGA memory.
If I'm not wrong, PGA memory will be allocated when a user logs on.
This PGA memory will be independent from your SGA used by the instance.
If your server has 16GB of physical memory, probably 20% (3.2GB) is taken up by OS, left with 12.8GB(assuming you are using raw files).
So, if you allocate 12.8GB to SGA, and your individual PGA memory is set at (let's say) 10Mb. Then when you start to have 20 users logon and run some operations, the server will start to page which is no good.
Care have to be taken when you think there is excess physical memory and want to allocate all to Oracle.
In SUN, probably to best way is to use "top" and see how much available physical memory is sitting idle.
What's next after 10g?
Click Here to Expand Forum to Full Width