Re: More Ammo Against Dynamic SQL?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: kjped1313@xxxxxxxxx
- Date: Tue, 24 Nov 2009 10:00:52 +0100
Something I haven't seen in this thread (but I may have missed it) is
that if you find a killer query in your SGA, it wil be very difficult to
grep the code (or search DBA_SOURCE) to find hence it comes. The problem
is that people who monitor and troubleshoot databases usually don't know
the code, and can waste a lot of time because of dynamic SQL in
fire-fighting mode.
That said, to play the devil's advocate, although the issues you are
mentioning are always associated with dynamic SQL, you can also write
dynamic SQL that uses bind variables, etc. I don't consider dynamic SQL
to be absolutely evil; it sometimes happens to be the most reasonable
way to work with a disastrous database design (this is another debate).
It's just that people don't know how to use it properly, and where
appropriate. Very often, people go for dynamic SQL because it's for
them 'the easiest way'. Coding dynamic SQL well requires putting a lot
of intelligence in SQL construction, and in fact it's hard. As a rule of
thumb, the easiest way is the worst one. Ad augusta per angusta.
I am also moderately convinced by the argument about 'everything should
be in packages and procedures'. Well, I am one who has found, in code
currently in production, little gems on this pattern:
function count_something(p_category in varchar2)
return number
is
n number := 0;
begin
for rec in (select id from table where category = p_category)
loop
n := n + 1;
end loop;
return n;
exception
when others return 0;
end;
This wonderful piece of code had of course been duplicated umpteen times
in a package, with slight variations such as additional conditions in
the where clause. "Package good, SQL in code bad" looks to me like an
over-simplification. The only positive thing is that it's usually easier
for me to check the code in DBA_SOURCE than elsewhere. But whenever I do
it, I end up depressed for the day. I have written a C program to search
packages for poor programming practices, statements in cursor loops,
'when others', trying to measure cyclomatic complexity and the like ...
I was generating so many alerts I have stopped using it (and I'm not
talking dynamic SQL here).
If the packages were coded by some of the people on this list, I'd have
no qualm. When they are coded by the youngest and cheapest coders
available on the market who have an even weaker grasp of SQL than of
your favorite procedural or OO language, you can fear the worst and
usually the worst happens. Since the criterion to accept or reject code
is usually 'does it give the good result' ...
I'm fully on the same wave-length as Dan Fink on this; the real question
is about coding well, and not whether it's done in SQL, VB, java, PHP or
python. And on the particular question of dynamic SQL, the argument you
are most likely to meet is 'yes, but we have a list of identifiers and
we must build dynamically an IN list ...'. I've been up against this
for years. The first question to ask is where those identifiers are
coming from. If they are returned by another query, you should whisper a
four-letter word - join. If not, before jumping for the temporary table
(may be justified for very big volumes, though), you should mention
slicing on the fly a list passed as a single variable, or having
fix-size in-lists to which the same number of arguments is always
passed, some arguments being repeated if need be to get the proper
count. And so on. But very often, the question is not of tuning SQL
statements but thinking anew database accesses.
Thanks for having read my rant so far ;-)
Stéphane Faroult
Kellyn Pedersen wrote:
> I am working on a presentation to convince my company against some of
> the dastardly dynamic SQL that we have in our code. We perform
> everything from inserts, updates, deletes, selects and CTAS' all with
> dynamic SQL and it's killing me!
> I would love any new reasons NOT to use it, as I have all the standard
> reasons like, inability to reuse sql in the buffer, parsing issues,
> bind peeking issues, execution plan instability, etc..
> Thanks for the assist! :)
>
> Kellyn Pedersen
> Multi-Platform DBA
> I-Behavior Inc.
> http://www.linkedin.com/in/kellynpedersen
>
> "Go away before I replace you with a very small and efficient shell
> script..."
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
