Category - TUNING

The PGA,Background Process,Server Process & Client Process

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.

Description of Figure 14-3 follows

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.

Description of Figure 14-4 follows

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.

Description of Figure 14-5 follows

 

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).

Description of Figure 15-1 follows

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.