You work for ABC Pvt Ltd. The company has recently upgraded one of its development databases to Oracle Database 11g from Oracle Database 10g. You noticed that the System Global Area (SGA) is undersized as shown in the Exhibit.
To investigate further, you checked the related parameters as shown below:
SQL> show parameter sga_max_size
NAME TYPE VALUE
—————————— ————— —————–
sga_max_size big integer 500M
SQL> show parameter target
NAME TYPE VALUE
—————————— ————— ——————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 384M
sga_target big integer 384M
Which is the best solution that you would recommend?
A.
Increase the value of the SGA_MAX_SIZE parameter.
B.
Set MEMORY_MAX_TARGET and MEMORY_TARGET parameters.
C.
Reduce the value of the PGA_AGGREGATE_TARGET parameter.
D.
Increase the value of both SGA_TARGET and PGA_AGGREGATE_TARGET parameters.
E.
Diagnose further to identify which component in SGA is undersized and then resize it.
why not A?
“Why not A”?
A will not actually do anything. Setting SGA_MAX_SIZE only sets the maximum value that SGA_TARGET can be set to. If MEMORY_TARGET is not enabled (as above) then SGA_TARGET would have to be increased manually by the DBA (can be done as online operation).
Remembering that this is an Oracle exam (who emphasise using AMM) and considering that they have not given information about how much memory is physically on the box – we can see that there is at least 768MB (SGA_TARGET+PGA_AGGREGATE_TARGET). There may not be enough memory available to physically increase SGA_TARGET. However, if we set MEMORY_TARGET to 768MB, then Oracle will attempt to balance memory usage between SGA and PGA. Based on this, Oracle could allocate to SGA_TARGET up to the size of SGA_MAX_SIZE – which could solve the issue.
C will not do anything, as it only deallocates space from PGA. There is nothing automatic to allocate extra space to SGA and the answer does not specify you will manually add space there either.
D is not correct, as you have no indication as to whether this is a valid operation. If there is not enough physical memory on the box, then this operation will fail. Further, there is no reason to increase PGA at all.
E is incorrect as you have SGA_TARGET enabled. Oracle is already dynamically resizing SGA components as they needs it.
Sorry, slight typo
“However, if we set MEMORY_TARGET to 768MB” – we need to set memory_target to more than 768MB. Oracle will take the values of SGA_TARGET and PGA_AGGREGATE_TARGET as minimums for each memory area. Anything we allocate in excess of 768MB to MEMORY_TARGET can be allocated to SGA.
Answer still stands though