Re: More Ammo Against Dynamic SQL?
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, stbaldwin@xxxxxxxxxxxxxxxx
- Date: Sun, 29 Nov 2009 19:36:36 -0800 (PST)
I'm in complete agreement with you gentlemen and thank you, great advice.
I believe I am going the exact route Steve has listed out here, but as some
have used dynamic SQL due to partitions, etc, others have abused it and created
SQL that they can't even recognize it when it is pinpointed as an issue.
I truly feel I work in an environment of "too much of a good thing is not a
good thing..." I know it's difficult for folks to try new ways to do things
when the old way has worked with them, but I need to present folks with some
reasons why to not ALWAYS use one way over trying a new way. They are getting
better with some, but there are many times where dynamic SQL is not a
requirement or even worse and this has happened- only compiles in one GUI
tool, (so try to compile the code even with SQL Plus and it fails!)
Even though folks say they don't want to try a new way because this is the way
it's always worked for them, they often do things twice, three times, instead
of just doing it right.... :)
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..."
--- On Tue, 11/24/09, Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx> wrote:
From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
Subject: Re: More Ammo Against Dynamic SQL?
To: oracle-l@xxxxxxxxxxxxx
Date: Tuesday, November 24, 2009, 4:38 PM
This is very sound advice. Saying something like "dynamic SQL is always bad"
is ridiculous. It's not the tool you use for building an application that
matters so much as how you use it. It is just as easy to bring a DB server to
its knees by using bad static SQL with bind variables as it is with dynamic SQL.
The things I've found to be very important :
Peer review all code before deploying to production. Making people accountable
for the code they write can work wonders for the care they take.
Ensure you have a good DB design to begin with. Sometimes developers are
hamstrung because of conflicts between user requirements and data structures
they are constrained with.
Ensure the developers have a volume testing environment and there are
procedures in place to ensure their use. Something that performs well in an
environment with x rows may not necessarily perform well in an environment with
1000x rows.
Ensure that developers have access to runtime statistics of the applications
they are responsible for so when a poorly performing app does slip through, or
a change in object statistics causes the optimizer to make a dumb decision,
they can be quickly identified.
Work with developers to help them understand how to write better code rather
than beating them up all the time for writing crappy code. Share your
knowledge and be part of the solution.
Steve
On Wed, Nov 25, 2009 at 10:11 AM, Tim Gorman <tim@xxxxxxxxx> wrote:
Kellyn,
There are several situations, usually involving data warehouse types of
workload, where it is useful to deliberately generate "un-shareable SQL" using
dynamic SQL, primarily to take full advantage of partition pruning,
sub-partition pruning, and column-level statistics. If the application is
submitting a small number of very long-running SQL statements, involving the
chance to either "prune" to specific partitions or sub-partitions, or involving
skewed data supported by indexes, or both, then it often makes good sense to
provide the optimizer with all of the advantages possible to ensure that the
long-running operation is as efficient as possible, and embedding literal data
values in the text of the SQL statement using dynamic SQL can enable those bits
of functionality.
As with most things in Oracle, there is no one single answer. There are good
guidelines, and conscientious people want to know how to decide which mechanism
to use. Every time we try to act like a hammer and treat everything like a
nail, we're sure to run across something requiring a screwdriver, and the
result is a bashed-in screw which just a mess. So, rather than attempting to
issue a fatwa on dynamic SQL, it might make better sense to find an example of
abuse by dynamic SQL and demonstrate the benefits of static SQL and
bind-variables via test cases, but also demonstrate a counter example where
dynamic SQL and embedded literal data values save the day by enabling the use
of column-level statistics to choose or reject an index on badly-skewed data,
perhaps?
Hope this helps...
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO 80163-0791
website = http://www.EvDBT.com/
email = Tim@xxxxxxxxx
mobile = +1-303-885-4526
fax = +1-303-484-3608
Lost Data? http://www.ora600.be/
Other related posts:
