Understanding Shared Pool 1 and sharedpool

Understanding Shared Pool 1 and sharedpool

The Heap management shared pool is managed by means of Heap memory management (KGH: Kernel Generic Heap ). starting from Oracle 9i, You can have multiple TOP-level heap instances. The TOP-level heap instances can be divided into multiple sub-heaps. The sub-HEAP instances also have sub-heaps. the structure of the heap and the secondary heap is basically the same. physically, a heap is composed of multiple memory zones connected in the form of a linked list. 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 CHUNK can be viewed in the internal view of X $ KSMSP. 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 tracking file. alter session set events 'immediate trace name heapdump level 2 ';
Let's take a look at the dump file.
Sys @ PROD> select * from v $ version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production

Sys @ PROD> select * from v $ sgainfo where name = 'granule size ';

NAME BYTES RES
---------------------------------------------
Granule Size 4194304 No

**************************************** **************
Heap dump heap name = "sga heap (1, 0)" desc = 0x2002b1cc
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 27 (granule size: 4194304)
EXTENT 0 addr = 0x4d000000
Chunk 4d000038 sz = 24 R-freeable "reserved stoppe"
Chunk 4d000050 sz = 4193964 R-perm "perm" alo = 4193964
Chunk 4d3ffefc sz = 236 R-free ""
Chunk 4d3fffe8 sz = 24 R-freeable "reserved stoppe"
EXTENT 1 addr = 0x4e000000
Chunk 4e000038 sz = 24 R-freeable "reserved stoppe"
Chunk 4e000050 sz = 212888 R-free ""
Chunk 4e033fe8 sz = 24 R-freeable "reserved stoppe"
Chunk 4e034000 sz = 1544904 perm "perm" alo = 1544904
Chunk 4e1ad2c8 sz = 2436408 perm "perm" alo = 455948
EXTENT 2 addr = 0x4e400000
Chunk 4e400038 sz = 24 R-freeable "reserved stoppe"
Chunk 4e400050 sz = 212888 R-free ""
Chunk 4e433fe8 sz = 24 R-freeable "reserved stoppe"
Chunk 4e434000 sz = 3973960 perm "perm" alo = 3973960
Chunk 4e7fe348 sz = 3976 perm "perm" alo = 3976
Chunk 4e7ff2d0 sz = 3376 free ""
EXTENT 3 addr = 0x4e800000
Chunk 4e800038 sz = 24 R-freeable "reserved stoppe"
Chunk 4e800050 sz = 212888 R-free ""
Chunk 4e833fe8 sz = 24 R-freeable "reserved stoppe"
Chunk 4e834000 sz = 3953996 perm "perm" alo = 3953996
Chunk 4ebf954c sz = 16400 perm "perm" alo = 16400
Chunk 4ebfd55c sz = 7936 perm "perm" alo = 7936
Chunk 4ebff45c sz= 2980 free ""

It can be seen that Oracle allocates space for each "sga heap" in EXTENT form each time, and the size of each EXTENT is related to GRANULE to add all chunks in EXTENT 0: 24 + 4193964 + 236 + 24 = 41900004-56 add all chunks in EXTENT 1: 24 + 212888 + 24 + 1544904 + 2436408 = 41900004-56 add all chunks in EXTENT 2: 24 + 212888 + 24 + 3953996 + 16400 + 7936 + 2980 = 4194304-56

What is this 56? I'm not using Baidu. I guess it's the EXTENT HEADER size.
ChunkChunk is stored in the memory in the form of Chain. Each Chunk contains two parts: Header and Body. The Chunk state is roughly divided into: FREE: RECREATABLE can be used immediately: Renewable FREEABLE: only PERMANENT, which saves the necessary object status during the Session or Call period, can be reused for chunks in FREE and RECREATABLE states.

Free ListFree 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 the Bucket increases with the increase of the number. The Free Chunk under each Bucket is connected as a Linked list.

As mentioned in the DBA Practice Guide, the Chunk in the Same Bucket is not sorted by size. It is also similar in the DBA thinking sky.


