没有必胜的秘籍，没有方程式遵循~~ 要赢~~只有全身心的投入！

晶晶实验二十一：一个简单4031例子的引申:

上一篇 / 下一篇  2008-03-26 07:51:47 / 个人分类：晶晶oracle实验系列

查看( 1472 ) / 评论( 33 )
一个简单4031例子的引申:ITPUB个人空间}!a%A T/i-T/d6~

bcmr+N"Z.AJ6j0 内存，像来是兵家必争之地。内存，像来也是一个注定要多事的地方。内存的管理，稍有不甚，即会内存泄露。就算不泄露，如果应用大量占用内存而不释放，再大也嫌少的内存将会迅速告磬。在Oracle中，常用的内存组件中，最容易出问题的，就是共享池了。共享池内存不足后，会报出一个ORA-04031错误，下面就处理这个错误方面的问题，说一下简单的例子，请ITPUB中的坛友补充。ITPUB个人空间c:Fqj,?2LP,a+c3CV~

F1BYgB"VG|0    开发人员说在测试环境中，有一个应用运行后不久就会报出4031错误，因为是9.2.0.1的系统，我设置了如下参数：ITPUB个人空间]9a1J2~YN G/K^
alter system set events '4031 trace name errorstack level 3';
?O1@0dR9g0 ITPUB个人空间0M3ag6Xjb1Y9YWv0m
又运行一遍应用。果然，运行不久后，又报出了4031错误，查看DUMP文件，报出错误的语句是：
_ ?Sy Z5sHg2j0
j(c*Hw j+Z#EZ0 select id from t3 where myid=XXXXITPUB个人空间pI}(U:Ck

+RS:b kF{ M0 并且，在DUMP文件中发现大量select COL from TABLE where COL=1、select COL from TABLE where COL=2，直到COL=XXXX的语句，至此，这例4031错误已经真相大白，开发人员没有使用绑定变量，并且打开的游标没有关闭。打开源代码看了一下，果然有一段循环不停的打开游标、执行查询，好，错误的原因到此，水落石出。开发人员说，一开始先报了一个01000，超出游标数，他将open_cursor设为6000后，就报出了4031错误。ITPUB个人空间S-Xl,m)p
ITPUB个人空间| s#eJ W!t
这是一个非常简单的例子，下面我来模拟一下：
c!i)BO jKR |0 步1：修改打开游标数：ITPUB个人空间lN4`L,dd#H
alter system set open_cursors=5000;ITPUB个人空间Gvkix@2Uy4mh
ITPUB个人空间q&O]9J-b$BAi&tM%^
步2：跟踪错误堆栈：
:PY%t`5m:W?GFu0 alter system set events '4031 trace name errorstack level 3';
X2z'[p/@3}0
:B N0vt-W`1Nv{0 步3：执行下如下
'N3Z3R%G8|9Y"f2e0 declare
#PWLO,QH0 msql varchar2(500);ITPUB个人空间;{`-^k oEu2_8R
mcur number;ITPUB个人空间#H M.wt{,G
mstat number;ITPUB个人空间i`&K)E1C
jg varchar2(4000);
2v&hX$c6O}(g0 begin
BA+t].yI0   for i in 1..2000 loop
"[D:_}f3[.G/o v#s c0      mcur:=dbms_sql.open_cursor;ITPUB个人空间c m Et t
     msql:='select id from t3 where myid='||to_char(i);ITPUB个人空间+gVk0Ga h
     dbms_sql.parse(mcur,msql,dbms_sql.native);ITPUB个人空间,kh1t(I;@'T
     dbms_sql.define_column(mcur,1,jg,4000);ITPUB个人空间 ])c~e'CC_Q&wcU
     mstat:=dbms_sql.execute(mcur);
.qS1t F+Q@(d;t0    end loop;
t*Vk6F2_%G?0    dbms_sql.close_cursor(mcur);ITPUB个人空间$b(q]ra9\y
end;ITPUB个人空间z)Zs9?%C*f AaN aI
/
j)H9u+?5A4dJ0 ITPUB个人空间f'^)I DuM%Too:s$NI
第 1 行出现错误:ITPUB个人空间P!u+ga6E.y
ORA-04031: 无法分配 1272 字节的共享内存 ("shared pool"，"select id from t3 where myid..."，"Typecheck heap"，"kkotp : kkoiqd")
uU9Wm5yn _0 ORA-06512: 在"SYS.DBMS_SYS_SQL", line 826
Sqt3i^`.J0 ORA-06512: 在"SYS.DBMS_SQL", line 32
'HiF'o.V:~0 ORA-06512: 在line 12
u ib5t&Pa F0
Q+J8JK$R/h?hEH0 步4：查看跟踪文件：
z1G1JK*cRL0U0 在跟踪文件的开始，有如下内容：
f NF p%M m0 ksedmp: internal or fatal error
"]8jq!cC.T)u0 ORA-04031: 无法分配 1272 字节的共享内存 ("shared pool"，"select id from t3 where myid..."，"Typecheck heap"，"kkotp : kkoiqd")
a$RkAb8@Se0 Current SQL statement for this session:ITPUB个人空间B1Z*_ H9^6vR1i
select id from t3 where myid=944 ITPUB个人空间bc/[};J@9t

