Re: Dynamic SQL

"Sergey V. Udaltsov" <sergey.oudaltsov_at_clients.ie> wrote in message news:20010330.161011.653468858.4765_at_clients.ie...
> Hi all
>
> Does anybody have any estimation about performance impact of Dynamic SQL
> in stored PL/SQL procedures (using either EXECUTE or dbml_sql methods)?
>
> Any references, numbers or something else.
>
> We are now trying to choose between flexbility and speed - so this
> question is really critical for us.
>
> Thanks for any info
>
> --
> Dr. Sergey V. Udaltsov
> Brainbench MVP for Java 1
> http://www.brainbench.com

Since Oracle 8i dbms_sql is obsolete and you should use native dynamic sql (open <cursor var> for :sqlstatement)
as that according to Oracle also has better performance compared to dbms_sql.
Generally speaking *full* dynamic sql (so with hardcoded literals instead of bind variables) should be avoided at all cost. Bind variables only require a few more keystrokes, so you don't loose flexibility. You should use bind variables in dynamic sql, because any dynamic sql statement *always* incur a parse, except when yo do use bind variables it is a soft parse, and without bind variables it is a hard parse. This latter operation will hog the CPU.
You could easily compare timing for soft parse and hard parse by probing into v$sesstat and v$sysstat

Hth,

Sybrand Bakker, Oracle DBA Received on Fri Mar 30 2001 - 09:40:57 CST