[20210412]分析会话占用的共享内存段.txt

来源：这里教程网时间：2026-03-03 16:36:56 作者：

[20210412]分析会话占用的共享内存段.txt --//以前学习oracle，总是提到设置过大session_cached_cursors值会导致使用shared pool消耗过大。 --//总是想了解每个回话到底会消耗多少共享内存段，限于自己当时的能力，一直没做这方面的探究。 --//看了一些资料，做一些探究，首先说说明受限我的学习环境，对于许多东西完全是一知半解，一些可能仅仅是我的猜测。 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production --//建立执行脚本aa.sql $ echo 'alter session set session_cached_cursors=200;' > aa.sql $ echo set term off >> aa.sql $ seq 200 | xargs -IQ echo 'select * from dept where deptno=Q;' >> aa.sql $ echo set term on >> aa.sql --//以scott用户登陆，不执行任何命令。我的测试环境很干净，很容易通过别的方式确定回话的SID=142。 2.分析： --//首先每个共享内存段的使用在x$ksmsp上都有记录,而回话占用shared pool的chunk在x$ksmsp.KSMCHCOM标识为KKSSP^NNNN,其中的NNNN表示为 --//回话的SID。 --//以sys用户登陆打开新的session 2： SYS@book> select * from x$ksmsp where ksmchcom='KKSSP^142' ; ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FECF9A579B8 930 1 1 1 KKSSP^142 000000007EA43E78 2136 freeabl 0 000000007CB7C610 00007FECF9A47388 1638 1 1 1 KKSSP^142 000000007E34ED88 2136 freeabl 0 000000007CB7C610 00007FECF9A33B30 3551 1 1 1 KKSSP^142 000000007DFBFA70 2136 freeabl 0 000000007CB7C610 00007FECF9A22750 4206 1 1 1 KKSSP^142 000000007DEA4568 2136 freeabl 0 000000007CB7C610 00007FECF99F5670 4987 1 1 1 KKSSP^142 000000007DD67DC0 2136 freeabl 0 000000007CB7C610 00007FECF99FD6D8 5356 1 1 1 KKSSP^142 000000007DCC7028 2136 freeabl 0 000000007CB7C610 00007FECF9A01BB0 5527 1 1 1 KKSSP^142 000000007DC89BD8 2136 freeabl 0 000000007CB7C610 00007FECF9A85408 5684 1 1 1 KKSSP^142 000000007DC5E1A8 2136 freeabl 0 000000007CB7C610 00007FECF9A853B0 5685 1 1 1 KKSSP^142 000000007DC5D950 2136 freeabl 0 000000007CB7C610 00007FECF9A8AB60 5894 1 1 1 KKSSP^142 000000007DBBEA70 2136 freeabl 0 000000007CB7C610 00007FECF9BFE5E8 9545 1 1 1 KKSSP^142 000000007D54F2B8 2136 freeabl 0 000000007CB7C610 00007FECF9BBE3D8 12311 1 1 1 KKSSP^142 000000007CF9FAE0 2136 freeabl 0 000000007CB7C610 00007FECF9BC0098 12413 1 1 1 KKSSP^142 000000007CF565A0 2136 freeabl 0 000000007CB7C610 00007FECF9B9DE68 12924 1 1 1 KKSSP^142 000000007CE26358 2136 freeabl 0 000000007CB7C610 00007FECF9B9E590 12996 1 1 1 KKSSP^142 000000007CE06170 2136 freeabl 0 000000007CB7C610 00007FECF9C994C0 13457 1 1 1 KKSSP^142 000000007CD269F0 2136 freeabl 0 000000007CB7C610 00007FECF9C9D418 13644 1 1 1 KKSSP^142 000000007CCD5A08 2136 recr 4095 000000007CB7C610 00007FECF9B41160 14527 1 1 1 KKSSP^142 000000007CAC1268 2136 freeabl 0 000000007CB7C610 00007FECF9AE1948 15562 1 1 1 KKSSP^142 000000007C8C8C80 2136 freeabl 0 000000007CB7C610 00007FECF9AE12C0 15581 1 1 1 KKSSP^142 000000007C8BAAC8 2136 freeabl 0 000000007CB7C610 00007FECF99F2200 17352 1 1 1 KKSSP^142 000000007C497630 2136 freeabl 0 000000007CB7C610 00007FECF9ACC710 18602 1 1 1 KKSSP^142 000000007C1F64B8 2136 freeabl 0 000000007CB7C610 00007FECF9ACC450 18610 1 1 1 KKSSP^142 000000007C1F2CA0 2136 freeabl 0 000000007CB7C610 00007FECF9B12DE8 19615 1 1 1 KKSSP^142 000000007C00B5E0 12352 R-freea 0 000000007CB7C610 00007FECF999BF00 20724 1 1 1 KKSSP^142 000000007BE3D5E0 2136 freeabl 0 000000007CB7C610 00007FECF9B02960 20990 1 1 1 KKSSP^142 000000007BDD26D0 2136 freeabl 0 000000007CB7C610 00007FECF99B9BE8 21460 1 1 1 KKSSP^142 000000007BCD0E38 2136 freeabl 0 000000007CB7C610 00007FECF99BBE28 21546 1 1 1 KKSSP^142 000000007BC95670 2136 freeabl 0 000000007CB7C610 00007FECF99A3DF0 21621 1 1 1 KKSSP^142 000000007BC6CF68 2136 freeabl 0 000000007CB7C610 29 rows selected. --//你可以发现KSMCHPAR值都是一样的，相当于堆的描述符。通过它查询结果集也是一样的。 SYS@book> select count(*) from x$ksmsp where KSMCHPAR=hextoraw('000000007CB7C610'); COUNT(*) ---------- 29 SYS@book> select sum(KSMCHSIZ) from x$ksmsp where ksmchcom='KKSSP^142' ; SUM(KSMCHSIZ) ------------- 72160 --//做一个转储分析： SYS@book> @ tix New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0001.trc SYS@book> oradebug setmypid Statement processed. SYS@book> ORADEBUG DUMP HEAPDUMP_ADDR 1 0x000000007CB7C610 Statement processed. SYS@book> ORADEBUG help DUMP DUMP <dump_name> <lvl> [addr] Invoke named dump --//简单说明ORADEBUG DUMP HEAPDUMP_ADDR后面的参数lvl=1,仅仅显示chunk，并不转储chunk内容。lvl=2,加上转储chunk的内容。 $ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0001.trc -- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com ) Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason ---------- ------- ------------ ----------------- ----------------- ----------------- 17408 68 256 , KKSSP^142, freeable, kgllk 13944 249 56 , KKSSP^142, freeable, kglseshtSegs 12312 1 12312 , KKSSP^142, freeable, kglseshtTable 9472 37 256 , KKSSP^142, freeable, kglpn 8064 252 32 , KKSSP^142, freeable, kglseshtSegs 2560 10 256 , KKSSP^142, freeable, KQR ENQ 1840 1 1840 , KKSSP^142, free, 1536 6 256 , KKSSP^142, freeable, kglll 1064 7 152 , KKSSP^142, free, 760 1 760 , KKSSP^142, freeable, kglss 504 7 72 , KKSSP^142, freeable, kglseshtSegs 480 10 48 , KKSSP^142, free, 304 1 304 , KKSSP^142, perm, perm 288 1 288 , KKSSP^142, freeable, kgllk 160 4 40 , KKSSP^142, freeable, kglseshtSegs 96 1 96 , KKSSP^142, perm, perm 80 1 80 , KKSSP^142, perm, perm 56 1 56 , KKSSP^142, free, 48 1 48 , KKSSP^142, freeable, kglsesht 3.继续： --//session 1: --//执行aa.sql脚步5次以上。 SCOTT@book> @ aa.sql Session altered. --//session 2： SYS@book> select count(*) from x$ksmsp where KSMCHPAR=hextoraw('000000007CB7C610'); COUNT(*) ---------- 65 SYS@book> select sum(KSMCHSIZ) from x$ksmsp where ksmchcom='KKSSP^142' ; SUM(KSMCHSIZ) ------------- 149080 SYS@book> @ tix New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0002.trc SYS@book> ORADEBUG DUMP HEAPDUMP_ADDR 1 0x000000007CB7C610 Statement processed. $ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0002.trc -- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com ) Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason ---------- ------- ------------ ----------------- ----------------- ----------------- 91392 357 256 , KKSSP^142, freeable, kgllk 13944 249 56 , KKSSP^142, freeable, kglseshtSegs 12312 1 12312 , KKSSP^142, freeable, kglseshtTable 9472 37 256 , KKSSP^142, freeable, kglpn 8064 252 32 , KKSSP^142, freeable, kglseshtSegs 2560 10 256 , KKSSP^142, freeable, KQR ENQ 2064 43 48 , KKSSP^142, free, 1584 1 1584 , KKSSP^142, free, 1536 6 256 , KKSSP^142, freeable, kglll 1064 7 152 , KKSSP^142, free, 760 1 760 , KKSSP^142, freeable, kglss 504 7 72 , KKSSP^142, freeable, kglseshtSegs 304 1 304 , KKSSP^142, perm, perm 288 1 288 , KKSSP^142, freeable, kgllk 224 4 56 , KKSSP^142, free, 160 4 40 , KKSSP^142, freeable, kglseshtSegs 96 1 96 , KKSSP^142, perm, perm 80 1 80 , KKSSP^142, perm, perm 48 1 48 , KKSSP^142, freeable, kglsesht 4.对比分析: $ diff -Nur <(./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0001.trc) \ <(./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0002.trc) --- /dev/fd/63 2021-04-12 09:23:26.407242026 +0800 +++ /dev/fd/62 2021-04-12 09:23:26.407242026 +0800 @@ -3,23 +3,23 @@ Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason ---------- ------- ------------ ----------------- ----------------- ----------------- - 17408 68 256 , KKSSP^142, freeable, kgllk + 91392 357 256 , KKSSP^142, freeable, kgllk 13944 249 56 , KKSSP^142, freeable, kglseshtSegs 12312 1 12312 , KKSSP^142, freeable, kglseshtTable 9472 37 256 , KKSSP^142, freeable, kglpn 8064 252 32 , KKSSP^142, freeable, kglseshtSegs 2560 10 256 , KKSSP^142, freeable, KQR ENQ - 1840 1 1840 , KKSSP^142, free, + 2064 43 48 , KKSSP^142, free, + 1584 1 1584 , KKSSP^142, free, 1536 6 256 , KKSSP^142, freeable, kglll 1064 7 152 , KKSSP^142, free, 760 1 760 , KKSSP^142, freeable, kglss 504 7 72 , KKSSP^142, freeable, kglseshtSegs - 480 10 48 , KKSSP^142, free, 304 1 304 , KKSSP^142, perm, perm 288 1 288 , KKSSP^142, freeable, kgllk + 224 4 56 , KKSSP^142, free, 160 4 40 , KKSSP^142, freeable, kglseshtSegs 96 1 96 , KKSSP^142, perm, perm 80 1 80 , KKSSP^142, perm, perm - 56 1 56 , KKSSP^142, free, 48 1 48 , KKSSP^142, freeable, kglsesht --//主要变化集中在kgllk。可以发现每个chunk大小256

编辑推荐：

返回列表

[20210412]分析会话占用的共享内存段.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推