In the apps I'm faced with, dynamic sql means
'always parse' (whether soft or hard, doesn't matter: always parse)
Reopening cursors? Never heard of, we use ODBC (and running ODBC in
that front end pretty means setting up a cursor for every statement,
closing it after it has run, and *NEVER* reopening it)
Using bind variables? Didn't hear of bind variables. Explained them to
use bind variables, nothing happened, it seems to be impossible for the
front-end to user bind variables.
Using stored procedures in ODBC, returning a REF cursor? Showed them
how to do that. Never saw any working implementation. I can get it
working, no problem, but the developers don't seem to understand me, or
are unwilling to implement it, because it's too much work.
And because every statement is being parsed all the time (which can be
easily demonstrated from statspack), we know have a server which
heavily suffers from library latch contention, and is usually pegged at
100 percent for most of the day.
Mind you, this is an application where someone had the very smart idea
to implement all number(10) surrogate keys as varchar2(10) left padded
with '0'.
And no, this cannot be changed, as it would cost too much.
Regards,
--
Sybrand Bakker
Senior Oracle DBA
Received on Mon Apr 24 2006 - 05:31:00 CDT
