Examine the following parameters for a database instance:
MEMORY_MAX_TARGET=0
MEMORY_TARGET=0
SGA_TARGET=0
PGA_AGGREGATE_TARGET=500m
Which three initialization parameters are not controlled by Automatic Shared Memory
Management (ASMM)?
A.
LOG_BUFFER
B.
SORT_AREA_SIZE
C.
JAVA_POOL_SIZE
D.
STREAMS_POOL_SIZE
E.
DB_16K_CACHE_SZIE
F.
DB_KEEP_CACHE_SIZE
Explanation:
Manually Sized SGA Components that Use SGA_TARGET Space
SGA Component, Initialization Parameter
/ The log buffer
LOG_BUFFER
/ The keep and recycle buffer caches
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
/ Nonstandard block size buffer caches
DB_nK_CACHE_SIZE
Note:
* In addition to setting SGA_TARGET to a nonzero value, you must set to zero all initialization
parameters listed in the table below to enable full automatic tuning of the automatically sized SGA
components.
* Table, Automatically Sized SGA Components and Corresponding Parameters
http://docs.oracle.com/cd/E16655_01/server.121/e17636/memory.htm#ADMIN13020
The answer is C,D,F
able 6-2 Automatically Sized SGA Components and Corresponding Parameters
SGA Component Initialization Parameter
Fixed SGA and other internal allocations needed by the Oracle Database instance
N/A
The shared pool
SHARED_POOL_SIZE
The large pool
LARGE_POOL_SIZE
The Java pool
JAVA_POOL_SIZE
The buffer cache
DB_CACHE_SIZE
The Streams pool
STREAMS_POOL_SIZE
The manually sized parameters listed in Table 6-3, if they are set, take their memory from SGA_TARGET, leaving what is available for the components listed in Table 6-2.
Table 6-3 Manually Sized SGA Components that Use SGA_TARGET Space
SGA Component Initialization Parameter
The log buffer
LOG_BUFFER
The keep and recycle buffer caches
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
Nonstandard block size buffer caches
DB_nK_CACHE_SIZE
okay whatever I mentioned above is controlled by ASMM when sga_target is non-zero value.
The question here is that they given SGA_TARGET=0 so automatic shared memory management is disabled. so it wont control any of these parameters to the given setting.
parameter A,E,F will not be controlled by ASMM at anytime.
so the question is to SGA_TARGET=0 or in general (any time ASMM non-controlled parameters)
The answer is A,E,F
Because:
The followin pools are manually sized componentes and are not affected by ASMM:
* Log Buffer
* Other buffer caches (such as KEEP and RECYCLE) and other block sizes.
* Fixed SGA and other internal allocations.
reference: Oracle Database 12c: Administration Workshop
The question is ambiguous. The answer may be AEF or BCD based on how one interprets the question. Probably some moron set the question!!!
AEF
A E and F
If we consider that ASMM is disabled, we have 5 answers to select (ACDEF), because only the sort area is in PGA. This does not correspond to the case.
AEF seems correct
AEF is final answer.
Table 6-2 Manually Sized SGA Components that Use SGA_TARGET Space
The log buffer
LOG_BUFFER
The keep and recycle buffer caches
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
Nonstandard block size buffer caches
DB_nK_CACHE_SIZE
Ref: http://docs.oracle.com/cd/E18283_01/server.112/e17120/memory004.htm
AEF
AEF
The Oracle Automatic Shared Memory Management is a feature that automatically readjusts the sizes of the main pools (db_cache_size, shared_pool_size, large_pool_size, java_pool_size) based on existing workloads.
You need to note that Automatic Shared Memory Management does not change the multiple block sizes and the KEEP pool. In an article titled Automatic Shared Memory Management we see:
Some pools in SGA are not subject to dynamic resizing, and must be specified explicitly. Notable among them are the buffer pools for nonstandard block sizes and the non-default ones for KEEP or RECYCLE. If your database has a block size of 8K, and you want to configure 2K, 4K, 16K, and 32K block-size pools, you must set them manually.
Their sizes will remain constant; they will not shrink or expand based on load. You should consider this factor when using multiple-size buffer, KEEP, and RECYCLE pools.
In addition, log buffer is not subject to the memory adjustment—the value set in the parameter log_buffer is constant, regardless of the workload. ( In 10g, a new type of pool can also be defined in the SGA: Streams pool, set with parameter streams_pool_size. This pool is also not subject to automatic memory tuning.)
Hence, the answers are: A, E, F
When using ASMM, configuration of the following buffers remains manual:
Log buffer
KEEP/RECYCLE buffer caches
Nonstandard block size buffer caches
AEF
AEF
All ABCDEF are correct.
Because setting those paramter disable ASMM.