open_cursors query

[mahajanakhil198]

The max value allowed for open_cursors is 65535. Is there any disadvantage if we set this value to max value?

[malay_biswal]

I do not see any issue.
But in practically speaking value should be less. What happens is if there is any error usually DBAs look that this session needed more open_curso and then look the code to figure out why it's requiring that much open_cursor. Mostly poor developement.

If you increase value mostly you will not able to figure out these kind of situations.

[vnktummala]

only disadvantage is if the cursors are not closing properly in the application code then the open cursors keep filling the buffer. Usually this won't happen in the base application but if there is any customizations done then there is a good chance that developers are forget to close the cursors properly or just overlook.


Thanks,

[PAVB]

The max value allowed for open_cursors is 65535. Is there any disadvantage if we set this value to max value?

Please remember open_cursors defines max number of cursors EACH session can concurrently have - 65K private sql areas for a single session?

Have you ever hit ORA-01000?

If not, that means system is not even using as many cursors as current value of open_cursors is set.

[mahajanakhil198]

Please remember open_cursors defines max number of cursors EACH session can concurrently have - 65K private sql areas for a single session?

Have you ever hit ORA-01000?

If not, that means system is not even using as many cursors as current value of open_cursors is set.

The customer faced the error ORA-01000. We recommended to increase the value to 2000 but he asked us what if he sets the maximum value. That's why, I just want to be sure.


It's Windows 2003 Server here on target side and RHEL from source side.