Databases Reference
In-Depth Information
The following is the output when the
WORKLOAD_BIND
stored procedure is executed:
Note that elapsed time, despite flushing of the shared pool, is slightly more than five seconds.
In the previous example, the
WORKLOAD_BIND_STATIC
procedure of the package was
executed, which is functionally equivalent to the previous function's use. This example uses
static SQL instead of dynamic SQL; the results obtained by this procedure are as follows:
This execution, on the test machine used, lasts for slightly more than four seconds.
There is better performance and reduced execution time in this example, when using bind
variables to execute the query. This is due to the process involved in evaluating queries (and
DML commands) by the parser. When using bind variables, the query is being hard parsed for
the first time, and subsequent calls to the same statements—but with different parameters—will
require only a soft parse, as illustrated in the example at the end of
Chapter 1
.
Another advantage of using static SQL to query the database instead of dynamic SQL, is
minimizing parser overhead when executing the query. The results show that using bind
variables is very important when evaluating database performance. However, this kind of
behavior has to be pursued in the implementation phase of the application. In order to
improve an application, which doesn't use bind variables, every query and DML command
would have to be rewritten.
