Introducing Adaptive Cursor Sharing for bind variable peeking

In the previous chapter, we have explored the (recommended) use of bind variables.

In this recipe, we will see how using bind variables can be disadvantageous in certain situations and learn about a feature of Oracle Database 11g that helps us with this.

How to do it...

The following steps will demonstrate Adaptive Cursor Sharing:

1. Connect to SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create a table for testing with a field ID that equals 1:

   CREATE TABLE sh.MY_TEST AS SELECT
     OBJECT_NAME AS NAME, 1 AS ID
   FROM ALL_OBJECTS NOLOGGING;
   

3. Insert eight records with different values for the ID field:

   INSERT INTO sh.MY_TEST (ID, NAME)
     VALUES (2, 'ONLY THIS RECORD HAS ID=2');
   INSERT INTO sh.MY_TEST (ID, NAME)
     VALUES (3, 'ONLY THIS RECORD HAS ID=3');
   INSERT INTO sh.MY_TEST (ID, NAME)
     VALUES (4, 'ONLY THIS RECORD HAS ID=4');
   INSERT INTO sh.MY_TEST (ID, NAME)
     VALUES (5, 'ONLY THIS RECORD HAS ID=5');
   INSERT INTO sh.MY_TEST (ID, NAME)
     VALUES (6, ...