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

Get Oracle Database 11gR2 Performance Tuning Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.