missing cursors in compare with open cursors

From: <keesdebie_at_gmail.com>
Date: 14 Jun 2006 07:21:03 -0700
Message-ID: <1150294863.897953.111580@y43g2000cwc.googlegroups.com>

In a session sometimes cursors are been opend but for some reason not parsed and closed.
Mostly it happens with applications where sometimes the statement crashed and the cursor will be a ghost.
With the script below you can asked the rdbms how many lost cursors there are.
Output script looks like:

DOC>  colomn 1 date
DOC>  colomn 2 showed all cursors which are opend not parsed of closed
DOC>  colomn 3 showed the current open cursors
DOC>  colomn 4 showed the difference off the ghost cursors
DOC>#

                                                         missing
                                                              or
                                                            open
                                                         cursors
                                                             not
                                              current     parsed
                                                 open         or
date                 ALL_INCLUDING_MISSING    cursors     closed
-------------------- --------------------- ---------- ----------
14-jun-2006 16:00:46                    89        283       -194

This query runs in 10g REL2 and give the above strainges values. Running in < 10g rel2
the output give a total open cursors and open cursoren and lost cursoren(if there are some)
total open cursors including missing = open cursors + lost cursors What has been changed in 10g REL2?

Script:
set lin 200
doc
  colomn 1 date
  colomn 2 showed all cursors which are opend not parsed of closed   colomn 3 showed the current open cursors   colomn 4 showed the difference off the ghost cursors #
col alle_including_missing heading "all|including|missing" col current_cursors heading "current|open|cursors" col lost heading "missing|or|open|cursors|not|parsed|or|closed" select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "date"
, s.all_including_missing
, oc.current_cursors
, s.all_including_missing-oc.current_cursors lost
from (select value all_including_missing

        from   v$sysstat
        where  statistic#=3
       ) s


,      (select count(1) current_cursors

        from   v$open_cursor
       ) oc

;
Regards,
Kees Received on Wed Jun 14 2006 - 09:21:03 CDT