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

Thread: open cursors

  1. #1
    Join Date
    Oct 2000
    Posts
    17

    Question

    Hi,

    Where can I find how many cursors are currently opened.

    I tried with following query, will it give accurate result ?

    select count(*) from v$open_cursor;
    COUNT(*)
    ----------
    51
    1 row selected.

    My init.ora shows Open_cursors = 150 , but I am getting error that open cursor exceed .

    V$sysstat shows :

    select value from v$sysstat where statistic# = 3;
    VALUE
    ----------
    200
    1 row selected.

    which one is correct ?

    Rgds,
    gv.

  2. #2
    Join Date
    Aug 2000
    Posts
    87
    hi ,

    Execute this script which will give the output u required.


    REM
    REM =====================================================================
    REM
    REM AUD043
    REM
    REM Opened Cursors by session
    REM
    REM Vinod Kumar M.G.
    REM
    REM =====================================================================
    START envi

    START titredba "Aud043" -
    "Number of opened cursors by session"

    COL sid FOR 999 HEAD "SID" TRUNC
    COL user_ FOR A29 HEAD "OS USER" TRUNC
    COL username FOR A30 HEAD "ORA USER" TRUNC
    COL name FOR A39 HEAD "STATISTICS" TRUNC
    COL value FOR 99999990 HEAD "OPENED|CURSORS" TRUNC

    break on report
    compute sum of value on report

    SPOOL aud043

    SELECT v.sid ,
    v.username ,
    p.username user_,
    s.value
    FROM v$sesstat s,
    v$session v,
    v$process p
    WHERE s.sid = v.sid
    AND 3 = s.statistic#
    AND p.addr = v.paddr
    ORDER BY
    1
    /
    SPOOL OFF


    Cheers,
    Vinod


  3. #3
    Join Date
    Oct 2000
    Posts
    17
    Hi Vinod,

    Thanks for the help. Is V$sesstat holds cumilative statistics or current statistics. we have BMC Patrol installed on our DB server. When I start the patrol agent, it connects to Oracle DB to gather statistics, at specified intervals it gathers statistics. When I run your query first time I found 2 open cursors for this Patrol user. After One hour again I run that query and found that this time 20 open cursors for this user.

    I am not able to figureout what is really going wrong, and where to find the info.

    rgds,
    gv

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    v$sesstat stores the information for a session, if you have had 100 cursor during session it will say 100. that is why after one hour the information Patrol gathers is increasing.

    The qury you issued in your previous post gathers the total open cursors opened in the system. If you join v$session and v$open_cursor you could see the current open cursor for each user.

    Just a note, open_cursors īs value is per user not per system

  5. #5
    Join Date
    Oct 2000
    Posts
    17
    Please correct me if I am wrong, if v$sesstat show 100 , that means at one particular point of time that user have had 100 open cursors.

    Whenever I join V$session with V$open_cursor I only found 1 open cursor for this user Patrol, at the same time when I query V$sesstat value for this particualr user always growing, evry 5 five minutes it is increased by 1 (for every 5 minutes info is gathered from this particular user, that time it will run some pl/sql programs, once after gathering information it supposed to close all the cursors). In this case I feel It is not closing all the previously opened cursors, Am I rcorrect?


    rgds,
    gv




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