Re: More Ammo Against Dynamic SQL?
- From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 25 Nov 2009 10:38:09 +1100
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:
