Internal Management Mechanism of Oracle Share Pool

Internal Management Mechanism of Oracle Share Pool

The share pool is managed using HEAP memory management. It is physically composed of multiple memory zones (EXTENT) and consists of multiple chunks of different sizes. The CHUNK can be reused and idle, and they are connected by lru list, free list, and reserved list.

Heap Management

The Shared Pool is managed using Heap memory management (KGH: Kernel Generic Heap ). from Oracle 9i, there can be multiple top-level HEAP (TOP-LEVLE HEAP), the top-level HEAP can be divided into multiple sub-HEAP, under the sub-HEAP also has sub-HEAP. The heap and secondary pair structures are basically the same. Physically, a heap is composed of links to multiple memory areas in the form of link lists. A memory area uses a Granule physically. A memory area consists of multiple chunks. Therefore, the chunk is the minimum heap memory unit.

The usage of the Chunk can be viewed in the X $ KSMSP internal view. Each heap header contains the available chunk list and the used chunk list. The dump heap command can be used to observe the relationship between heap and extent in the trace file.

Alter system set events 'immediate trace name heapdump level 2 ';

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

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

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

Case:

SQL> select name, bytes/1024/1024 from v $ sgainfo;

Name bytes/1024/1024

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

Fixed SGA Size 1.27566528

Redo Buffers' 5.59765625

Buffer Cache Size 180

Shared Pool Size 104

Large Pool Size 4

Java Pool Size 4

Streams Pool Size 0

Shared IO Pool Size 0

Granule Size 4

Maximum SGA Size 498.875

Startup overhead in Shared Pool 52

Name bytes/1024/1024

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

Free SGA Memory Available 200

12 rows selected.

[Oracle @ node1 ~] $ More/u01/app/oracle/diag/rdbms/prod/trace/prod_ora_14983.trc

Trace file/u01/app/oracle/diag/rdbms/prod/trace/prod_ora_14983.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1

System name: Linux

Node name: node1

Release: 2.6.18-194. el5

Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010

Machine: i686

Instance name: prod

Redo thread mounted by this instance: 1

Oracle process number: 32

Unix process pid: 14983, image: oracle @ node1. (TNS V1-V3)

* ** 14:58:47. 532

* ** Session id: (32.758) 14:58:47. 532

* ** Client id :() 14:58:47. 532

* ** Service name :( SYS $ USERS) 14:58:47. 532

