oracle 游标泄露问题查找

游标会占用系统资源,oracle中游标分配在shared pool内存池,用完了连接(connection)、语句(statement,preparedStatement)和结果集(resultSet)一定要记得释放,不然系统打开游标会持续上升,直到达到系统设置的阈值,无法获取游标的事务就会失败。

查看系统配置的最大打开游标数量和当前已打开游标数量

sql> select (select count(*) from V$OPEN_CURSOR) as opened_cursors, 
(select value from v$parameter where name='open_cursors') as max_cursors from dual;
OPENED_CURSORS MAX_CURSORS
-------------- ------------------------------
          1362 65535

查询会话打开的游标

sql> select s.sid, s.username, s.osuser,s.machine, a.value 
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null 
order by a.value desc;
       SID USERNAME                       OSUSER                         MACHINE                             VALUE
---------- ------------------------------ ------------------------------ ------------------------------ ----------
       405 TT                             NETWORK?SERVICE                WORKGROUP\VIRT-APP-EXTERN              51
       466 TT                             NETWORK?SERVICE                WORKGROUP\VIRT-APP                     49
       491 TT                             Administrator                  WORKGROUP\VIRT-APP                     13
       422 TT                             Administrator                  WORKGROUP\VIRT-APP                     11
       475 TT                             Administrator                  WORKGROUP\VIRT-APP                     10
......

或者

sql> select o.sid, s.username, s.osuser, s.machine, count(*) num 
from v$open_cursor o, v$session s 
where o.sid = s.sid 
group by o.sid,s.username, s.osuser, s.machine 
order by num desc;
 SID USERNAME                       OSUSER                         MACHINE                               NUM
---------- ------------------------------ ------------------------------ ------------------------------ ----------
       464 TT                             NETWORK?SERVICE                WORKGROUP\VIRT-APP-EXTERN              34
       471 TT                             NETWORK?SERVICE                WORKGROUP\VIRT-APP-EXTERN              30
       475 TT                             Administrator                  WORKGROUP\VIRT-APP                     28
       491 TT                             Administrator                  WORKGROUP\VIRT-APP                     27
......

查询会话执行的sql语句

sql> select sid, sql_text, count(sql_text) as num
from v$open_cursor
group by sid, sql_text having count(sql_text)>5
order by num desc;
      SID SQL_TEXT                                                            NUM
---------- ------------------------------------------------------------ ----------
       464 select b.*,bb.buildno,bb.BUILDNAME from bldroom b join build         40
       464 select t2.businessname,t3.firstname,t1.opion,t1.receivedatet         36
       464 select distinct t2.certid,t2.certno from rightcertroom t1 jo         34
......

或者更直接的查询每个sql语句打开的游标数量

sql> select s.sid, s.osuser,s.machine, o.sql_text, count(o.sql_text) as num
from v$open_cursor o
join v$session s on o.sid=s.sid
group by s.sid, s.osuser,s.machine,o.sql_text having count(o.sql_text)>50
order by num desc;
      SID OSUSER     MACHINE              SQL_TEXT                                                            NUM
---------- ---------- -------------------- ------------------------------------------------------------ ----------
       464 tomcat8    clean                select t2.businessname,t3.firstname,t1.opion,t1.receivedatet         35
       464 tomcat8    clean                select b.*,bb.buildno,bb.BUILDNAME from bldroom b join build         34
       464 tomcat8    clean                select distinct t2.certid,t2.certno from rightcertroom t1 jo         28
......

找到对应的SQL语句就可以审计对应的代码,查找资源泄露的情况。

===
一个人能够洋洋得意地随著军乐队在四列纵队里行进,单凭这一点就足以使我对他轻视。他所以长了一个大脑,只是出于误会;单单一根脊髓就可满足他的全部需要了。文明国家的这种罪恶的渊薮,应当尽快加以消灭。由命令而产生的勇敢行为,毫无意义的暴行,以及在爱国主义名义下一切可恶的胡闹,所有这些都使我深恶痛绝。 —— 爱因斯坦

    分享到:

Leave a Reply

Your email address will not be published. Required fields are marked *