open_cursors

Hi,

I am running the below query to see the current open_cursors in my one of the databases.

Quote:

---

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

---

max_open_cur is keep on increasing and some times application are hitting ORA-1000. When I talked with developers they obviously says they are closing all the cursors.

I have tried with SQLNET.EXPIRE_TIME=10 but that is not useful.

Can some one suggest me a way to close the open_cursors by adding any parameter in sqlnet.ora or listener.ora? Appreciate your help.

Thanks,

ORA-1000 means a session is exceeding the value set on open_cursors intialization parameter. Please note this value is "per-session" as opposite to "per-system".

What's current value for open_cursors parameter?

Hi PAVB,

currently open_cursors=2000

Is v$open_cursor is the right view to find the open cursors group by SID? I want to gather some evidence which session is keep on opening cursors and not closing before go back to my development team.

I believe Oracle cleans the open_cursors based on it's famous LRU algorithm. Is there any way or any hidden parameter to tell Oracle to clean the open cursors if they are not being reused for a certain period of time?

Thanks,

Quote:

---

Is v$open_cursor is the right view to find the open cursors group by SID?

---

Yes, I always go for the same table.

Quote:

---

I believe Oracle cleans the open_cursors based on it's famous LRU algorithm. Is there any way or any hidden parameter to tell Oracle to clean the open cursors if they are not being reused for a certain period of time?

---

I don't know if there is anything to do that for you, but you can monitor open cursor as your need using following, for a session:
select value from v$mystat m, v$statname s where m.statistic# = s.statistic# AND s.name='opened cursors current' ;

Also, I doubt it must've to do with implicit cursors, check your queries, close cursors explicitely wherever possible. You can %FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT to some extent to control opening cursors.

HTH
gtcol

Thanks Gtcol.

I have taken the report from v$open_cursor and provided the list to the development team to check the code and close all the cursors properly. They have fixed some code and i can see the significant improvement now.

Thanks,