open_cursors's impact?

[ericfjchen]

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

[Yogeeraj_1]

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

[Yogeeraj_1]

hi again,

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

regards
Yogeeraj

[Eric Antunes]

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