Manual shared memory management should be rarely used; therefore, I’m not going to discuss this technique in detail.
This is mainly because it’s easier to use one of the previously described methods where Oracle manages the SGA memory for you.
However, if you require some degree of control over the auto-tunable areas of the SGA memory, then set MEMORY_TARGET and SGA_TARGET to zero.
When MEMORY_TARGET is set to zero, this disables AMM; when SGA_TARGET is set to zero, this disables ASMM.
When the automatic memory management techniques are disabled, you can manually set the size of your SGA by specifying sizes for the following memory parameters: DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE. Each of these parameters has a default value that Oracle will use in the event you don’t explicitly set them.
For example, the DB_CACHE_SIZE will be set to either 48MB or 4MB times the number of CPUs, whichever happens to be the largest for your system.
Be aware of the possibility that Oracle might perform some degree of automatic SGA memory reallocation, even when you’ve turned the feature off. Oracle does this to keep processes from running out of space in the shared pool.
Tip See Oracle Support note 1269139.1 for further details on automatic SGA resizing when using manual shared memory management.
Summary
In this chapter, we took a look at the Oracle memory structures. We started at the process and session level, examining the PGA and UGA and their relationship. We saw how the mode in which we connect to Oracle dictates how memory is organized. A dedicated server connection implies more memory used in the server process than under a shared server connection, but that use of a shared server connection implies there will be the need for a significantly larger SGA.
Then we discussed the main structures of the SGA itself. We discovered the differences between the shared pool and the large pool and looked at why we might want a large pool to “save” our shared pool. We covered the Java pool and how it is used under various conditions, and we looked at the block buffer cache and how that can be subdivided into smaller, more focused pools.
Now we are ready to move on to the physical processes that make up the rest of an Oracle instance.