A further understanding of shared pool principle based on parametric shared_pool_reserved_size

Conclusions1, the parameters associated with the shared pool are: shared_pool_size,shared_pool_reserved_size,_shared_pool_reserved_pct
_shared_pool_reserved_min_alloc
2,shared_pool_reserved_size generally default is Shared_pool_size 5%, this ratio is controlled by the parameter _shared_pool_reserved_pct
3,_shared_pool_reserved_min_alloc controls the minimum allocation size in the reserved pool, with a default size of 4400 and a range of 4000bytes to 60M (of course, based on the current shared pool size)
Otherwise, the database cannot be restarted.
4, adjust the Shared_pool_reserved_size,_shared_pool_reserved_pct,_shared_pool_reserved_min_alloc all to restart the library before you can
5, adjust the above parameters can be independent of each other, that is, adjust a parameter, other parameters will not dynamically adjust the corresponding, still maintain the original value
6, each reserved list corresponds to the heap heap, that is, how many stacks have the number of reserved lists
14 bucket are included under each reserved list
7, adjust the above several parameters, found that the retention list of relevant data has not changed significantly, of course, also limited to my understanding of this piece is not in place
8,X$KSMSPR records the allocation of chunk, chunk size total of 2 species, the 1th type is 48 bytes, the 2nd 4529992 is 4424K, the specific test see after the UE to find the analysis of the match (in the article to find the matching)

9, for the use of X$KSMSPR, see the following test--Learn about the X view associated with chunk allocation (match lookup in the article)

X$ksmsp is the parent set of X$KSMSPR, which contains the latter
Test

----Oracle Version
Sql> SELECT * from V$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bi


--parameters related to the reserved area, visible shared_pool_reserved_size=shared_pool_size*_shared_pool_reserved_pct, and _shared_pool_reserved_ PCT default value is 5%
Sql> Show Parameter Shared_pool


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Shared_pool_reserved_size Big Integer 44M
Shared_pool_size Big Integer 0


Controls how much of the allocation from the shared pool to the retention pool, visible as 5%
_shared_pool_reserved_pct 5 Percentage Memory of The shared pool allocated for
The reserved area
The minimum allocation size in the reserved pool is 4400 bytes, which is less than this size and does not allocate memory from the reserved pool, but allocates memory from the free list and LRU list
_shared_pool_reserved_min_alloc 4400 Minimum allocation s Ize in bytes for reserved area
of shared pool


V$sgainfo or __shared_pool_size to get the current shared pool size
Sql> Select name,bytes/1024/1024 as mb,resizeable from V$sgainfo where name= ' Shared Pool Size ';


NAME MB RES
-------------------------------- ---------- ---
Shared Pool Size 832 Yes


__shared_pool_size 872415232 Actual size in bytes of shared pool





Sql> Select 872415232*0.05/1024/1024 MB from dual;


MB
----------
41.6


First, the influence of the parameter shared_pool_reserved_size adjustment on the shared pool memory allocation is studied.
----Not adjusted before shared_pool_reserved_size
----A total of 4 heap
Sql> host More/home/ora10g/admin/ora10g/udump/ora10g_ora_14042.trc|grep-i--color SGA
HEAP DUMP HEAP name= "SGA HEAP" desc=0x60000058
HEAP DUMP HEAP name= "SGA HEAP (1,0)" Desc=0x60034fe0
HEAP DUMP HEAP name= "SGA HEAP (1,1)" desc=0x60036838
Chunk 0a3b7bc20 sz= 4190296 recreate "ksfd SGA I/o B" latch= (nil)
HEAP DUMP HEAP name= "SGA HEAP (1,2)" desc=0x60038090
HEAP DUMP HEAP name= "SGA HEAP (1,3)" Desc=0x600398e8


---visible free list for each heap corresponding to a reserved area, totaling 4
Sql> host More/home/ora10g/admin/ora10g/udump/ora10g_ora_14042.trc|grep-i--color "RESERVED free LISTS:"
RESERVED Free LISTS:
RESERVED Free LISTS:
RESERVED Free LISTS:
RESERVED Free LISTS:


--The free list for each reserved area contains 14 bucket, a total of 56 bucket, and the reserved extents section contains bucket in the chunk of each reserved area in the list, as you can understand, Oracle uses bucket to manage memory allocations of different sizes
Sql> host More/home/ora10g/admin/ora10g/udump/ora10g_ora_14042.trc|grep-i--color RESERVED
Reserved granule count (granule size 16777216)
RESERVED extents
Reserved granules for root (granule size 16777216)
Chunk 0a1000058 sz= r-freeable "reserved Stoppe"
Chunk 0a10ccfd0 sz= r-freeable "reserved Stoppe"
Chunk 0a2000058 sz= r-freeable "reserved Stoppe"
Chunk 0a20ccfd0 sz= r-freeable "reserved Stoppe"
Chunk 0a4000058 sz= r-freeable "reserved Stoppe"
Chunk 0a40ccfd0 sz= r-freeable "reserved Stoppe"
RESERVED Free LISTS:
Reserved Bucket 0 size=32
Reserved Bucket 1 size=4400
Reserved Bucket 2 size=8216
Reserved Bucket 3 size=8696
Reserved Bucket 4 size=8704
Reserved Bucket 5 size=8712
Reserved Bucket 6 size=8720
Reserved Bucket 7 size=9368
Reserved Bucket 8 size=9376
Reserved Bucket 9 size=12352
Reserved Bucket size=12360
Reserved Bucket size=16408
Reserved Bucket size=32792
Reserved Bucket size=65560
Total reserved free space = 2518488
Reserved granules for root (granule size 16777216)
Chunk 0a3000058 sz= r-freeable "reserved Stoppe"
Chunk 0a30ccfd0 sz= r-freeable "reserved Stoppe"
RESERVED Free LISTS:
Reserved Bucket 0 size=32
Reserved Bucket 1 size=4400
Reserved Bucket 2 size=8216
Reserved Bucket 3 size=8696
Reserved Bucket 4 size=8704
Reserved Bucket 5 size=8712
Reserved Bucket 6 size=8720
Reserved Bucket 7 size=9368
Reserved Bucket 8 size=9376
Reserved Bucket 9 size=12352
Reserved Bucket size=12360
Reserved Bucket size=16408
Reserved Bucket size=32792
Reserved Bucket size=65560
Total reserved free space = 839496
Reserved granules for root (granule size 16777216)
Chunk 0a0000058 sz= r-freeable "reserved Stoppe"
Chunk 0a00ccfd0 sz= r-freeable "reserved Stoppe"