Re: Dynamic sql

fitzjarrell_at_cox.net schrieb:

> dev..._at_gmail.com wrote:
> > Why in sql server environment I ever heard BAD of dynamic sql and in
> > Oracle I'm reading instead that it is cool?
> >
> > Only because of the bind variables way to secure from sql injection?
>
> I wonder where you read dynamic sql generation is 'cool' in Oracle; it
> isn't any better for performance in Oracle than it was for SQL Server.

In some (rare) cases it is. Consider bind variable peeking with skewed data.

> Dynamic SQL is still hard parsed every time it is executed, even if the
> same statement is executed multiple times,

Dynamic SQL is only hard parsed once if the same statement is executed multiple times.

SQL> DECLARE

  2     SqlStatement  VARCHAR2(500);
  3     Dummy         NUMBER;
  4  BEGIN
  5     FOR csRec IN (SELECT name, value
  6                     FROM v$sysstat
  7                    WHERE NAME IN('parse count (total)', 'parse
count (hard)')) LOOP
  8        DBMS_OUTPUT.put_line(csRec.name || ': ' || csRec.value);
  9     END LOOP;
 10     SqlStatement := 'select count(*) from user_objects where
object_id = :oid ';
 11     FOR i IN 1 .. 100 LOOP
 12        EXECUTE IMMEDIATE SqlStatement
 13                     INTO Dummy
 14                    USING i;
 15     END LOOP;
 16     FOR csRec IN (SELECT name, value
 17                     FROM v$sysstat
 18                    WHERE NAME IN('parse count (total)', 'parse
count (hard)')) LOOP
 19        DBMS_OUTPUT.put_line(csRec.name || ': ' || csRec.value);
 20     END LOOP;

parse count (total): 136265
parse count (hard): 10242
parse count (total): 136367
parse count (hard): 10243

With dbms_sql (i.e. non-native dynamiv SQL) you can even avoid soft parsing mutliple times.

> and even if it uses some
> form of bind variable.

What is that: 'some form of bind variable' ? Is it just a 'bind variable' ?

...
> Post this link where dynamic sql is considered 'cool' in Oracle. I'd
> like to see who stated such a fallacy.
So would I.

Urs Metzger Received on Tue Aug 29 2006 - 10:31:04 CDT