Monday, June 12, 2006

Static VS Dynamic SQL

Which is best static or Dynamic? Surely Static because of the reason below.

In order that a SQL satement can be executed, it has to be parsed (checked for syntactic and semantic correctness) and the execution plan has to be calculated. All this costs computational resources.To save on these costs, the Oracle instance maintains a system-wide LRU cache (aka the shared cursor cache) - exposed via v$sqlarea - of previously encountered SQL statements and appropriate derived information so that when the next SQL statement is submitted for parsing it is checked for match against the cached ones. (The definition of the criterea for a match is beyond the scope of this tutorial. Roughly speaking, the current statement must be both textually identical to its match candidate, famously to the extent of whitespace and upper/lower case identity, and the types of the bind variables must match.) When the current statement is matched, the stored derived information (parse tree, execution plan, etc) is reused and computational cost is saved.

The execution plan of the sql statements are chached only if it is static if it is dynamic it won't use the already stored execution plan, so it incurs additional cost of parsing, preparing execution plan and then running it.
But why Dynamic SQL?We use Dynamice SQL at some places we dont' know the where clause till runtime. So we go for dynamic sql. In Oracle Dynamic SQL is executed using "EXECUTE IMMEDIATE" of DBMS_SQL pacakge.

For more information on static and dynamic sql with Cursors visit the following link.


Post a Comment

<< Home