sql语句如下,这个sql在以前的低版本oracle是可以运行的,现在升级11g后就有问题。特点是查询没反应,但也不报错。另外运行其它的简单sql是可以正常查询出数据。大家看看啥回事。
select distinct tc.ym,tab.* from (
select ta.no,ta.pk_detail,explanation,deptcode,localcreditamount,localdebitamount,ta.free1,tb.valuecode from
(SELECT v.no, d.pk_detail,d.explanation,dpt.deptcode, d.localcreditamount, d.localdebitamount, d.free1
FROM BD_ACCSUBJ a, gl_detail d, BD_DEPTDOC dpt, gl_voucher v, gl_freevalue f, bd_glorgbook g, bd_glorg c
where c.entitytype = 0 and c.pk_entityorg = '1020' and c.dr <> 1
and dpt.pk_corp = c.pk_entityorg and c.pk_glorg = g.pk_glorg and a.pk_glorgbook = g.pk_glorgbook
and v.pk_glorgbook = g.pk_glorgbook
and d.pk_voucher = v.pk_voucher and a.PK_ACCSUBJ = d.PK_ACCSUBJ
and d.assid = f.freevalueid and f.valuecode = dpt.deptcode
and a.subjcode = '113307'
and v.discardflag <> 'Y' and v.dr <> 1
and (d.free1 is not null or trim(d.free1) <> '')
and (v.errmessage is null or trim(v.errmessage) = '')
--and (d.free2 is null or trim(d.free2) = '')
-- and to_number(concat(v.year, v.period)) = 200808
order by v.year, v.period, v.no, d.pk_detail) ta,
(SELECT v.no, d.pk_detail, f.valuecode
FROM BD_ACCSUBJ a, gl_detail d, gl_voucher v, bd_glorgbook g, bd_glorg c, gl_freevalue f, bd_subjass sa, bd_bdinfo bd, bd_currtype curr
where c.entitytype = 0 and c.pk_entityorg = '1020' and c.dr <> 1
and c.pk_glorg = g.pk_glorg and a.pk_glorgbook = g.pk_glorgbook
and v.pk_glorgbook = g.pk_glorgbook
and d.pk_voucher = v.pk_voucher and a.PK_ACCSUBJ = d.PK_ACCSUBJ
--and to_number(concat(v.year, v.period)) = 200810
and v.discardflag <> 'Y' and v.dr <> 1
and d.assid = f.freevalueid(+)
and (d.free1 is not null or trim(d.free1) <> '')
--and (d.free2 is null or trim(d.free2) = '')
and bd.bdcode = '73' and a.subjcode = '113307'
and sa.pk_bdinfo=f.checktype and bd.pk_bdinfo=sa.pk_bdinfo and a.pk_accsubj=sa.pk_accsubj
and d.pk_currtype = curr.pk_currtype
order by v.year, v.period, v.no, d.pk_detail) tb
where tb.pk_detail = ta.pk_detail ) tab,
(SELECT d.free1,concat(v.year, v.period) ym
FROM BD_ACCSUBJ a, gl_detail d, gl_voucher v, bd_glorgbook g, bd_glorg c, gl_freevalue f, bd_subjass sa, bd_bdinfo bd, bd_currtype curr
where c.entitytype = 0 and c.pk_entityorg = '1020' and c.dr <> 1
and c.pk_glorg = g.pk_glorg and a.pk_glorgbook = g.pk_glorgbook
and v.pk_glorgbook = g.pk_glorgbook
and d.pk_voucher = v.pk_voucher and a.PK_ACCSUBJ = d.PK_ACCSUBJ
and to_number(concat(v.year, v.period)) >= 201003
and to_number(concat(v.year, v.period)) <= 201003
and v.discardflag <> 'Y' and v.dr <> 1
and d.assid = f.freevalueid(+)
and (d.free1 is not null or trim(d.free1) <> '')
--and (d.free2 is null or trim(d.free2) = '')
and bd.bdcode = '73' and a.subjcode = '113307'
and sa.pk_bdinfo=f.checktype and bd.pk_bdinfo=sa.pk_bdinfo and a.pk_accsubj=sa.pk_accsubj
and d.pk_currtype = curr.pk_currtype
and valuecode = 'C04'
--and d.localdebitamount > 0.000001
order by v.year, v.period, v.no, d.pk_detail) tc
where tc.free1 like replace(tab.free1,tab.deptcode,'%')
select distinct tc.ym,tab.* from (
select ta.no,ta.pk_detail,explanation,deptcode,localcreditamount,localdebitamount,ta.free1,tb.valuecode from
(SELECT v.no, d.pk_detail,d.explanation,dpt.deptcode, d.localcreditamount, d.localdebitamount, d.free1
FROM BD_ACCSUBJ a, gl_detail d, BD_DEPTDOC dpt, gl_voucher v, gl_freevalue f, bd_glorgbook g, bd_glorg c
where c.entitytype = 0 and c.pk_entityorg = '1020' and c.dr <> 1
and dpt.pk_corp = c.pk_entityorg and c.pk_glorg = g.pk_glorg and a.pk_glorgbook = g.pk_glorgbook
and v.pk_glorgbook = g.pk_glorgbook
and d.pk_voucher = v.pk_voucher and a.PK_ACCSUBJ = d.PK_ACCSUBJ
and d.assid = f.freevalueid and f.valuecode = dpt.deptcode
and a.subjcode = '113307'
and v.discardflag <> 'Y' and v.dr <> 1
and (d.free1 is not null or trim(d.free1) <> '')
and (v.errmessage is null or trim(v.errmessage) = '')
--and (d.free2 is null or trim(d.free2) = '')
-- and to_number(concat(v.year, v.period)) = 200808
order by v.year, v.period, v.no, d.pk_detail) ta,
(SELECT v.no, d.pk_detail, f.valuecode
FROM BD_ACCSUBJ a, gl_detail d, gl_voucher v, bd_glorgbook g, bd_glorg c, gl_freevalue f, bd_subjass sa, bd_bdinfo bd, bd_currtype curr
where c.entitytype = 0 and c.pk_entityorg = '1020' and c.dr <> 1
and c.pk_glorg = g.pk_glorg and a.pk_glorgbook = g.pk_glorgbook
and v.pk_glorgbook = g.pk_glorgbook
and d.pk_voucher = v.pk_voucher and a.PK_ACCSUBJ = d.PK_ACCSUBJ
--and to_number(concat(v.year, v.period)) = 200810
and v.discardflag <> 'Y' and v.dr <> 1
and d.assid = f.freevalueid(+)
and (d.free1 is not null or trim(d.free1) <> '')
--and (d.free2 is null or trim(d.free2) = '')
and bd.bdcode = '73' and a.subjcode = '113307'
and sa.pk_bdinfo=f.checktype and bd.pk_bdinfo=sa.pk_bdinfo and a.pk_accsubj=sa.pk_accsubj
and d.pk_currtype = curr.pk_currtype
order by v.year, v.period, v.no, d.pk_detail) tb
where tb.pk_detail = ta.pk_detail ) tab,
(SELECT d.free1,concat(v.year, v.period) ym
FROM BD_ACCSUBJ a, gl_detail d, gl_voucher v, bd_glorgbook g, bd_glorg c, gl_freevalue f, bd_subjass sa, bd_bdinfo bd, bd_currtype curr
where c.entitytype = 0 and c.pk_entityorg = '1020' and c.dr <> 1
and c.pk_glorg = g.pk_glorg and a.pk_glorgbook = g.pk_glorgbook
and v.pk_glorgbook = g.pk_glorgbook
and d.pk_voucher = v.pk_voucher and a.PK_ACCSUBJ = d.PK_ACCSUBJ
and to_number(concat(v.year, v.period)) >= 201003
and to_number(concat(v.year, v.period)) <= 201003
and v.discardflag <> 'Y' and v.dr <> 1
and d.assid = f.freevalueid(+)
and (d.free1 is not null or trim(d.free1) <> '')
--and (d.free2 is null or trim(d.free2) = '')
and bd.bdcode = '73' and a.subjcode = '113307'
and sa.pk_bdinfo=f.checktype and bd.pk_bdinfo=sa.pk_bdinfo and a.pk_accsubj=sa.pk_accsubj
and d.pk_currtype = curr.pk_currtype
and valuecode = 'C04'
--and d.localdebitamount > 0.000001
order by v.year, v.period, v.no, d.pk_detail) tc
where tc.free1 like replace(tab.free1,tab.deptcode,'%')
当然,你现在的错误,肯定不是这个原因,因为你所执行的代码都是SELECT语句!
1.其他未提交事务锁定目前需要读取的资源,造成堵塞
你可以执行select * from v$session,看看row_wait_obj#那个字段有没有值不是-1的,如果有,说明数据库目前存在堵塞
2.数据库性能
这个你可以执行一下查询计划,看看sql的开销如何.就可以判定了
主要是这个原因,再楼上的兄弟们,ORACLE的读不会被阻塞的,不存在读被写锁住的情况。以下几个思路可以解决问题:1)比较旧版数据库 与 新版数据库的执行计划2)当在11g上执行时,查看v$session_wait, 看执行此sql的Session在等待啥事件,以此做出相应的处理3)当SQL执行几分钟后,从v$session_longops视图查看此SQL需多久才能执行完毕
ORDER BY 排序操作也是耗资源和时间的操作,可以通过加索引排序,这样就快了