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 : http://www.onjava.com/pub/a/onjava/2...L-4GV796923290
please offer your opinion about this confusing material.
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.
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.