Showing results for 
Search instead for 
Did you mean: 

Total SGA size

Total SGA size

Once there was a description about calculating 32-bit Oracle maximum single/total SGA's size on 64-bit node. Maybe someone have document describing calculation of SGA's with 64-bit Oracle8i on HPUX11.0 64-bit node and can share it.

Andreas D. Skjervold
Honored Contributor

Re: Total SGA size

Excerpt from Metalink:

The maximum SGA size for 32-bit Oracle on 32- and 64-bit HP-UX 11.0 is approximately 1.75 GB. This requires two separate shared memory segments.
The maximum for the system-wide shared memory is also limited to 1.75 GB, unless you are using the the Memory Windows feature in Oracle 8.0.6 or later.

The maximum amount of system wide shared memory for 64-bit HP-UX 11.0 running 64-bit applications is limited by the amount of physical memory
on your system.

Memory Windows for HP-UX 11.0

Memory Windows is a feature of 32- and 64-bit HP-UX 11.0 that allows independent processes to use more physical memory.

On 32-bit HP-UX 11.0, there is both a process limit and a system-wide limit of 1.75GB total shared memory. For example, if one Oracle instances uses up 1.25 GB of shared memory, another completely independent instance can use only (1.75 GB - 1.25 GB) = .5 GB worth of shared memory.

On HP-UX 11.0 64-bit you can run 32-bit Oracle or 64-bit Oracle.
If you run 64-bit Oracle on HP-UX 11.0 64-bit, the memory limit for the SGA is about 4 million terabytes or whatever RAM you have on your system.


How does Oracle work with Memory Windows?

Oracle must be running on an HP system that has Memory Windows enabled.
Once Memory Windows is enabled, you need to increase the number of Memory Windows on the machine by tuning the HP-UX kernel parameter "max_mem_window" to a value at least
as large as the number of windows you'll eventually need.

Once the HP and Oracle Memory Windows features are installed,
the administrator make entries in the "/etc/services.window" file.
This file maps the Oracle instance IDs to Window keys, the same way the
"/etc/services" file maps service names to port numbers. The system
automatically reads the "/etc/services.window" file to start the Oracle
processes in the correct Window.

Implementing Oracle with Memory Windows

The procedure is simple. For every Oracle instance, place its ORACLE_SID
name in the first column of the "/etc/services.window" file and the Window
key in the second column. For example, if you are running two Oracle
instances, one named "sales" and the other named "dev", this is how
your /etc/services.window file might look:

sales 5
dev 6

The oracle binary uses the "$ORACLE_SID" environment variable plus the
"/etc/services.window" entry to determine which Window an Oracle process
needs to be running in.

You can use any Window key except for 0, which the system reserves
for the "global window". If you accidentally use the Window key 0,
the oracle instance will start up, but it will not be executing in
its own Window, and as a result you will not see any benefits of
Memory Windows.

Limitations of Oracle with Memory Windows

Memory Windows provides Oracle instances with more memory. However,
there is a limitation. Processes running in one Window cannot share
memory or memory-mapped files with processes running in another Window.
This is not an issue with Oracle itself, because distinct instances
do not communicate via shared objects. However, it is possible that
a custom or third-party tool monitors Oracle by looking at its shared
memory segments. If this is the case, the tool also has to be mapped
to a Memory Window.

Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Honored Contributor

Re: Total SGA size

Hello Arturas,

Andreas is right on the point!
But if you are looking for an easy "recipe" to calculate your SGAs, well:
- do not use any system having less tha 1GB of RAM
- do not use more than 300-400MB of UNIX buffer cache
- do use Advanced VxFS with the proper mount options to avoid double-buffering
- do not use RAW-devices until you are very well-trained in Oracle-administration
- allocate more than 100MB of "shared_pool"
- do not use "db_block_size" smaller than 8192bytes
- do use "sort_area" and "retained_sort_area" bigger than 4MB, each
- use a bigger "log_buffer_size"
- use different channels for datafiles, on-line redo-logfiles, and off-line redo-logfiles, when possible
and the most important:
- use as many "db_block_buffers" as possible, whithout running into pageing/swapping!