RE: More Ammo Against Dynamic SQL?

From: Christopher Boyle <>
Date: Tue, 24 Nov 2009 11:38:10 -0500
Message-ID: <015501ca6d24$8309d3b0$891d7b10$_at_com>

Might I also add that it seems to play havoc with trying to tune using DBMS_PROFILER? I have a

EXECUTE IMMEDIATE 'CREATE INDEX IDX_B ON Big_Honking_Table_90_Million_Rows (col1,col2)';

Showing up as only taking 2 milliseconds. Much as I'd like to believe that, it's not likely. Unless I'm doing something wrong with the profiler.

-----Original Message-----
From: [] On Behalf Of Martin Bach
Sent: Tuesday, November 24, 2009 8:48 AM To:
Cc: oracle-l
Subject: 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.



Martin Bach
OCM 10g

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.
> "Go away before I replace you with a very small and efficient shell
> script..."
-- --
Received on Tue Nov 24 2009 - 10:38:10 CST

Original text of this message