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:
