05-20-03, 03:51 #1Registered User
- Join Date
- May 2002
Unanswered: Oracle instances and physical RAM
I am interested to know how Oracle uses the physical memory on a machine.
When you size the parameters in the INIT.ORA file you determine the size of the SGA, but how much of this SGA is loaded into physical memory and how much is used by Virtual memory? (or is it more of a case, if the RAM is available, then it will grab it?)
However, is this all of the RAM then that the instance needs? or will processes spawned by the Instance result in the Oracle DBMS using additional RAM as well (additional to that which it will grab upon booting the server)?
I am asking this for I am about to build an Oracle server that will have multiple instances on it. But I am not sure exactly how big to make the SGA's do I create them to utilise the max amount of ram possible, or make them smaller so that there is a fair bit of ram left vacant on the machine?
Can someone point me in the right direction, I have tried looking at various documentation out there, but it basically talks about sizing the various parameters to create the SGA. I have not found anything discussing my other thoughts.Daniel Buckingham
05-20-03, 08:12 #2Drunkard
- Join Date
- Nov 2002
- Desk, slightly south of keyboard
By default Oracle will not grab all ram allocated to it on startup. If you size the SGA at 500Mb, Oracle won't allocate that 500Mb until it needs it.
You can override this behaviour by putting PRE_PAGE_SGA = YES into your init.ora. The documentation suggests there may be a slight performance degradation with connect times for this though.
Running the server as DEDICATED I'm also seeing the usual additional 4Mb per connection over and above the initial SGA allocation.
How much of your total ram you give to Oracle is another matter. It will also be very much OS dependant, and appears (from my reading so far) to be a bit of guesswork according to your instance, the OS, what other instances are about etc.
I would try to avoid getting any of Oracle's ram being paged by the OS, as Oracle's paging to tempspace is more efficient (Oracle is better placed to decide what to page). Unfortunately, under NT it is not possible to avoid this. Again this will be OS dependant.