O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required