!?7P3P j R:y3d Xx0 这已经显示出来了引发错误的语句：select id from t3 where myid=944 ，再向下查看，可以发现大量的select id from t3 where myid=XXX语句。
Km-o.?7jF0
0|Gu'Q"p v(ce}G0 下面，再深入的想一下，如果上面的匿名过程并不是执行到将共享池内存占尽才被迫终止，而是执行多少次后，不关闭游标正常终止，在此会话退出前，游标在共享池中的部分内存，将不会被释放。而此时共享池自由内存已经被占用的差不多了，有其他的编写正确的过程、SQL语句执行时，由于无法取得足够的共享池内存，将会报出4031错误。此时，虽然我们可以看到引发4031错误的语句，但此语句并不是造成错误的真正原因，原凶另有其人。对于这样的情况，该如何找到真正的原凶呢？比如说，将上面的匿名过程改为：ITPUB个人空间C Q(mF U.X(@l ^n
declare ITPUB个人空间0X:\2Z4j a]0S
msql varchar2(500);ITPUB个人空间LgW7Q/c7kh:T KE/@3Q
mcur number;
%f+[&@)x,j;UP5EN|v0 mstat number;ITPUB个人空间C0l Trh;`
jg varchar2(4000);
"IFL+XA,A~;q;U0 begin
N!u Xv1r(W1vcd5YY6p0                          --上面例子中，循环执行到944次被中断，这次我将循环次数改为850,
:f9} t5D3G9d!O%E [|9}Y0   for i in 1..850 loop   --即可以消耗大量内存，又不会报出4031错误。
I7b;N!?t7t0      mcur:=dbms_sql.open_cursor;ITPUB个人空间sAOj+D x"a!{A
     msql:='select id from t3 where myid='||to_char(i);  ITPUB个人空间$f/l|.J$p_w
     dbms_sql.parse(mcur,msql,dbms_sql.native);
@wG)GW F*['y%X0      dbms_sql.define_column(mcur,1,jg,4000);ITPUB个人空间^(A#s P1N
     mstat:=dbms_sql.execute(mcur);ITPUB个人空间g/Lhl)u~gn
   end loop;ITPUB个人空间X%w1M'L2z5Q
   dbms_sql.close_cursor(mcur);ITPUB个人空间NG$W%QyO
end;
4D y `B/g~QU @ ye@0 /ITPUB个人空间#uv,{8F*jN
ITPUB个人空间fC{1wNK
上面的过程可以正常执行完毕（如果在测试中仍会报出4031，可以将循环次数再缩小一些），这时，共享池的自由内存应该已经不多了。另开一些会话，随便运行一些消耗共享池的语句，不一会，4031出现了：
-Wl,J:N)R'H0 ksedmp: internal or fatal errorITPUB个人空间Y*@enn
ORA-04031: 无法分配 4088 字节的共享内存 ("shared pool"，"STATSPACK"，"PL/SQL MPCODE"，"BAMIMA: Bam Buffer")ITPUB个人空间| c p'@So
Current SQL statement for this session:ITPUB个人空间8t UAg{w6E
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
'CB5Aq Z^BF]qxJ0 ITPUB个人空间 |N$aC.L!~
引发4031的语句是STATSPACK，但，真正的原凶不是它。ITPUB个人空间]_'v8F:U-J?
好了，下面我用一个脚本，试验一下该如何定位问题的原凶。ITPUB个人空间j;p+`!i,]$t%D*I
ITPUB个人空间O1OG2N(kY P'S
步1：准备基本的记录共享池状态的表：
&i)`'].i"}&G!\'O4GS0 create table jj_ksmsp as select * from x$ksmsp where 0=1;
^$y E3Y5CE-nz;sb0 alter table jj_ksmsp add(id number);ITPUB个人空间0fH4L'\o+|_
create table jj_sqlarea as select * from v$sqlarea where 0=1;ITPUB个人空间t7zq zkrK
alter table jj_sqlarea add(id number);
c1Inxze G0 ITPUB个人空间T#]-j},`(Sk0E~d5zy!p P
var id number;
hq*c;S5`V0 exec :id:=1;
XA-f"N^0 ITPUB个人空间/mmIhl6@c ?
步2：ITPUB个人空间~~!Q&Y$^
建立一个脚本 my_shared_stat.sql：
7MPpp'\2Y(l`0 ITPUB个人空间j\%~,xE0{
insert into jj_ksmsp select x$ksmsp.*,:id from x$ksmsp;
g.}0^lVn+`$M0 insert into jj_sqlarea select v$sqlarea.*,:id from v$sqlarea;
N$b~H,NV"J:[@$u0 exec :id:=:id+1
:z9Q;O tP)^ d0 commit;
"m8P [N x"Qc.h0s0
k'] lN#xL Q0 步3：在于步1同一会话中，每隔一段时间运行此脚本my_shared_stat.sqlITPUB个人空间c3p3j3w Iq;qF

K s'f)V5oe(b9|e0 步4：随便的在其他会话中做些操作ITPUB个人空间6qAl!K6N N:} c X
ITPUB个人空间(rD2Rbj}:v%U
步5：在任一会话中，运行如下匿名过程：ITPUB个人空间.\ T3mmOV't e Gz
declare
*ZKaP,|W)~ R)N4H0 msql varchar2(500);ITPUB个人空间W Gc{Sp+E
mcur number;ITPUB个人空间i[ n:|}HL
mstat number;
o.t/R@saFR0 jg varchar2(4000);
/m:k!HM:h0 begin
-re7`JV7o$}X K0                          --上面例子中，循环执行到944次被中断，这次我将循环次数改为850,ITPUB个人空间8KCg"R-`{h0U
  for i in 1..850 loop   --即可以消耗大量内存，又不会报出4031错误。
