Oracle memory management (one): oracle Memory Management

Oracle memory management (one): oracle Memory Management

[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) fixedPGA (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, which are defined in the source code and allocated during compilation, it can be considered a PGA reserved memory.

 

(2)Variable PGA (Variable PGA)-Variable PGA is implemented by memory Heap allocation. Its space allocation and usage can change, 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 usedStores session logon information and other related information,For the Shared Server mode, this part of memory is shared rather than private.

 

(2) Private SQL Area-Private SQL Area:Private SQL Area includes binding variable information, querying execution status information, and querying the 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)

What is the difference between the operating system and oracle memory management?

Oracle memory management is divided into PGA and SGA, PGA management service process and background process SGA management various pools

The memory management of the operating system also has different DOS single tasks and Windows multi-threaded Linux multi-user management algorithms. We need to find the best memory optimization method suitable for the system..
 
Oracle allocates memory based on

Depends on the memory size of your computer, whether it is a 32-bit system, and your oracle environment.
It should be noted that the memory allocated to oracle cannot exceed 1.5 GB. If the physical memory of the server is less than 2 GB, the allocation ratio is 60%. If the physical memory is greater than 2 GB, it can be adjusted according to the actual situation. For example, if the physical memory is 4 GB, you can allocate 30% to oracle (the above is a description of the memory of 32-bit machines. The memory allocated by 64-bit computers to oracle is not limited to GB and can be allocated according to the actual memory ).
If you configure your own memory, you can allocate the shared pool, buffor cache, java pool, and other sizes as needed.