晶晶实验二十一 一个简单4031例子的引申.docx
- 文档编号:11961810
- 上传时间:2023-06-03
- 格式:DOCX
- 页数:9
- 大小:19.68KB
晶晶实验二十一 一个简单4031例子的引申.docx
《晶晶实验二十一 一个简单4031例子的引申.docx》由会员分享,可在线阅读,更多相关《晶晶实验二十一 一个简单4031例子的引申.docx(9页珍藏版)》请在冰点文库上搜索。
晶晶实验二十一一个简单4031例子的引申
晶晶实验二十一:
一个简单4031例子的引申:
一个简单4031例子的引申:
内存,像来是兵家必争之地。
内存,像来也是一个注定要多事的地方。
内存的管理,稍有不甚,即会内存泄露。
就算不泄露,如果应用大量占用内存而不释放,再大也嫌少的内存将会迅速告磬。
在Oracle中,常用的内存组件中,最容易出问题的,就是共享池了。
共享池内存不足后,会报出一个ORA-04031错误,下面就处理这个错误方面的问题,说一下简单的例子,请ITPUB中的坛友补充。
开发人员说在测试环境中,有一个应用运行后不久就会报出4031错误,因为是9.2.0.1的系统,我设置了如下参数:
altersystemsetevents'4031tracenameerrorstacklevel3';
又运行一遍应用。
果然,运行不久后,又报出了4031错误,查看DUMP文件,报出错误的语句是:
selectidfromt3wheremyid=XXXX
并且,在DUMP文件中发现大量selectCOLfromTABLEwhereCOL=1、selectCOLfromTABLEwhereCOL=2,直到COL=XXXX的语句,至此,这例4031错误已经真相大白,开发人员没有使用绑定变量,并且打开的游标没有关闭。
打开源代码看了一下,果然有一段循环不停的打开游标、执行查询,好,错误的原因到此,水落石出。
开发人员说,一开始先报了一个01000,超出游标数,他将open_cursor设为6000后,就报出了4031错误。
这是一个非常简单的例子,下面我来模拟一下:
步1:
修改打开游标数:
altersystemsetopen_cursors=5000;
步2:
跟踪错误堆栈:
altersystemsetevents'4031tracenameerrorstacklevel3';
步3:
执行下如下
declare
msqlvarchar2(500);
mcurnumber;
mstatnumber;
jgvarchar2(4000);
begin
foriin1..2000loop
mcur:
=dbms_sql.open_cursor;
msql:
='selectidfromt3wheremyid='||to_char(i);
dbms_sql.parse(mcur,msql,dbms_sql.native);
dbms_sql.define_column(mcur,1,jg,4000);
mstat:
=dbms_sql.execute(mcur);
endloop;
dbms_sql.close_cursor(mcur);
end;
/
第1行出现错误:
ORA-04031:
无法分配1272字节的共享内存("sharedpool","selectidfromt3wheremyid...","Typecheckheap","kkotp:
kkoiqd")
ORA-06512:
在"SYS.DBMS_SYS_SQL",line826
ORA-06512:
在"SYS.DBMS_SQL",line32
ORA-06512:
在line12
步4:
查看跟踪文件:
在跟踪文件的开始,有如下内容:
ksedmp:
internalorfatalerror
ORA-04031:
无法分配1272字节的共享内存("sharedpool","selectidfromt3wheremyid...","Typecheckheap","kkotp:
kkoiqd")
CurrentSQLstatementforthissession:
selectidfromt3wheremyid=944
这已经显示出来了引发错误的语句:
selectidfromt3wheremyid=944,再向下查看,可以发现大量的selectidfromt3wheremyid=XXX语句。
下面,再深入的想一下,如果上面的匿名过程并不是执行到将共享池内存占尽才被迫终止,而是执行多少次后,不关闭游标正常终止,在此会话退出前,游标在共享池中的部分内存,将不会被释放。
而此时共享池自由内存已经被占用的差不多了,有其他的编写正确的过程、SQL语句执行时,由于无法取得足够的共享池内存,将会报出4031错误。
此时,虽然我们可以看到引发4031错误的语句,但此语句并不是造成错误的真正原因,原凶另有其人。
对于这样的情况,该如何找到真正的原凶呢?
比如说,将上面的匿名过程改为:
declare
msqlvarchar2(500);
mcurnumber;
mstatnumber;
jgvarchar2(4000);
begin
--上面例子中,循环执行到944次被中断,这次我将循环次数改为850,
foriin1..850loop--即可以消耗大量内存,又不会报出4031错误。
mcur:
=dbms_sql.open_cursor;
msql:
='selectidfromt3wheremyid='||to_char(i);
dbms_sql.parse(mcur,msql,dbms_sql.native);
dbms_sql.define_column(mcur,1,jg,4000);
mstat:
=dbms_sql.execute(mcur);
endloop;
dbms_sql.close_cursor(mcur);
end;
/
上面的过程可以正常执行完毕(如果在测试中仍会报出4031,可以将循环次数再缩小一些),这时,共享池的自由内存应该已经不多了。
另开一些会话,随便运行一些消耗共享池的语句,不一会,4031出现了:
ksedmp:
internalorfatalerror
ORA-04031:
无法分配4088字节的共享内存("sharedpool","STATSPACK","PL/SQLMPCODE","BAMIMA:
BamBuffer")
CurrentSQLstatementforthissession:
DECLAREjobBINARY_INTEGER:
=:
job;next_dateDATE:
=:
mydate;brokenBOOLEAN:
=FALSE;BEGINstatspack.snap;:
mydate:
=next_date;IFbrokenTHEN:
b:
=1;ELSE:
b:
=0;ENDIF;END;
引发4031的语句是STATSPACK,但,真正的原凶不是它。
好了,下面我用一个脚本,试验一下该如何定位问题的原凶。
步1:
准备基本的记录共享池状态的表:
createtablejj_ksmspasselect*fromx$ksmspwhere0=1;
altertablejj_ksmspadd(idnumber);
createtablejj_sqlareaasselect*fromv$sqlareawhere0=1;
altertablejj_sqlareaadd(idnumber);
varidnumber;
exec:
id:
=1;
步2:
建立一个脚本my_shared_stat.sql:
insertintojj_ksmspselectx$ksmsp.*,:
idfromx$ksmsp;
insertintojj_sqlareaselectv$sqlarea.*,:
idfromv$sqlarea;
exec:
id:
=:
id+1
commit;
步3:
在于步1同一会话中,每隔一段时间运行此脚本my_shared_stat.sql
步4:
随便的在其他会话中做些操作
步5:
在任一会话中,运行如下匿名过程:
declare
msqlvarchar2(500);
mcurnumber;
mstatnumber;
jgvarchar2(4000);
begin
--上面例子中,循环执行到944次被中断,这次我将循环次数改为850,
foriin1..850loop--即可以消耗大量内存,又不会报出4031错误。
mcur:
=dbms_sql.open_cursor;
msql:
='selectidfromt3wheremyid='||to_char(i);
dbms_sql.parse(mcur,msql,dbms_sql.native);
dbms_sql.define_column(mcur,1,jg,4000);
mstat:
=dbms_sql.execute(mcur);
endloop;
dbms_sql.close_cursor(mcur);
end;
/
在此步骤中,循环的次数要根据自己共享池的大小而定,目的是既要让此过程消耗共享池内存,又不至于用尽共享池内存。
步6:
继续随便的在其他会话中做些操作,看什么时候会出现4031错误,
我是在执行Statspack抓取快照时,出现的4031。
sid=16pid=15>execstatspack.snap
BEGINstatspack.snap;END;
*
第1行出现错误:
ORA-04031:
无法分配2196字节的共享内存("sharedpool","STATS$SNAPSHOT","KGLSheap","KGLSMEMBLOCK")
ORA-06512:
在"PERFSTAT.STATSPACK",line1361
ORA-06512:
在"PERFSTAT.STATSPACK",line2442
ORA-06512:
在"PERFSTAT.STATSPACK",line91
ORA-06512:
在line1
selectsharable_mem,persistent_mem,runtime_mem,executions,substr(sql_text,1,60)from(select*fromjj_sqlareaorderbysharable_memdesc)whererownum<=30andid=&id;
步7:
分析原因:
建立如下脚本,名为show_ksmsp.sql:
selectksmchcom,decode(round(ksmchsiz/1000),0,'0-1K',1,'1-2K',2,'2-3K',3,'3-4K',
4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
'8-9k',9,'9-10k','>10K')"size",
count(*),ksmchclsStatus,sum(ksmchsiz)Bytes
fromjj_ksmsp
whereKSMCHCOM='freememory'andid=&id
groupbyksmchidx,ksmchcls,
'sgaheap('||KSMCHIDX||',0)',ksmchcom,ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
1,'1-2K',2,'2-3K',3,'3-4K',4,'4-5K',5,'5-6k',6,
'6-7k',7,'7-8k',8,'8-9k',9,'9-10k','>10K');
依次运行:
idle>@show_ksmsp
输入id的值:
1
KSMCHCOMsizeCOUNT(*)STATUSBYTES
-------------------------------------------------
freememory0-1K90free5088
freememory1-2K1free1324
freememory>10K2free6318988
id为1时的资料,Freememory大于10K的Chunk有两个,共6M多字节。
idle>@show_ksmsp
输入id的值:
2
原值6:
whereKSMCHCOM='freememory'andid=&id
新值6:
whereKSMCHCOM='freememory'andid=2
KSMCHCOMsizeCOUNT(*)STATUSBYTES
-------------------------------------------------
freememory0-1K106free5972
freememory3-4K1free2988
freememory>10K2free5730568
id为2时的资料,Freememory大于10K的Chunk有两个,共5M多字节。
idle>@show_ksmsp
输入id的值:
3
KSMCHCOMsizeCOUNT(*)STATUSBYTES
-------------------------------------------------
freememory0-1K527free26348
freememory2-3K2free4052
freememory3-4K1free3408
freememory4-5K1free3556
freememory>10K1free17052
id为3时的资料,Freememory大于10K的Chunk只剩1个,也就是17K左右。
1K之下的Chunk则有527个。
这时,已经开始有碎片了。
idle>@show_ksmsp
输入id的值:
4
KSMCHCOMsizeCOUNT(*)STATUSBYTES
-------------------------------------------------
freememory0-1K524free26256
freememory1-2K11free6568
freememory>10K5R-free429700
id为4时的资料,Freememory大于10K的Chunk已经没有了,只剩下0-1K、和1-2K。
好了,不用再看下去了,问题应该是发生在ID为3或4时。
在JJ_SQLAREA中查看ID为3的行:
步8:
查找原凶:
先显示ID为3时,前10条占用内存最多SQL声明:
selectVERSION_COUNT,PARSE_CALLS,executions,MEM,
round(mem/(selectsum(sharable_mem+persistent_mem+runtime_mem)fromjj_sqlarea
whereid=3)*100,2)"Mem%",substr(sql_text,1,150)
from(selectjj_sqlarea.*,sharable_mem+persistent_mem+runtime_memMEMfromjj_sqlarea
orderbymemdesc)whererownum<=&slandid=3;
输入sl的值:
10
VERSION_COUNTPARSE_CALLSEXECUTIONSMEMMem%SUBSTR(SQL_TEXT,1,150)
--------------------------------------------------------------------------------------------------------------
132913181.07insertintojj_sqlareaselectv$sqlarea.*,:
idfromv$sql
11182958.98select'sid='||a.sid||'pid='||b.pidSIDfromv$session
11151594.61selectOPEN_MODEfromv$database
353047967.56selectvalue$fromsys.props$wherename=:
1
11142644.5selecttc.type#,tc.intcol#,tc.position#,c.type#,c.lengt
2124141439.49selecti.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i
13335620.42insertintojj_ksmspselectx$ksmsp.*,:
idfromx$ksmsp
11131049.37selectbaseobject,type#,update$,insert$,delete$,refnewna
11128772.34selecto.owner#,o.obj#,decode(o.linkname,null,decode(u.
2124027017.32selectts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols已选择10行。
ID为3时,占用内存最多的SQL声明,是我自己的向JJ_SQLAREA中收集数据的脚本,它也只不过占用了所有SQL声明内存总量的1.07%。
再看下面的这些SQL,并不能发现什么异常,扩大显示范围,显示前100条:
VERSION_COUNTPARSE_CALLSEXECUTIONSMEMMem%SUBSTR(SQL_TEXT,1,150)
------------------------------------------------------------------------------------------------
(省略部分内容...............)
1118930.11selectidfromt3wheremyid=433
1118930.11selectidfromt3wheremyid=547
1118930.11selectidfromt3wheremyid=604
1118930.11selectidfromt3wheremyid=661
1118930.11selectidfromt3wheremyid=490
1118886.1selectidfromt3wheremyid=554
1118862.1selectidfromt3wheremyid=403
1118862.1selectidfromt3wheremyid=480
1118862.1selectidfromt3wheremyid=475
1118862.1selectidfromt3wheremyid=470
1118862.1selectidfromt3wheremyid=465
1118862.1selectidfromt3wheremyid=460
(省略部分内容...............)
发现大量硬解析,好了,原凶终于浮出水面。
总结一下,此处,查看某条语句是否占用过多的共享池内存,或是否有大量相似的SQL语句,是找到问题的一般途径。
很多时候,“原凶”可能是Oracle的Bug。
因此,如果真出现了4031,还要对引发共享池内存泄露的BUG有所了解。
还有一些4031,是不可再现的,重启数据库后,再也没有出现过。
这种情况最是让人揪心,好像你的数据库中有了一颗定时炸弹。
对于可再现的4031,我上面的例子中脚本,只是一个非常简单的例子,真的要想找出原凶,还可以收集更多的视图中的资料,将收集资料的脚本my_shared_stat.sql,设为一个定时执行的自动任务。
当然也可以使用STATSPACK,不过STATSPACK收集的资料过多,有些对4031是没用的,我觉得如果要想让收集频率更高一些的话,可以自己设计脚本,有针对性的收集一些视图的资料。
这样收集资料的脚本需要做的工作更少,完成的速度更快,收集的频率也可以更高。
上面例子中,我的脚本只是测试用的,因此,统计的信息并不全面,在出现4031时,还应该关注的视图有
V$SHARED_POOL_RESERVED
v$sgastat
V$SQL_SHARED_MEMORY
x$kghlu
x$ksmlru
v$resource_limit
可以用于4031的跟踪事件还有:
altersystemsetevents'4031tracenameheapdumplevel2';
altersystemsetevents'immediatetracenamelibrary_cachelevel11';
对于可重现的4031,使用statspack在比较大的时间粒度下,持续的收集资料,找出引发4031出现的时段,在再相应的时段中,用自己的脚本,以比较高的频率(比较细的时间粒度)收集有针对性的资料,对于发现错误的原凶,也很有帮助。
另外,对你所使用的数据库版本4031方面的BUG要有所了解,很多4031问题,都是由BUG引发的。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 晶晶实验二十一 一个简单4031例子的引申 实验 十一 一个 简单 4031 例子 引申