Solved

HOLD_CURSOR

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Required to Import Oracle Database Table 3 45
Pl/SQL Query 31 62
oracle rollup query 3 38
Oracle 12c patching 1 38
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

744 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

14 Experts available now in Live!

Get 1:1 Help Now