nerdexam
Oracle

1Z0-060 · Question #196

You configure your database Instance to support shared server connections. Which two memory areas that are part of PGA are stored in SGA instead, for shared server connections?

The correct answer is A. User session data C. Persistent area containing bind variable values that are supplied to a SQL statement at run time. In shared server mode, the UGA components that must persist across server process handoffs - including session data and the persistent cursor area holding bind variable values - are relocated from the PGA to the SGA.

New Features for Database Infrastructure and Architecture

Question

You configure your database Instance to support shared server connections. Which two memory areas that are part of PGA are stored in SGA instead, for shared server connections?

Options

  • AUser session data
  • BStack space
  • CPersistent area containing bind variable values that are supplied to a SQL statement at run time
  • DLocation of the runtime area for DML and DDL Statements
  • ELocation of a part of the runtime area for SELECT statements

How the community answered

(27 responses)
  • A
    70% (19)
  • B
    19% (5)
  • D
    4% (1)
  • E
    7% (2)

Why each option

In shared server mode, the UGA components that must persist across server process handoffs - including session data and the persistent cursor area holding bind variable values - are relocated from the PGA to the SGA.

AUser session dataCorrect

User session data (the User Global Area - UGA) must be accessible to whichever shared server process picks up the next client call; Oracle moves it to the SGA Large Pool (or Shared Pool if Large Pool is not configured) so all shared server processes can reach it between calls.

BStack space

Stack space is a per-call, process-local structure allocated on the OS stack of the currently executing server process; it is inherently tied to the active call and remains in the PGA of whichever shared server handles the call.

CPersistent area containing bind variable values that are supplied to a SQL statement at run timeCorrect

The persistent area of a cursor, which stores bind variable values for reuse across execute calls, is part of the UGA and must survive between client calls regardless of which shared server process handles them; in shared server mode it moves to the SGA alongside the rest of the UGA.

DLocation of the runtime area for DML and DDL Statements

The runtime area for DML and DDL statements is created for the duration of a single statement execution and is held in the PGA of the assigned shared server process; it is released when the call completes and does not need to be visible to other processes.

ELocation of a part of the runtime area for SELECT statements

Only the persistent UGA state of a SELECT statement (such as the cursor open indicator) resides in the SGA; the runtime area including row source execution state and fetch buffers is held in the PGA of the shared server processing the current fetch call and is not stored in the SGA.

Concept tested: Shared server UGA relocation from PGA to SGA

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/memory-architecture.html

Topics

#shared server#PGA#SGA#UGA memory

Community Discussion

No community discussion yet for this question.

Full 1Z0-060 Practice