Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004

    Unanswered: Dynamic or Static SQL is better?

    Hi all,
    I knew that using Dynamic SQL is always a better solution especially using it with bind variables(Thanks Tom Kyte
    But recently I read a paper that noted many opposite and odd thins to me such as using static SQL is better in client side and also using thin driver for jdbc programs increase performance more than oci drivers!
    you could find this paper at :
    please offer your opinion about this confusing material.

  2. #2
    Join Date
    Mar 2002
    Reading, UK
    I think there is some confusion in terminology. Dynamic sql is sql that is generated at run time (and for example executed using execute immediate) and isnt anything to do with bind variables. You can use bind variables in both dynamic and static sql, though on a few occassions using bind variables may not result in as efficient an execution plan as using literals. The advantage of bind variables though is when you execute a query many times where it can cache the execution plan.

    As always its best to try it out in your particular production environment.


  3. #3
    Join Date
    Sep 2004
    London, UK
    using static SQL is better in client side
    Some would argue that you should have no SQL at all in the client application. That may or may not be practical for your application, but you could at least try to minimize it.

  4. #4
    Join Date
    Aug 2004
    Your link does not work, but I think I know what article you're speaking of. Here is Tom Kyte's proof that they were wrong because their test was flawed : "Parse CPU to Parsed Elapsed"

    In an OLTP system, you want your queries to be parsed as little as possible because you will execute many short queries (queries per second scenario), so always use bind variables on 3GL languages such as Java, C, C++ to soft parse as much as you can (also prepare once, execute many)... But when using PL/SQL, you should use static SQL when you can because PL/SQL is optimized for doing that (PL/SQL has a special cache mechanism optimized for static SQL which allow it to parse once - execute many static SQL "naturally"). In a datawarehouse however (seconds per query scenario), you should often use static SQL because you want your long queries to use the best plan at the point in time you execute it (the parse phase won't be long compared to the execute phase).

    For more info about that, you can search asktom, or buy and read Tom Kyte's "Effective Oracle By Design". GREAT book.

    Best Regards,


  5. #5
    Join Date
    Mar 2004
    Hi RBARAER and other friends,
    Thank you so much for your nice reply.Hope you best things!
    -Good Luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts