PGA Study:
Every time when we talk about memory of a Oracle DB the emphasis is more to the SGA than the PGA. Last month when I was asked to look after an issue for an application which is exhausting the server memory due to high consumption of the PGA, then i realised that I lack the basic details of PGA and assured myself with a detailed study about the same. In this blog am hoping to cover the need for PGA, Sizing of PGA and performance impact due to improper PGA sizing.
What is PGA?
From documentation, A PGA is a memory region that contains data and control information for a server process. It is non-shared 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.
An analogy for a PGA is a temporary countertop workspace used by a file clerk. 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.
What are contents of PGA?
1. Session memory(UGA):
* Holds session variables, Logon Information. In Shared server this is stored in SGA.
2. Private SQL area:
* Holds bind variable values, query state information & query execution work areas.PGA while using dedicated & SGA while using shared.
* A cursor is a name or handle to a specific private SQL area. 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.
* The client process is responsible for managing private SQL areas. The allocation and deallocation of private SQL areas depends largely on the application, although the number of private SQL areas that a client process can allocate is limited by the initialization parameter OPEN_CURSORS.
* Sub-divided to
a. Run-time 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. The run-time area is freed when the SQL statement is closed.
b. Persistent 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.
3. SQL Work area:
* Work area is a private allocation of PGA memory used for memory-intensive operations. Few ex: Sort area for a sort operation, hash area for hash join to build hash table, bitmap merge area for bitmap merge.
Documentation Reference
Enough of long texts now we ll jump in to few queries for a better understanding.
From sesstat or mystat we can view what is the Pga and Uga allocated for a session.
Here below, we see the PGA stats for my current session.
In next post, Ill discuss about few parameters which determines the size of the PGA allocation to a DB.
Every time when we talk about memory of a Oracle DB the emphasis is more to the SGA than the PGA. Last month when I was asked to look after an issue for an application which is exhausting the server memory due to high consumption of the PGA, then i realised that I lack the basic details of PGA and assured myself with a detailed study about the same. In this blog am hoping to cover the need for PGA, Sizing of PGA and performance impact due to improper PGA sizing.
What is PGA?
From documentation, A PGA is a memory region that contains data and control information for a server process. It is non-shared 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.
An analogy for a PGA is a temporary countertop workspace used by a file clerk. 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.
What are contents of PGA?
1. Session memory(UGA):
* Holds session variables, Logon Information. In Shared server this is stored in SGA.
2. Private SQL area:
* Holds bind variable values, query state information & query execution work areas.PGA while using dedicated & SGA while using shared.
* A cursor is a name or handle to a specific private SQL area. 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.
* The client process is responsible for managing private SQL areas. The allocation and deallocation of private SQL areas depends largely on the application, although the number of private SQL areas that a client process can allocate is limited by the initialization parameter OPEN_CURSORS.
* Sub-divided to
a. Run-time 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. The run-time area is freed when the SQL statement is closed.
b. Persistent 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.
3. SQL Work area:
* Work area is a private allocation of PGA memory used for memory-intensive operations. Few ex: Sort area for a sort operation, hash area for hash join to build hash table, bitmap merge area for bitmap merge.
Documentation Reference
Enough of long texts now we ll jump in to few queries for a better understanding.
From sesstat or mystat we can view what is the Pga and Uga allocated for a session.
Here below, we see the PGA stats for my current session.
SQL> select sid||'--'||name||'--'||value from v$mystat natural join v$statname where name like '%memory%' and sid=sys_context('USERENV','SID');
SID||'--'||NAME||'--'||VALUE
--------------------------------------------------------------------------------
130--session uga memory--1302772
130--session uga memory max--1302772
130--session pga memory--1970404
130--session pga memory max--1970404
130--redo k-bytes read (memory)--0
130--redo k-bytes read (memory) by LNS--0
130--workarea memory allocated--1204
130--sorts (memory)--0
Now we ll see the allocation at the process level.
SQL> select s.sid,p.pid from v$session s,v$process p where s.paddr=p.addr and sid=sys_context('USERENV','SID');
SID PID
---------- ----------
130 22
SQL> select pid,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem from v$process where pid=22;
PID PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------ ------------- ---------------- -----------
22 660114 1280022 0 2263062
SQL> select * from v$process_memory where pid=22; PID SERIAL# CATEGORY ALLOCATED USED MAX_ALLOCATED ---------- ---------- --------------- ---------- ---------- ------------- 22 13 SQL 46760 11856 1262732 22 13 PL/SQL 2024 136 2024 22 13 Other 1231238 1231238 SQL> select sum(allocated),sum(max_allocated) from v$process_memory where pid=22; SUM(ALLOCATED) SUM(MAX_ALLOCATED) -------------- ------------------ 1280022 2487838We can see from v$process & v$process_memory and equate sum of individual allocated memory with the PGA_ALLOC_MEM from v$process, though we see a slight difference in the max allocated. Below view gives us an overview at the instance level.
SQL> select * from v$pgastat; NAME VALUE UNIT -------------------------------------------------- ---------- ------------ aggregate PGA target parameter 306184192 bytes aggregate PGA auto target 254923776 bytes global memory bound 61236224 bytes total PGA inuse 22934528 bytes total PGA allocated 29167616 bytes maximum PGA allocated 52337664 bytes total freeable PGA memory 0 bytes process count 26 max processes count 32 PGA memory freed back to OS 0 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 1341440 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes processed 53265408 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent recompute count (total) 1428Documentation Reference
In next post, Ill discuss about few parameters which determines the size of the PGA allocation to a DB.
No comments:
Post a Comment