SGA Max Size
YenYang, May 26, 2004 - 9:11 am UTC
Im working on my home PC for testing on Oracle 9.1 on Win 2000.
In my init.ora, SGA_MAX_SIZE = 157286400 (150MB) but the alert log shows SGA_MAX_SIZE = 161028720 (153 MB).
The total SGA it shows is 161028720 (153 MB).
The show parameter displays SGA_MAX_SIZE = 161028720 (153 MB).Why is init.ora value not considered ? Also why is SGA_MAX_SIZE value not shown in alert log and total SGA is greater than init.ora's value ?
Im starting the DB with the same init.ora. Im sure about that.
May 26, 2004 - 11:43 am UTC
Did oracle shrink the memory usages?
Rollingpig, September 15, 2004 - 12:00 am UTC
I know that when setting the sga_max_size,Oracle only allocate memory of total size of db_cache_size+shared_pool_size+large_pool_size+java_pool_size.
SQL> show parameters sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 8391554864
SQL> show parameters pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 33554432
large_pool_size big integer 218103808
olap_page_pool_size integer 33554432
shared_pool_size big integer 536870912
SQL> show parameters db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 4294967296
SQL>!vmstat 1 2
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr m0 m1 m3 m4 in sy cs us sy id
0 4 0 21785552 7348424 70 356 224 0 0 0 0 42 41 65 71 531 4579 1480 1 3 96
0 5 0 11763208 2467048 0 6 0 0 0 0 0 42 50 70 74 568 1259 1771 0 1 99
It show that oracle only allocate about 5G mem.
Then I try to increase the db_cache_size
SQL> alter system set db_cache_size=6G
2 /
System altered.
SQL>!vmstat 1 22
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr m0 m1 m3 m4 in sy cs us sy id
0 4 0 21170920 7050640 67 346 211 0 0 0 0 41 41 66 72 533 4376 1490 1 3 96
0 5 0 8003248 354736 0 6 0 0 0 0 0 32 56 79 66 576 551 1621 0 1 99
This show oracle allocate about 7G mem.
It does show a dymantic increase of mem usage.
Next step,I try to decrease the db_cache_size
SQL> alter system set db_cache_size=2G
2 /
System altered.
SQL>!vmstat 1 5
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr m0 m1 m3 m4 in sy cs us sy id
0 4 0 15220880 3068936 39 229 102 1 0 0 0 37 42 70 77 581 3054 1771 1 2 97
0 5 0 9557400 295280 30 322 8 0 0 0 0 17 31 86 91 701 1880 2094 0 1 98
0 5 0 9557400 295280 0 0 0 0 0 0 0 48 65 67 65 588 759 1898 0 3 97
0 5 0 9557400 295280 0 0 0 0 0 0 0 12 26 76 103 537 593 1772 0 0 100
0 6 0 9557192 295120 187 1674 24 0 0 0 0 8 14 67 105 570 3127 2075 1 3 96
It does not show a dymantic shrink of mem.
Could you kindly explain this?
September 15, 2004 - 9:14 am UTC
vmstat is not a good indicator of how much memory a process/set of processes is using. also, vmstat output is affected by many things depending on which OS you happen to be running on (eg: solaris grabs almost all free memory for the OS file system buffer cache)
Did oracle shrink the memory usages?
Paul Xu, November 24, 2004 - 10:13 am UTC
To see Solaris OS grab all free memory is not true. Solaris does grab some free memory, but to an certain extent.
I was hoping you can give some insight as to why free swap space also shrinks with free memory when SGA or db_cache increase. Your explanation is useless to me.
November 24, 2004 - 10:21 am UTC
You got it wrong -- ask Sun about how their swap works, for you see -- the OS does that.
And yes, Solaris will grab as much free memory as it can to buffer the OS file system. The more you read, the more it takes. It is normal, natural, expected and 100% "a good thing" to use all of the RAM you bought, don't you think?
Swap is used as a backing store, OS's grab swap pessimistically (up front, before you need it).
Otherwise, if they needed to swap and there was no swap -- you die and that is considered "bad"
You can ask Sun if you like, but it is the way it works (and it is a sun question more so than anything else)
large sga
reader, January 06, 2005 - 3:49 pm UTC
Is it true that if I have to set a "large" value for SGA that exceeds the limitatio of os, do i have to use db_block_buffers instead of db_cache_size? Any link or pointers for a doc on this?
Thanks.
January 06, 2005 - 7:07 pm UTC
not that I've heard of, not sure what you mean by exceeding the limitations of the OS either?
SGA size and process memory
Kumar, March 04, 2006 - 10:16 pm UTC
From above
--
it does not affect process memory, such as that used in a dedicated server for sorting, hashing, holds your plsql variables, etc....
We then dynamically allocation memory in each process (dedicated server as they are known) for your
session.
---
The above 2 statements look contrary to me.
Does SGA size affects process size(dedicatd server)?
Thanks.
March 05, 2006 - 1:37 pm UTC
when you take two statements entirely out of context and present them, sure - they can look contradictory.
Look at the paragraph in front of the first statement:
...
the sga max size is an upper bound on the SGA only. and it is an upper bound
the dba could resize it to -- we do not dynamically grow and shrink the sga
without being told to do so....
we were talking about the SGA. Process memory is not allocated out of the SGA at all, so the sga max size won't affect the PROCESS memory.
SGA = shared global area, large pre-allocated memory segment.
PGA = process global area, allocated dynamically in process memory, not taken from the SGA.
SGA and process size
Kumar, March 06, 2006 - 2:18 pm UTC
I asked this question because on my database on HP-UX the process sizes are big as seen from glance and increase/decrease with increasing/decreasing SGA and are independent of PGA_aggregate_target.
And I logged a TAR with Oracle support also but they could not find why it is happening.
Thanks
March 08, 2006 - 4:17 pm UTC
they should have know.
These tools report back attached shared memory segments. You have to use "better" tools to see the real stuff going on.
tools like "top", "glance" report back all memory accessible by a process - including the shared memory, which is misleading.
Process Memory usage utility for HP
J. Laurindo Chiappa, March 07, 2006 - 11:56 am UTC
Maybe you can try Ivan Skytte Jørgensen's pmap for HP-UX:
</code>
http://i1.dk/download/pmap/ <code>
and try some investigation ...
Regards,
J. Laurindo Chiappa