一、先说dump命令
ALTER SESSION SET EVENTS
'immediate trace name heapdump level level';
Levels are:
Level Description
1 PGA summary
2 SGA summary
4 UGA summary
8 Callheap (Current)
16 Callheap (User)
32 Large pool
64 Streams pool
128 Java pool
1025 PGA with contents
2050 SGA with contents
4100 UGA with contents
8200 Callheap with contents (Current)
16400 Callheap with contents (User)
32800 Large pool with contents
65600 Streams pool with contents
131200 Java pool with contents
二、dump sga
SQL> alter session set events 'immediate trace name heapdump level 2';
Session altered.
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- -------------------------- --------------------------------------------------------------------------------
1 Default Trace File /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6092.trc
三、第一段：
*** 2023-03-08T16:44:58.205699+08:00
KGH Latch Directory Information
ldir state: 2 rover: 146
Slot [ 1] Latch: 0x74570630 Index: 1 Flags: 3 State: 2 next: 0x7f5511b0
Slot [ 2] Latch: 0x74570340 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 3] Latch: 0x74570050 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 4] Latch: 0x7456fd60 Index: 1 Flags: 3 State: 2 next: 0x7f551810
Slot [ 5] Latch: 0x7456fa70 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 6] Latch: 0x7456f780 Index: 1 Flags: 3 State: 2 next: 0x7f550db0
...
Slot [161] Latch: 0x7d43d260 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [162] Latch: 0x7d43d180 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [163] Latch: 0x7f531b48 Index: 1 Flags: 3 State: 2 next: (nil)
网上很多人说，这部分记录的是shared pool中的latch信息。每个latch的具体信息可以通过视图V$LATCH、V$LATCH_PARENT、V$LATCH_CHILDREN或者表x$ksllt查出。
v$latch_children 记录每条拥有子latch 的latch 信息
v$latch_parent 记录没有自latch 的latch。
SQL> select count(*) from v$latch_children;
COUNT(*)
----------
17195
SQL> select count(*) from v$latch;
COUNT(*)
----------
983
检查发现latch和这部分对不上。当然，查看数据库有几个子池也可以通过v$latch_children.name=‘shared pool’，观察其中的get_miss数，会有七行，但是使用几个子池就会有几个子池的数据有变化。这个get_miss数也可以用于判断shared_pool大小是否合适。
Yong Huang大佬在itpub有一段回复
That information is NOT the same as KGH Latch Directory. The latch directory should have much fewer entries than the count of rows in v$latch_children.
That is, not all child latches are assigned to the directory at the same time.
It appears to be created at the instance startup time when SGA is created (function ksmcsg calls kghlatch_dir_init), and later at various times,
some functions call kghlatch_register to register individual latches (such as "row cache child latch") into the directory。
The size of the directory is internally determined, not affected by cpu_count (at least not before 10.2.0.4), while the number of library cache
latches *is* determined by cpu_count (unless explicitly set with _kgl_latch_count). When you have too many CPU's, you hit the limit of latch directory size.
See bugs 5888835, 7115828, etc, documented in Notes 428226.1 and 852346.1.
The latch directory shown in SGA heap dump shows all the slots in the directory, latch address (matching that in v$latch_children or v$latch(_parent)), index of
latch-specific object the latch covers.
To summarize, the latch directory shown in your trace file is not visible from any v$ or x$ table as far as we know. The address (right after "Latch: ") matches the address
in v$latch or v$latch_children. Since the latch directory shows only a subset of the latches in v$latch_children, there may be some significance in placing a latch in the directory
versus not putting it in the directory. We don't know what this significance is.
所以不用太关注他
四、第二段
第一部分
HEAP DUMP heap name="sga heap" desc=0x60146360
extent sz=0xfe0 alt=336 het=32767 rec=9 flg=0x82 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0x0 heap=(nil)
fl2=0x64, nex=(nil), idx=0
pdb id=0, src pdb id=0
ds for latch 1: 0x60147cb8 0x6014c5c0
reserved granule count 11 (granule size 16777216) -->16M -->16*11=176M
RESERVED EXTENTS
0x6b000000 0x6a000000 0x69000000 0x68000000
0x67000000 0x66000000 0x65000000 0x64000000
0x63000000 0x62000000 0x61000000
Counted elements on list: 11
这是堆dump信息的头部，heap name说明了内存所述的堆，shared pool是属于SGA区的，因此，这里是"sga heap"。desc=0x60146360堆描述符地址
extent sz段的大小。sz=0xfe0–>4064–>距离4096缺少32，很奇怪的数字。
第二部分
HEAP DUMP heap name="sga heap(1,0)" desc=0x60147cb8
extent sz=0xfe0 alt=336 het=32767 rec=9 flg=0x82 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0x1000000 heap=(nil)
fl2=0x24, nex=(nil), idx=1, dsxvers=1, dsxflg=0x0
dsx first ext=0x7f000000
dsx empty ext bytes=0 subheap rc link=0x7f0000d0,0x7f0000d0
dsx heap size=268433792, dsx heap depth=0
pdb id=0, src pdb id=0
latch set 1 of 1
durations enabled for this heap
reserved granules for root 11 (granule size 16777216)
其中sga heap(1,0)第一个数字表示子池第一个子堆，也可以称为子池，由参数_kghdsidx_count直接控制。 _kghdsidx_count的最大允许值是7 即最多 7个 shared pool subpool。目前看默认数量应该受sga_target参数控制。每个子池由X$KGHLU视图管理。下面测试一下
SQL> SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND TRANSLATE (ksppinm, '_', '#') LIKE '%kghdsidx_count%';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ ------------------------------ ------------------------------
_kghdsidx_count 1 max kghdsidx count
SQL> alter system set "_kghdsidx_count"=6 scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 520093696 bytes
Database Buffers 1862270976 bytes
Redo Buffers 6959104 bytes
Database mounted.
Database opened.
SQL> SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND TRANSLATE (ksppinm, '_', '#') LIKE '%kghdsidx_count%';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ ------------------------------ ------------------------------
_kghdsidx_count 6 max kghdsidx count
SQL> alter system set events 'immediate trace name heapdump level 2';
System altered.
[oracle@19c01 ~]$ grep "sga heap" /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_27867.trc
HEAP DUMP heap name="sga heap" desc=0x60146330
HEAP DUMP heap name="sga heap(1,0)" desc=0x60147c88
HEAP DUMP heap name="sga heap(1,3)" desc=0x6014c590
HEAP DUMP heap name="sga heap(2,0)" desc=0x6015c3b0
HEAP DUMP heap name="sga heap(2,3)" desc=0x60160cb8
HEAP DUMP heap name="sga heap(3,0)" desc=0x60170ad8
HEAP DUMP heap name="sga heap(3,3)" desc=0x601753e0
HEAP DUMP heap name="sga heap(4,0)" desc=0x60185200
HEAP DUMP heap name="sga heap(4,3)" desc=0x60189b08
HEAP DUMP heap name="sga heap(5,0)" desc=0x60199928
HEAP DUMP heap name="sga heap(5,3)" desc=0x6019e230
HEAP DUMP heap name="sga heap(6,0)" desc=0x601ae050
HEAP DUMP heap name="sga heap(6,3)" desc=0x601b2958
现在来看第二个数字。这些子堆中的每一个都被分成一定的范围。请注意上面的字段 xsz=0x1000000，它是一个范围的大小，将 xsz=0x1000000 转换为十进制表示 extent 大小为 16MB。这个16MB由参数_ksmg_granule_size控制，
这个子堆中有 20 个这样的区段。20166=1920M
SQL> show parameter sga
sga_target big integer 2304M
[oracle@19c01 ~]$ grep "EXTENT " /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6092.trc
EXTENT 0 addr=0x6d000000
EXTENT 1 addr=0x6f000000
EXTENT 2 addr=0x71000000
EXTENT 3 addr=0x72000000
EXTENT 4 addr=0x74000000
EXTENT 5 addr=0x75000000
EXTENT 6 addr=0x76000000
EXTENT 7 addr=0x77000000
EXTENT 8 addr=0x78000000
EXTENT 9 addr=0x79000000
EXTENT 10 addr=0x7a000000
EXTENT 11 addr=0x7b000000
EXTENT 12 addr=0x7c000000
EXTENT 13 addr=0x7d000000
EXTENT 14 addr=0x7e000000
EXTENT 15 addr=0x7f000000
EXTENT 0 addr=0x6c000000
EXTENT 1 addr=0x6e000000
EXTENT 2 addr=0x70000000
EXTENT 3 addr=0x73000000
SQL> SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND TRANSLATE (ksppinm, '_', '#') LIKE '%ksmg_granule_size%';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ ------------------------------ ------------------------------
_ksmg_granule_size 16777216 granule size in bytes
sga heap(1,0) sga heap(1,1) sga heap(1,2) sga heap(1,3),每个子池会有4个子分区。这里有个地方
Riyaj大佬说
Notice that all “sga heap(1,0)” chunks has allocation comment as “perm”. Allocation comment “perm” is passed for permanent chunks. In a nutshell, all permanent chunks are allocated from first mini-heap
in each of these sub heaps i.e. sga heap(1,0), sga heap(2,0) and sga heap(3,0) and so on. They are only allocated in the first mini-heap and not in any other mini-heap in these sub-heaps. Another example:
PL/SQL DIANA type chunks are allocated only in fourth mini-sub-heap (1,3) (2,3) and (3,3) [in this instance].
所有“sga heap(1,0)”块的分配注释都是“perm”。为永久块传递分配注释“perm”。简而言之，所有永久块都是从每个子堆中的第一个迷你堆分配的，即 sga heap(1,0)、sga heap(2,0) 和 sga heap(3,0) 等等。它们只分配在第一个小堆中，
而不分配在这些子堆中的任何其他小堆中。另一个例子：PL/SQL DIANA 类型块仅分配在第四个微型子堆 (1,3) (2,3) 和 (3,3)。咱们观察一下
[oracle@19c01 unix]$ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_27867.trc |grep 'perm'
15865432 1 15865432 , sga heap(5,0), perm, perm
15760632 1 15760632 , sga heap(2,0), perm, perm
15683144 1 15683144 , sga heap(1,0), perm, perm
15428072 1 15428072 , sga heap(3,0), perm, perm
15394000 1 15394000 , sga heap(2,0), perm, perm
14684856 1 14684856 , sga heap(4,0), perm, perm
14285888 1 14285888 , sga heap(3,0), perm, perm
14048080 1 14048080 , sga heap(1,0), perm, perm
13354552 1 13354552 , sga heap(6,0), perm, perm
11848664 1 11848664 , sga heap(6,0), perm, perm
11041528 1 11041528 , sga heap(4,0), perm, perm
10433832 1 10433832 , sga heap(5,0), perm, perm
7657472 1 7657472 , sga heap(3,0), perm, perm
7340168 1 7340168 , sga heap(2,0), perm, perm
7205112 1 7205112 , sga heap(1,0), perm, perm
3976088 1 3976088 , sga heap(6,0), perm, perm
120 1 120 , sga heap(6,3), perm, perm
120 1 120 , sga heap(6,0), perm, perm
120 1 120 , sga heap(5,3), perm, perm
120 1 120 , sga heap(5,0), perm, perm
120 1 120 , sga heap(4,3), perm, perm
120 1 120 , sga heap(4,0), perm, perm
120 1 120 , sga heap(3,3), perm, perm
120 1 120 , sga heap(3,0), perm, perm
120 1 120 , sga heap(2,3), perm, perm
120 1 120 , sga heap(2,0), perm, perm
120 1 120 , sga heap(1,3), perm, perm
120 1 120 , sga heap(1,0), perm, perm
观察发现sga heap(1,3)分配了一个120bytes的一个chunk,Riyaj大佬用10g测试，这里测试版本测试为19c，看来有了新机制。同时也观察发现大部分perm类型都集中在0号迷你堆中。其它堆占用perm很小。同时他说
Drawback is that these chunks are contained in that mini-heap. For example, let’s say that chunks with permanent duration must be allocated and there is no free space in the first mini-heap
( say sga heap(1,0) ) and if a new extent can’t be added to that mini-heap then ORA-4031 error is thrown even if there is plenty of free space in some other mini-heap ( say sga heap(1,1),
sga heap(1,2) etc). So permanent chunks will be allocated only from sga heap(N, 0) [ where N is sub-heap id 1, 2,3… ] and if that mini-heap runs out of space, ORA-4031 will be thrown.
This is why client encountered ORA-4031 errors. Even though there was plenty of free memory in other pools, simply permanent chunks can not be allocated in the first mini-heap leading to
ORA-4031 errors. Of course, other chunks in that mini-heap can not be deallocated either since those chunks are also permanent chunks. Chances of these errors occurring in other sub-heaps
such as sga heap(N,1), sga heap(N,2) etc are less since recreatable/freeable chunks can be flushed to accommodate incoming requests. Quick resolution was to increase shared pool_size temporarily
(until we can reduce perm chunk usage due to another issue, which will be resolved soon).
假设必须分配具有永久持续时间的块，并且第一个迷你堆（例如 sga heap(1,0) ）中没有可用空间，并且如果无法将新范围添加到该迷你堆那么即使在其他一些小堆（比如 sga heap(1,1)、sga heap(1,2) 等）中有足够的可用空间，
也会抛出 ORA-4031 错误。因此，永久块将仅从 sga heap(N, 0) [其中 N 是子堆 id 1, 2,3…] 分配，如果该迷你堆空间不足，将抛出 ORA-4031.
也就是说oracle按照持续时间在各个子堆中分配内存，由参数_enable_shared_pool_durations控制
SQL> SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND TRANSLATE (ksppinm, '_', '#') LIKE '%_enable_shared_pool_durations%';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ ------------------------------ ----------------------------------------
_enable_shared_pool_durations TRUE temporary to disable/enable kgh policy
测试一下这个参数
SQL> alter system set "_enable_shared_pool_durations"=false scope=spfile;
System altered.
SQL> SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND TRANSLATE (ksppinm, '_', '#') LIKE '%kghdsidx_count%';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ ------------------------------ ------------------------------
_kghdsidx_count 6 max kghdsidx count
[oracle@19c01 unix]$ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_33437.trc
-- Heapdump Analyzer v1.03 by Tanel Poder ( https://blog.tanelpoder.com )
Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason
------------ ------- ------------ ----------------- ----------------- -----------------
15865432 1 15865432 , sga heap(3,0), perm, perm
15760632 1 15760632 , sga heap(6,0), perm, perm
15737072 1 15737072 , sga heap(5,0), perm, perm
15422616 1 15422616 , sga heap(1,0), perm, perm
15395536 1 15395536 , sga heap(6,0), perm, perm
14684856 1 14684856 , sga heap(2,0), perm, perm
14302976 1 14302976 , sga heap(1,0), perm, perm
13462304 1 13462304 , sga heap(5,0), perm, perm
13354552 1 13354552 , sga heap(4,0), perm, perm
11848664 1 11848664 , sga heap(4,0), perm, perm
10255952 1 10255952 , sga heap(3,0), perm, perm
9678696 1 9678696 , sga heap(2,0), perm, perm
9437976 9 1048664 , sga heap(4,0), freeable, SO private sga
7280912 1 7280912 , sga heap(3,0), perm, perm
7113512 1 7113512 , sga heap(2,0), perm, perm
4190416 1 4190416 , sga heap(4,0), recreatPT, KSFD SGA I/O b
3976088 1 3976088 , sga heap(4,0), perm, perm
3924656 1 3924656 , sga heap(5,0), perm, perm
3870400 1 3870400 , sga heap(1,0), perm, perm
2766512 1 2766512 , sga heap(6,0), perm, perm
2569712 1 2569712 , sga heap(5,0), free,
2326632 1 2326632 , sga heap(2,0), free,
2096952 1 2096952 , sga heap(6,0), R-free,
2096952 1 2096952 , sga heap(5,0), R-free,
2096952 1 2096952 , sga heap(4,0), R-free,
2096952 1 2096952 , sga heap(3,0), R-free,
2096952 1 2096952 , sga heap(2,0), R-free,
2096952 1 2096952 , sga heap(1,0), R-free,
2088160 2 1044080 , sga heap(4,0), freeable, SO private sga
1622104 1 1622104 , sga heap(4,0), free,
1169144 1 1169144 , sga heap(3,0), free,
1048544 1 1048544 , sga heap(4,0), recreatPT, SO private sga
1039496 1 1039496 , sga heap(4,0), freeable, SO private sga
果然如此，没有其它的迷你堆了。看起来这些微型堆在 Oracle 内部资源中被正式称为持续时间。每个子堆中正好有 4 个持续时间，每个持续时间都有自己的空闲列表。x$ksmsp.ksmchdur 列指示该块的持续时间。
五、第三部分
EXTENT 0 addr=0x6d000000
Chunk 06d000068 sz= 48 R-stopper "reserved stoppe"
Chunk 06d000098 sz= 839480 R-free " "
Chunk 06d0ccfd0 sz= 48 R-stopper "reserved stoppe"
Chunk 06d0cd000 sz= 7921912 perm "perm " alo=212264
Chunk 06d85b0f8 sz= 4738608 free " "
Chunk 06dcdff28 sz= 2072 freeable "parameter table"
Chunk 06dce0740 sz= 2072 freeable "parameter table"
Chunk 06dce0f58 sz= 2072 freeable "parameter table"
Chunk 06dce1770 sz= 2072 freeable "parameter table"
Chunk 06dce1f88 sz= 2072 freeable "parameter table"
Chunk 06dce27a0 sz= 2072 freeable "parameter table"
Chunk 06dce2fb8 sz= 2072 freeable "parameter table"
Chunk 06dce37d0 sz= 2072 freeable "parameter table"
Chunk 06dce3fe8 sz= 2072 freeable "parameter table"
Chunk 06dce4800 sz= 2072 freeable "parameter table"
Chunk 06dce5018 sz= 2072 freeable "parameter table"
Chunk 06dce5830 sz= 4096 freeable "KGLH0^297ea332 " ds=0x6f9de6f0
Chunk 06dce6830 sz= 816 recreatUT "KQR PO " A08:43:10
Chunk 06dce6b60 sz= 4096 freeableU "KGLH0^955be8a9 " ds=0x6dce9ff0
Chunk 06dce7b60 sz= 4096 recreatPC "KGLH0^3258ad32 "
ds 06dce8bc8 sz= 4096 ct= 1
Chunk 06dce8b60 sz= 504 freeable "KGLDA "
Chunk 06dce8d58 sz= 560 recreatPT "KGLHD "
Chunk 06dce8f88 sz= 4096 recreatUC "KGLH0^955be8a9 " A08:43:10
ds 06dce9ff0 sz= 8192 ct= 2
类型是chunk chunk地址，大小，类型，分配原因
free：即空闲chunk，可以随时分配给适合大小的请求；
freeable：这种状态的chunk表示它当前正在被使用，但是这种使用是短期的，比如在一次调用中或者一个会话中，会话或者调用解释就可以被释放出来。这种状态的chunk是不放在LRU链表中的，一旦使用结束，自动成为free状态，放到空闲列表中；
recreatable：这种状态的chunk正在被使用，但是它所包含的对象是可以被暂时移走、重建，比如解析过的语句。它是被LRU链表管理的。
permanent：顾名思义，这种状态的chunk所包含的对象是永远不会被释放的。即使flush shared pool也不会释放。
R开头的reserved_pool池chunk
ds 06fd34c88 sz= 20480 ct= 5
06cf9eac0 sz= 4096
06cf9fac0 sz= 4096
06cfa0ac0 sz= 4096
06cfa1ac0 sz= 4096
ds表示堆描述符，也就是此处是一个子堆，sz=20480刚好是下面5个chunk的总大小，ct=5表示有五个chunk
六、查看 reserved_pool chunk 分配
select * from x$ksmspr;
ADDR INDX INST_ID CON_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- -------------------- ---------------- ---------- ---------------- ---------- ----------------
00007F2ABE8F0C30 0 1 1 reserved stoppe 000000006D0CCFD0 48 R-freea 0 00
00007F2ABE8F0BD8 1 1 1 free memory 000000006D000098 839480 R-free 0 00
00007F2ABE8F0B80 2 1 1 reserved stoppe 000000006D000068 48 R-freea 0 00
00007F2ABE8F0B28 3 1 1 reserved stoppe 000000006B0CCFD0 48 R-freea 0 00
00007F2ABE8F0AD0 4 1 1 free memory 000000006B000098 839480 R-free 0 00
00007F2ABE8F0A78 5 1 1 reserved stoppe 000000006B000068 48 R-freea 0 00
00007F2ABE8F0A20 6 1 1 reserved stoppe 000000006A0CCFD0 48 R-freea 0 00
00007F2ABE8F09C8 7 1 1 free memory 000000006A000098 839480 R-free 0 00
00007F2ABE8F0970 8 1 1 reserved stoppe 000000006A000068 48 R-freea 0 00
00007F2ABE8F0918 9 1 1 reserved stoppe 00000000680CCFD0 48 R-freea 0 00
00007F2ABE8F08C0 10 1 1 free memory 0000000068000098 839480 R-free 0 00
00007F2ABE8F0868 11 1 1 reserved stoppe 0000000068000068 48 R-freea 0 00
00007F2ABE8F0810 12 1 1 reserved stoppe 00000000660CCFD0 48 R-freea 0 00
00007F2ABE8F07B8 13 1 1 free memory 0000000066000098 839480 R-free 0 00
KSMCHCLS类型全部为R-开头，是保留池内存分配使用情况。
七、查看子池分配、
SQL> select * from x$ksmss;
ADDR INDX INST_ID CON_ID KSMSSLEN KSMSSNAM KSMDSIDX KSMSSGBL
---------------- ---------- ---------- ---------- ---------- ---------------------------------------------------- ---------- ------------
00007F42806AE408 0 1 0 33554432 free memory 0 TRUE
00007F42806AE408 1 1 0 0 miscellaneous 0 TRUE
00007F42806AE408 2 1 0 14565968 free memory 1 TRUE
00007F42806AE408 3 1 0 0 miscellaneous 1 TRUE
00007F42806AE408 4 1 1 141728 kcbi io desc slot 1 TRUE
00007F42806AE408 5 1 1 40 ksm_obc_path 1 TRUE
00007F42806AE408 6 1 1 262144 object temp hash buckets 1 TRUE
00007F42806AE408 7 1 1 912 parameter value memory 1 TRUE
00007F42806AE408 8 1 1 39672 db_files 1 TRUE
KSMDSIDX表示子池号
SQL> select ksmdsidx, sum(ksmsslen) from x$ksmss group by ksmdsidx;
KSMDSIDX SUM(KSMSSLEN)
---------- -------------
6 83886080
1 67108864
2 83886080
4 67108864
5 67108864
3 67108864
0 33554432
7 rows selected.
查看空闲内存
SQL> select ksmssnam, sum(ksmsslen) from x$ksmss where ksmdsidx=0 group by ksmssnam order by 2 ;
KSMSSNAM SUM(KSMSSLEN)
---------------------------------------------------- -------------
miscellaneous 0
free memory 33554432
八、共享池分配
select * from x$ksmsp;
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------------- ---------- ---------------- ---------- ----------------
00007F427F0B9EF0 34047 1 1 3 1 KGLH0^2224358a 000000007BE87E68 4096 recr 4095 000000007BE88ED0
00007F427F0B9E98 34048 1 1 3 1 KQR PO 000000007BE87A38 1072 recr 544 00
00007F427F0B9E40 34049 1 1 3 1 KGLHD 000000007BE87708 816 recr 80 00
00007F427F0B9DE8 34050 1 1 3 1 KGLHD 000000007BE873D8 816 recr 80 00
测试一下共享池和会话的关系
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE CON_ID
---------- ---------- ---------- ----------
257 0 0 1
查看k$ksmsp
ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------- -------------------------------- ---------------- ---------- ---------------- ---------- ----------------
00007F5068316448 29947 1 1 4 1 KKSSP^257 0000000074EDD120 2136 recr 4095 00000000664B0660
00007F50686369C8 40333 1 1 4 1 KKSSP^257 0000000065479F60 4096 freeabl 0 00000000664B0660
00007F5068636970 40334 1 1 4 1 KKSSP^257 0000000065478F60 4096 freeabl 0 00000000664B0660
00007F5068636810 40338 1 1 4 1 KKSSP^257 0000000065475860 12352 freeabl 0 00000000664B0660
00007F50686367B8 40339 1 1 4 1 KKSSP^257 0000000065474860 4096 freeabl 0 00000000664B0660
00007F5068636760 40340 1 1 4 1 KKSSP^257 0000000065473860 4096 freeabl 0 00000000664B0660
00007F5068636708 40341 1 1 4 1 KKSSP^257 0000000065472860 4096 freeabl 0 00000000664B0660
00007F50686366B0 40342 1 1 4 1 KKSSP^257 0000000065471860 4096 freeabl 0 00000000664B0660
00007F5068636600 40344 1 1 4 1 KKSSP^257 0000000065470048 4096 freeabl 0 00000000664B0660
00007F50686365A8 40345 1 1 4 1 KKSSP^257 000000006546F048 4096 freeabl 0 00000000664B0660
00007F5068636550 40346 1 1 4 1 KKSSP^257 000000006546E048 4096 freeabl 0 00000000664B0660
00007F50686364F8 40347 1 1 4 1 KKSSP^257 000000006546D048 4096 freeabl 0 00000000664B0660
12 rows selected.
KKSSP^257是会话257号占用，也就是说shared pool中有一部分内存也是按会话分配的，此时dump一下sga
alter session set events 'immediate trace name heapdump level 2'
查看trace日志
Chunk 06546d048 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
Chunk 06546e048 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
Chunk 06546f048 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
Chunk 065470048 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
Chunk 065471048 sz= 2072 freeable "parameter table"
Chunk 065471860 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
Chunk 065472860 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
Chunk 065473860 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
Chunk 065474860 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
Chunk 065475860 sz= 12352 freeable "KKSSP^257 " ds=0x664b0660
Chunk 0654788a0 sz= 816 recrUT001 "KQR PO " B14:06:45
Chunk 065478bd0 sz= 816 recrUT001 "KGLHD " B14:06:45
Chunk 065478f00 sz= 96 free " "
Chunk 065478f60 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
Chunk 065479f60 sz= 4096 freeable "KKSSP^257 " ds=0x664b0660
也可以用tanel poder的脚本过滤
[oracle@19c01 unix]$ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/prodcdb/PRODCDB/trace/PRODCDB_ora_54448.trc |grep 'KKSSP^257'
40960 10 4096 , sga heap(4,0), freeable, KKSSP^257
12352 1 12352 , sga heap(4,0), freeable, KKSSP^257
2136 1 2136 , sga heap(4,0), recrPT001, KKSSP^257
再dump一下KKSSP^257的对描述看下
SQL> ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 2, addr 0x664b0660';
这个看着烦，还是用工具
[oracle@19c01 unix]$ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/prodcdb/PRODCDB/trace/PRODCDB_ora_54520.trc
-- Heapdump Analyzer v1.03 by Tanel Poder ( https://blog.tanelpoder.com )
Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason
------------ ------- ------------ ----------------- ----------------- -----------------
14336 256 56 , KKSSP^257, freeable, kglseshtSegs
12312 1 12312 , KKSSP^257, freeable, kglseshtTable
10000 250 40 , KKSSP^257, freeable, kglseshtSegs
7888 34 232 , KKSSP^257, freeable, kgllk
5104 22 232 , KKSSP^257, freeable, kglpn
1160 5 232 , KKSSP^257, freeable, KQR ENQ
1152 1 1152 , KKSSP^257, freeable, kglss
928 4 232 , KKSSP^257, freeable, kglll
480 5 96 , KKSSP^257, free,
376 1 376 , KKSSP^257, free,
320 5 64 , KKSSP^257, freeable, kglseshtSegs
248 1 248 , KKSSP^257, perm, perm
120 1 120 , KKSSP^257, perm, perm
120 1 120 , KKSSP^257, free,
112 1 112 , KKSSP^257, free,
104 1 104 , KKSSP^257, perm, perm
80 1 80 , KKSSP^257, freeable, kglseshtSegs
56 1 56 , KKSSP^257, free,
48 1 48 , KKSSP^257, freeable, kglsesht
其中kgllk是library cache lock，kglpn是library cache pin。那么和library cache有关，其它的暂时不知道。那么检查一下library cache看下
SQL> select s.sid, username, logon_time
2 ,(select kglnaobj||'('||kglobtyd||')' from x$kglob v
3 where kglhdadr = v.object_handle and rownum=1) kobj_name
4 ,v.*
5 from v$libcache_locks v, v$session s
6 where holding_session = s.saddr
7 and s.sid = 257;
SID USERNAME LOGON_TIME KOBJ_NAME TYPE ADDR HOLDING_USER_SES HOLDING_SESSION OBJECT_HANDLE LOCK_HELD REFCOUNT MODE_HELD MODE_REQUESTED SAVEPOINT_NUMBER CON_ID
---------- ---------- ------------ ------------------------------------------------------------ -------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- -------------- ---------------- ----------
257 SYS 09-MAR-23 IS_VPD_ENABLED(FUNCTION) LOCK 0000000070DD6150 000000007C1C52E8 000000007C1C52E8 000000006F9BA600 00 1 1 0 39199 1
257 SYS 09-MAR-23 DICTIONARY_OBJ_OWNER(FUNCTION) LOCK 0000000070DD6238 000000007C1C52E8 000000007C1C52E8 000000006F9BA2D0 00 1 1 0 39031 1
257 SYS 09-MAR-23 table_1_ff_2fe_0_0_0(CURSOR) LOCK 0000000070DD83D8 000000007C1C52E8 000000007C1C52E8 0000000075CA75E8 00 1 1 0 29472 1
257 SYS 09-MAR-23 table_1_ff_306_0_0_0(CURSOR) LOCK 0000000070DD85A8 000000007C1C52E8 000000007C1C52E8 0000000074BD4870 00 1 1 0 36024 1
257 SYS 09-MAR-23 table_1_ff_302_0_0_0(CURSOR) LOCK 0000000070DD8690 000000007C1C52E8 000000007C1C52E8 000000006FA327D8 00 1 1 0 35835 1
257 SYS 09-MAR-23 table_1_ff_302_0_0_0(CURSOR) LOCK 0000000070DD8D48 000000007C1C52E8 000000007C1C52E8 000000006FA313B0 00 1 1 0 0 1
257 SYS 09-MAR-23 table_1_ff_306_0_0_0(CURSOR) LOCK 0000000070DDAD10 000000007C1C52E8 000000007C1C52E8 0000000074BD3448 00 1 1 0 0 1
257 SYS 09-MAR-23 DBMS_STANDARD(PACKAGE) LOCK 0000000070DDB620 000000007C1C52E8 000000007C1C52E8 000000007294E2C8 00 1 1 0 39086 1
257 SYS 09-MAR-23 table_1_ff_308_0_0_0(CURSOR) LOCK 0000000070E16280 000000007C1C52E8 000000007C1C52E8 0000000070DBD548 00 1 1 0 0 1
257 SYS 09-MAR-23 table_1_ff_308_0_0_0(CURSOR) LOCK 0000000070E16368 000000007C1C52E8 000000007C1C52E8 0000000070DBE970 00 1 1 0 20092 1
257 SYS 09-MAR-23 table_1_ff_304_0_0_0(CURSOR) LOCK 0000000070E164B0 000000007C1C52E8 000000007C1C52E8 0000000074D4A870 00 1 1 0 19908 1
257 SYS 09-MAR-23 table_1_ff_304_0_0_0(CURSOR) LOCK 0000000070E16598 000000007C1C52E8 000000007C1C52E8 0000000074D49448 00 1 1 0 0 1
257 SYS 09-MAR-23 DICTIONARY_OBJ_NAME(FUNCTION) LOCK 0000000070E16CD8 000000007C1C52E8 000000007C1C52E8 0000000068E5A1B0 00 1 1 0 39144 1
257 SYS 09-MAR-23 table_1_ff_300_0_0_0(CURSOR) LOCK 0000000070E47008 000000007C1C52E8 000000007C1C52E8 0000000071E42A08 00 1 1 0 0 1
257 SYS 09-MAR-23 table_1_ff_300_0_0_0(CURSOR) LOCK 0000000070E89308 000000007C1C52E8 000000007C1C52E8 0000000071E43E30 00 1 1 0 19155 1
257 SYS 09-MAR-23 DATABASE(PUB SUB INTERNAL INFORMATION) LOCK 0000000070EAFF00 000000007C1C52E8 000000007C1C52E8 0000000070BBA900 00 1 1 0 39704 1
257 SYS 09-MAR-23 select count(*) from t(CURSOR) LOCK 0000000070EB12A0 000000007C1C52E8 000000007C1C52E8 000000006673EBB8 00 1 1 0 0 1
257 SYS 09-MAR-23 table_1_ff_2fe_0_0_0(CURSOR) LOCK 0000000070EEEC28 000000007C1C52E8 000000007C1C52E8 0000000075CA61C0 00 1 1 0 0 1
257 SYS 09-MAR-23 select count(*) from t(CURSOR) LOCK 0000000070EEEDF8 000000007C1C52E8 000000007C1C52E8 000000006673FFE0 00 1 1 0 43339 1
19 rows selected.
看来会话连接后shared pool会在library为会话分配一部分区域存储sql解析相关的东西。那么断开连接看看
SQL> select s.sid, username, logon_time
2 ,(select kglnaobj||'('||kglobtyd||')' from x$kglob v
3 where kglhdadr = v.object_handle and rownum=1) kobj_name
4 ,v.*
5 from v$libcache_locks v, v$session s
6 where holding_session = s.saddr
7 and s.sid = 257;
no rows selected
会话断开马上消失。难道这就是游标句柄相关的东西。
最后修改时间：2023-03-13 19:14:15
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
