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

Thread: open cursors

  1. #1
    Join Date
    Jul 2002
    Posts
    2
    This rather unsettling statistic has me scratching my head:

    SVRMGR> select name,value from v$sysstat where statistic#=3;
    NAME VALUE
    ---------------------------------------------------------------- ----------
    opened cursors current 3,8655E+10
    1 row selected.

    ......whereas........

    VALUE NUMBER
    SVRMGR> select name,value from v$sysstat where statistic#=192;
    NAME VALUE
    ---------------------------------------------------------------- ----------
    session cursor cache count 17504

    ........seems more reasonable yet the numbers don't add up

    SVRMGR> select maximum,count from v$session_cursor_cache;
    MAXIMUM COUNT
    ---------- ----------
    300 0
    1 row selected.

    1 row selected.
    SVRMGR> select value,sid from v$sesstat where statistic#=192 group by sid,value
    2> /
    VALUE SID
    ---------- ----------
    0 1
    0 2
    0 3
    0 4
    0 5
    0 6
    0 7
    0 8
    0 9
    154 10
    17 11
    24 13
    33 15
    79 16
    9 17
    32 18
    14 19
    30 20
    8 21
    79 22
    44 24
    199 25
    4 27
    12 28
    10 29
    91 30
    8 36
    40 37
    53 38
    4 39
    172 41
    15 42
    96 44
    199 47
    61 48
    99 50
    14 51
    0 52
    7 53
    57 54
    10 55
    14 56
    34 57
    26 59
    8 62
    193 63
    66 66
    62 67
    13 68
    90 69
    18 70
    66 71
    139 72
    53 rows selected.

    ... on a machine with 2G memory a lot seems to be eaten up unnecessarily

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Unhappy


    Do your developers close their cursors in all their code??

    example:

    open C1;
    fetch c1 into v_num;
    close C1;

    (then do their manipulations)

    or do they leave C1 open as they manipulate?

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Check your session_cached_cursors and open_cursors options. These represent the number/session. so in your case the total seems to be almost there when you add up all the cursors/session. If you feel that the value is too high, check to make sure the following.

    1. reduce the # of open cursors per session
    2. session cached cursors

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    and you can see them all in v$open_cursor

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