• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2055
  • Last Modified:


I have heard that setting  the option 'HOLD_CURSOR=yes' in oracle precompiler
improves the performance of database access. This is due to the fact that
oracle will not re-parse the SQL statements which are already parsed.

Any idea how I can use it or where can I find more information about HOLD_CURSOR?

1 Solution
Refer to Oracle's documentation:

Pro*C/C++ Precompiler Programmer's Guide

Appendix C - Performance Tuning.

from documentation :

When HOLD_CURSOR=NO (the default), after Oracle executes the SQL statement and the cursor is closed, the precompiler marks the link between the cursor and cursor cache as reusable. The link is reused as soon as the cursor cache entry to which it points is needed for another SQL statement. This frees memory allocated to the private SQL area and releases parse locks. However, because a PREPAREd cursor must remain active, its link is maintained even when HOLD_CURSOR=NO.

When HOLD_CURSOR=YES, the link between the cursor and cursor cache is maintained after Oracle executes the SQL statement. Thus, the parsed statement and allocated memory remain available. This is useful for SQL statements that you want to keep active because it avoids unnecessary reparsing.

from me :

However this will not increase the performance as you want if you open cursors or do complicated select sentences in a loop statement. If this is your case, you should change the logic of the loop and the reading of the tables you are using.

In my experience, I have reduce the time to 70% in some procedures or PRO*C/C++ programs eliminating this "close-open" loop using alternative ways.

you can find information in
Pro*C/C++ Precompiler Programmer's Guide

and in your Pro*C program after u define ur variables and include header files...

and remember ...
When HOLD_CURSOR=YES and RELEASE_CURSOR=NO, the link is maintained; the precompiler does not reuse it. This is useful for SQL statements that are executed often because it speeds up subsequent executions. There is no need to reparse the statement or allocate memory for an Oracle private SQL area

here is a tabular description
HoldCursor    ReleaseCursor    Links
No            No               reusable
Yes           No               maintain
No            Yes              remove
Yes           Yes              remove

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now