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 connection?
A.
User session data
B.
Stack space
C.
Private SQL area
D.
Location of the runtime area for DML and DDL Statements
E.
Location of a part of the runtime area for SELECT statements
Explanation:
A: PGA itself is subdivided. The UGA (User Global Area) contains session state information,
including stuff like package-level variables, cursor state, etc. Note that, with shared server, the UGA is in
the SGA. It has to be, because shared server means that the session state needs to be accessible to all
server processes, as any one of them could be assigned a particular session. However, with dedicated
server (which likely what you’re using), the UGA is allocated in the PGA.
C: The Location of a private SQL area depends on the type of connection established for a session. If a
session is connected through a dedicated server, private SQL areas are located in the server process’
PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in
the SGA.
Note:
* System global area (SGA)
The SGA is a group of shared memory structures, known as SGA components, that contain data and
control information for one Oracle Database instance. The SGA is shared by all server and background
processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
* Program global area (PGA)
A PGA is a memory region that contains data and control information for a server process. It is nonshared
memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to
the server process. There is one PGA for each server process. Background processes also allocate their
own PGAs. The total memory used by all individual PGAs is known as the total instance PGA memory, and
the collection of individual PGAs is referred to as the total instance PGA, or just instance PGA. You use
database initialization parameters to set the size of the instance PGA, not individual PGAs.
Oracle Database Concepts 12c