* Module name :( sqlplus @ node1 (TNS V1-V3) 14:58:47. 532

* ** Action name :() 14:58:47. 532

KGH Latch Directory Information

Ldir state: 2 last allocated slot: 99

Slot [1] Latch: 0x200065ec Index: 1 Flags: 3 State: 2 next: (nil)

Slot [2] Latch: 0x3e75571c Index: 1 Flags: 3 State: 2 next: (nil)

 

... (Omitted)

 

Slot [99] Latch: 0x2002616c Index: 1 Flags: 3 State: 2 next: 0x1

**************************************** **************

Heap dump heap name = "sga heap" desc = 0x200010b4

Extent sz = 0x7ad4 alt = 124 het = 32767 rec = 9 flg =-126 opc = 0

Parent = (nil) owner = (nil) nex = (nil) xsz = 0x0 heap = (nil)

Fl2 = 0x60, nex = (nil)

Ds for latch 1: 0x2002a990 0x2002b5c8 0x2002c200 0x2002ce38

Reserved granule count 0 (granule size 4194304)

**************************************** **************

Heap dump heap name = "sga heap (1, 0)" desc = 0x2002a990

Extent sz = 0xfc4 alt = 124 het = 32767 rec = 9 flg =-126 opc = 0

Parent = (nil) owner = (nil) nex = (nil) xsz = 0x400000 heap = (nil)

Fl2 = 0x20, nex = (nil)

Latch set 1 of 1

Durations enabled for this heap

Reserved granules for root 0 (granule size 4194304)

EXTENT 0 addr = 0x3b800000

Chunk 3b800038 sz = 24 R-freeable "reserved stoppe"

Chunk 3b800050 sz = 212888 R-free ""

Chunk 3b833fe8 sz = 24 R-freeable "reserved stoppe"

Chunk 3b834000 sz = 3981312 perm "perm" aloo = 3290272

EXTENT 1 addr = 0x3bc00000

Chunk 3bc00038 sz = 24 R-freeable "reserved stoppe"

Chunk 3bc00050 sz= 212888 R-free ""

Chunk 3bc33fe8 sz = 24 R-freeable "reserved stoppe"

Chunk 3bc34000 sz = 3510272 perm "perm" alo = 3510272

Chunk 3bf8d000 sz = 465920 perm "perm" alo = 465920

Chunk 3bffec00 sz= 5120 free ""

EXTENT 2 addr = 0x3c000000

Chunk 3c000038 sz = 24 R-freeable "reserved stoppe"

Chunk 3c000050 sz = 212888 R-free ""

Chunk 3c033fe8 sz = 24 R-freeable "reserved stoppe"

Chunk 3c034000 sz = 3850176 perm "perm" alo = 3850176

Chunk 3c3dffc0 sz = 131088 perm "perm" alo = 131088

Chunk 3c3fffd0 sz = 48 free ""

EXTENT 3 addr = 0x3c400000

Chunk 3c400038 sz = 24 R-freeable "reserved stoppe"

Chunk 3c400050 sz= 212888 R-free ""

Chunk 3c433fe8 sz = 24 R-freeable "reserved stoppe"

Chunk 3c434000 sz = 3980368 perm "perm" alo = 3980368

Chunk 3c7ffc50 sz= 944 free ""

EXTENT 4 addr = 0x3c800000

Chunk 3c800038 sz = 24 R-freeable "reserved stoppe"

Chunk 3c800050 sz= 212888 R-free ""

Chunk 3c833fe8 sz = 24 R-freeable "reserved stoppe"

Chunk 3c834000 sz = 3672700 perm "perm" alo = 3672700

Chunk 3cbb4a7c sz = 308240 perm "perm" aloo = 308240

Chunk 3cbffe8c sz= 372 free ""

CHUNK

Chunk is stored in the memory zone as a chain. Each chunk contains two parts: header and body. The chunk status can be divided into free (available immediately), recreatable (renewable) freeable (only saves the necessary object status during the session or call period), permanent (permanent, non-renewable), and so on. The chunk in the free and recreatable states can be reused.

 

FREE LIST

Free list is mainly used to manage idle chunks and is managed by buckets. Starting from Oracle 9i, a heap has a total of 255 buckets. The size of the free chunk contained in a bucket increases with the increase of the bucket number. The free chunk under a bucket is linked to an Ed list.

(1) obtain the shared pool latch and find the suitable idle chunk in the free list. If a contention occurs when obtaining the shared pool latch, a latch: shared pool wait event occurs. At this time, Oracle will keep holding the shared pool latch until the required memory is obtained. In a shared pool with serious memory fragmentation, the process holding the shared pool latch takes a longer time.

(2) If there is no idle chunk of the proper size, use it after finding a larger idle chunk (split, the remaining memory areas are reregistered to the free list. Memory splitting means that fragments in the memory are increasing. Because the memory size required by each cursor is different, the idle memory of the shared pool is not as fixed as the idle memory in the buffer cache.

(3) If the free list is retrieved and no suitable idle chunk is found, the lru list is retrieved. The chunk on the lru list is recreatable and is not currently in use (not in the pin State ).

(4) If no suitable chunk is found on the lru list and the requested memory has not reached the implicit parameter _ shared_pool_reserved_min_alloc, the remaining memory space in the share pool will be allocated.

(5) If all of the above request memory fails, a ORA-4031 error occurs

Free lists:

Bucket 0 size = 16

Bucket 1 size = 20

Bucket 2 size = 24

Bucket 3 size = 28

Bucket 4 size = 32

Bucket 5 size = 36

Bucket 6 size = 40

Bucket 7 size = 44

Bucket 8 size = 48

Chunk 3c3fffd0 sz = 48 free ""

Chunk 3ebfffd0 sz = 48 free ""

Bucket 9 size = 52

Bucket 10 size = 56

Bucket 11 size = 60

Bucket 12 size = 64

Bucket 13 size = 68

Bucket 14 size = 72

Bucket 15 size = 76

Bucket 16 size = 80

Bucket 17 size = 84

Bucket 18 size = 88

Chunk 3e7fffa8 sz = 88 free ""

Bucket 19 size = 92

Bucket 20 size = 96

Bucket 21 size = 100

Bucket 22 size = 104

Bucket 23 size = 108

Bucket 24 size = 112

Bucket 25 size = 116

Bucket 26 size = 120

Bucket 27 size = 124

......

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page