?
Solved

HOLD_CURSOR

Posted on 2000-04-07
3
Medium Priority
?
2,035 Views
Last Modified: 2010-05-18
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?

Thanks.
0
Comment
Question by:ymlew
3 Comments
 
LVL 5

Expert Comment

by:sbenyo
ID: 2692800
Refer to Oracle's documentation:

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

Appendix C - Performance Tuning.


0
 
LVL 2

Expert Comment

by:NetoMan
ID: 2694022
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.

0
 
LVL 1

Accepted Solution

by:
sohill earned 300 total points
ID: 2699165
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...
EXEC ORACLE OPTION (HOLD_CURSOR=YES);

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

   
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question