Dynamic Sql for better performance?

In my application, many of our sql statements are prepared once and executed repeatedly.
This was done this way for performance reasons when the application was originally written to use an Informix database. We ported to Oracle a few years ago and have continued using dynamic sql in many places.

In the oracle documentation in the Pro C/C++ Programmers guide under 'When to use Dynamic SQL', it says to use it when there are 'unknowns', and it says nothing about performance. We have no unknowns and are purely using dynamic sql for performance reasons. Is this giving us better performance?

When does it start to make sense to use prepares and executes? When the number of columns being selected and/or updated becomes high? How high is high? 20 columns? 40 columns? 80 columns? ?? I know that "prepares" save on parsing repeatedly, but since the binding of the host variables
are done in the OPEN or EXECUTE statement, does the 'parsing once' save all that much?

Thanks for any insights.

      Lisa Received on Tue Nov 13 2001 - 15:58:46 CST