| Robert 2007-10-11, 3:55 am |
| On Wed, 10 Oct 2007 21:37:18 -0300, Clark F Morris <cfmpublic@ns.sympatico.ca> 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 SOMETIMES causes
the hard parse rate to skyrocket. It doesn't ALWAYS do that. If very skillfully written it
can even be faster, for instance if it dynamically inserts hints based on performance
statistics.
The first thing you need is facts on the hard (most important) and soft parse rates. They
are in v$sysstat, and are usually viewed with a tool such as TOAD or STATSPACK. There is a
problem if the hard parse rate is over 150/sec or hard parses are greater than 10% of
total parses or parsing cpu time is greater than 10% of total cpu time. If there are a lot
of soft parses and execute to parse ratio is less than 70%, it means the library cache is
too small.
Dynamic SQL that uses bind variables is much better than the kind that stuffs data values
into the statement as literals. The latter causes a hard parse on every execution, while
the former often does not. If a dynamic statement is EXACTLY the same as a previous one,
it will be found in the library cache and execute as fast as static i.e. with a soft parse
for the binds.
Some shops use libraries that insert bind variables as literals, then execute 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 millions of
rows .. slowly.
For really fast performance, use bulk binds, which read, update or insert entire 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 INTO :a, :b ...
LIMIT :lim and FORALL i IN INSERT ... That's the way to go if you enjoy VSAM-like
processing where join logic is in the program rather than in SQL, and for browsing long
lists on the screen. SQL*Loader doesn't do bulk inserts. Custom written programs 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 problem is
that, when there's an error, it doesn't tell you which row(s) had errors. You 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.
|