Solved

HOLD_CURSOR

Posted on 2000-04-07
3
1,987 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 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXECUTE IMMEDIATE 5 53
Fastest way to replace data in Oracle 5 51
join 2 views with 5 conditions 3 46
Query to identify changes between rows of two tables 8 37
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

861 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now