Oracle memory management (one)

Oracle memory management (one)

[In-depth analysis of oracle-eygle] Study Notes

1. Memory Management

The memory used by the ORACLE database mainly involves two aspects: PGA and SGA.

1.1 PGA Management

PGA refers to the Program Global Area, which is a memory Area used by the Server Process to contain data and control information. PGA is non-shared memory, when a server process is started or created (when the system is running, sorting and connection risk operations may also require further PGA allocation), andExclusive Access for Server Process, so the data structure in PGA does not need to be protected by latch;

1.2 What is PGA

Process Creation usually has two modes: The Dedicated Server mode (Dedicated Server) and the Shared Server mode (SharedServer ). In the dedicated server mode, Oracle starts an Oracle process for each session. In the Shared Server mode, a certain number of server processes are usually started on the server, then multiple clients request to share the same Oracle service process.

From memory allocation and usage, PGA can be divided into two areas:

(1) Fixed PGA-Fixed PGA is similar to Fixed SGA, which contains a large number of atomic variables, small data structures, and pointers to variable PGA, these variables are defined in the source code and allocated during compilation. They can be considered as the memory reserved by PGA.

(2) Variable PGA (Variable PGA)-Variable PGA is implemented by memory Heap allocation, and its space allocation and usage can be changed, you can use the internal view X $ KSMPP ([K] ernel [S] ervice [M] emory [P] GA hea [P]) to query the allocation and usage of variable PGA memory. The variable regions of PGA mainly include session memory and private SQL zone.

17: 21: 39sys @ felix SQL> select * from x $ ksmpp;

ADDR INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR

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

20177fc3a71d91e0 1 allocate kzthsm 20177fc3a74e9700 120 freeabl 0 00

%7fc3a71d9188 1 kpuinit env han %7fc3a74e8f70 1936 freeabl 0 00

%7fc3a71d9130 1 Alloc environm %7fc3a74e87a0 2000 recr 4095 %7fc3a74e95c8

%7fc3a71d90d8 1 Alloc environm %7fc3a74e77d0 4048 freeabl 0 %7fc3a74e95c8

%7fc3a71d9080 1 permanent memor %7fc3a74e7780 80 perm 0 00

%7fc3a71d9028 1 permanent memor %7fc3a74e7730 80 perm 0 00

%7fc3a71d8fd0 1 Alloc environm %7fc3a74e97f0 8312 freeabl 0 %7fc3a74e95c8

%7fc3a71daf60 1 kzsna: login nam %7fc3a74e97b8 56 freeabl 0 00

20177fc3a71daf08 1 kgh stack 20177fc3a74eb8a8 17024 freeabl 0 00

%7fc3a71daeb0 1 external name %7fc3a74f1bf8 32 freeabl 0 00

20177fc3a71dae58 1 Alloc environm 20177fc3a74f0b78 4224 freeabl 0 20177fc3a74e95c8

20177fc3a71dae00 1 KSZ pga subheap 20177fc3a74f0ad8 160 freeabl 0 00

%7fc3a71dada8 1 KJZT context %7fc3a74f0a78 96 freeabl 0 00

20177fc3a71dad50 1 sioheapd_kdlwpg 20177fc3a74f09a8 208 freeabl 0 00

20177fc3a71dacf8 1 bcheapd_kdlwpga 20177fc3a74f0878 304 freeabl 0 00

20177fc3a71daca0 1 iovecheapd_kdlw 20177fc3a74f0790 232 freeabl 0 00

%7fc3a71dac48 1 peshm. c: Proces %7fc3a74f01d0 1472 recr 4095 %7fc3a75160f0

%7fc3a71dabf0 1 permanent memor %7fc3a74efb68 1640 perm 0 00

20177fc3a71dab98 1 krbrpcact 20177fc3a74f3cc8 64 freeabl 0 00

%7fc3a71dab40 1 permanent memor %7fc3a74f1c58 8304 perm 0 00

Variable PGAIt is further composed of the following two parts:

(1) Session Memory-Session Memory is used to store Session login information and other related information. In Shared Server mode, this part of Memory is shared rather than private.

(2) Private SQL Area-Private SQL Area: Private SQL Area contains bound variable information, query execution status information, and query workspace. Each session that sends an SQL query has a private SQL zone. For the dedicated server mode, this part is allocated in PGA. For the Shared Server mode, this part is allocated in SGA.

A concept that needs to be understood here is Cursor ). Oracle Applications or users' applications may display or implicitly Open Cursor for Task Processing during execution. To Open a Cursor, You need to allocate an SQL Area. The management of private SQL zones is the responsibility of the user process, and the allocation and recovery depend on specific applications. To prevent excessive SQL workspace allocation, oracle uses the OPEN_CURSORS parameter to limit the number of cursors that each user process can open at the same time. A private SQL zone is allocated when the Cursor is opened. It is released when the execution ends when the Cursor is closed.

To put it simply, the execution of user processes and the use of Cursor are the main consumption of PGA memory and the content we are most concerned about when optimizing database performance.In fact, the database activity is mainly the Cursor activity..

The private SQL area consists of the following two parts:

(1) Permanent region-Persistent Area:This area contains information such as bound variables. The memory is released only when the cursor is closed.

(2) Runtime region-Runtime Area: This region stores the information required for running SQL statements. It is created before executing a request.Including the status information of the query execution (for example, the progress of the full table scan is recorded for the full table scan), SQL work areas (this part of the region is allocated under memory-intensive requests, for example, Sort or Hash-Join. For DML statements, the region is released after the SQL statement is executed. For query statements, the region is released after the record is returned or when the query is canceled)