Memory Structures - Expert Oracle Database Architecture - page 166

■ In Oracle9 i and before, only manual shared memory management was available—the parameter SGA_TARGET
did not exist and the parameter SGA_MAX_SIZE specified the maximum size of the SGa.

Note

When automatic memory management is 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 have 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.
Here's an example of an initialization file that enables manual shared memory management:

*.compatible='12.1.0.1'
*.control_files='/u01/dbfile/ORA12CR1/control01.ctl','/u02/dbfile/ORA12CR1/control02.ctl'
*.db_block_size=8192
*.db_name='ORA12CR1'
*.memory_target=0
*.sga_target=0
*.db_cache_size=1G
*.shared_pool_size=256M
*.pga_aggregate_target=256m
*.open_cursors=300
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.undo_tablespace='UNDOTBS1'

One aspect to be aware of with manual shared memory management in Oracle 11g Release 2 and higher, is that
even when you explicitly turn off all automatic memory management (by setting MEMORY_TARGET and SGA_TARGET to
zero), Oracle may still do some automatic reallocation of memory from the database buffer cache to the shared pool.
When running out of space in the shared pool, Oracle will automatically add space to the shared pool to avoid the
ORA-04031 “ Unable to allocate %s bytes of shared memory ” error.
You can view the automatic resizing of SGA memory by querying the V$MEMORY_RESIZE_OPS view. The OPER_MODE
column will contain the value of either DEFERRED or IMMEDIATE for any automatic SGA resizing operations. When using
manual SGA memory management, SGA automatic resizing is disabled for DEFERRED mode requests, but allowed for
IMMEDIATE mode requests. Therefore, when you're using manual shared memory management, you may see GROW and
SHRINK operations (in the OPER_TYPE column) for IMMEDIATE auto-tuning requests for the DB_CACHE_SIZE and SHARED_
POOL size memory areas.
A simple example will demonstrate what's described in the prior paragraphs. First, I'll create the DBMS_SHARED_
POOL package so that I can pin objects in the shared pool:

SYS@ORA12CR1> @?/rdbms/admin/dbmspool
Session altered.
Package created.
Grant succeeded.
Session altered.

SYS@ORA12CR1> grant execute on dbms_shared_pool to eoda;
Grant succeeded.