Program Global Area (PGA)
=> The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system.
=> Because the PGA is process-specific, it is never allocated in the SGA.
=> 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.
=> Background processes also allocate their own PGAs.
The following figure shows an instance PGA (collection of all PGAs) for an instance that is not configured for shared servers.
Contents of the PGA
The PGA is subdivided into different areas, each with a different purpose.
The following figure shows the possible contents of the PGA for a dedicated server session.
cursor
=> A cursor is a name or handle to a specific private SQL area.
=> As shown in the following graphic, you can think of a cursor as a pointer on the client side and as a state on the server side.
=> Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.
Demo for PGA Memory Allocations and Server , Client & Bankground process
Let we connect with SYS user and check details for Background process and server processes
SQL> @pga_uages.sql
PNAME USERNAME OSUSER SID SERIAL# SPID PGA_USED_MEM_MB PGA_ALLOC_MEM_MB PGA_FREEABLE_MEM_MB PGA_MAX_MEM_MB STATUS SERVICE_NAME LAST_CALL_ET_SECS
----- ---------- -------- ---------- ---------- ---------- --------------- ---------------- ------------------- -------------- -------- --------------- -----------------
OFSD SYS oracle 13 58398 24351 2.85 3.52 0.25 3.52 ACTIVE SYS$BACKGROUND 690149
SYS oracle 100 10758 20008 4.11 4.35 0.00 4.35 ACTIVE SYS$USERS 0
SYSRAC grid 72 2660 24917 2.30 3.41 0.75 3.79 INACTIVE SYS$USERS 690092
VKTM (oracle) oracle 5 63192 24335 1.31 1.46 0.00 1.46 ACTIVE SYS$BACKGROUND 690152
GEN0 (oracle) oracle 6 28904 24340 1.34 1.46 0.00 1.46 ACTIVE SYS$BACKGROUND 690149
MARK (oracle) oracle 7 61086 24402 2.45 2.65 0.00 2.65 ACTIVE SYS$BACKGROUND 690148
MMAN (oracle) oracle 8 56785 24344 1.31 1.46 0.00 1.46 ACTIVE SYS$BACKGROUND 690149
GEN1 (oracle) oracle 10 45606 24346 1.97 3.59 1.06 3.59 ACTIVE SYS$BACKGROUND 690149
SCMN (oracle) oracle 9 30898 24346 1.18 1.28 0.00 1.28 ACTIVE SYS$BACKGROUND 690149
Types of Processes
A database instance contains or interacts with multiple processes.
Processes are divided into the following types:
=> A client process runs the application or Oracle tool code.
=>An Oracle process is a unit of execution that runs the Oracle database code. Oracle processes include the following subtypes:
A background process starts with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.
A server process performs work based on a client request.
For example, these processes parse SQL queries, place them in the shared pool, create and execute a query plan for each query, and read buffers from the database buffer cache or from disk.
Server processes, and the process memory allocated in these processes, run in the database instance. The instance continues to function when server processes terminate.
A slave process performs additional tasks for a background or server process.
Below figure shows a system global area (SGA) and background processes using dedicated server connections.
For each user connection, a client process runs the application. This client process that is different from the dedicated server process that runs the database code.
Each client process is associated with its own server process, which has its own program global area (PGA).
Overview of Client/Server Architecture
In the Oracle Database environment, the database application and the database are separated into a client/server architecture.
The components are as follows:
The client runs the database application, for example, SQL*Plus or a Visual Basic data entry program, that accesses database information and interacts with a user.
The server runs the Oracle Database software and handles the functions required for concurrent, shared data access to an Oracle database.
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444