Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Sizing oracle instance using sga_max_size or sga_target?

543412Jan 2 2007 — edited Jan 11 2007
I'm trying to size two 10.2.0.2 dev instances on one Solaris 9 server , and am
wondering if I'm calculating the SGA/PGA's correctly. Does Oracle allocate/take
up the value in sga_max_size or sga_target? Here's my scenario below:

Given 32GB of physical RAM, I started off with a budget of about 25GB.
Based on usage requirements, I sized the two instances as follows:

Instance A
12GB sga_max_size
9GB sga_target
2GB pga_aggregate_target
expected total RAM usage = 11GB

Instance B
3.5GB sga_max_size
3GB sga_target
1GB pga_aggregate_target
expected total RAM usage = 4GB

I figured I was way under. However, when I started instance A, here's what I
observed: the system had over 28GB available (including physical/swap)

swap -s
total: 114368k bytes allocated + 86696k reserved = 201064k used . 28,833,232k available

After I started Instance A, the available ram dropped 20GB down to 8gb,
way more than the 12GB I expected it to. Instance A seemed to have a footprint of 20GB.

swap -s
total: 10138984k bytes allocated + 1292120k reserved = 11431104k used, 7,929,848k available

For instance B, I paid closer attention and started the database with following params:

*.pga_aggregate_target=1g
*.sga_target=3g
*.sga_max_size=3500m
*.processes=1000

- Like I tried earlier, the mem availability showed about 8GB:

total: 10138984k bytes allocated + 1292120k reserved = 11431104k used, 7,929,848k available

- I started instance up and it took 3500MB in sga:

Total System Global Area 3674210304 bytes (3504m)
Fixed Size 2033256 bytes (1980k)
Variable Size 1090525592 bytes (1040m)
Database Buffers 2566914048 bytes (2448m)
Redo Buffers 14737408 bytes (14m)

- the post-swap image took up effectively 7GB! By my calculations, a 3.5GB SGA_MAX and
1GB PGA should have only lowered the available mem by 4.5GB.

SQL> !swap -s
total: 13377232k bytes allocated + 1751536k reserved = 15128768k used, 1049136k available

- yet when I look at the 'top' results, it says 16.3GB free:

load averages: 0.29, 0.68, 0.58 easaut01 17:17:16
268 processes: 266 sleeping, 1 stopped, 1 on cpu
CPU states: 96.5% idle, 2.6% user, 0.7% kernel, 0.2% iowait, 0.0% swap
Memory: 32.0G real, 16.3G free, 14.5G swap in use, 860M swap free

So what is taking up this extra memory? Both instances are sized to about 12 and 4.5GB,
yet their observed footprints are 20 and 7GB, respectively. Is the user session data
taking up memory? Am I not supposed to use sga_target in calculating my memory?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 8 2007
Added on Jan 2 2007
12 comments
2,830 views