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.
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 |
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 |
[…] Jonathan Lewis : Cursor Sharing […]
Pingback by Jonathan Lewis : Cursor Sharing | Database Scene — July 16, 2013 @ 9:17 am BST Jul 16,2013 |