?
Solved

HOLD_CURSOR

Posted on 2000-04-07
3
Medium Priority
?
2,015 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup
Suggested Courses

752 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