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.
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)- A70% (19)
- B19% (5)
- D4% (1)
- E7% (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.
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.
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.
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.
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.
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
Community Discussion
No community discussion yet for this question.