Databases Reference
In-Depth Information
How it works...
Static SQL is a common way in which SQL code is inserted in applications. The static SQL
statements are hardcoded in application sources. Dynamic SQL, instead, is evaluated at
runtime and then executed; this is the default behavior of many APIs, such as JDBC and
ODBC. Static SQL is the natural choice for pre-compiler-based environments, such as
PRO*COBOL and PRO*C.
In PL/SQL, we have both static and dynamic SQL. We are forced to use Dynamic SQL when
we are in one of the situations listed in the previous section, because there isn't any feature
to use with static SQL to meet those requirements.
In situations other than those listed earlier, the use of dynamic SQL can lead to unnecessary
parsing and poor performance.
In this recipe, we are discussing performance-related issues
related to the use of dynamic SQL in Oracle databases. We aren't
considering security flaws, such as SQL injection and others.
There's more...
Dynamic SQL is a powerful feature of the database but it should be used carefully.
When we execute Native Dynamic SQL (NDS), we are forcing a soft parse to occur, if the
statement executed is equal to the previous statement, the parse phase is skipped. To
avoid this drawback, we can use the
DBMS_SQL
package (its use is beyond the scope of this
topic), with which we can control which cursor we want to reuse to avoid reparsing the same
statement over and over.
Even when we use dynamic SQL, we can use bind variables and we have to use them if we
want to obtain good performance. Often developers, who write dynamic SQL, append the
actual values within the query statement as literals. We have seen this in the
A working
example
recipe in
Chapter 1
, not using bind variables led to poor performance, and we will
investigate this further in
Using bind variables
in
Chapter 4
.
Obviously, there are elements that cannot bind, for example, table and column names cannot,
as actual values have to be there when the parser analyses the statement to generate the
execution plan. Besides parsing and the bind variables, there are other things to be cautious
about when using dynamic SQL in PL/SQL:
F
The code is more prone to bugs
F
The database can't check dependencies for dynamic SQL
F
Tuning a dynamic SQL procedure can be difficult
Let's explore these issues.
