关于深入shared pool
周末又看了一下shared pool，在阅读了老白和老盖及dsi后写下这个笔记，如有不对的理解不正确地方还望日后批评指正
版本oracle 10g 2
shared pool 是sga中的重要组成部份，通过shared_pool_size指定大小，如要启用了asmm大小很根据需要动态调整
shared pool 由perm，libary cache,row catch(dictionary catch),reserved area(保留),free area 组成。
perm :process,session,lock(latch),trasaction,resource lib cache :pl/sql ，parse code,sql explan row catch :table column definition and grant privs ‘s infomation reserved area: reserved large object use，it will be allocate until shared pool not allocate enouth space, default size shared_pool *5%, free area:it can immediate allocate ,free list manager
dump 出来看看
sys@ANBOB> alter session set events 'immediate trace name heapdump level 2'; sys@ANBOB> oradebug setmypid; sys@ANBOB> oradebug tracefile_name;
摘自部份trace文件内容
HEAP DUMP heap name="sga heap(1,0)" desc=0x2001a468 extent sz=0xfc4 alt=108 het=32767 rec=9 flg=-126 opc=0 parent=(nil) owner=(nil) nex=(nil) xsz=0x400000 EXTENT 0 addr=0x30400000 Chunk 30400038 sz= 24 R-freeable "reserved stoppe" Chunk 30400050 sz= 212888 R-free " " Chunk 30433fe8 sz= 24 R-freeable "reserved stoppe" Chunk 30434000 sz= 3981312 perm "perm " alo=3742412 EXTENT 1 addr=0x30800000 Chunk 30800038 sz= 24 R-freeable "reserved stoppe" Chunk 30800050 sz= 212888 R-free " " Chunk 30833fe8 sz= 24 R-freeable "reserved stoppe" Chunk 30834000 sz= 3214744 perm "perm " alo=3214744 Chunk 30b44d98 sz= 766568 free " " EXTENT 2 addr=0x30c00000 Chunk 30c00038 sz= 24 R-freeable "reserved stoppe" Chunk 30c00050 sz= 212888 R-free " " Chunk 30c33fe8 sz= 24 R-freeable "reserved stoppe" Chunk 30c34000 sz= 3924808 perm "perm " alo=3924808 Chunk 30ff2348 sz= 56504 free " " EXTENT 3 addr=0x31000000 Chunk 31000038 sz= 24 R-freeable "reserved stoppe" Chunk 31000050 sz= 212888 R-free " " 省略 EXTENT 5 addr=0x31800000 Chunk 31800038 sz= 24 R-freeable "reserved stoppe" Chunk 31800050 sz= 212888 R-free " " Chunk 31833fe8 sz= 24 R-freeable "reserved stoppe" Chunk 31834000 sz= 3981244 perm "perm " alo=3981244 Chunk 31bfffbc sz= 68 free " 省略 EXTENT 8 addr=0x32800000 Chunk 32800038 sz= 24 R-freeable "reserved stoppe" Chunk 32800050 sz= 212888 R-free " " Chunk 32833fe8 sz= 24 R-freeable "reserved stoppe" Chunk 32834000 sz= 3903820 perm "perm " alo=3903820 Chunk 32bed14c sz= 32768 perm "perm " alo=32768 Chunk 32bf514c sz= 43024 perm "perm " alo=43024 Chunk 32bff95c sz= 1700 free " " Total heap size = 37748232 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 Bucket 9 size=52 Bucket 10 size=56 Bucket 11 size=60 Bucket 12 size=64 Bucket 13 size=68 Chunk 31bfffbc sz= 68 free " " --EXTENT 5 中 Bucket 14 size=72 Bucket 15 size=76 Chunk 317fffb4 sz= 76 free " " --EXTENT 4 中 Bucket 16 size=80 Bucket 17 size=84 Bucket 18 size=88 Bucket 19 size=92 。。。 Bucket 165 size=676 Bucket 166 size=680 Bucket 167 size=684 Bucket 168 size=688 Bucket 169 size=692 Bucket 170 size=696 Bucket 171 size=700 Bucket 172 size=704 Bucket 173 size=708 Bucket 174 size=712 Bucket 175 size=716-----由4递增改为8 Bucket 176 size=724 Bucket 177 size=732 Bucket 178 size=740 Bucket 179 size=748 Bucket 180 size=756 Bucket 181 size=764 Bucket 182 size=772 Bucket 183 size=780 Bucket 184 size=788 Bucket 185 size=796 Bucket 186 size=804 Bucket 187 size=812----改为64递增 Bucket 188 size=876 Bucket 189 size=940 Bucket 190 size=1004 Bucket 191 size=1068 Bucket 192 size=1072 Bucket 193 size=1076 Bucket 194 size=1132 Bucket 195 size=1196 Bucket 196 size=1260 Bucket 197 size=1324 Bucket 198 size=1388 Chunk 31fffa64 sz= 1436 free " " --在extent 6 中 Bucket 199 size=1452 Bucket 200 size=1516 Bucket 201 size=1580 Bucket 202 size=1644 Chunk 32bff95c sz= 1700 free " " --在extent 8 中 Bucket 203 size=1708 Bucket 204 size=1772 Bucket 205 size=1836 Bucket 206 size=1900 Bucket 207 size=1964 Bucket 208 size=2028 Bucket 209 size=2092 Bucket 210 size=2156 Bucket 211 size=2220 Bucket 212 size=2284 Bucket 213 size=2348 Bucket 214 size=2412 Bucket 215 size=2476 。。。 Bucket 237 size=3884 Bucket 238 size=3948 Bucket 239 size=4012 -----改为递增84 Bucket 240 size=4096 Bucket 241 size=4100 -----改为递增4 Bucket 242 size=4108 -----改为递增8 Bucket 243 size=8204 -----改为递96 Bucket 244 size=8460 ---256 Bucket 245 size=8464 ---4 Bucket 246 size=8468 Bucket 247 size=8472 Bucket 248 size=9296 ----824 Bucket 249 size=9300 ----4 Bucket 250 size=12320 ----3020 Bucket 251 size=12324 ----4 Bucket 252 size=16396 ----4172 Bucket 253 size=32780 ----16384 Chunk 30ff2348 sz= 56504 free " " --extent 2中 Bucket 254 size=65548 Chunk 30b44d98 sz= 766568 free " " --extent 1中 Total free space = 827216 RESERVED FREE LISTS: Reserved bucket 0 size=16 Reserved bucket 1 size=4400 Reserved bucket 2 size=8204 Reserved bucket 3 size=8460 ...
初始的,数据库启动以后,shared pool多数是连续内存块，当空间分配使用以后,内存块开始被分割,碎片开始出现，Oracle请求shared pool空间时，首先进入相应的Bucket进行查找。如果找不到,则转向下一个非空的bucket,获取第一个chunk。分割这个chunk,剩余部分会进入相应的Bucket,进一步增加碎片。shared_pool free lists 划分了255个bucket,8i前从9i以后并且以开始递增是以4，8，64..的容量递增，以至于后来的无规律跳动，个人觉得应该是内在被分割后的碎片。碎片过多会导致查找free Lists的时间增加，从而使shared pool latch被长时间持有，导致更多的Latch竞争。
我们可以从数据库的内部视图监控,x$ksmsp其中每一行都代表着shared pool中的一个chunk, 也可以关注v$shared_pool_reserved 查看reserved pool使用情况
sys@ANBOB> select component,oper_type,initial_size,final_size,start_time from v$sga_resize_ops where component='shared pool'; COMPONENT OPER_TYPE INITIAL_SIZE FINAL_SIZE START_TIME -------------------- --------------------------------------- ------------ ---------- ------------------- shared pool STATIC 0 79691776 2011-10-15 15:38:29 shared pool GROW 79691776 83886080 2011-10-15 15:45:20 sys@ANBOB> show parameter shared_pool NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ shared_pool_reserved_size big integer 3984588 shared_pool_size big integer 0 sys@ANBOB> select 3984588*20 from dual; 3984588*20 ---------- 79691760
差不多验证了shared_pool_reserved_size 是shared_pool_size 20%说法
查看shared_pool大小
sys@ANBOB> SELECT pool,sum(bytes) FROM V$SGASTAT group by pool;
POOL SUM(BYTES)
------------------------------------ ----------
171965040
java pool 4194304
shared pool 79696468
large pool 4194304
表x$kghlu可以查看shared pool中的LRU列表,x$kghlu One-row summary of LRU statistics for the shared pool
sys@ANBOB> select
2 indx,
3 kghlurcr,
4 kghlutrn,
5 kghlufsh,
6 kghluops,
7 kghlunfu,
8 kghlunfs
9 from
10 sys.x$kghlu;
INDX KGHLURCR KGHLUTRN KGHLUFSH KGHLUOPS KGHLUNFU KGHLUNFS
---------- ---------- ---------- ---------- ---------- ---------- ----------
0 217 446 0 32136 0 0
1 777 981 13448 52258 0 0
2 3871 5868 0 28571 1 540
3 0 0 0 0 0 0
oracle server memory is managed by a generic heap manager, when a user asks for a chunk of space , that chunk comes from a particula heap,the user can ask for space that is permanently allocated in the heap or for space that can be freed and reused.
when a chunk of space that may be freed is allocated from a heap,it is possible to specify that the contents of the chunk are recreatable . if this option is specified,then the chunk of space can be explicitly unpinned when not in use.
when a user requests space from a heap and no more space is available , the heap manager can use a callback routine to request that the owner of an unpinned,recreatable chunk of space free the chunk,Unpinned chunks of space are kept on an LRU list so that the heap manager can determine whice one to try to free up first.a heap is composed of a set of contiguous chunks of space called extents. when the users asks for a chunk of space from a heap , the heap manager looks in the set of extents contained in the heap for an unused piece of space of the requested size.if one canot be found ,then the heap manager uses a callback to request a new extent,and adds it to the heap.
a chunk from ane heap may contain another heap;this is known as a subheap The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms.
Oracle also flushes a shared SQL area from the shared pool in these circumstances:
When the ANALYZE statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool. The next time a flushed statement is run, the statement is parsed in a new shared SQL area to reflect the new statistics for the schema object.
If a schema object is referenced in a SQL statement and that object is later modified in any way, the shared SQL area is invalidated (marked invalid), and the statement must be reparsed the next time it is run.
If you change a database’s global database name, all information is flushed from the shared pool.
The administrator can manually flush all information in the shared pool to assess the performance (with respect to the shared pool, not the data buffer cache) that can be expected after instance startup without shutting down the current instance. The statement ALTER SYSTEM FLUSH SHARED_POOL is used to do this.
Allocation Classes Viewed in X$KSMSP
Allocation Classes
The column KSMCHCLS may take the following values:
- • free: free memory
• Freeabl: freeable chunk
• recr: re-creatable chunk
• perm: permanent chunk
• mark: freeable chunk that is associated with a mark
• R-free: free memory belonging to the reserved list
• R-freea: freeable chunk belonging to the reserved list
• R-recr: re-creatable chunk belonging to the reserved list
• R-perm: permanent chunk belonging to the reserved list
The Reserved Pool
- •The reserved pool is actually part of the sharedpool.
• It is sized with SHARED_POOL_RESERVED_SIZE.
• It defaults to 5% of SHARED_POOL_SIZE.
• It is reserved for chunks that are larger than a threshold.
• The threshold can be set with the parameter _SHARED_POOL_RESERVED_MIN_ALLOC.
• The threshold defaults to 5000 bytes.
• This area is often not used and therefore wasted.
Given the previous algorithms to find suitable chunks of available memory, it is clear that a request for a very large chunk may result in the release of many areas that are frequently used. This situation is undesirable and very bad for performance. To prevent it, the Oracle server uses the reserved list.
The Oracle server reserves part of the shared pool to be used for large memory allocations only. The size of this area defaults to 5% of the total size of the shared pool. It can be set explicitly with the parameter SHARED_POOL_RESERVED_SIZE. Only memory requests for chunks that are larger than the threshold are allocated from the reserved pool. Most of the memory allocation requests are less than 5000 bytes in size.
This is also the default value for the threshold. This value can be changed with the parameter _SHARED_POOL_RESERVED_MIN_ALLOC, but it is not recommended to change this value.
Actually, the entire reserved pool could very well be a waste of space. If the database has been running for a while at peak load and the reserved pool has not been used, it is probably a good idea to get rid of it and allow normal memory allocations to use that area.
微信扫一扫，打赏作者吧～
目前这篇文章有1条评论(Rss)评论关闭。