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.