Oracle Database 11gR2 Performance Tuning Cookbook

Using bind variables

We have discussed bind variables in the A working example recipe in Chapter 1, Starting with Performance Tuning.

In this recipe, it is time to dig deeper into this topic, illustrating the benefits of using bind variables and testing the result of our efforts with simple examples. We will see examples on query statements, but the same methodologies and results apply to DML statements.

Getting ready

Follow these steps to prepare the database:

1. Create a package named Chapter4 to test various aspects related to bind variables.
2. Connect to SQL*Plus using the SH schema:

   CONNECT sh@TESTDB/sh
   

3. Create the required package:

   CREATE OR REPLACE PACKAGE sh.CHAPTER4 AS
     PROCEDURE WORKLOAD_NOBIND;
     PROCEDURE WORKLOAD_BIND;
    PROCEDURE WORKLOAD_BIND_STATIC; ...