The shared pool
The Shared Pool is a larger part of SGA. There are a lot of DBAs that you don't understand why Shared pool is used. I don't know how to set the Size of Shared Pool. I am free. Big. Sometimes it may be not big enough, but more is a waste of memory space. And too big Shared Pool will affect performance.
CHUNK:
To better understand Shared Pool, you have to learn more about X $ KSMSP table. Each record in this table corresponds to a CHUNK in Shared pool memory.
SQL> SELECT KSMCHCOM, KSMCHCLS, KSMCHSIZ from x $ kSMSP;
KSMCHCOM KSMCHCLS KSMCHSIZ
---------------------- ----------
KGL Handles Recr 496
PL / SQL MPCODE RECR 1624
Dictionary Cach FreeAbl 4256
Free memory free 1088
Library Cache FreeAbl 568
Library Cache Recr 584
Multiblock Rea FreeAbl 2072
Permanent Memor Perm 1677104
Row Cache Lru RECR 48
Session Param V Freeabl 2936
SQL Area FreeAbl 2104
SQL Area RECR 1208
The above is an example of this table query. (There is a record of about 5726 lines for the entire table)
After each Shared Pool Chunk has been assigned, the code transforms the statement and makes it actually called once. This statement can be seen in the KSMCHCOM field in the X $ KSMSP table, describe allocation Memory chunk. Every chunk is larger than the object it contains, because each Chunk has more 16-byte header information for storing the type of CHUNK, Category, and Size Size also used for Shared pool management. Link pointer.
Memory Chunk has four categories that can be seen from KSMCHCLS in the X $ KSMSP table.
Free CHUNK is called Free Chunk. It does not contain valid objects, which can be freely assigned.
RECR: Reconstructive Chunk (Recreatable Chunk), this Chunk has an object, and these objects can be temporarily deleted when necessary and reconstructs in the case of need. An example, containing shared sql stat's chunks Can be rebuilt
Freeabl: Released Chunk (FreeAbl Chunk), which also includes objects, which are often available during the survival of a session and is not available after the session is broken. These chunks can be released before the session disconnected. It can be partially released. It can be released all. Thefreeable chunk cannot be temporarily deleted because it is not reconstructive.
Perm: Permanent Memory CHUNK (Perminent Memory Chunk). It contains objects that cannot be released, which is permanently existing in memory. Some big permanent memory CHUNK also contains a quantity of Free Space. Need to be released to Shared Poo.
SELECT
KSMCHCOM CONTENTS,
COUNT (*) Chunks,
SUM (Decode (KSMCHCLS, 'RECR', KSMCHSIZ) Recreatable,
SUM (Decode (KsmchCls, 'Freeabl', Ksmchsiz) Freeable,
SUM (KSMCHSIZ) Total
From
SYS.X_ $ KSMSP
WHERE
INST_ID = Userenv ('Instance') Andksmchcls Not Like 'R%'
GROUP BY
KSMCHCOM
KSMCHCOM Chunks Recr FreeAbl Total
-------------------------------------------------------------------------------- --------
KGFF HEAP 6 1296 2528 3824
KGK Contexts 2 2400 2400
KGK HEAP 2 1136 1136
KGL Handles 571 178616 178616
Kqls Heap 404 87952 52488 612840
PL / SQL Diana 274 42168 459504 501672
PL / SQL MPCODE 57 14560 88384 102944
PLS CCA HP DESC 1 168 168
PLS NON-LIB HP 1 2104 2104
Character Set M 5 23504 23504
Dictionary cach 108 223872 223872
Fixed Allocatio 9 360 360
Free Memory 185 614088
Kzull 1 48 48
Library Cache 1612 268312 356312 624624
Multiblock REA 1 2072 2072
Permanent Memor 1 1677104
Reserved Stoppe 2 48
Row Cache Lru 24 1168 1168
Session Param V 8 23488 23488
SQL Area 983 231080 1303792 1534872
Table Column 19 18520 18520
Table Definiti 2 176 176
The above query can get related information such as all Chunks in Shared Pool. Only some records are listed above.
Free Lists:
Free Chunks in Shared Pool, which is organized into free Lists or Buckets in their size. The following table is the correspondence of Bucket number and Free Chunks Size:
Use the following scripts to query the number of Chunks and the free space in each free list .SELECT
Decode (Sign (KSMCHSIZ - 80), -1, 0, Trunc (1 / log (KSMCHSIZ - 15, 2)) - 5)
BUCKET,
SUM (KSMCHSIZ) Free_Space,
COUNT (*) free_chunks,
Trunc (AVG (KSMCHSIZ)) AVERAGE_SIZE,
Max (KSMCHSIZ) BIGGEST
From
SYS.X_ $ KSMSP
WHERE
INST_ID = Userenv ('Instance') and
KSMCLS = 'Free'
GROUP BY
Decode (Sign (KSMCHSIZ - 80), -1, 0, Trunc (1 / log (KSMCHSIZ - 15, 2)) - 5)
Bucket Free_Space Free_Chunks Average_size Biggest
---------- ------------------------------------- ---
0 166344 3872 42 72
1 32208 374 86 96
4 928 1 928 928
6 11784 4 2946 3328
When a process requires Shared Pool Memory to assign a Chunk, it will first go to the free Lists to find the chunk matching the size match. If you don't do it with the desired Size, you can allocate it will continue in Free Lists. Looking for a bigger chunk. If the CHUNK found is 24 bytes or more, this chunk will be splitted to use, return the remaining Free Space section to Free Lists. If you can't find it, It will be used from a non-empty free Lists to use it. The final way is to describe the free LISTS in the LRU mechanism.
Free Lists Description, management and chunk allocation are made under the protection of Shared Pool Laths. If Shared pool contains a lot of little free chunks, Shared Pool Lathes will be described when this special Free Lists describes It will be occupied for a long time. In fact, the competition and waiting of Shared Pool Lathes often appear are because of a large amount of small free chunks. So add Shared Pool Size not to reduce Shared Pool Laths Contention and make This competition is even more serious, which is the previously mentioned SIZE of Shared Pool, which does not necessarily improve performance.
Lru Lists:
If a process is not available from Shared Pool Free Lists, it will remove a CHUNKS containing the recreatable object from Shared Pool to release enough Chunks to meet your own use.
RECREATABLE CHUNKS has two types: one is Pinned in memory, one is a .pinned_pool.keep execute in memory in memory. DBMS_Shared_Pool.keep executes. DBMS_Shared_Pool. DBMS_Shared_pool .Keep is required to intervene, and can only be in library cache. And Pinned Chunks is automatically executed, which is automatically executed when Chunks is using objects that are using objects. Pinned recreatable chunks is The space cannot be released, and Unpinned Recreatable Chunks can be released. In Shared Pool, Unpinned Chunks is organized into two LISTs, which are arranged in LRU mechanisms. They are called temporary LRU LIST and cycles, respectively. Lru List. Chunks placed at the last side of the MRU (MOSTLY USED) in Unpinned, which will be removed from this MRU LIST when pinned.
When a process needs to be newly assigned some Shared Pool Memory memory, Chunks will also delete from the LRU List's most end. Chunks is set by Flushed in each 8 Chunks (temporarily clever) ----- first is From the temporary LRU List, then from the looped LRU List. Chunks is not considering the size of the size and is flushed by the LRU mechanism. Some chunks cannot be being used by flushed, such as DBMS_SHARED_POOL.Keep, containing library cache The CHUNKS of the object cannot be being removed by flushed. These chunks are replaced by Pinned mode.
Unpinned Recreatable Chunks Two types of LRU LISTS: Temporary LRU LIST and looped LRU LIST length can be queried from the Fixed table x $ kghlu, and the number of CHUNKS that has false has been dropped from Pinned and Unpinned or The number of Chunks deleted from LRU List. From x $ kghlu table, you can know that the number of times LRU LISTS is completely but unsuccessful (not understood), and the recent request CHUNK failed to see the following statement:
Column KGHlurcr Heading "Recurrent | Chunks"
Column KGHLUTRN Heading "Transient | Chunks"
Column KGHlufsh Heading "flushed | chunks"
Column Kghluops Heading "Pins and | Releases"
Column KGhlunfu Heading "ORA-4031 | Errors"
Column Kghlunfs Heading "Last Error | Size"
SELECT
Kghlurcr,
Kghlutrn,
Kghlufsh,
Kghluops,
Kghlunfu,
Kghlunfs
From
SYS.X_ $ KGHLU
WHERE
INST_ID = Userenv ('Instance')
/
Recurrent Transient Flushed Pins and ORA-4031 Last Error
Chunks Chunks Chunks Releases Errors Size
---------- -------------------------------------- -----------
121 164 148447 4126701 0 0 0
The length of the Lru Lists and the Flush rate have a great dependency on the recent application and work flow. Long or short LRU Lists will not cause problems, but Chunks Flush takes an important part of the health management of memory. If Transient Chunks longer than Recuit CHUNKS, the Shared Pool Size is too big. The hit rate of Chunks Flush to other LRU operations If it is greater than 1/20, the Shared Pool Size is too small. (Above this is someone else's experience) SPARE Free memory:
If a large memory request cannot be assigned from Free Lists and Lru Lists flush, Oracle will be implemented in another way.
Oracle's reorder is not used to engage in idle memory. When some Chunks have been released them will be used with the CHUNK back (here, the so-called joint is the front one free chunk to join a free chunk to becomes one more Big Free Chunk. Generally, Oracle will only join the Free Space in the Shared Pool after the execution of the ALTER SYSTEM FLUSH Shared Pool, even when Shared pool has sufficient continuous memory segment. Memory request assignment may also fail. If idle memory is divided into multiple small Chunks, it is possible to not meet large memory request allocation.
In order to meet the large memory request assignment, Oracle uses a heavy way to release more memory to Shared Pool. In fact, Oracle is only assigned by the original shared pool size to Shared Pool when the instance is started. Shared pool (ie, Shared_Pool_SIZE) Half of the memory to Shared Pool. When you need to use more, you need to use the back-to-heavy way.
Oracle's excess idle memory (Spare Free Memory) is hidden in a permanent memory CHUNKS (Perminent Memory Chunks). These memory is not in Free Lists, so it is not possible to be used immediately. You can see Free Memory Statistics in V $ SGASTAT. Spare Free Memory Chunks will be released to Shared Pool when necessary. Since these Spare Free Memory has been assigned to Shared Pool, but also requests Shared Pool Memory but If Free Memory is assigned, ORA-4031'Unable to allocate X bytes of shared memory 'is generated. If you run in a peak login in the database, Oracle also has the remaining spare free memory of the remaining part of the SPARE FREE MEMORY. The shared_pool_size set is too large. You can view the remaining Spare Free Memory by the following scripts.
SELECT
AVG (v.value) shared_pool_size,
Greatest (S.KSMSSLEN) - SUM (P.KSMCHSIZ), 0) Spare_free,
TO_CHAR
100 * Greatest (AVG (S.KSMSSLEN) - SUM (P.KSMCHSUE), 0) / AVG (v.value),
'99999'
) || '%' WaStage
From
Sys.x_ $ KSMSS S,
Sys.x_ $ KSMSP P,
SYS.V_ $ Parameter V
WHERE
s.inst_id = Userenv ('Instance') and
p.inst_id = useerenv ('instance ") and
P.KSMCHCOM = 'free memory' and
S.KSMSNAM = 'free memory' andv.name = 'shared_pool_size'
/
The Reserved List:
Generally, the biggest chunks will not exceed 5000bytes. If there is a problem with chunks, Oracle is another way, the RESERD Shared Pool that is mentioned below, and will not go to Shared Pool Free Lists and LRU LISTS Look for CHUNKs that adapt or larger size to use it.
Oracle preserves a part of Shared Pool. The default is 5% of Shared_Pool_Size. You can also set the size via the parameter shared_pool_reserved_size. The corresponding parameter name, this part is part of the SHARED POOL. SIZE.
More than 5000bytes of Chunks will be placed in the reservations of Shared Pool, starting to be set by implied parameters _shared_pool_reserved_min_alloc, but can not be modified after the instance is started. So small chunks will not be put in Shared Pool The reservoir is coming. Free memory in the reserved section of Shared Pool is not included in a single reserved free list, but in reserved shared pool. However, reserved shared Pool is unpounded by its own LRU LISTS to serve Unpinned Recreatable Chunks. So, the big chunks is not flushed (empty) when the memory space is released in the general free Lists. And in the Reserved Shared Pool Free Lists When the memory space is released, the little Chunks will not be only large Chunks by Flushed (the big chunks here, we refer to more than 5000bytes chunks)
In a V $ shared_pool_reserved view, we can view the reserved shared pool statistics. Special field request_misses, which shows the number of times that is not immediately satisfied from the reserved shared pool free lists. This value should be 0. That is to say in the absence of flush unpinned Recreatable Chunks should have a sufficient number of Free Memory in Reserved Shared Pool Freeable memory to satisfy the request of a short time. USED_SPACE field of view V $ SHARED_POOL_RESERVED view to determine how much Reserved Shared Pool SIZE is Appropriate. View the Size and HWM of the reserved shared pool with the following scripts. It is best to check the use of the query before turning off the database and combined with V $ resource_limit to see other resources.
Prompt reserved pool high-water-mark since instance startup
Prompt ================================================= ===
SELECT
SUM (R.KSMCHSIZ) - & Stopper Reserved_size,
Sum
R.KSMCHSIZ -
Decode (H.Kghlunfu, 0, Decode (R.indx, 1, R.KSMCHSIZ, 0), 0)) - & Stopper High_water_mark,
TO_CHAR
100 * (SUM
R.KSMCHSIZ -
Decode (H. Kghlunfu, 0, Decode (R.indx, 1, R.KSMCHSIZ, 0), 0)
) - 48
) / (SUM (R.KSMCHSIZ) - & Stopper,
'99999'
) || '%' "usage"
From
SYS.X_ $ KGHLU H,
SYS.X_ $ KSMSPR R
WHERE
H.inst_id = userenv ('instance ") and
R.Inst_id = Userenv ('Instance')
/
Prompt
RESERVED_SIZE HIGH_WATER_MARK USAGE
---------------------------------
256000 15080 6%
Marking Objects for Keeping:
In a Size appropriate shared pool, Dead Chunks will not be cleared. Any clearing action means that some useful objects will also be cleared. Especially reconstructive objects, because they are only certain Time is used, but the cost of being reconstructed may be large because they may be a big object or need more complex processing processes.
Alleviation of this risk is to use dbms_shared_pool.keep to take some users yourself to ShareP to Shared Pool. This program puts objects and related sub-objects in library cache and marke them in Shared Pool So the most appropriate do this time is to make a Keep operation after the instance is started to avoid more fragments. It is often wrong to think that the large object such as a package does not require Keep to Shared Pool because they are generally Put it to Shared Pool's reserved part, so the chance to be cleared is very small. Otherwise, many big objects are actually allocating Shared pool memory with a number of small chunks, can't get special in their size. Protection. By viewing table x $ kSMLRU, you can confirm which library cache objects need Keep to Shared Pool.
Flushing the shared pool:
The only way to engage the continuous free chunks is to use the empty shared pool displayed by the ALTER System Flush Shared Pool. In practice, the empty shared pool will reduce Shared Pool Latch competition, and it will reduce the generation of ORA-4031 error, and will reduce immediate Performance competition, especially those who are being keep to Shared Pool. Conversely, if those primary objects have keep to Shared Pool, and your shared pool size is more appropriate, just use flush shared pool, otherwise The instance will be particularly large in the normal runtime requirements on the normal runtime.
Generally, you will do Flush Shared Pool after backup at night. Other time is a lot of shards or spaces in Shared pool. When you do Flush Shared Pool, you must confirm that you don't have unnecessary gaps for Cahed Sequence. To achieve This purpose can use Sequence Keep to Shared Pool, and if you are a single instance, you can use the ALTER Sequence Nocache. You can use the following procedure to confirm which Sequence needs Keep to SHRAED POOL. If you need Shutdown Abort to close the database is best First, Sequence Keep to Shared Pool first.
Declare
Cursor Cached_Sequences IS
Selectsequence_owner,
SEQUENCE_NAME
From
Sys.dba_sequences
WHERE
Cache_size> 0;
Sequence_OWNER VARCHAR2 (30);
SEQUENCE_NAME VARCHAR2 (30);
Begin
Open cached_sequence;
loop
Fetch cached_sequences INTO sequence_owner, sequence_name
EXIT WHEN CACHED_SEQUENCES% NOTFOUND;
Sys.dbms_shared_pool.keep (sequence_owner || '.' || sequence_name, 'q');
End loop;
END;
/
Heaps and subheaps
FIXED TABLES X $ is starting with KSM and KGH, which represents the Oracle Memory Manager and Stack Manager model. Both are closely linked. Memory Manager is responsible for the operating system OS Interacting to get memory for Oracle use and memory allocation information. The heap manager is a dynamic memory allocation and management. SHared pool is also called SGA HEAP. This reason is.
A stack contains a heap descriptor and one or more memory regions, of course, a subtle. In this case, the heap of the 堆 descriptor and memory area are allocated with the chunks of the parent. The size of HEAP is The type of HEAP and the List header information of Heap's Free List and LRU List are different. Each memory area has head information points to a heap up and back, and how many space-assignable information is available in this HEAP .
In addition to the reserved section, the SHARED POOL has the same structure as the shared pool itself. The memory is allocated with Chunks. Free chunks is organized into free Lists. Unpinned Recreatable Chunks is also organized into two LRU LISTS: Transient Chunks and Recurrent chunks. Substacks also have permanent memory CHUNKS. It is also possible to include sub-stacks, depth is 4.
Cached Pool's most objects are actually in the subtle for a new Chunks lookup space, just like shared pools to find space for a new Chunks. The difference is that the child is from the district Increasing, while Shared Pool is a fixed size Size. Subridic New Area is assigned by the minimum size (the smallest size of the Chunks), so it usually looks for the smallest Chunks.
The large pool:
If the parameter large_pool_size is set, the Large Pool will be configured as a separate heap in the SGA. It is not part of the Shared Pool, which is protected with Large Memory Latch. Large pool only includes idle Chunks and releasable Chunks. Does not include Recreatable Chunks, so the LRU mechanism of the heap manager is not used.
To prevent fragmentation in Large Pool, all LARGE POOL CHUNKS SIZE is generally made as its size in parameter _large_pool_min_alloc, the default value is 16K. This parameter does not have to be tuned.
In the following cases, you recommend that you set the large_pool_size parameter to use the large pool:
Use MTS
Used Recovery Manager (RMAN)
Use parallel query options Parallel Query Option (PQO)
