Dynamic Sampling – 2

Dynamic Sampling – 2:
I’ve written about dynamic sampling in the past, but here’s a little wrinkle that’s easy to miss. How do you get the optimizer to work out the correct cardinality for a query like (the table creation statement follows the query):

select count(*)
from t1
where n1 = n2
;

create table t1
as
with generator as (
 select --+ materialize
  rownum id
 from dual
 connect by
  level <= 1e4
)
select
 mod(rownum, 1000) n1,
 mod(rownum, 1000) n2
from
 generator v1,
 generator v2
where
 rownum <= 1e6 ; 

If you’re running 11g and can changed the code there are a couple of easy options – adding a virtual column, or applying extended stats and then modifying the SQL accordingly would be appropriate.

-- Virtual Column alter table t1 add (  n3 generated always as ( case n1 when n2 then 1 end) virtual ) ; execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for columns n3 size 1')

-- Extended Stats

begin
 dbms_output.put_line(
  dbms_stats.create_extended_stats(
   ownname  => user,
   tabname  => 'T1',
   extension => '(case n1  when n2 then 1 else null end)'
  )
 );

 dbms_stats.gather_table_stats(
  ownname   => user,
  tabname   =>'T1',
  block_sample   => true,
  method_opt   => 'for columns (case n1  when n2 then 1 else null end) size 1'
 );
end;
/

select count(*)
from t1
where (case n1 when n2 then 1 else null end)= 1
;

If you can’t change the SQL statement, there’s always the option for bypassing the problem by fixing a suitable execution plan with an SQL Baseline, of course. Alternatively, if you can think of the right hint you could create an “SQL Patch” for the statement – but what hint might be appropriate ? I’ll answer that question in a minute.
Here’s another option, though: get Oracle to use dynamic sampling. (You probably guessed that from the title of the post.) So which level would you use to make this work ? Left to its own devices, Oracle would calculate the selectivity of the predicate n1 = n2 as the smaller of the two separate predicates “n1 = unknown” and “n2 = unknown”. So you might hope that level 3 (Oracle is “guessing”) or level 4 (more than one predicate on a single table) might be appropriate. It’s the latter that works. If you execute “alter session set optimizer_dynamic_sampling=4;” before executing this query, Oracle will sample the table before optimising.
The method works, but can you apply it ? Possibly not, if you’re not allowed to inject any extra SQL anywhere – after all, you probably don’t want to set the parameter at the system level (spfile or init.ora) because it may affect lots of other queries – introducing more work because of the sample, and then risking unexpected changes in execution plans. Setting the parameter for a session is often no better. And this brings me back to the SQL Patch approach – if you don’t want to create a baseline for the query then perhaps a patch with the hint /*+ opt_param(‘optimizer_dynamic_sampling’ 4) */ will do the trick. Don’t forget all the doubling of single quotes that you’ll need, though (this is the code fragment I used):

begin
 sys.dbms_sqldiag_internal.i_create_patch(
  sql_text =>
'
select
 count(*)
from t1
where n1 = n2
',
  hint_text => 'opt_param(''optimizer_dynamic_sampling'' 4)'

 );
end;
/

For more analysis and commentary on the SQL Patch mechanism, you might like to read Dominic Brooks’ mini-series:

•  part 1
• part 2
• part 3
• part 4

DIGITAL JUICE