Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Function predicate – the limitation of dynamic sampling

leave a comment »

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.

About these ads

Written by Dion Cho

February 19, 2009 at 11:30 am

Posted in Optimizer

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: