Hi all,
This is simple right?
Some dev is forgetting to close the cursors. 🙂
If you don’t know what I’m talking about, I couldn’t find better reference than this article by Tech on the Net.
As DBA, we can identify the application schema which is causing the issue by the following:
SQL> select user_name, count(*) from v$open_cursor group by user_name; USER_NAME COUNT(*) ------------------------------ ---------- SCHEMA1 116 SCHEMA2 339 SCHEMA3 25 SCHEMA4 235136 SCHEMA5 147 SCHEMA6 2 SCHEMA7 75 SCHEMA8 12 SCHEMA9 294
As you can see, schema 4 is causing the issue…
You can also filter by SQL:
SQL> select sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor group by sql_text, user_name order by count(*) desc;
SQL_TEXT OPEN CURSORS USER_NAME
------------------------------------------------------------ ------------ ------------------------------
DELETE FROM MYTABLEEXAMPLE WHERE CREATION_TIME < :1 1585 DB1
SELECT A.NAME, A.MIME_TYPE, A.DATA, A.ID FROM ASSETS A, SITE 734 DB1
select sys_context('userenv', 'instance') from dual 531 DB1
select value$ from props$ where name = 'GLOBAL_DB_NAME' 474 DB1
SELECT COUNT(*) FROM USERS_LOGINS WHERE USER_ID=:1 AND SESS 88 DB1
select value$ from props$ where name = 'GLOBAL_DB_NAME' 84 SYS
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U 84 SYS
SELECT ID, NAME, USER_ID, MIME_TYPE, ATTRIBUTE_ELEMENT, DATA 83 DB1
select /*+ connect_by_filtering */ privilege#,level from sys 82 SYS
select privilege# from sysauth$ where (grantee#=:1 or grante 82 SYS
select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON 82 SYS
SELECT SD.ID, SD.SITE_ID, SD.STYLE_ID, SD.TYPE, D.ID, D.NAME 73 DB1
SELECT ID, SEQ, LAYOUT_ID, BGCOLOR, BORDERCOLOR, BORDERSIZE, 68 DB1
And as you can see, the delete is the problem.
Or even filter by SID:
SQL> select sid, count(*) from v$open_cursor group by sid order by 2 desc; SID COUNT(*) ---------- ---------- 1184 1200 1773 929 746 755 3677 258 1338 246 3320 103 1441 88 2024 80 1190 78 954 75 3336 74 1318 73 2629 71 2518 66 2025 62 2516 61 365 56 474 55
It all depends on your root cause problem and you need to adapt to your need.
With SID, for example, you can investigate on prior sqls on this session and map the circunstance of issue, helping Dev to find and solve the problem.
Hope it helps!
See you next week!
