Re: More Ammo Against Dynamic SQL?

Hi Kellyn,
I had to support a ctos application once that was cross-platform (read: didn't perform anywhere) and initially written for the dreaded SQL server 7.0. The application was used to trawl a website, generate a site map and report dead links amongst other things.
The port to oracle was the worst piece of "engineering" I ever saw: for each run of the website analysis, it created lots of dynamic tables and other database objects, and all sql obviously used literals. Even though it used stored code within the database, everything was done through "execute immediate". What sense does a table name such as "xxxrun_123" make? Not much for me.
Needless to say the CPU on the box was powerless and there wasn't enough shared pool available for all the unsharable SQL. And that application wasn't even written in Java!
End of story-there was no way this could be improved and the system was moved back to SQL Server again, where this apparently wasn't an issue (besides the fact that the box was 4 times as powerful and had more memory than the Oracle box.
As you can imagine I don't like excessive usage of dynamic SQL, neither within the database nor through other layers. 

Cheers,

Martin
--
Martin Bach
OCM 10g
http://martincarstenbach.wordpress.com

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..." 




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

Other related posts: