application support

application open cursor exceeding
—Total cursors open, by session:
SQL> select a.value, s.username, s.sid, s.serial#
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name = ‘opened cursors current’
5 order by s.username;

VALUE USERNAME SID SERIAL#
———- —————————— ——- ———-
508 AAA 11901 28
448 AAA 3135 709
453 AAA 3760 3442
446 AAA 4389 2907
410 AAA 5022 1782
410 AAA 5651 1118
132 AAA 6265 675
496 AAA 8773 56
485 AAA 10649 5053

—Find the Query Address
SELECT COUNT(*), address
FROM v$open_cursor
WHERE sid = 11901
GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);

COUNT(*) ADDRESS
———- —————-
70 00000000FA260070
143 000000015EE776D8
144 000000015C417048
144 000000015C4167C8

—find the SQL Statement
SELECT address,sql_fulltext
FROM v$sql
WHERE address IN (‘00000000FA260070′,’000000015EE776D8′,’000000015C417048′,’000000015C4167C8’);

ADDRESS SQL_FULLTEXT
—————- ——————————————————————————–
000000015C417048 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM3=:1
000000015C417048 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM3=:1
000000015C417048 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM3=:1
000000015C417048 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM3=:1
000000015EE776D8 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM2=:1
000000015EE776D8 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM2=:1
00000000FA260070 select MCC,MNC from TBLAP_NAME WHERE LAC=:1 and NAS_IP=:2
00000000FA260070 select MCC,MNC from TBLAP_NAME WHERE LAC=:1 and NAS_IP=:2
000000015C4167C8 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM4=:1
000000015C4167C8 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM4=:1
000000015C4167C8 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM4=:1
000000015C4167C8 select count(*) from TBLRADIUSCUSTOMER WHERE PARAM4=:1

Advertisement