Re: Pro*C performance tips?

From: Steven Lebrun <lebrun_at_ll.mit.edu>
Date: Tue, 28 Jan 92 12:21:22 GMT
Message-ID: <1992Jan28.122122.10086_at_ll.mit.edu>

> The areas I have found useful so far are:
>
> * select from table A into a 100 element array
> * put a noncompressed index on table A but not on B or C
> * increased MAXOPENCURSORS from 10 to 20
> * set HOLD_CURSOR=YES at the PCC level
> * set REBIND=NO at the PCC level

I would like to add a word of caution about setting REBIND=NO in C programs. Setting REBIND=NO can cause a C program to fail depending on the storage class of the host variables. If the address of the host variables do not change between one execution of a SQL statement and the next execution of the SQL statement then the REBIND=NO option will improve performance. If the host variables are local to the function where the SQL statement resides, then the host variable addresses will change every time the function is called. In this case, the REBIND=NO option will cause the SQL statement to fail due to the fact that the host variables are not at the same addresses.

Cases when the REBIND=NO option will work:

1. When the host variables are either global or static and therefore have the same address throughout the life of the program.
2. When the SQL statement is reparsed everytime the addresses of the host variables change. For example, if your SQL Statements use a CURSOR that is opened within the same function everytime the function is called will cause the program to rebind the host variables.

Reference: ORACLE Manual: Programmer's Guide to the Precompilers,

                          Version 1.3,       pages 10-15 and E-8



+---------------------------------------------------------------------------+
| Steven F. LeBrun              |      MIT Lincoln Laboratory, Group 91     |
|                               |        244 Wood Street, Room LI-136       |
| lebrun_at_ll.mit.edu (Internet)  |          Lexington, MA 02173-9108         |
| lebruns_at_rigel     (internal)  |  Phone:(617)-981-5742, FAX:(617)-981-0991 |
 +---------------------------------------------------------------------------+

| "All that is gold does not glitter, Not all those who wonder are lost;" |

+---------------------------------------------------------------------------+

Received on Tue Jan 28 1992 - 13:21:22 CET