Oracle's memory Management (one)

"Deep analysis Oracle-eygle" study notes

1. Memory Management

The memory used by the Oracle database involves two main areas: the PGA and the SGA.

1.1 PGA Management

The PGA refers to the program global Zone, which is the area of memory used by the server process to contain data and control information, the PGA is non-shared memory, and is allocated when the server process is started or created (when the system is running, sorting, The connection wind operation may also require further PGA allocation), and for the server process exclusive access, so the data structure in the PGA does not need to be protected by latch;

1.2 What is PGA

Process creation typically has two modes: Private server mode (dedicated server) and Shared server mode (Sharedserver). In private server mode, Oracle initiates an Oracle process for each session, whereas in Shared server mode, a certain number of server processes are typically started on the server side, and then the same Oracle service process is shared by multiple client requests.

The PGA can be divided into two zones from memory allocation and use:

(1) Fixed PGA - fixed PGA similar to fixed SGA, contains a large number of atomic variables, small data structures, and pointers to variable PGA, which are defined in the source code and allocated at compile time and can be considered as reserved memory of the PGA

(2) variable PGA (Variable PGA)-the variable PGA is implemented through a specific memory heap allocation, its spatial allocation and use can vary, through the internal View x$ksmpp ([K]ernel [S]ervice] [m]emory [P] GA hea[p]) can query the allocation and usage of variable PGA memory. The PGA's variable zone mainly contains session memory and private SQL area.

17:21:[email protected] Sql>select * from X$KSMPP;

ADDR inst_id ksmchcom ksmchptr ksmchsiz ksmchcls Ksmchtyp ksmchpar

---------------- ------------------------- ---------------- ---------- --------- -------------------------

00007FC3A71D91E0 1 Allocate KZTHSM 00007fc3a74e9700 FREEABL 0 00

00007fc3a71d9188 1 Kpuinit Env han 00007fc3a74e8f70 1936 freeabl 0 00

00007fc3a71d9130 1 Alloc environm 00007fc3a74e87a0 recr 4095 00007fc3a74e95c8

00007fc3a71d90d8 1 Alloc environm 00007fc3a74e77d0 4048 freeabl 0 00007fc3a74e95c8

00007fc3a71d9080 1 Permanent Memor 00007fc3a74e7780 Perm 0 00

00007fc3a71d9028 1 Permanent Memor 00007fc3a74e7730 Perm 0 00

00007fc3a71d8fd0 1 Alloc environm 00007fc3a74e97f0 8312 freeabl 0 00007fc3a74e95c8

00007fc3a71daf60 1 kzsna:login Nam 00007fc3a74e97b8 freeabl 0 00

00007fc3a71daf08 1 kgh Stack 00007fc3a74eb8a8 17024 freeabl 0 00

00007fc3a71daeb0 1 External name 00007fc3a74f1bf8 FREEABL 0 00

00007FC3A71DAE58 1 Alloc environm 00007fc3a74f0b78 4224 freeabl 0 00007fc3a74e95c8

00007fc3a71dae00 1 Ksz PGA Subheap 00007fc3a74f0ad8 freeabl 0 00

00007FC3A71DADA8 1 Kjzt Context 00007fc3a74f0a78 0 00 freeabl

00007FC3A71DAD50 1 SIOHEAPD_KDLWPG 00007fc3a74f09a8 208 freeabl 0 00

00007fc3a71dacf8 1 BCHEAPD_KDLWPGA 00007fc3a74f0878 304 Freeabl 0 00

00007FC3A71DACA0 1 IOVECHEAPD_KDLW 00007fc3a74f0790 232 freeabl 0 00

00007FC3A71DAC48 1 peshm.c:proces 00007fc3a74f01d0 1472 RECR 4095 00007fc3a75160f0

00007FC3A71DABF0 1 Permanent memor 00007fc3a74efb68 1640 Perm 0 00

00007fc3a71dab98 1 krbrpcact 00007fc3a74f3cc8 freeabl 0 00

00007FC3A71DAB40 1 Permanent memor 00007fc3a74f1c58 8304 Perm 0 00

variable PGA further consists of the following two parts:

(1) Session memory The-session memory is used to hold the logon information and other related information for the session, and for the shared server mode, this part is shared rather than private.

(2) Private SQL zone-Private SQL area:Private SQL Region contains binding variable information, query execution status information, query workspace, and so on. Each session that issues a SQL query has a private SQL area, which is allocated within the PGA for the private server mode, and for shared server mode, in this part of the SGA.

One of the concepts you need to know here is the cursor. When an Oracle application or user's application executes, it is possible to display or implicitly open the cursor (the open cursor) for task processing, and to open the cursor requires that the SQL area be allocated. Managing the private SQL area is the responsibility of the user process, while allocation and recycling relies on the specific application, and in order to prevent excessive SQL workspace allocations, Oracle limits the number of cursors that each user process can open at the same time by using the Open_cursors parameter. A private SQL area is allocated when the cursor is opened and released when the execution end cursor is closed.

In short, the task execution of the user process and the use of the cursor are the main consumers of PGA memory, and we are most concerned about the database performance optimization , in fact the activity of the database is mainly cursor activity .

The private SQL zone consists of the following two parts:

(1) Permanent zone-persistent area: This area contains information such as binding variables, which are freed only when the cursor is closed.

(2) Runtime Zone-runtime area: This area holds the information required by the SQL statement to run and is created first when the request is executed, which contains state information about the execution of the query (for example, full table sweep, the progress of the full table sweep, etc.), SQL work areas (this part of the area is allocated under memory-intensive requests, such as sort or hash-join, and for DML statements, the SQL statement releases the zone when it is executed, and the query statement is released after the record Returns or when the query is canceled)