8JOd.eY?(d k[3U0      mcur:=dbms_sql.open_cursor;
NjdhqhC0      msql:='select id from t3 where myid='||to_char(i);  ITPUB个人空间8KC Dx#f2x\
     dbms_sql.parse(mcur,msql,dbms_sql.native);
_#zWu2l#b S~J0      dbms_sql.define_column(mcur,1,jg,4000);ITPUB个人空间 ?7F rH9~m0e2h
     mstat:=dbms_sql.execute(mcur);
p N(?7q7bj8K:yX0    end loop;
(iA$q8fKWr i%m0    dbms_sql.close_cursor(mcur);
Q-v CVN7dI,YGQ0 end;ITPUB个人空间8T9VS*Zl3eF2W
/
PN(q HfB0 在此步骤中，循环的次数要根据自己共享池的大小而定，目的是既要让此过程消耗共享池内存，又不至于用尽共享池内存。
Kv&dpC%b0 ITPUB个人空间AT!q6jk&y ZP^
步6：继续随便的在其他会话中做些操作，看什么时候会出现4031错误，ITPUB个人空间 QXQ3^ g:p$ewL i
我是在执行Statspack抓取快照时，出现的4031。ITPUB个人空间+GB,g@R!|qX
sid=16 pid=15> exec statspack.snap
FM6FwqM/c6~V0 BEGIN statspack.snap; END;
9pY1ejj3[-L9S5s0 ITPUB个人空间#E jK Fn$h
*
C Y2q`FhA)TR0 第 1 行出现错误:ITPUB个人空间:ve/bT-hrC.?
ORA-04031: 无法分配 2196 字节的共享内存 ("shared pool"，"STATS$SNAPSHOT"，"KGLS heap"，"KGLS MEM BLOCK")ITPUB个人空间tb%VJGHQSB
ORA-06512: 在"PERFSTAT.STATSPACK", line 1361ITPUB个人空间8^r4I X @ m
ORA-06512: 在"PERFSTAT.STATSPACK", line 2442ITPUB个人空间9h luX }{
ORA-06512: 在"PERFSTAT.STATSPACK", line 91
0| o5O%E!k8l X v&O0 ORA-06512: 在line 1ITPUB个人空间*_)v'y+JT8J/V d/qL O(Z

vN Em7O"\2x!sw0 select sharable_mem,persistent_mem,runtime_mem, executions  ,substr(sql_text,1,60) from (select * from jj_sqlarea order by sharable_mem desc) where rownum<=30 and id=&id;ITPUB个人空间e)\Sm*bfoa

S/C:NfoF0 步7：分析原因：ITPUB个人空间)u@7R'osn&S!F
建立如下脚本，名为show_ksmsp.sql:
.\{ tv}\3^#yu0 select ksmchcom, decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',ITPUB个人空间)`'hK.vvv+Y#b-bD
4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,ITPUB个人空间"c3sLp6lc$h
'8-9k', 9,'9-10k','> 10K') "size",
]9E#_fk\7ve0 count(*),ksmchcls Status, sum(ksmchsiz) BytesITPUB个人空间nSk(TFtcif T
from jj_ksmspITPUB个人空间v z7r"K0n
where KSMCHCOM = 'free memory' and id=&idITPUB个人空间P!wu3R t1B6F
group by ksmchidx, ksmchcls,
n^ MI&~0 'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
p*@*T Cs[4Ui0 1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
2_5h?F ew'P&}#|5AX0 '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');ITPUB个人空间0NT)^1}2\R3g*W:z

L:Ft'gz&v W0 依次运行：ITPUB个人空间 ZJ.]0S DR4W}Z
idle> @show_ksmsp
hc F:Ifv8Y0 输入 id 的值:  1ITPUB个人空间7MIgHEn o2]7W)M

pE E9A~$e&@ r0 KSMCHCOM         size    COUNT(*) STATUS        BYTES
'IQgB7x*Up0 ---------------- ----- ---------- -------- ----------
Z?"l]"v7sS0 free memory      0-1K          90 free           5088
!v_[%? [VH o0 free memory      1-2K           1 free           1324
7Q)~d@-ui d#Xj&S0 free memory      > 10K          2 free        6318988
#v7lnSn"t0 id为1时的资料，Free memory大于10K的Chunk有两个，共6M多字节。
7}'_D[DkU!sE0 ITPUB个人空间a5?iv"xg{"D?
idle> @show_ksmsp
GpwY;c8t }|0 输入 id 的值:  2
@Ap3Z,}0 原值    6:  where KSMCHCOM = 'free memory' and id=&id
4Vr,qL7lw!{7ay0 新值    6:  where KSMCHCOM = 'free memory' and id=2ITPUB个人空间4jK,@.~C#u6h&@
KSMCHCOM         size    COUNT(*) STATUS        BYTES
8N)N9F"C#uz0j,VC0 ---------------- ----- ---------- -------- ----------
\ @Vul#zj/_h(^0 free memory      0-1K         106 free           5972
g'hQz!G-Aam;It3X0 free memory      3-4K           1 free           2988
\m)pM!y0 free memory      > 10K          2 free        5730568
\O)@ lxGl0 id为2时的资料，Free memory大于10K的Chunk有两个，共5M多字节。
.WUW z4}0 ITPUB个人空间7^\1s}q k
idle> @show_ksmsp
B!J5QE)b4H f z0 输入 id 的值:  3ITPUB个人空间pu8} L(J*N OE4j
KSMCHCOM         size    COUNT(*) STATUS        BYTESITPUB个人空间+dcE!Wh(k*`c
---------------- ----- ---------- -------- ----------ITPUB个人空间 K2JQoKw8W
free memory      0-1K         527 free          26348ITPUB个人空间8q}]&[pB7bgH
free memory      2-3K           2 free           4052
8p,W+i$tuP-u0 free memory      3-4K           1 free           3408ITPUB个人空间&T|Z~@4{6Z(f
free memory      4-5K           1 free           3556ITPUB个人空间 @1UI5h+`:c6e6k;I1O
free memory      > 10K          1 free          17052
8J8a]2Ngxx N0 id为3时的资料，Free memory大于10K的Chunk只剩1个，也就是17K左右。1K之下的Chunk则有527个。这时，已经开始有碎片了。ITPUB个人空间+L wf@(ttt
ITPUB个人空间H'n3op ^,R
idle> @show_ksmsp
]B HX W lx?2Z,JM0 输入 id 的值:  4
1|yR~~ ipY#X0 KSMCHCOM         size    COUNT(*) STATUS        BYTESITPUB个人空间`(^ res |O2b K
---------------- ----- ---------- -------- ----------ITPUB个人空间K ^_.b0DcmXZ%i
free memory      0-1K         524 free          26256ITPUB个人空间2wTeGa Kv
free memory      1-2K          11 free           6568ITPUB个人空间2V3Vd5h2mH CTG]
free memory      > 10K          5 R-free       429700
CN\a.X#J4Q:z*D$D0 id为4时的资料，Free memory大于10K的Chunk已经没有了，只剩下0-1K、和1-2K。ITPUB个人空间0MSg,}i4n#qFe
ITPUB个人空间 q4hTxL0fd
好了，不用再看下去了，问题应该是发生在ID为3或4时。在JJ_SQLAREA中查看ID为3的行：ITPUB个人空间2hh_UY2p
步8：查找原凶：ITPUB个人空间$}7m {y;R r
先显示ID为3时，前10条占用内存最多SQL声明：ITPUB个人空间4| o"w7D+}'`1n3J$[
select  VERSION_COUNT,PARSE_CALLS,executions , MEM,ITPUB个人空间V5y4VOjt)q
round(mem/(select sum(sharable_mem+persistent_mem+runtime_mem) from jj_sqlareaITPUB个人空间8MQ3Bx%U*O w#]
  where id=3)*100,2) "Mem%", substr(sql_text,1,150) ITPUB个人空间Cxy)eTB_{%p V_e0N
from (select jj_sqlarea.*,sharable_mem+persistent_mem+runtime_mem MEM from jj_sqlarea
:t~%]"@[,s0   order by mem desc)  where rownum<=&sl and id=3;
'U-WE(_6T Ok0 输入 sl 的值:  10
r*[+]5y.S.m9M0 ITPUB个人空间z[[;i8j;Z0t8F D
VERSION_COUNT PARSE_CALLS EXECUTIONS        MEM       Mem% SUBSTR(SQL_TEXT,1,150)
2|Qc5u6^~0 ------------- ----------- ---------- ---------- ---------- --------------------------------------------------------
B7sh|*k EC z }0             1           3          2      91318       1.07 insert into jj_sqlarea select v$sqlarea.*,:id from v$sql
Gi.HW S0             1           1          1      82958        .98 select 'sid='||a.sid||' pid='||b.pid SID from v$sessionITPUB个人空间!V%F)A w o3q
            1           1          1      51594        .61 select OPEN_MODE from v$database
5D3tJ8PL2Z0             3           5         30      47967        .56 select value$ from sys.props$ where name = :1
8A9} MDt8|m0             1           1          1      42644         .5 select tc.type#,tc.intcol#,tc.position#,c.type#, c.lengt
W;mg5fi b&o0             2          12         41      41439        .49 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i
N#\vO8VX0             1           3          3      35620        .42 insert into jj_ksmsp select x$ksmsp.*,:id from x$ksmspITPUB个人空间}0Y\L1G7yva(A+G
            1           1          1      31049        .37 select baseobject,type#,update$,insert$,delete$,refnewnaITPUB个人空间qX;v/OsrC
            1           1          1      28772        .34 select o.owner#,o.obj#,decode(o.linkname,null, decode(u.
