Re: Static versus dynamic SQL was Re: OO and IBM z series COBOL was Re: Discussions o

On Wed, 10 Oct 2007 21:37:18 -0300, Clark F Morris <cfmpublic@ns.sympatico.c
a> wrote:

>On Wed, 10 Oct 2007 16:35:49 -0600, "Frank Swarbrick"
><Frank.Swarbrick@efirstbank.com> wrote:
> 
>
>Back in the 1998 - 1999 timeframe I was on a Year 2000 project.  The
>client decided to move their ledger and other accounting to Oracle
>Financials on AIX boxes.  Another group was working on that and an
>outsourcing company was involved.  There were sever performance
>problems and when the consultants attached to the Year 2000 project
>investigated, they found dynamic SQL was being used.  Switching to
>static SQL apparently got rid of much of the performance problem.
>Since this is second hand from one of those involved, I am sketchy on
>the detail but I suspect it may in part have to do with bind and
>parsing overhead.  Those who are really into data base probably can
>explain the trade-offs and technical issues far better than I can.

Generally, it's true that dynamic SQL is slower than static, because it SOME
TIMES causes
the hard parse rate to skyrocket. It doesn't ALWAYS do that. If very skillfu
lly written it
can even be faster, for instance if it dynamically inserts hints based on pe
rformance
statistics.

The first thing you need is facts on the hard (most important) and soft pars
e rates. They
are in v$sysstat, and are usually viewed with a tool such as TOAD or STATSPA
CK. There is a
problem if the hard parse rate is over 150/sec or hard parses are greater th
an 10% of
total parses or parsing cpu time is greater than 10% of total cpu time. If t
here are a lot
of soft parses and execute to parse ratio is less than 70%, it means the lib
rary cache is
too small.

Dynamic SQL that uses bind variables is much better than the kind that stuff
s data values
into the statement as literals. The latter causes a hard parse on every exec
ution, while
the former often does not. If a dynamic statement is EXACTLY the same as a p
revious one,
it will be found in the library cache and execute as fast as static i.e. wit
h a soft parse
for the binds.

Some shops use libraries that insert bind variables as literals, then execut
e each
statement dynamically. I worked at one shop that had all the SQL statements 
in a table.
Every SQL required a fetch to get he statement, a home-grown parse to insert
 variables,
then a dyamic EXECUTE IMMEDIATE. This was in batch programs that processed m
illions of
rows  .. slowly.

For really fast performance, use bulk binds, which read, update or insert en
tire arrays
with a single SQL statement. They are much faster than reading with a cursor
 and inserting
single rows inside a program loop. The syntax is SELECT ... BULK COLLECT INT
O :a, :b ...
LIMIT :lim and FORALL i IN INSERT ...  That's the way to go if you enjoy VSA
M-like
processing where join logic is in the program rather than in SQL, and for br
owsing long
lists on the screen. SQL*Loader doesn't do bulk inserts. Custom written prog
rams using
them can run 3-5 times faster. When moving millions of rows, it's worth the 
trouble to
write (or generate) custom programs that move 1,000 rows per SQL. The only p
roblem is
that, when there's an error, it doesn't tell you which row(s) had errors. Yo
u have to
re-insert them individually to find out (or use the SAVE EXCEPTIONS feature)
. Also, you
can't use sequences.

Before Oracle 8,  bulk binds were available only in PL/SQL. Now they can be 
issued from
Cobol or any other language, even dynamic SQL.