Re: More Ammo Against Dynamic SQL?

From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Sun, 29 Nov 2009 19:36:36 -0800 (PST)
Message-ID: <240446.48297.qm_at_web32007.mail.mud.yahoo.com>

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_at_multiservice.com> wrote:

From: Steve Baldwin <stbaldwin_at_multiservice.com> Subject: Re: More Ammo Against Dynamic SQL? To: oracle-l_at_freelists.org
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_at_evdbt.com> 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_at_EvDBT.com
mobile    = +1-303-885-4526
fax       = +1-303-484-3608

Lost Data? http://www.ora600.be/

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Nov 29 2009 - 21:36:36 CST