Dion Cho – Oracle Performance Storyteller

I had Oracle Performance In Depth seminar a couple of days ago. Here is one of the interesting discussions at the seminar.

Dynamic sampling is a very powerful technology, but has following limitations.

1. It works at the single table level, not query level
2. It can’t handle any type of unknown value like bind variable or subquery.

For instance, dynamic sampling does not work with following patterns of queries.

select ... from t1 where c1 = :b1;
select ... from t1 where c1 in (select c2 from t2 where c3 = 1);
select ... from t1, t2 where t1.c1 = t2.c1 and t3 = 1;

On discussing this limitations, one of the participants gave a very smart question.

“I think that following patterns of queries are also not appropriate for dynamic sampling. What you think …?”

select ... from t1 where c1 = function1(...);
select ... from t1 where function2(c1) = ...;

Yes! Very smart guy.

See following test case.

UKJA@ukja102> create table t1(c1 int);

Table created.

Function func1 returns always “1″.

UKJA@ukja102> create or replace function func1(v1 int)
 2  return number
 3  deterministic
 4  is
 5  begin
 6    return 1;
 7  end;
 8  /

Function created.

UKJA@ukja102> 
UKJA@ukja102> 
UKJA@ukja102> insert into t1
 2  select level from dual connect by level <= 10000
 3  ;

10000 rows created.

UKJA@ukja102> 
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

PL/SQL procedure successfully completed.

Oracle can’t estimate the cardinality of following predicate without function based index.

UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
 2  from t1
 3  where func1(c1) = 1 -- or c1 = func1(1)
 4  ;

 COUNT(*) 
---------- 
 10000 

Estimates = 100, Acutal = 10000 – big discrepancy

UKJA@ukja102> select * from table
 2  (dbms_xplan.display_cursor(null,null,'allstats last'));
 
------------------------------------------------------------------------------------- 
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------- 
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.12 |      23 | 
|*  2 |   TABLE ACCESS FULL| T1   |      1 | 100 |  10000 |00:00:00.09 |      23 | 
------------------------------------------------------------------------------------- 
 

With dynamic sampling?

UKJA@ukja102> select /*+ gather_plan_statistics dynamic_sampling(t1 4) */ count(*)
 2  from t1
 3  where func1(c1) = 1
 4  ;

 COUNT(*) 
---------- 
 10000 

No good. Dynamic sampling did not work at all.

UKJA@ukja102> select * from table
 2  (dbms_xplan.display_cursor(null,null,'allstats last'));
 
------------------------------------------------------------------------------------- 
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------- 
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.12 |      23 | 
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    100 |  10000 |00:00:00.09 |      23 | 
--------------------------------------------------------------------------------

But fortunately, Oracle can sample successfully some built-in functions.

UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
 2  from t1
 3  where mod(c1,2) = 1
 4  ;

 COUNT(*) 
---------- 
 5000 
UKJA@ukja102> select * from table
 2  (dbms_xplan.display_cursor(null,null,'allstats last'));

------------------------------------------------------------------------------------- 
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------- 
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.04 |      23 | 
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    100 |   5000 |00:00:00.03 |      23 | 
------------------------------------------------------------------------------------- 

UKJA@ukja102> 
UKJA@ukja102> select /*+ gather_plan_statistics dynamic_sampling(t1 4) */ count(*)
 2  from t1
 3  where mod(c1,2) = 1
 4  ;

 COUNT(*) 
---------- 
 5000 
UKJA@ukja102> select * from table
 2  (dbms_xplan.display_cursor(null,null,'allstats last'));

------------------------------------------------------------------------------------- 
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------- 
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.03 |      23 | 
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   5000 |   5000 |00:00:00.02 |      23 | 
------------------------------------------------------------------------------------- 
 
Note 
----- 
 - dynamic sampling used for this statement

This would be classified as one of the limitations of dynamic sampling.