select
sum(decode(t.account_code,'资产类合计',nvl(t.et_value,0),0)),
sum(decode(t.account_code,'负债类合',nvl(t.et_value,0),0))
into v_asset_begin_year_sum,v_debt_begin_year_sum
from value_report t
sum(decode(t.account_code,'资产类合计',nvl(t.et_value,0),0)),
sum(decode(t.account_code,'负债类合',nvl(t.et_value,0),0))
into v_asset_begin_year_sum,v_debt_begin_year_sum
from value_report t
估计服务器资源太少了,CPU100%
因为有个存储过程在Oracle 8i数据库服务器中运行了半年多正常。
现在移植到Oracle9i数据库服务器中,结果发现对存储过程编译时死机,CPU100%。
各位高手帮忙分析一下问题可能出在什么地方?
[select
sum(decode(t.account_code,'资产类合计',nvl(t.et_value,0),0)),
sum(decode(t.account_code,'负债类合',nvl(t.et_value,0),0))
into v_asset_begin_year_sum,v_debt_begin_year_sum
from value_report t]
这个是提示出问题的语句。
w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr;我们就经常遇到这种问题,当一个session调用了数据库中的某个包,另一个session长是重新创建或者编译该包时,便会出现这种情况~~导致两边都停止响应解决的办法是编译时让调用该包的用户切断连接,如果已发生ddl锁,则只能杀掉其进程