RgCX/~ah0             2          12         40      27017        .32 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols已选择10行。ITPUB个人空间/`1tZ L)@)k&hT^A1L
ITPUB个人空间r%z9oB0s0HN
ID为3时，占用内存最多的SQL声明，是我自己的向JJ_SQLAREA中收集数据的脚本，它也只不过占用了所有SQL声明内存总量的1.07%。再看下面的这些SQL，并不能发现什么异常，扩大显示范围，显示前100条：ITPUB个人空间.R`"W&ZK:h
VERSION_COUNT PARSE_CALLS EXECUTIONS        MEM       Mem% SUBSTR(SQL_TEXT,1,150)
} LUA*|#Alk[#a0 ------------- ----------- ---------- ---------- ---------- ------------------------------------------
]v`9hA @7Lnv!Ml0                      （省略部分内容...............）
;o,K*l(_|*b$w*h0             1           1          1       8930        .11 select id from t3 where myid=433ITPUB个人空间8M`,GS:kx s4Gt
            1           1          1       8930        .11 select id from t3 where myid=547
v m#dxO/V/~"G0             1           1          1       8930        .11 select id from t3 where myid=604ITPUB个人空间#]D7kZawxd
            1           1          1       8930        .11 select id from t3 where myid=661
zSMvego+p"P0             1           1          1       8930        .11 select id from t3 where myid=490
ck5D9t.Y7e#}lH0H$xU0             1           1          1       8886         .1 select id from t3 where myid=554ITPUB个人空间puE7H1d^|{!fD
            1           1          1       8862         .1 select id from t3 where myid=403
