| open_cursors value is exceeded [message #65392] |
Tue, 07 September 2004 03:54  |
sri
Messages: 154
Registered: February 2000
|
Senior Member |
|
|
Kindly provide me the valuable suggestion for open_cursors.
I got an error that open_cursors exceeded.
|
|
|
|
| Re: open_cursors value is exceeded [message #65393 is a reply to message #65392] |
Tue, 07 September 2004 04:52  |
Reema
Messages: 50
Registered: July 2003
|
Member |
|
|
Hi,
You can do two things,
1) Change the inti.ora parameter open_cursors = <increased-no>
2) In your application code , use For ...Cursors, coz that automatically closes the cursor.
3) If you cannot use For..Cursor, take care to close the cursors. Otherwise the opened cursors will persist in memory and increase the open_cursors count unnecesarrily.
Any doubts , revert back.
Reema
|
|
|
|
| Re: open_cursors value is exceeded [message #65394 is a reply to message #65393] |
Tue, 07 September 2004 16:53 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Often an excessive number of open cursors is due to coding bugs (like not closing cursors when exceptions occur). Monitor the open cursors on a new connection to see if they simply grow over time (indicating a cursor leak). I wouldn't just increase open_cursors without investigating...
select * from v$sesstat where statistic#= 3;
--Try something like this (by user)
select user_name, count(*) from v$open_cursor
group by user_name, sid
having count(*) >= 100 -- whatever number you choose
order by 2 desc;
This type of code will work too - but shouldn't just be used to cover up for bad logic:
IF my_cur%ISOPEN THEN
CLOSE my_cur;
END IF;
|
|
|
|
|
|
| Re: open_cursors value is exceeded [message #65402 is a reply to message #65398] |
Thu, 09 September 2004 08:06 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
No, the shared pool is a memory resource for caching dictionary info and parsed statements. open_cursors is just an upper limit number. Increasing/decreasing it won't change performance. I'd say it's main purpose is to help you identify cursor leaks and maybe poor programming. If you understand why your app needs so many open cursors, then by all means increase it.
If you open a cursor at a lower level of scope (between a begin/end or in a procedure/function, then when you exit that scope level (you complete the begin/end of proc/funct, then any open cursors in that lower level will automatically be released. Ref-cursors are different because they could get opened at a lower level and the handle to that cursor is passed out - so it doesn't just close.
Run the open cursor queries (my previous posting) to monitor the open cursors. If they grow steadily over time - that could indicate a coding problem.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
