Pages

Tuesday, November 15, 2011

Oracle SGA_TARGET Parameter


SGA_TARGET

The SGA_TARGET parameter determines whether our database will use automatic shared memory management. In order to use automatic shared memory management, set the SGA_TARGET parameter to a positive value. We don’t have to specify the five automatic shared memory components in our initialization file (shared pool, database buffer cache, Java pool, large pool, and streams pool). Oracle will show zero values for these when we query the V$PARAMETER view, which shows the values of all our initialization parameters. We may, however, choose minimum values for any of the five auto-memory parameters, in which case we should specify the values in the initialization file.
If we set the SGA_TARGET parameter to zero, we disable automatic shared memory management, and we have to manually set the values for all the previously mentioned SGA components. When we use automatic shared memory management by setting a value for the SGA_TARGET parameter, the memory we allocate for the manually sized components (the log buffer, the buffer cache keep pool, the buffer cache recycle pool, the nondefault-sized buffer cache pools and nKBsized buffer caches, and the fixed SGA allocations) will be deducted from the SGA_TARGET value first.
To get a quick idea about how much memory to allocate for the SGA_TARGET parameter under automatic shared memory management, run the following query:

SQL> SELECT (
(SELECT SUM(value) FROM V$SGA) -
(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
) "SGA_TARGET"
FROM DUAL;

The value for SGA_TARGET must be a minimum of 64MB, and the upper limit is dependent on
the operating system.

Default value: 0 (no automatic shared memory management)
Parameter type: Dynamic. You can use the ALTER SYSTEM command to modify the value.

Note If we set the SGA_TARGET parameter, we can leave out all the automatic SGA parametersDB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE—from our init.ora file, unless we want to ensure a minimum size for some of these, in which case we can explicitly specify them in the file.


No comments:

Post a Comment