Cursor Sharing

Here’s a couple of extracts from a trace file after I’ve set optimizer_dynamic_sampling to level 3. I’ve run two, very similar, SQL statements that both require dynamic sampling according to the rules for the parameter – but take a look at the different ways that sampling has happened, and ask yourself what’s going on:

Statement 1 produced this sampling code:

SELECT  /* OPT_DYN_SAMP */
        /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
            NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE
        */
        NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM    (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */
                1 AS C1,
                CASE WHEN CASE "T1"."ID" WHEN 1 THEN 100 ELSE 1 END =0 AND "T1"."SKEW">=:B1 AND "T1"."SKEW"<=:B2 THEN 1 ELSE 0 END AS C2
           FROM   "TEST_USER"."T1" SAMPLE BLOCK (0.740741 , 1) SEED (1) "T1"
         ) SAMPLESUB

Statement 2 produced this sampling code:

SELECT  /* OPT_DYN_SAMP */
          /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
              NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE
         */
         NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01")
FROM   (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */
                 :"SYS_B_02" AS C1,
                 CASE WHEN
                         CASE "T1"."ID" WHEN :"SYS_B_03" THEN :"SYS_B_04" ELSE :"SYS_B_05" END =:"SYS_B_06"
                         AND "T1"."SKEW">=:"SYS_B_07" AND "T1"."SKEW"<=:"SYS_B_08"
                    THEN :"SYS_B_09" ELSE :"SYS_B_10"
               END AS C2
        FROM "TEST_USER"."T1" SAMPLE BLOCK (:"SYS_B_11" , :"SYS_B_12") SEED (:"SYS_B_13") "T1"
       ) SAMPLESUB

You’ll notice that the second sampling statement has a load of bind variables of the form :”SYS_B_nn” – which tells us that cursor_sharing has been enabled by the session (not, I hasten to add, by me – it’s Oracle doing something in the environment it set up to run the recursive SQL) – and by checking v$sql_optimizer_env we can see the cursor_sharing has been set to “similar” for BOTH statements.

SO why has bind variable substitution taken place for one statement and not the other ? The answer is: that’s just the way it happens. If you take a look at the original SQL you may guess what causes the difference to appear.

declare

	m_n number;
	m_n1	number := 50;
	m_n2	number := 52;

begin

	execute immediate 'alter session set tracefile_identifier = ''vars''';
	select	max(id)
	into	m_n
	from	t1
	where	skew between m_n2 and m_n2
	and	case id when 1 then 100 else 1 end = 0
	;

	execute immediate 'alter session set tracefile_identifier = ''cons''';
	select	max(id)
	into	m_n
	from	t1
	where	skew between 50 and 52
	and	case id when 1 then 100 else 1 end = 0
	;

end;
/

The first SQL statement arrives at the database with a mixture literals and bind variables (as the pl/sql variables are replaced with :b1 and :b2); the second SQL statement arrives with nothing but literals, and that’s the one where substitution takes place.

Perhaps the people who designed the feature decided that if the code saw some SQL with a mix of binds and literals the person who had written the SQL knew what they were doing and therefore shouldn’t be second-guessed. Unfortunately that’s not the whole story – because the same rule doesn’t apply to the underlying pair of SQL statements (which, of course, are different because they have WHERE clauses) when I run them outside the pl/sql block. So there’s still more work to do before I know exactly when the optimizer will, or will not, use bind variable substitution – unless someone else has already worked it out and feels like sharing.

Footnote: For this test I had set optimizer_dynamic_sampling to level 3, which means it should happen when the optimizer is guessing; and the presence of the case operator triggered the sample.