SGA Vs Physical Memory..

[seshib]

Hi guys,

I have little bit doubt can any one clear it off...

How much % can i allocate to SGA from physical memory..

Ex: H.D capacity is 40GB then how much % can allocate SGA ? is any default % for SGA ? and What is Fixed size ?

[abhaysk]

Had you had a bit of knowledege about CO(Computer Organization) then You would have asked q like how much % of ( RAM size + Paging Mem ).

Abhay.

[pravin]

yous should keep the GSA 60% or less of your physical ram

[shashikumar]

Hi

1. Check out the total amount of physical memory (RAM) present on the database server.

2. Check the total size of the database.

3. Oracle recomends 1/3rd the size of the RAM, but it depends on the application and the type of transaction that will happen on the database.

Better sit with Application Developers and the Account Manager, Know the system well then set the SGA size.

[marist89]

Originally posted by pravin
yous should keep the GSA 60% or less of your physical ram

Poppycock. On a box with 16G you want to leave 6.5G free? Oracle should use as much memory is on the box that prevents the box from swapping.

[shashikumar]

Dear Jeff

SGA size should be defined by the DBA. It is not dynamically allocated by the Operating System. Swapping is done by operating system and will taken care will installation of operation system or you can add more swap space according to requirement. Dont mix with swapping and SGA.

[marist89]

Originally posted by shashikumar
SGA size should be defined by the DBA. It is not dynamically allocated by the Operating System.

Really?!?!?!

Swapping is done by operating system and will taken care will installation of operation system or you can add more swap space according to requirement. Dont mix with swapping and SGA.

Oil & Water. Drinking & Driving. Fertilizer & Diesel Fuel. Those are things that shouldn't be mixed.

However, SGA and swapping are something that should be mixed. In fact, managing memory is a fundemental concept for operating a database.

SGA > RAM == swapping
SGA + User Sessions > RAM == swapping
SGA + User Sessions + Box services > RAM == swapping
SGA + User Sessions + Box services < RAM == good

[shashikumar]

Well something is still not convinced.

While designing SGA,

1. Calculate the total no. of user sessions.
2. Calculate apprx PGA size and the sort area size.
3. Set the parameters for the SGA, depending on the database objects for sizing shared pool, log buffer or data buffer cache.

When does the swapping occurs.. ?
When there is no free space for user sessions in the database to perform their sort operations, then paging and swapping occurs.

[marist89]

Originally posted by shashikumar
When there is no free space for user sessions in the database to perform their sort operations, then paging and swapping occurs.

I think you are confusing sorting to swapping. Sorting occurs inside the database, swapping occurs outside the database in the OS. If oracle requests a page of memory that is not available the OS will swap some inactive memory to disk and then reuse it. When the inactive page of memory is needed again, it will be swapped back in and another page will be swapped out. Since Disk I/O is much slower than memory access, this will significantly slow down your database.

[shashikumar]

True....