Re: More Ammo Against Dynamic SQL?
- From: Martin Bach <development@xxxxxxxxxxxxxxxxx>
- To: kjped1313@xxxxxxxxx
- Date: Tue, 24 Nov 2009 13:48:16 +0000
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..."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
