DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: open_cursors query

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    open_cursors query

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

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by mahajanakhil198 View Post
    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.
    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.

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by PAVB View Post
    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.
    Last edited by mahajanakhil198; 06-01-2010 at 09:37 AM. Reason: added more information
    lucky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width