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 particularsession. 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 asSGA 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.
Reference: Oracle Database Concepts 12c
A, C – TRUE
The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.
http://docs.oracle.com/cd/E11882_01/server.112/e40540/memory.htm#CNCPT89067
AC
A and C
Take the exam today, the option C changed, it not ‘Private SQL area’ any more
What was the anserw so? In 2 hours I’ll be taking my exam…
Please let me know about new quest…i’ll take my exam 12 decemb
hey james let us know if the questions here are on it? and if this version 2 is all the exam? which do you guys study for>?
E is correct as per http://www.dba-oracle.com/oracle_news/2005_12_26_pga_memory_explained.htm
AE – OK
A and C are correct,
PGA Usage in Dedicated and Shared Server Modes
============================================================================
Nature of session memory is Private in Dedicated, Shared in Shared server,
Location of the persistent area is Private in Dedicated, Shared in Shared Server,
Location of the run-time area for DML/DDL statements is Private in both dedicated and Shared server mode.
Private SQL area is private in Dedicated and Shared in Shared sever mode.
A and C (Maybe they change SQL Private Area for Persistent Area)
A private SQL area is divided into the following areas:
– The run-time area
– The persistent area
Session Memory and Persistent Area are located in the SGA for Shared Server
Oracle® Database Concepts 12c Release 1 (12.1)
AC
DE can be found in following doc. http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm#CNCPT1243
These stay in the PGA
C: but only if it should be written as persistent area
A:UGA in SGA
https://docs.oracle.com/cd/E11882_01/server.112/e40540/memory.htm#CNCPT803
The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.
AC
The PGA is a memory heap that contains session-dependent variables required by a dedicated or shared server process. The server process allocates memory structures that it requires in the PGA. Therefore, Choice A is correct.
A private SQL area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.
A private SQL area is divided into the following areas:
(i) The run-time area
This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.
Oracle Database creates the run-time area as the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.
(ii) The persistent area
This area contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.
Answers are: A, C
AC