Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2005

    Unanswered: What resources freed in Session Cache using CLOSE vs FREE commands for Pro*C cursors

    I have several Pro*C programs that ALLOCATE, OPEN, FETCH and CLOSE a cursor. Because of the number of users accessing the service, we are getting an ORA-1000 (max open cursors) error.
    The DBA's have traced for the session id and its two threads for the service in question. The current open cursors count keeps going up even though the cursor is closed (EXEC SQL CLOSE). For testing purposes, I added EXEC SQL FREE <cursorname>; immediately after every EXEC SQL CLOSE <cursorname>; The current open cursor count did not go up and stayed at zero. The Pro*C precomiler settings are RELEASE_CURSOR=YES, HOLD_CURSOR=NO, MAXOPENCURSORS=10. On the database, OPEN_CURSORS = 5000. All users connect to ORACLE using one login/session.

    1. What resources and where in memory are released when issuing an EXEC SQL CLOSE?
    2. What resources and where in memory are released when issuing an EXEC SQL FREE?
    3. What resources in the Session Cache are released in EXEC SQL CLOSE?
    4. What resources in the Session Cache are released in EXEC SQL FREE?
    5. Must ALLOCATE always be paired with a FREE or is CLOSE good enough?

    Any help is greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Guwahati, India

    Max_Open_Cursor is too Hign


    I am surprised looking at the value of Max_Open_cursor = 5000 . I generally use a value 500-1000 . I am running a production server with 120 users. Lot of cursors opened by the Programs . But I have not get this problem in last three years.

    Going by the logic, there must be some problem in your programs . I Generally use OPEN cursor_name, FETCH cursor_name, CLOSE cursor_name in Pro*C.
    Check the loops in your programs .
    G J Shankar Nath
    IT Consultant, Vedswasti Services Pvt. Ltd

  3. #3
    Join Date
    Apr 2005

    More information on FREE vs CLOSE for ALLOCATEd cursors.

    After more analysis of the Pro*C services, we found that the services that only use EXEC SQL OPEN, FETCH and CLOSE do not accumulate current open_cursors (open cursors is reset to zero). There are a total of 5 out of 100 services that use the EXEC SQL ALLOCATE command and it is only these services that have a growing current open_cursors count.

    It looks to me like the EXEC SQL ALLOCATE must also have an accompanying EXEC SQL FREE to release the cursor from the session cache. The close is not enought. To help prove this, I took one of the services with the ALLOCATE code and put EXEC SQL FREE after every EXEC SQL CLOSE statement currently in the code. After doing this the current cursor count went back to zero.

    I am trying to find out the details of what resources are released using CLOSE vs FREE when a cursor has ben ALLOCATEd. Any feedback is greatly appreciated. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts