Home » RDBMS Server » Performance Tuning » open_cursors value is exceeded
open_cursors value is exceeded [message #65392] Tue, 07 September 2004 03:54 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #65398 is a reply to message #65393] Tue, 07 September 2004 21:32 Go to previous messageGo to next message
sri
Messages: 154
Registered: February 2000
Senior Member
Can i increase the SHARED_POOL_SIZE To resolve the above problem,
Re: open_cursors value is exceeded [message #65402 is a reply to message #65398] Thu, 09 September 2004 08:06 Go to previous messageGo to next message
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.
Re: open_cursors value is exceeded [message #65974 is a reply to message #65392] Sat, 12 February 2005 02:09 Go to previous message
Pravin Singh
Messages: 2
Registered: February 2005
Junior Member
You just need to increase the init.ora parameter of OPEN_CURSOR
Previous Topic: CBO vs RBO Explain plan
Next Topic: What is size of the INSTANCE
Goto Forum:
  


Current Time: Wed Feb 28 13:15:18 CST 2024