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 following (the table creation statement follows the query):

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

-- ---------------------------------------------------
-- Statements to create and gather stats on the table.
-- ---------------------------------------------------

rem
rem     Script:         column_equality.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Apr 2013
rem 

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

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                estimate_percent => 100,
                method_opt       => 'for all columns size 1'
        );
end;
/

If you’re running 11g and can change 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.

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',
		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 often seems to 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 sampling and 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

Update (April 2019)

Since this note was written the “create patch” procedure has moved from an internal dbms package to a public dbms package. Depending on your version of Oracle you may find a back-port which does most of the job for you, or if you’re on a newer version of Oracle you may find that create_sql_patch is now a function of dbms_sqldiag. See this note for more details.