nerdexam
Oracle

1Z0-060 · Question #204

An Automatic Database Diagnostic Monitor (ADDM) finding in your production database reports that the shared pool is inadequately sized. You diagnose that this is due to the different kinds of…

The correct answer is C. setting the SGA_TARGET parameter to 300M. Enabling Automatic Shared Memory Management by setting SGA_TARGET allows Oracle to dynamically redistribute memory between SGA components such as the shared pool and buffer cache based on actual workload demand. This directly addresses peak-hour shared pool pressure without…

New Features for Performance

Question

An Automatic Database Diagnostic Monitor (ADDM) finding in your production database reports that the shared pool is inadequately sized. You diagnose that this is due to the different kinds of workloads and this occurs only during peak hours. The following are the parameter settings for the database instance:

You want to balance the memory between the System Global Area (SGA) components depending on the workload. Which option would solve this problem?

Exhibit

1Z0-060 question #204 exhibit

Options

  • Asetting the PGA_AGGREGATE_TARGET parameter to 200M and the SGA_MAX_SIZE
  • Bsetting the MEMORY_TARGET and SGA_MAX_SIZE parameters to 400M
  • Csetting the SGA_TARGET parameter to 300M
  • Dsetting the SGA_MAX_SIZE parameter to 400M

How the community answered

(34 responses)
  • A
    3% (1)
  • B
    6% (2)
  • C
    79% (27)
  • D
    12% (4)

Why each option

Enabling Automatic Shared Memory Management by setting SGA_TARGET allows Oracle to dynamically redistribute memory between SGA components such as the shared pool and buffer cache based on actual workload demand. This directly addresses peak-hour shared pool pressure without manual intervention.

Asetting the PGA_AGGREGATE_TARGET parameter to 200M and the SGA_MAX_SIZE

PGA_AGGREGATE_TARGET governs Program Global Area memory allocated to server processes for sort and hash operations, not SGA component distribution; pairing it with SGA_MAX_SIZE does nothing to enable dynamic rebalancing within the SGA.

Bsetting the MEMORY_TARGET and SGA_MAX_SIZE parameters to 400M

MEMORY_TARGET enables Automatic Memory Management for the combined SGA and PGA pool but requires MEMORY_MAX_TARGET to be configured first, and combining it with SGA_MAX_SIZE can create parameter conflicts; it also does not specifically target SGA component rebalancing.

Csetting the SGA_TARGET parameter to 300MCorrect

Setting SGA_TARGET to a non-zero value (300M) activates Automatic Shared Memory Management, which enables Oracle to auto-tune the sizes of the shared pool, buffer cache, large pool, Java pool, and streams pool dynamically. When peak-hour workloads increase demand on the shared pool, Oracle automatically reallocates memory from less-pressured components, resolving the ADDM-reported inadequacy.

Dsetting the SGA_MAX_SIZE parameter to 400M

SGA_MAX_SIZE defines only the upper ceiling of total SGA allocation and does not activate any automatic resizing mechanism; without SGA_TARGET being set, individual component sizes remain statically determined by their individual parameters.

Concept tested: Automatic Shared Memory Management with SGA_TARGET

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-memory.html

Topics

#SGA_TARGET#ASMM#shared pool sizing#ADDM findings

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-060 Practice