EM Event: Metrics “Current Open Cursors Count” is at %

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s