Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

open_cursors's impact?

ericfjchen
Regular Advisor

open_cursors's impact?

Originally , DB's open_cursors is 6000. However, programs still hitted ORA-01000: maximum open cursors exceeded. We decide to set open_cursors = 7000. But we don't know what impact this parameter do. Pls kindly explain it.

Thanks
Eric
3 REPLIES
Yogeeraj_1
Honored Contributor

Re: open_cursors's impact?

hi,

It will affect performance in that your applications will work where right now they fail ;)

Seriously, open_cursors will set a maximum number of cursors a process can open. If you set it to 1000 (the recommended setting) Oracle will actually allocate cursors 64 at a time, thus setting this artificially high does NOT have a negative impact on resource usage.

Note that, open_cursors is what controls how many cursors java/jdbc is going to open. A program that leaks cursors will ultimately hit this upper bound. To be able to identify those programs that must be fixed versus those programs which need more cursors than others, you may query the V$SESSTAT to identify sessions with more then 5,900 open cursors -- then, if you have that sid, you can goto v$open_cursor and see the cursors.

hope this helps!
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: open_cursors's impact?

hi again,

see also Metalink Note:1012266.6 with subject: Overview of ORA-1000 Maximum Number of Cursors Exceeded.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: open_cursors's impact?

Hi Eric,

I also think 6000 is too high for open_cursors parameter: I have it set with 255 and have no problem at all... Remenber that this value is for EACH SINGLE USER PROCESS. See Metalink Note 30781.1 for better understanding this parameter.

Best Regards,

Eric
Each and every day is a good day to learn.