oracle超长游标代码值,ztwz项目游标超长最大数得分析过程

1.查看打开得游标汇总得情况：

select sid,hash_value,sql_text,count(*) from v$open_cursor group by sid,hash_value,sql_text order by 3 desc;[@more@]

ztwz项目游标超长最大数得分析过程

1.查看打开得游标汇总得情况：

select sid,hash_value,sql_text,count(*) from v$open_cursor group by sid,hash_value,sql_text order by 3 desc;

2.发现sid＝34得进程有大量的游标打开如下sql，直接导致超长最大游标数：

SELECT NULL AS TABLE_CAT,

O.OWNER AS TABLE_SCHEM,

O.OBJECT_NAME AS TABLE_NAME,

O.OBJECT_TYPE AS TABLE_TYPE,

NULL AS REMARKS

FROM ALL_OBJECTS O

WHERE O.OWNER LIKE :1 ESCAPE '/'

AND O.OBJECT_NAME LIKE :2 ESCAPE '/'

AND O.OBJECT_TYPE IN ('xxx', 'TABLE')

ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME;

3.查看sid=34得session具体在作什么操作：

select pid from v$process where addr=(select paddr from v$session where sid=34);

--result is: 33

oradebug setorapid 33

oradebug event 10046 trace name context forever,level 12;

观察sid=34得session得v$open_cursor中关于SELECT NULL AS TABLE_CAT得cursor是否增加了，如果增加了就：

oradebug event 10046 trace name context off;

oradebug TRACEFILE_NAME;

4.分析输出日值可以发现可疑问题，每当出现SELECT NULL AS TABLE_CAT时，都是对HI_PSNDOC_DEPTCHG表进行操作，据此可以怀疑问题可能跟对HI_PSNDOC_DEPTCHG表得操作得代码有问题。这需要开发来查看相关得代码，确认问题，下面是日志中得内容：

sql1:

SELECT NULL AS table_cat,

o.owner AS table_schem,

o.object_name AS table_name,

o.object_type AS table_type,

NULL AS remarks

FROM all_objects o

WHERE o.owner LIKE :1 ESCAPE '/'

AND o.object_name LIKE :2 ESCAPE '/'

AND o.object_type IN ('xxx', 'TABLE')

ORDER BY table_type, table_schem, table_name；

其中的object_name是:HI_PSNDOC_DEPTCHG

sql2:

select distinct a.pk_om_job from om_job a, bd_psndoc b, sm_userandclerk c, hi_psndoc_deptchg d where c.userid

= :1 and b.pk_corp = :2 and a.isabort = 'N' and a.dr = 0 and c.pk_psndoc = b.pk_psnbasdoc and a.pk_om_job =

b.pk_om_job and d.pk_psndoc = b.pk_psndoc and nvl ( d.lastflag, 'Y' ) = 'Y' and nvl ( d.poststat, 'Y' ) = 'Y';

=====================

PARSING IN CURSOR #961 len=338 dep=0 uid=46 oct=3 lid=46 tim=1395935342533 hv=669053422 ad='8b414308'

SELECT NULL AS table_cat,

o.owner AS table_schem,

o.object_name AS table_name,

o.object_type AS table_type,

NULL AS remarks

FROM all_objects o

WHERE o.owner LIKE :1 ESCAPE '/'

AND o.object_name LIKE :2 ESCAPE '/'

AND o.object_type IN ('xxx', 'TABLE')

ORDER BY table_type, table_schem, table_name

END OF STMT

PARSE #961:c=30000,e=21956,p=0,cr=6,cu=0,mis=1,r=0,dep=0,og=0,tim=1395935342526

BINDS #961:

bind 0: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=4000 offset=0

bfp=ffffffff7c317ae8 bln=4000 avl=04 flg=05

value="CWDB"

bind 1: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=4000 offset=0

bfp=ffffffff7c33f060 bln=4000 avl=17 flg=05

value="HI_PSNDOC_DEPTCHG"

EXEC #961:c=0,e=4468,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1395935347241

WAIT #961: nam='SQL*Net message to client' ela= 2 p1=1952673792 p2=1 p3=0

WAIT #961: nam='SQL*Net message from client' ela= 195 p1=1952673792 p2=1 p3=0

WAIT #961: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0

WAIT #961: nam='SQL*Net message from client' ela= 334 p1=1952673792 p2=1 p3=0

WAIT #961: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0

FETCH #961:c=0,e=557,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=4,tim=1395935348655

WAIT #961: nam='SQL*Net message from client' ela= 553 p1=1952673792 p2=1 p3=0

WAIT #962: nam='SQL*Net message to client' ela= 2 p1=1952673792 p2=1 p3=0

WAIT #962: nam='SQL*Net message from client' ela= 300 p1=1952673792 p2=1 p3=0

=====================

PARSING IN CURSOR #962 len=328 dep=0 uid=46 oct=3 lid=46 tim=1395935351844 hv=481727585 ad='8bed5138'

select distinct a.pk_om_job from om_job a, bd_psndoc b, sm_userandclerk c, hi_psndoc_deptchg d where c.userid = :1 and b.pk_corp = :2 and a.isabort = 'N' and a.dr = 0 and c.pk_psndoc = b.pk_psnbasdoc and a.pk_om_job = b.pk_om_job and d.pk_psndoc = b.pk_psndoc and nvl ( d.lastflag, 'Y' ) = 'Y' and nvl ( d.poststat, 'Y' ) = 'Y'

END OF STMT

PARSE #962:c=10000,e=1958,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1395935351836

BINDS #962:

bind 0: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=4000 offset=0

bfp=ffffffff7c323338 bln=4000 avl=20 flg=05

value="1009AA00000000000EGH"

bind 1: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=4000 offset=0

bfp=ffffffff7c322380 bln=4000 avl=04 flg=05

value="1009"

EXEC #962:c=10000,e=12210,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1395935364169

WAIT #962: nam='SQL*Net message to client' ela= 2 p1=1952673792 p2=1 p3=0

WAIT #962: nam='SQL*Net message from client' ela= 191 p1=1952673792 p2=1 p3=0

WAIT #962: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0

WAIT #962: nam='SQL*Net message from client' ela= 200 p1=1952673792 p2=1 p3=0

FETCH #962:c=0,e=65,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=4,tim=1395935364916

WAIT #962: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0

WAIT #962: nam='SQL*Net message from client' ela= 189 p1=1952673792 p2=1 p3=0

WAIT #962: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0

WAIT #962: nam='SQL*Net message from client' ela= 162 p1=1952673792 p2=1 p3=0

STAT #962 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT UNIQUE '