@W1|y Un0             1           1          1       8862         .1 select id from t3 where myid=480ITPUB个人空间HHS}7`2y
            1           1          1       8862         .1 select id from t3 where myid=475
A4p| o l%Y K0             1           1          1       8862         .1 select id from t3 where myid=470ITPUB个人空间 o#MK-D'j
            1           1          1       8862         .1 select id from t3 where myid=465ITPUB个人空间F/D7x4r%EK$u%a"G
            1           1          1       8862         .1 select id from t3 where myid=460
ZD^5qX0                      （省略部分内容...............）
r:oimJ TU0
#M6|#b3h8Y0 发现大量硬解析，好了，原凶终于浮出水面。ITPUB个人空间Sk(U"P DX(T

yWy{{` w@6b]L0 总结一下，此处，查看某条语句是否占用过多的共享池内存，或是否有大量相似的SQL语句，是找到问题的一般途径。
L/]+[ Q,T1V0 很多时候，“原凶”可能是Oracle的Bug。因此，如果真出现了4031，还要对引发共享池内存泄露的BUG有所了解。ITPUB个人空间p%B8D9D,n S.sL
还有一些4031，是不可再现的，重启数据库后，再也没有出现过。这种情况最是让人揪心，好像你的数据库中有了一颗定时炸弹。
;\ hiv;Vf9x8Ofl0
I*Ezr:F i0 对于可再现的4031，我上面的例子中脚本，只是一个非常简单的例子，真的要想找出原凶，还可以收集更多的视图中的资料，将收集资料的脚本my_shared_stat.sql，设为一个定时执行的自动任务。当然也可以使用STATSPACK，不过STATSPACK收集的资料过多，有些对4031是没用的，我觉得如果要想让收集频率更高一些的话，可以自己设计脚本，有针对性的收集一些视图的资料。这样收集资料的脚本需要做的工作更少，完成的速度更快，收集的频率也可以更高。ITPUB个人空间O iX f*l#^\ K[6z

8O @(O(Rzz5y @0 上面例子中，我的脚本只是测试用的，因此，统计的信息并不全面，在出现4031时，还应该关注的视图有ITPUB个人空间N/n/Md3H'RY
V$SHARED_POOL_RESERVEDITPUB个人空间,GsV@"E'M
v$sgastat
.R'`3@lvE[0 V$SQL_SHARED_MEMORYITPUB个人空间&|7O_`T
x$kghluITPUB个人空间\;vH4y h5A.B/k9Aa4B
x$ksmlruITPUB个人空间8dMX `8b&{{
v$resource_limitITPUB个人空间i GaXv.Y#t;{*b

.N(G$nwt s Ah0 可以用于4031的跟踪事件还有：ITPUB个人空间3a q:^)].Tl
alter system set events '4031 trace name heapdump level 2';
J4q] L|\FCPl0 alter system set events 'immediate trace name library_cache level 11';
cZ.`u6v ^ I1v's5Agb0 ITPUB个人空间I2PbC}$l
对于可重现的4031，使用statspack在比较大的时间粒度下，持续的收集资料，找出引发4031出现的时段，在再相应的时段中，用自己的脚本，以比较高的频率（比较细的时间粒度）收集有针对性的资料，对于发现错误的原凶，也很有帮助。另外，对你所使用的数据库版本4031方面的BUG要有所了解，很多4031问题，都是由BUG引发的。ITPUB个人空间QuT(C[3X?r

3w)Kqr4R.L0 [ 本帖最后由 晶晶小妹 于 2008-3-26 07:55 编辑 ]

论坛模式 推荐 收藏 分享给好友 推荐到圈子 管理

TAG:

DBA 足迹 jimhou 发布于2008-03-26 08:27:55
顶
dhs0227的个人空间 dhs0227 发布于2008-03-26 08:39:03
頂
bluemoon0083发布于2008-03-26 08:48:07
不错顶一下
Oracle的琐碎生活 yuxuan 发布于2008-03-26 09:00:15
很详细，学习
vepeta发布于2008-03-26 09:15:34
学习！
mustapha的个人空间 mustapha 发布于2008-03-26 09:22:18
分析的很清晰，支持
fikong2005的个人空间 fikong2005 发布于2008-03-26 09:37:11
先顶再看
rollingpig的个人空间 rollingpig 发布于2008-03-26 09:38:12
hehe
8eeY]6N'G这个procedure ,当年我写过，就是为了向开发说明 shared_pool再大，也不能开太大的open cursors. 当然，为了速度我只parse 而不执行。
3z?%ik.~[php]
gyH4i_,Ecfor i in 1..850 loop   --即可以消耗大量内存，又不会报出4031错误。space.itpub.netv'z)`jQ6R
     mcur:=dbms_sql.open_cursor;
j
cw0g7b&M6w8S[|*MITPUB个人空间     msql:='select id from t3 where myid='||to_char(i);  ITPUB个人空间nl it~        }mgU
     dbms_sql.parse(mcur,msql,dbms_sql.native);
0~upy'q?.b?.ru&e   end loop;
m&P
u1E.CR5]&f3[[oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net   dbms_sql.close_cursor(mcur);oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net,n1{9t aoAd
end;
R4n
NF/B
@vgOS"G..j/pwD4T-|\I
.
8E1w4p6w)_"D?oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net[/php]
W
G r'ByJOeoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net另外，找元凶的更好方法是看 v$open_cursor+V$sqlArea group by SID
W QX~
r?D[php]``a,U5|z3BA9zo
select /*+ordered use_hash(c s) no_merge(s) no_merge(c) */ITPUB个人空间;f9E9@c q
sid,sum(sharable_mem)/1024/1024 from
l6j&J
AYv$open_cursor c , v$sqlarea s
%[,l!y"Td3g?Yoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netwhere c.hash_value =s.hash_valueoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net5TY4fZF
group by c.sidspace.itpub.net?6y,d
Wz/f1T
qkH
order by 2 descD8D-k/Uev8K(Q `
...
^!vn"|(D:?2S6ygITPUB个人空间...
u        Tt q }[/php]oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net4P[2mi9K0^'K#@u
O.kGpVp].q2F/{+m
立刻就可以找出占用大量share pool memory的SIDGj.l+u6x"XD
然后需要的话再select * from v$open_cursor where sid = .. 看到具体语句
q5v!^+m_R
#btwAB)Gspace.itpub.net[ 本帖最后由 rollingpig 于 2008-3-26 09:40 编辑 ]
Alienovo的个人空间 Alienovo 发布于2008-03-26 09:47:50
不错  学习一下
AAA wangfans 发布于2008-03-26 10:00:02
学习
jieyancai发布于2008-03-26 10:04:26
透过现象看本质，强！
五“宅”一生发布于2008-03-26 10:39:56
学习，支持晶晶！
paul oracle my love paulyibinyi 发布于2008-03-26 11:26:45
不错
john_77的个人空间 john_77 发布于2008-03-26 11:29:22
学习了
cyt2005发布于2008-03-26 11:44:27

QUOTE:

原帖由 rollingpig 于 2008-3-26 09:38 发表
&z;Tr8YS$FOhehe z)PD\-u3L%q3z
这个procedure ,当年我写过，就是为了向开发说明 shared_pool再大，也不能开太大的open cursors. 当然，为了速度我只parse 而不执行。
Vj&Sm2vITPUB个人空间[php]
y]Nn6`F-nspace.itpub.netfor i in 1..850 loop   --即可以消耗大量内存，又不会报出4031错误。
Z
~)pL1C3@     mcur:=dbms_sql.open_cursor;
+v        YDX+jW;ym     msql:='select id from t3 where myid='||to_char(i);  
y iXa;lB/Ad4zRspace.itpub.net     dbms_sql.parse(mcur,msql,dbms_sql.native);
X!O
Zd&d   end loop;
PX%L'cl
~u2X q   dbms_sql.close_cursor(mcur);space.itpub.net f+AK/nU&W \m@
end;
[
y^J&rf I/o..
Sj"c'm;G
wjW^.
*`P`q
`D|a;Ip[/php]C~_.n+N0E&\:B
另外，找元凶的更好方法是看 v$open_cursor+V$sqlArea group by SIDspace.itpub.net!O*Mn+x
sS+V|k
[php]I
M%KG$F7K)a
select /*+ordered use_hash(c s) no_merge(s) no_merge(c) */ITPUB个人空间s
W5D4WT:];R~
sid,sum(sharable_mem)/1024/1024 from }{*qf)Q0gM3B
s,R-L
v$open_cursor c , v$sqlarea s
^
s7cZ_3?{where c.hash_value =s.hash_value
1`!Lh.pN6K6ngroup by c.sid
P5N)hZf6\border by 2 desc
~1S5w*Y@o{
e
jITPUB个人空间...space.itpub.net4] oB(d
YH2aoYWx
...
Mv3n8M(v#J[/php]
Q(n6|!F@ o)U@+~ITPUB个人空间
I"o*b't6Rl立刻就可以找出占用大量share pool memory的SID
        a4Q+`8rw6n3N/b?然后需要的话再select * from v$open_cursor where sid = .. 看到具体语句
u0n3G5^D7woracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netmh:~7^S-I

@l7n'g0f^
Akspace.itpub.net"l8P3JZ&W7P!Os
斑竹使问题由繁入简
feng_xin的个人空间 feng_xin 发布于2008-03-26 11:48:22

QUOTE:

原帖由 rollingpig 于 2008-3-26 09:38 发表 ITPUB个人空间        o/sn|:`k*|
hehe
!NMxv3`B6EZoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net这个procedure ,当年我写过，就是为了向开发说明 shared_pool再大，也不能开太大的open cursors. 当然，为了速度我只parse 而不执行。ITPUB个人空间E(L8[Z;\a(q1Z
[php]
`|.m8L;?*a.Gfor i in 1..850 loop   --即可以消耗大量内存，又不会报出4031错误。
A#e;`H5C(Nspace.itpub.net     mcur:=dbms_sql.open_cursor;
$p.@(o4g2e:C:HC        ^
g+?g9A
d     msql:='select id from t3 where myid='||to_char(i);  
9k2S
q)s|ITPUB个人空间     dbms_sql.parse(mcur,msql,dbms_sql.native);
!k
j5@"d.aQ5g$B s   end loop;cP?%WX*q#Us'p
   dbms_sql.close_cursor(mcur);
u,Gp^/_hkaITPUB个人空间end;!U5PXx,XF E3^w
..
Oo&`QvQ1mQITPUB个人空间.
K4l%CFY\jAoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net[/php]oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netM(m9F        j'q fG
另外，找元凶的更好方法是看 v$open_cursor+V$sqlArea group by SID

F'j}"@&e7v O'p4}oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net[php]space.itpub.net[-Mh'M3N\~%FZ0v
select /*+ordered use_hash(c s) no_merge(s) no_merge(c) */
(r
M{ p$@;Uspace.itpub.netsid,sum(sharable_mem)/1024/1024 from space.itpub.net7GW2H2i2?"\Yk4N6o
v$open_cursor c , v$sqlarea s
@(Jb3w,{4iwhere c.hash_value =s.hash_valuespace.itpub.net3I0y8g        xK$s'U4I"G
group by c.sideBLu"?$s$ju2~qNo
order by 2 desc
1q+\_wu7`(F...oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netvV2x
}+c]Q p
...ITPUB个人空间5H&Qk]T
[/php]+Zn_P2tI7\P I

0_E:A7o/Y&vIoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net立刻就可以找出占用大量share pool memory的SID
-@BKk+MddEITPUB个人空间然后需要的话再select * from v$open_cursor where sid = .. 看到具体语句
`5yZ0N
C Jt
oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net3|ISn R^TI
7gz&L/h.CQ_.A5A
space.itpub.net        Z@
`
w^!S&A;I
4031的错误大部分是未绑定变量SQL语句引起的。排除bug后，就要从v$sqlarea与v$open_cursor中找出元凶sql语句。space.itpub.netL"N9h1l3zo$Zye r}

lsS~4h!ya元凶有两种：
` I/N}4KL+?1， 未绑定变量并且大量执行的sql, 这种sql就是晶晶模拟出来的现象。可以通过如下SQL语句抓出来。
X\{OnkU(n
]oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net先找出sql:

CODE:

select count(*), sum(sharable_mem),substr(sql_text,50) from v$sqlarea group by substr(sql_text,50) order by 1,2;substr取的字符的个数，根据应用进行调整。DN9{F] Cc

cNW*wRspace.itpub.net然后根据找出的SQL确定是哪个应用执行的

CODE:

select sid,sharable_mem,s.sql_text from v$open_cursor c , v$sqlarea s where c.hash_value =s.hash_value and s.sql_text like 'xxxxxxxx%'2， 单条SQL需要大量内存空间，如果内存里有空间但是是由很多小空间碎片组成的话，当执行这个占用内存大的单条sql时，由于不能分配空间，而产生4031错误。这种sql一般来说都是够长够复杂的sql. 可以通过如下SQL语句抓出来。
)d!bZ:N q }.~ E;TITPUB个人空间先找出sql

CODE:

select sid,s.sharable_mem,s.sql_text,s.ADDRESS,s.HASH_VALUE from v$open_cursor c , v$sqlarea s where c.hash_value =s.hash_value order by sharable_mem;通常sql_text不能把sql显示完整，用如下查询找出完整的sql.

CODE:

select a.address,a.hash_value,a.piece,a.sql_text,a.command_type from v$sqltext a,
(select sid,s.sharable_mem,s.sql_text,s.ADDRESS,s.HASH_VALUE from v$open_cursor c , v$sqlarea s where c.hash_value =s.hash_value and s.sharable_mem>xxxxx) b
where a.address=b.address and a.hash_value=b.hash_value
order by 1,2,3;
guyuanli的个人空间 guyuanli 发布于2008-03-26 11:53:04
学习了,支持晶晶!
qsxing发布于2008-03-26 12:16:42
学习了....
isongshu发布于2008-03-26 12:31:21
学习一下
jiangjh62发布于2008-03-26 13:39:09
3Q ,很不错
晶晶小妹的个人空间 晶晶小妹 发布于2008-03-26 18:19:21

QUOTE:

原帖由 rollingpig 于 2008-3-26 09:38 发表
"aI7w*v
QH-n5Jk*doracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.nethehe space.itpub.netiLB*[0Z
这个procedure ,当年我写过，就是为了向开发说明 shared_pool再大，也不能开太大的open cursors. 当然，为了速度我只parse 而不执行。#W8q/HY?x
[php]
6l?$H(~t*Bfor i in 1..850 loop   --即可以消耗大量内存，又不会报出4031错误。gI0F
zRD1?_#I
     mcur:=dbms_sql.open_cursor;^(i2jL }m1~
     msql:='select id from t3 where myid='||to_char(i);  
Yc5u1Z,as1EITPUB个人空间     dbms_sql.parse(mcur,msql,dbms_sql.native);Qrh7E u8Et WE_*a F
   end loop;
;X4W'Z2ve8H!|UITPUB个人空间   dbms_sql.close_cursor(mcur);
g#@        O$^        R&oyITPUB个人空间end;oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net&|0j%x7fi8P
..
!\ M?2A*GY1L!X1LN.
8?/q-?*`)oX        ^oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net[/php];Ny DLL{"Z
另外，找元凶的更好方法是看 v$open_cursor+V$sqlArea group by SIDoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net`7tkv        n|s$}:X
[php]\6W4Jrr_
select /*+ordered use_hash(c s) no_merge(s) no_merge(c) */
;?d
S"D*Ua
uGsid,sum(sharable_mem)/1024/1024 from
0OK#D6f&I        D]2a$Y ispace.itpub.netv$open_cursor c , v$sqlarea s)j/WU*s2L:]+B4}5f^
where c.hash_value =s.hash_value
?t8^&x        q9H5c
Rspace.itpub.netgroup by c.sidITPUB个人空间
@#EvH9KD?
order by 2 desc
#W,B1S;_XQI4tITPUB个人空间...oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netMa8P"\"I sz2IM.]3G
...space.itpub.net
LU|v N
[/php]x4D\*f(mCe

#M j$z&Qt*W4NITPUB个人空间立刻就可以找出占用大量share pool memory的SIDoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net&E&r|
|\W
c^ _|3oi
然后需要的话再select * from v$open_cursor where sid = .. 看到具体语句
space.itpub.net$Z0Jjg7o

$y]R2d/uH]sspace.itpub.netxakdB:GB%^.iy p
老大补充的好，大家还有什么好意见补充一下，我写个专门“抓元凶”的脚本，也方便一下初学者。
DragonBill的个人空间 DragonBill 发布于2008-03-26 18:39:02

liyongdong的个人空间 liyongdong 发布于2008-03-27 11:14:14
支持
louis_xu的个人空间 louis_xu 发布于2008-03-27 12:22:48
小妹有日子没操刀了
3hy(kCgI我顶
eagle_fan的个人空间 eagle_fan 发布于2008-03-27 12:46:54
select substr(sql_text,1,40),count(*) from v$sql group by substr(sql_text,1,40) having count(*)>1000
wsny的个人空间-Thunder Ping wsny 发布于2008-03-27 13:04:06
小妹的开发团队的技术挺落后啊。 .l"\ynAMU$^pd
其实现在OR mapping的工具的存在，基本上可以避免绑定变量的问题了。
idoi发布于2008-03-27 13:32:33
顶，学习
leeking1105发布于2008-03-28 17:33:31
顶
hardman / 硬汉 zuohao_lu 发布于2008-03-28 18:16:36
学习
congya的个人空间 congya 发布于2008-03-28 19:27:09
学习一下,十分好呀!
我来说两句

(可选)

日历

« 2010-09-03  
日 一 二 三 四 五 六
   1234
567891011
12131415161718
19202122232425
2627282930  

数据统计

  • 访问量: 86329
  • 日志数: 32
  • 图片数: 4
  • 建立时间: 2008-02-15
  • 更新时间: 2008-05-29

RSS订阅

Open Toolbar