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

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

[20210421]分析会话占用的共享内存段2.txt --//以前学习oracle，总是提到设置过大session_cached_cursors值会导致使用shared pool消耗过大。 --//我的测试并没有消耗多少,仅仅利用以前的模拟出现ora-04031脚本的情况. 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 SCOTT@book> create table a1 ( id1 number,id2 number); Table created. SCOTT@book> alter system set open_cursors=50000 scope=memory ; System altered. --//退出再登录才能生效。 $ cat sharepool/shp7.sql declare msql varchar2(500); mcur number; mstat number; begin for i in 1 .. &&1 loop mcur := dbms_sql.open_cursor; msql := 'select id1 from a1 where id2='||to_char(i); dbms_sql.parse(mcur,msql,dbms_sql.native); -- mstat := dbms_sql.execute(mcur); end loop; end; / --//仅仅分析，不执行，同时由于open_cursors参数的作用，导致占用大量共享内存。 2.测试: --//session 1: SCOTT@book> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 295 7 28288 DEDICATED 28289 21 4 alter system kill session '295,7' immediate; --//session 2: SYS@book> select * from x$ksmsp where ksmchcom='KKSSP^295' ; SYS@book> select count(*) from x$ksmsp where ksmchcom='KKSSP^295' ; --//最好执行多次,避免刷出共享池. --//session 1: SCOTT@book> @ sharepool/shp7 10000 PL/SQL procedure successfully completed. --//session 2: SYS@book> select count(*) from x$ksmsp where ksmchcom='KKSSP^295' ; COUNT(*) ---------- 2968 SYS@book> oradebug setmypid Statement processed. SYS@book> oradebug dump heapdump_addr 1 0x000000007DAB8830 Statement processed. SYS@book> select sum(KSMCHSIZ),avg(KSMCHSIZ),ksmchcom from x$ksmsp where ksmchcom = 'KKSSP^295' group by ksmchcom order by 1 desc; SUM(KSMCHSIZ) AVG(KSMCHSIZ) KSMCHCOM ------------- ------------- ---------------- 6350624 2139.69811 KKSSP^295 $ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_28301.trc -- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com ) Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason ---------- ------- ------------ ----------------- ----------------- ----------------- 2488064 9719 256 , KKSSP^295, freeable, kgllk 2407424 9404 256 , KKSSP^295, freeable, kglll 425600 1400 304 , KKSSP^295, perm, perm 124256 353 352 , KKSSP^295, perm, perm 117248 458 256 , KKSSP^295, freeable, kglpn 116816 2086 56 , KKSSP^295, freeable, kglseshtSegs 103680 405 256 , KKSSP^295, freeable, KQR ENQ 43008 448 96 , KKSSP^295, perm, perm 31640 565 56 , KKSSP^295, free, 30240 105 288 , KKSSP^295, perm, perm 26976 562 48 , KKSSP^295, free, 22400 140 160 , KKSSP^295, perm, perm 22280 557 40 , KKSSP^295, free, 20640 215 96 , KKSSP^295, free, 15872 62 256 , KKSSP^295, perm, perm 14144 52 272 , KKSSP^295, freeable, kglll 13992 159 88 , KKSSP^295, freeable, kglseshtSegs 13664 61 224 , KKSSP^295, perm, perm 12312 1 12312 , KKSSP^295, freeable, kglseshtTable 12240 170 72 , KKSSP^295, freeable, kglseshtSegs 12240 153 80 , KKSSP^295, freeable, kglseshtSegs 12168 39 312 , KKSSP^295, perm, perm 10240 160 64 , KKSSP^295, freeable, kglseshtSegs 7968 249 32 , KKSSP^295, free, 6448 62 104 , KKSSP^295, freeable, kglseshtSegs 6240 15 416 , KKSSP^295, perm, perm 6160 11 560 , KKSSP^295, perm, perm 5824 52 112 , KKSSP^295, freeable, kglseshtSegs 5568 24 232 , KKSSP^295, perm, perm 5520 23 240 , KKSSP^295, perm, perm 5016 33 152 , KKSSP^295, freeable, kglseshtSegs 4896 17 288 , KKSSP^295, freeable, kglll 4608 24 192 , KKSSP^295, perm, perm 4440 15 296 , KKSSP^295, perm, perm 4224 44 96 , KKSSP^295, freeable, kglseshtSegs 4032 9 448 , KKSSP^295, perm, perm 3968 16 248 , KKSSP^295, perm, perm 3744 13 288 , KKSSP^295, freeable, kgllk 3520 8 440 , KKSSP^295, perm, perm 3200 10 320 , KKSSP^295, perm, perm 3168 12 264 , KKSSP^295, freeable, kgllk 2752 43 64 , KKSSP^295, free, 2720 5 544 , KKSSP^295, perm, perm 2400 20 120 , KKSSP^295, freeable, kglseshtSegs 2120 5 424 , KKSSP^295, perm, perm 1920 4 480 , KKSSP^295, perm, perm 1768 17 104 , KKSSP^295, perm, perm 1728 4 432 , KKSSP^295, perm, perm 1728 3 576 , KKSSP^295, perm, perm 1728 24 72 , KKSSP^295, free, 1680 6 280 , KKSSP^295, freeable, kgllk 1632 6 272 , KKSSP^295, freeable, kgllk 1536 12 128 , KKSSP^295, freeable, kglseshtSegs 1440 4 360 , KKSSP^295, perm, perm 1400 5 280 , KKSSP^295, freeable, kglll 1232 7 176 , KKSSP^295, perm, perm 1224 9 136 , KKSSP^295, freeable, kglseshtSegs 1216 2 608 , KKSSP^295, perm, perm 1120 14 80 , KKSSP^295, free, 1104 6 184 , KKSSP^295, perm, perm 1056 12 88 , KKSSP^295, free, 1024 8 128 , KKSSP^295, perm, perm 960 8 120 , KKSSP^295, perm, perm 816 1 816 , KKSSP^295, perm, perm 800 1 800 , KKSSP^295, perm, perm 792 3 264 , KKSSP^295, freeable, kglll 768 1 768 , KKSSP^295, perm, perm 760 1 760 , KKSSP^295, freeable, kglss 752 1 752 , KKSSP^295, perm, perm 736 2 368 , KKSSP^295, perm, perm 680 1 680 , KKSSP^295, perm, perm 624 1 624 , KKSSP^295, perm, perm 528 2 264 , KKSSP^295, freeable, kglpn 512 1 512 , KKSSP^295, perm, perm 504 3 168 , KKSSP^295, perm, perm 504 1 504 , KKSSP^295, perm, perm 496 1 496 , KKSSP^295, perm, perm 448 4 112 , KKSSP^295, perm, perm 432 3 144 , KKSSP^295, freeable, kglseshtSegs 272 1 272 , KKSSP^295, freeable, kglpn 152 1 152 , KKSSP^295, free, 80 1 80 , KKSSP^295, perm, perm 48 1 48 , KKSSP^295, freeable, kglsesht --//主要是kgllk占用较大。kglll 表示什么不清楚。 --//session 1: SCOTT@book> @ sharepool/shp7 20000 declare * ERROR at line 1: ORA-04031: unable to allocate 112 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","SQLA^337fc737","KGHSC_ALLOC_BUF:buf") ORA-06512: at "SYS.DBMS_SQL", line 1199 ORA-06512: at line 9 --//已经出现ora-04931错误。 --//session 2: SYS@book> select count(*) from x$ksmsp where ksmchcom='KKSSP^295' ; select count(*) from x$ksmsp where ksmchcom='KKSSP^295' * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","X$KSMSP","KGLH0^82d61778","kglHeapInitialize:temp") SYS@book> select count(*) from x$ksmsp where ksmchcom='KKSSP^295' ; COUNT(*) ---------- 7336 --//第2次执行通过。 SYS@book> @ tix New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_28301_0002.trc SYS@book> oradebug dump heapdump_addr 1 0x000000007DAB8830 Statement processed. $ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_28301_0002.trc -- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com ) Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason ---------- ------- ------------ ----------------- ----------------- ----------------- 6140928 23988 256 , KKSSP^295, freeable, kgllk 3486720 13620 256 , KKSSP^295, freeable, kglll 2720000 10625 256 , KKSSP^295, freeable, kglpn 957296 3149 304 , KKSSP^295, perm, perm 320488 5723 56 , KKSSP^295, freeable, kglseshtSegs 212256 603 352 , KKSSP^295, perm, perm 162304 634 256 , KKSSP^295, freeable, KQR ENQ 109632 1142 96 , KKSSP^295, perm, perm 98784 343 288 , KKSSP^295, perm, perm 74080 463 160 , KKSSP^295, perm, perm 56672 1012 56 , KKSSP^295, free, 51120 1065 48 , KKSSP^295, free, 35360 85 416 , KKSSP^295, perm, perm 35136 366 96 , KKSSP^295, free, 34640 866 40 , KKSSP^295, free, 33744 222 152 , KKSSP^295, free, 30688 137 224 , KKSSP^295, perm, perm 25872 294 88 , KKSSP^295, free, 24960 80 312 , KKSSP^295, perm, perm 24168 57 424 , KKSSP^295, perm, perm 20680 55 376 , KKSSP^295, perm, perm 20400 255 80 , KKSSP^295, free, 19712 77 256 , KKSSP^295, perm, perm 19440 45 432 , KKSSP^295, perm, perm 19312 142 136 , KKSSP^295, free, 18864 131 144 , KKSSP^295, free, 18560 116 160 , KKSSP^295, free, 18432 48 384 , KKSSP^295, perm, perm 18304 143 128 , KKSSP^295, free, 18240 57 320 , KKSSP^295, perm, perm 17952 66 272 , KKSSP^295, freeable, kglll 17928 249 72 , KKSSP^295, free, 17600 40 440 , KKSSP^295, perm, perm 17520 146 120 , KKSSP^295, free, 16920 47 360 , KKSSP^295, perm, perm 15456 42 368 , KKSSP^295, perm, perm 15456 138 112 , KKSSP^295, free, 14720 230 64 , KKSSP^295, free, 14112 84 168 , KKSSP^295, free, 13912 47 296 , KKSSP^295, perm, perm 13888 31 448 , KKSSP^295, perm, perm 13464 153 88 , KKSSP^295, freeable, kglseshtSegs 13416 129 104 , KKSSP^295, perm, perm 13064 71 184 , KKSSP^295, perm, perm 12864 67 192 , KKSSP^295, perm, perm 12384 43 288 , KKSSP^295, freeable, kgllk 12312 1 12312 , KKSSP^295, freeable, kglseshtTable 12240 170 72 , KKSSP^295, freeable, kglseshtSegs 11968 44 272 , KKSSP^295, freeable, kgllk 11840 148 80 , KKSSP^295, freeable, kglseshtSegs 11648 91 128 , KKSSP^295, perm, perm 11616 44 264 , KKSSP^295, freeable, kgllk 11424 102 112 , KKSSP^295, perm, perm 11024 106 104 , KKSSP^295, free, 10920 91 120 , KKSSP^295, perm, perm 10920 39 280 , KKSSP^295, freeable, kgllk 10912 44 248 , KKSSP^295, perm, perm 10240 160 64 , KKSSP^295, freeable, kglseshtSegs 10080 42 240 , KKSSP^295, perm, perm 10080 35 288 , KKSSP^295, freeable, kglll 9152 52 176 , KKSSP^295, perm, perm 9072 54 168 , KKSSP^295, perm, perm 9048 39 232 , KKSSP^295, perm, perm 7968 249 32 , KKSSP^295, free, 7920 45 176 , KKSSP^295, free, 7776 36 216 , KKSSP^295, freeable, kglseshtSegs 7656 33 232 , KKSSP^295, freeable, kglseshtSegs 6720 12 560 , KKSSP^295, perm, perm 5760 24 240 , KKSSP^295, freeable, kglseshtSegs 5600 25 224 , KKSSP^295, freeable, kglseshtSegs 5600 20 280 , KKSSP^295, freeable, kglll 5440 10 544 , KKSSP^295, perm, perm 5152 28 184 , KKSSP^295, free, 5016 19 264 , KKSSP^295, freeable, kglpn 4992 24 208 , KKSSP^295, freeable, kglseshtSegs 4896 18 272 , KKSSP^295, freeable, kglpn 4760 17 280 , KKSSP^295, freeable, kglpn 4608 16 288 , KKSSP^295, freeable, kglpn 4464 18 248 , KKSSP^295, freeable, kglseshtSegs 4200 21 200 , KKSSP^295, freeable, kglseshtSegs 3840 15 256 , KKSSP^295, freeable, kglseshtSegs 2904 11 264 , KKSSP^295, freeable, kglseshtSegs 2904 11 264 , KKSSP^295, freeable, kglll 2720 10 272 , KKSSP^295, freeable, kglseshtSegs 2112 11 192 , KKSSP^295, free, 1920 4 480 , KKSSP^295, perm, perm 1840 10 184 , KKSSP^295, freeable, kglseshtSegs 1728 3 576 , KKSSP^295, perm, perm 1584 1 1584 , KKSSP^295, free, 1536 8 192 , KKSSP^295, freeable, kglseshtSegs 1344 8 168 , KKSSP^295, freeable, kglseshtSegs 1232 7 176 , KKSSP^295, freeable, kglseshtSegs 1216 2 608 , KKSSP^295, perm, perm 840 3 280 , KKSSP^295, freeable, kglseshtSegs 816 1 816 , KKSSP^295, perm, perm 800 1 800 , KKSSP^295, perm, perm 768 1 768 , KKSSP^295, perm, perm 760 1 760 , KKSSP^295, freeable, kglss 752 1 752 , KKSSP^295, perm, perm 680 1 680 , KKSSP^295, perm, perm 640 1 640 , KKSSP^295, perm, perm 624 1 624 , KKSSP^295, perm, perm 568 1 568 , KKSSP^295, perm, perm 512 1 512 , KKSSP^295, perm, perm 504 1 504 , KKSSP^295, perm, perm 496 1 496 , KKSSP^295, perm, perm 320 2 160 , KKSSP^295, freeable, kglseshtSegs 288 1 288 , KKSSP^295, freeable, kglseshtSegs 280 1 280 , KKSSP^295, freeable, KQR ENQ 208 1 208 , KKSSP^295, free, 200 1 200 , KKSSP^295, free, 80 1 80 , KKSSP^295, perm, perm 48 1 48 , KKSSP^295, freeable, kglsesht

编辑推荐：

返回列表

[20210421]分析会话占用的共享内存段2.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推