-
open_cursors
Hi,
I am running the below query to see the current open_cursors in my one of the databases.
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Is v$open_cursor is the right view to find the open cursors group by SID?
Yes, I always go for the same table.
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|