[20210421]分析會話佔用的共享記憶體段2.txt
[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 ( )
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 ( )
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2769204/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210412]分析會話佔用的共享記憶體段.txt會話記憶體
- aix 共享記憶體段大小限制AI記憶體
- linux共享記憶體段研究Linux記憶體
- aix 共享記憶體段問題AI記憶體
- nginx共享記憶體分析Nginx記憶體
- filebeat實踐-記憶體佔用-最大記憶體佔用記憶體
- Windbg分析高記憶體佔用問題記憶體
- 利用Windbg分析高記憶體佔用問題記憶體
- Java逐層解析JSON的記憶體佔用分析JavaJSON記憶體
- 分析並優化 Android 應用記憶體佔用優化Android記憶體
- 修改oracle記憶體佔用Oracle記憶體
- 資源記憶體佔用記憶體
- Android圖片載入記憶體佔用分析Android記憶體
- Linux釋放記憶體及手動釋放Oracle共享記憶體段Linux記憶體Oracle
- python物件的記憶體佔用Python物件記憶體
- 共享記憶體記憶體
- 在C++Builder中建立共享記憶體段 (轉)C++UI記憶體
- 共享記憶體段未釋放導致資料庫記憶體被耗盡記憶體資料庫
- [20210407]分析sql語句的共享記憶體段3.txtSQL記憶體
- node計算記憶體佔用記憶體
- 託管堆記憶體佔用記憶體
- influxdb記憶體佔用剖析UX記憶體
- svchost.exe佔用很高的記憶體記憶體
- Win10記憶體佔用過多怎麼辦 win10清理記憶體佔用的方法Win10記憶體
- Golang 共享記憶體Golang記憶體
- POSIX共享記憶體記憶體
- oracle 共享記憶體Oracle記憶體
- POSIX 共享記憶體記憶體
- SHARED POOL中KGH: NOACCESS佔用大量記憶體的問題分析記憶體
- MySQL記憶體佔用計算公式MySql記憶體公式
- mariadb 記憶體佔用優化記憶體優化
- PHP計算出記憶體最高佔用.PHP記憶體
- AIX程式記憶體佔用數的計算AI記憶體
- [20210902]為什麼會使用多個共享記憶體段.txt記憶體
- 圖片基礎知識梳理(2) Bitmap 佔用記憶體分析記憶體
- 如何檢視MySQL資料庫佔多大記憶體，佔用太多記憶體怎麼辦？MySql資料庫記憶體
- 電腦記憶體佔用過高怎麼辦 電腦記憶體佔用過高解決方法記憶體
- C#獲取CPU佔用率、記憶體佔用、磁碟佔用、程式資訊C#記憶體