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 '

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6897/viewspace-970388/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6897/viewspace-970388/