The max value allowed for open_cursors is 65535. Is there any disadvantage if we set this value to max value?
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.
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.
Try hard to get what you like OR you will be forced to like what you get.
Please remember open_cursors defines max number of cursors EACH session can concurrently have - 65K private sql areas for a single session?
Originally Posted by mahajanakhil198
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
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.
Originally Posted by PAVB
It's Windows 2003 Server here on target side and RHEL from source side.
Last edited by mahajanakhil198; 06-01-2010 at 09:37 AM.
Reason: added more information
Click Here to Expand Forum to Full Width