So what is the actual situation? Free lists:
Bucket 0 size = 16
Bucket 1 size = 20
Chunk 4e38e234 sz = 20 free ""
Chunk 4e000038c sz = 20 free ""
Bucket 2 size = 24
Chunk 4e2a0ec4 sz = 24 free ""
Chunk 4d1e8e64 sz = 24 free ""
Chunk 4e147954 sz = 24 free ""
Chunk 4e33a93c sz = 24 free ""
Chunk 4e39b61c sz = 24 free ""
Bucket 3 size = 28
Chunk 4e218efc sz = 28 free ""
Chunk 4e1af51c sz = 28 free ""
Chunk 4e17b9dc sz = 28 free ""
Chunk 4e1635e4 sz = 28 free ""
Chunk 4e1ff95c sz = 28 free ""
Chunk 4e1cac74 sz = 28 free ""
Chunk 4e294914 sz = 28 free ""
Bucket 4 size = 32
Bucket 5 size = 36
Bucket 6 size = 40
Bucket 7 size = 44
Bucket 8 size = 48
Bucket 9 size = 52
Bucket 10 size = 56
Bucket 11 size = 60
Bucket 12 size = 64
Bucket 13 size = 68
Chunk 4d202ad4 sz = 68 free ""
Chunk 4e12a47c sz = 68 free ""
Chunk 4e390234 sz = 68 free ""
Bucket 14 size = 72
When a Chunk is found in the Bucket following the linked list, if the Chunk is in the last position of the linked list, we need to traverse the entire linked list, which greatly increases the time for holding the Shared pool latch. From my DUMP, we can see that this step may not be sorted, but because the number of buckets is greatly increased by 8i, how many chunks exist in each BUCKET? I really hope you can explain it to me.
1. Obtain the shared pool latch and find the appropriate 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 the chunk after finding a larger idle chunk (split), and reregister the remaining memory areas 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 no suitable idle chunks are found in the free list, 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
Memory fragmentation of the Shared pool is inevitable. to reduce the excessive fragmentation of the Shared Pool, if each memory segment of the released chain is physically adjacent, Oracle will merge it into a large memory segment. That is to say, Oracle will automatically perform memory fragmentation on a regular basis.
The Lru ListLru List stores unused chunks that can be reconstructed. When the idle Chunk cannot be found in the Free list, the unpinned recreatable chunks (Lru first) will be searched in the lru List ):
Chunk 517a621c sz = 32 recreate "fixed allow.o" latch = 0x2000fc90
Chunk 4ebbd94c sz = 332 recreate "KGLHD" latch = (nil)
Chunk 4eb9c7f8 sz = 332 recreate "KGLHD" latch = (nil)
Chunk 4eb66c68 sz = 332 recreate "KGLHD" latch = (nil)
Chunk 4eb6aaa4 sz = 332 recreate "KGLHD" latch = (nil)
Chunk 4eb3dadc sz = 332 recreate "KGLHD" latch = (nil)
Chunk 4eb3fde0 sz = 332 recreate "KGLHD" latch = (nil)
Chunk 4eb2e7fc sz = 332 recreate "KGLHD" latch = (nil)
Chunk 4efed6ec sz = 40 recreate "fixed allow.o" latch = 0x4ff04cd8
Chunk 4eb283ec sz = 540 recreate "kqr po" latch = 0x4ff04cd8
Chunk 4eb28608 sz = 540 recreate "kqr po" latch = 0x4ff04cd8
Chunk 4eb27398 sz = 32 recreate "fixed allow.o" latch = 0x4ff04920
Chunk 4eb2692c sz = 32 recreate "fixed allow.o" latch = 0x2000e4cc
Chunk 4eb258f4 sz = 32 recreate "fixed allow.o" latch = 0x4ff04570
The Reserved PoolOracle version supports the retention pool since 7.1.5. The purpose of the retention pool is to have a portion of the Reserved space when the sharing pool is fragmented, it is used for large memory allocation. You can use shared_pool_reserved_size to set the size of the reserved pool. Generally, you can leave it unspecified. The default value is 5% reserved pool usage condition 1. the chunk2s with sufficient size cannot be found in the Shared Pool freelist. No suitable chunk or recreatable can be found on the lru list and the chunk3. the allocated content must be greater than _ SHARED_POOL_RESERVED_MIN_ALLOC (the default value is 4400B ), that is to say, more than this memory allocation is considered to be a large memory allocation of some common system objects often in the 4000-4400B, so in the fragmentation is very serious and often due to ORA-04031 caused by downtime in the system, it is necessary to set this parameter to a smaller value (such as 4000 or 3800). select indx from x $ ksppi where upper (ksppinm) = '_ SHARED_POOL_RESERVED_MIN_ALLOC ';

INDX
----------
153

Sys @ PROD> select ksppstvl from x $ ksppsv where indx = 153;

KSPPSTVL
Bytes ----------------------------------------------------------------------------------------------------
4400

Select indx from x $ ksppi where upper (ksppinm) = '_ SHARED_POOL_RESERVED_PCT ';

INDX
----------
152

Select ksppstvl from x $ ksppsv where indx = 152;

KSPPSTVL
Bytes ----------------------------------------------------------------------------------------------------
5
Is the retention pool too small?
Select REQUEST_MISSES from v $ shared_pool_reserved;
This query queries the number of times (request_failures) that cannot be applied in the reserved area. We want it to be 0. If it is greater than 0, we need to consider increasing the size of the reserved area as needed.
Is the retention pool too large?
Reuest_misses is 0 and does not increase
Free space ≥ SHARED_POOL_RESERVED_SIZE Minimum
It should be noted that the reserved pool will expand as the shared Pool expands, and the pool cannot be reduced even after the Shared Pool is reduced. Flush is useless.