Minimizing latches using bind variables

In this recipe we will see how not using bind variables leads to latch contentions.

Getting ready

We will use the same package used in Chapter 4, where we have discussed using bind variables in our application code, to compare the execution with and without the use of bind variables.

How to do it...

The following steps will show how we can minimize latches by using bind variables:

1. Connect to the database as SYSDBA:

   CONNECT / AS SYSDBA
   

2. Query the V$SYSTEM_EVENT dynamic performance view to monitor latch-related events:

   COL EVENT FOR A37
   SELECT EVENT, TOTAL_WAITS, TIME_WAITED, AVERAGE_WAIT, TOTAL_TIMEOUTS
   FROM V$SYSTEM_EVENT WHERE EVENT LIKE 'latch:%' ORDER BY EVENT;
   

3. Connect to the SH schema and create the package CHAPTER4 containing a test workload:

   -- FROM CHAPTER 4 EXAMPLE...
   CONNECT sh@TESTDB/sh
   CREATE OR REPLACE PACKAGE sh.CHAPTER4 AS
     PROCEDURE WORKLOAD_NOBIND;
     PROCEDURE WORKLOAD_BIND;
     PROCEDURE WORKLOAD_BIND_STATIC;
     PROCEDURE TEST_INJECTION(
   ...