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

Thread: open_cursors

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    open_cursors

    Hi,

    I am running the below query to see the current open_cursors in my one of the databases.

    select max(a.value) as highest_open_cur, p.value as max_open_cur
    from v$sesstat a, v$statname b, v$parameter p
    where a.statistic# = b.statistic#
    and b.name = 'opened cursors current'
    and p.name= 'open_cursors'
    group by p.value;
    max_open_cur is keep on increasing and some times application are hitting ORA-1000. When I talked with developers they obviously says they are closing all the cursors.

    I have tried with SQLNET.EXPIRE_TIME=10 but that is not useful.

    Can some one suggest me a way to close the open_cursors by adding any parameter in sqlnet.ora or listener.ora? Appreciate your help.

    Thanks,
    Vijay Tummala

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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ORA-1000 means a session is exceeding the value set on open_cursors intialization parameter. Please note this value is "per-session" as opposite to "per-system".

    What's current value for open_cursors parameter?
    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.

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi PAVB,

    currently open_cursors=2000

    Is v$open_cursor is the right view to find the open cursors group by SID? I want to gather some evidence which session is keep on opening cursors and not closing before go back to my development team.

    I believe Oracle cleans the open_cursors based on it's famous LRU algorithm. Is there any way or any hidden parameter to tell Oracle to clean the open cursors if they are not being reused for a certain period of time?

    Thanks,
    Vijay Tummala

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

  4. #4
    Join Date
    Oct 2006
    Posts
    175
    Is v$open_cursor is the right view to find the open cursors group by SID?
    Yes, I always go for the same table.

    I believe Oracle cleans the open_cursors based on it's famous LRU algorithm. Is there any way or any hidden parameter to tell Oracle to clean the open cursors if they are not being reused for a certain period of time?
    I don't know if there is anything to do that for you, but you can monitor open cursor as your need using following, for a session:
    select value from v$mystat m, v$statname s where m.statistic# = s.statistic# AND s.name='opened cursors current' ;

    Also, I doubt it must've to do with implicit cursors, check your queries, close cursors explicitely wherever possible. You can %FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT to some extent to control opening cursors.

    HTH
    gtcol

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks Gtcol.

    I have taken the report from v$open_cursor and provided the list to the development team to check the code and close all the cursors properly. They have fixed some code and i can see the significant improvement now.

    Thanks,
    Vijay Tummala

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

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