Oracle Scratchpad

July 7, 2013

Cursor Sharing

Filed under: CBO,Oracle,Statistics,trace files — Jonathan Lewis @ 5:49 pm BST Jul 7,2013

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.

3 Comments »

  1. Hi Jonathan,
    unfortunately Oracle has not implemented its announcement for 12c from MOS note #1169017.1 (“The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available)”).

    Snippet from Oracle 12c (12.1.0.1):
    =============================================================================
    PAR# PARAMETER ORD VALUE DEFAULT
    —— ————————————————– ———- —————————— ——-
    2107 cursor_sharing 2 EXACT DEFAULT
    cursor_sharing 1 FORCE
    cursor_sharing 3 SIMILAR
    =============================================================================

    SIMILAR is still a possible and valid value, but i have not crosschecked its behavior in 12c until yet (maybe the other announcement from the MOS note is hopefully true – “Note: In 11.2.0.3 , although setting the parameter to SIMILAR will still be allowed, it will effectively behave as though cursor_sharing was set to FORCE.”).

    Regards
    Stefan

    Comment by Stefan Koehler — July 8, 2013 @ 8:16 am BST Jul 8,2013 | Reply

    • Stefan,

      I’ve just done a quick check – I could set it, but it seemed to be behaving like FORCE, i.e. it did bind variable substitution so I ended up with text in v$sql looking like: select skew2 from t1 where skew = :”SYS_B_0″; but it didn’t get re-optimized when I had a histogram on column SKEW (and that is a condition that caused re-optimisation in earlier versions of Oracle for cursor_sharing = similar).

      Comment by Jonathan Lewis — July 10, 2013 @ 8:50 pm BST Jul 10,2013 | Reply

  2. […] Jonathan Lewis : Cursor Sharing […]

    Pingback by Jonathan Lewis : Cursor Sharing | Database Scene — July 16, 2013 @ 9:17 am BST Jul 16,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers