Understanding the functions and composition of PGA (1) memory

When the client sends a connection request to the server, the server listens to the client's request. In dedicated server mode, a server process is derived from the server to represent the client's request, server process then initiates a connection to the instance to create a session, and PGA is allocated and used by server process.

 

PGA, this P, or the program translated, or converted to private, with different angles. Generally, we call it "the global area of the program". Its creation period:

 

1) Why is it allocated when server process is started or created? Without him, only PGA and server process are "two grasshoppers on one rope". Together, suffering a total.
2) during system operation, operations such as sort and hash join may also require further PGA allocation.

 

PGA exclusive access to server process, so there is no latch contention. It contains the data and control information of server process. You can use the C language to call malloc () for local allocation and Dynamic Scaling. PGA is private rather than shared. This mechanism is necessary because these resources can be cleared and released when the process dies.

 

Oracle Applications or users' applications may display or implicitly open cursor during execution. When you open cursor, You need to allocate an SQL area. When you open cursor, the private SQL area is allocated, in fact, the main activity of the database is the cursor activity. Simply put, the execution of user processes and the use of cursor are the main consumers of PGA.

 

The most sensitive contribution of user experience lies in PGA. The user sends a request that is executed in PGA. If hit in PGA, there is no need for soft parsing or hard parsing. This is called soft parsing. The cursor in PGA points to the data row in the buffer cache. A batch is returned, not one.

 

PGA can be fixed and variable PGA. The variable PGA is a memory heap, and the variable region of PGA is actually the most concerned PGA part. You can use x $ ksmpp to query the allocation and usage of a variable PGA. It mainly consists:
A) Session memory: used to store session login information and other related information
B) Private SQL Zone
B1) permanent zone: Includes Bound variables. The memory is released only when cursor is disabled.
B2) SQL Work Area: including the sort and hash areas. For DML transactions, the region is released after SQL Execution is completed. For select transactions, the region is released after the record is returned or when the query is canceled.

C) cursor and SQL region: the cursor here is a piece of memory, which is not a common "pointer"

 

The SQL workspace in the private SQL area is too small, which may cause disk I/O. To balance the memory and actual space allocated for SQL Execution, You have to convert the job to a temporary tablespace. Therefore, oracle divides the obtained SQL workspace by size:
A) optimal size: the SQL workspace can fully meet the memory required for SQL Execution.
B) one-pass size: perform an I/O operation with the temporary tablespace.
C) multi-pass size: perform multiple I/O operations with the temporary tablespace
When workload is not large, Oracle tends to allocate an optimal size SQL workspace for each user session.

 

UGA is your session status. Your session can always access this part of memory. The location of UGA depends on how you connect to Oracle. For dedicated server connection, UGA is created in PGA; for shared server connection, UGA is created in SGA. Therefore, PGA contains the process memory and Uga.