- 19 Aug, 2024
- 0 Comments
- 3 Mins Read
Overview of the Program Global Area (PGA) and User Global Area in oracle
Program Global Area (PGA)
A PGA, which is the abbreviation of Program Global Area, or a Private Global Area.
It is a memory area that contains a couple of sub-areas in it. Unlike the SGAs, PGAs are private for each user. So any other user cannot see or touch that memory area.
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.An analogy for a PGA is a temporary countertop workspace used by a file clerk. In this analogy, the file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done.The following figure shows an instance PGA (collection of all PGAs) for an instance that is not configured for shared servers. You can use an initialization parameter to set a target maximum size of the instance PGA. Individual PGAs can grow as needed up to this target size.
Session Memory Area:
- The first one is the Session Memory Area. When a user connects to the database then the database creates a session for that user.
- This session information is stored in this area.
- Here it basically stores the session’s variables, login information, session status etc. So, if you open unnecessary connections, you should know that they all have some memory in the database.
- It is extremely important when you use another language like Java or .net etc. Because sometimes you forget to close the connections and it causes some problems.
Private SQL Area:
It is divided into two sub-areas.
I)Persistent area:
- The persistent area contains the bind variable values of the cursors.
- In Oracle SQL support each query is turned to a cursor in the database.
- So the bind variables used in these cursors are stored here and released when the cursor is closed. The other private SQL area component is the run time area.
- Either you create it as an explicit cursor or implicit cursor.
II) Run time Area:
- It stores the execution state information. which contains the information about did we start reading the tables or how much percentage we read till now or did we finish reading from the tables.
- The next memory area in a PGA is the cursor area.
Cursor Area:
- This area stores the information of the cursors.
SQL Work Area:
- This area is used to operate the data returned from the discs.
- This area does these kinds of operations. As We can see it is divided into 4 sub-areas.
- The data read from the discs is sorted because of the order by, group by, roll-up or window commands of our queries.
- the data read from the discs is sorted because of the order by, group by, roll-up or window commands of your queries. We can easily understand what the next ones do by their names.
PGA Usage in Dedicated and Shared Server Modes
PGA memory allocation depends on whether the database uses dedicated or shared server connections.
User Global Area
- The User Global Area (UGA) is a memory region in Oracle that holds session-specific information. Unlike the Program Global Area (PGA), the UGA can reside in different memory locations depending on the server process configuration. For dedicated server processes, the UGA is part of the PGA. For shared server processes, the UGA is located in the System Global Area (SGA).
- Stores session-specific variables and state information for the user’s session.
- Holds data related to sorting operations specific to the session.
- Contains information required during the execution of PL/SQL blocks.
- Manages memory for session-related activities.
- The UGA is a part of the PGA. Each dedicated server process has its own PGA, which includes the UGA.
- The UGA resides in the SGA. This allows multiple user sessions to share the same memory structures, which is essential for the shared server architecture.