New Features for Administrators - Question 005

The Dynamic SGA feature allows the SGA to grow and shrink dynamically according to an ALTER SYSTEM command.

This avoids the previous need of shutting down the instance in order to modify the components of the SGA, namely the buffer cache and shared pool components. Which three statements are true for the Dynamic SGA feature? (Choose three.)

A. The maximum granule size is 4 MB.

B. The minimum SGA configuration is three granules.

C. SGA memory is based on granules by SGA components.

D. The size of the SGA components is set by the SGA_MAX_SIZE parameter.

E. The size of the SGA components is limited by the setting of SGA_MAX_SIZE parameter.

Answer: B, C, E

Explanation:

Oracle9i has enhanced the nature of SGA parameters; they are now dynamic. You can change the values of the shared pool and the buffer cache without restarting the database instance. The Oracle9i dynamic SGA concept enables you to take memory from one area of the SGA and allocate it to another area as needed while the database instance is up and running. Additionally, the unit of memory allocation for SGA is a granule in Oracle9i. At a minimum, one granule allocated for the shared pool, one for the buffer cache, and one for the redo log buffer. You can confirm a total amount of granules by checking the value of the column LO_SETID in the V$BUFFER_POOLS view. Oracle9i also introduces SGA_MAX_SIZE, a new static parameter that enables the DBA to start with a smaller SGA and dynamically increase it to the maximum value specified by SGA_MAX_SIZE.

If you do not set SGA_MAX_SIZE or if you set it to a value less than initial SGA size, you cannot increase the SGA size later.

Incorrect Answers

A: The size of a granule is 4 MB if the SGA at startup is less than 128 MB; it will be 16 MB otherwise.

D: The size of the SGA components is not set by the SGA_MAX_SIZE parameter. They are only limited by SGA_MAX_SIZE value. As a DBA, you can dynamically change the value of the parameters DB_CACHE_SIZE, SHARED_POOL_SIZE, and LARGE_POOL_SIZE up to the size of SGA_MAX_SIZE.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 180-182

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 16-17

Chapter 1: Oracle9i Database Administration and Management Features