Why so much of cursor remain INACTIVE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Why so much of cursor remain INACTIVE

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    Hi All

    I have issued the following query

    "select user_name , status, osuser, machine, a.sql_text
    from v$session b, v$open_cursor a
    where a.sid = b.sid"

    I am getting same SQL in the SQL_TEXT in my system.
    I have only 4 jdbc clients accessing the database but a same query is passed by them (They are accessing data through stored procedures so there is no question of similar but different query)

    One of my function contains a simple query 'SELECT * FROM ACTIVEUSERS' which occurs in the result od above query in SQL_TEXT' at least 30 times. I am closing all my cursors properly and even Java programs are also well drafted everyehere using stmt.close(). Then Why so much of cursors are created and also what the status is 'INACTIVE' ?

    Can oracle cache that query and use it for different clients.

    Should I set the parameter CURSOR_SHARING=Y. Is there any other way where I can close/reuse the INACTIVE cursors?

    Amol

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Try

    Code:
      ALTER SYSTEM SET CURSOR_SHARING=FORCE;

    Note: Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications and if your applications use stored outlines


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if you want to know exact cursors open you have to check v$sysstat, statistic#3
    v$open_cursor shows cached cursors

  4. #4
    Join Date
    Dec 2000
    Posts
    255
    The count on V$SYSSTAT gives me even more number i.e. 203 and where STATISTIC#=3 gives me just 1 record.
    The count of V$OPEN_CURSOR os still less i.e. 130 so what can be real numeber about my system.

    I have also used ALTER SYSTEM SET CURSOR_SHARING=FORCE;

    However there is still no difference. (By the how to undo forced cursor sharing)

    Amol

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you real number of open cursor is what v$sysstat shows


    ALTER SYSTEM SET CURSOR_SHARING=NONE

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