You are hired by ABC Pvt Ltd., and assigned the task of managing one of its development
databases. The company has recently upgraded this database to Oracle Database 11g from
Oracle Database 10g.
You noticed that SGA is undersized as shown in Exhibit 1.
To investigate further, you checked the related parameters as shown in Exhibit 2.
You executed the following command to solve the problem:
ALTER SYSTEM SET MEMORY_TARGET=800M;
What would happen?
A.
The implementation of AMM fails because the MEMORY_MAX_TARGET parameter is not set.
B.
It implements AMM by setting MEMORY_TARGET and MEMORY_MAX_TARGET parameters
to 800M.
C.
It implements Automatic Memory Management (AMM) by setting MEMORY_TARGET to 768M
and the MEMORY_MAX_TARGET parameter to 800M.
D.
The implementation of AMM fails because the MEMORY_TARGET parameter cannot be set to
a value less than the sum of the current PGA_AGGRAGATE_TARGET and SGA_MAX_SIZE
values.
B
“About Automatic Memory Management
The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).”
Mateo is wrong. The correct answer is A:
In this case the command will fail with
“ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET”
Because the MEMORY_TARGET initialization parameter is dynamic, you can change MEMORY_TARGET at any time without restarting the database. MEMORY_MAX_TARGET, which is not dynamic, serves as an upper limit so that you cannot accidentally set MEMORY_TARGET too high, and so that enough memory is set aside for the database instance in case you do want to increase total instance memory in the future. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting MEMORY_TARGET too low.
You can’t use “ALTER SYSTEM” with parameter MEMORY_TARGET if MEMORY_MAX_TARGET isn’t set.
You only can set MEMORY_TARGET in spfile than start the instance, so oracle automatically set MEMORY_MAX_TARGET with the same value of MEMORY_TARGET.
A is right. you have to set MEMORY_MAX_SIZE before setting MEMORY_TARGET