Oracle Database 11gR2 Performance Tuning Cookbook

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 ...