Re: Different Plans for Literal Vs Bind Variables

> I think the million dollar question here is - why is the CBO using the
> default 5% selectivity?  Why isn't it calculating the selectivity based on
> the peeked bind value like it's supposed to?

I think Ian might want to try 10.2.0.4; the following test case shows that
the CBO, in that version, does seem to peek for the binds "through" a
date function
(the default selectivity of 5% would 500 below) for similar test case:

SQL> select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
...

SQL> create table t (x date);

SQL> insert into t select to_date ('01012000','ddmmyyyy') from dual
connect by level <= 100;

100 rows created.

SQL> insert into t select to_date ('02012000','ddmmyyyy') from dual
connect by level <= 9900;

9900 rows created.

SQL> exec dbms_stats.gather_table_stats (user, 't', method_opt=>'for
all columns size 254', estimate_percent=>null);

SQL> select count(*) from t where x <= to_date ('01012000','ddmmyyyy');

  COUNT(*)
----------
       100

SQL> select * from table (dbms_xplan.display_cursor());

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100 |   800 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

SQL> select count(*) from t where x <= to_date ('02012000','ddmmyyyy');

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

SQL> select * from table (dbms_xplan.display_cursor());

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 10000 | 80000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<=TO_DATE(' 2000-01-02 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))


SQL> variable v varchar2(100)
SQL> exec :v := '01012000';

SQL> select count(*) from t t2 where x <= to_date (:v,'ddmmyyyy');

  COUNT(*)
----------
       100

SQL> select * from table (dbms_xplan.display_cursor());

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100 |   800 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<=TO_DATE(:V,'ddmmyyyy'))


SQL> exec :v := '02012000';
SQL> select count(*) from t t1 where x <= to_date (:v,'ddmmyyyy');

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

SQL> select * from table (dbms_xplan.display_cursor());

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 10000 | 80000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<=TO_DATE(:V,'ddmmyyyy'))


SQL> -- for comparison only: explain plan does not peek bind values,
uses default 5% selectivity
SQL> explain plan for
  2  select count(*) from t t3 where x <= to_date (:v,'ddmmyyyy');

SQL> select * from table (dbms_xplan.display());
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     8 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   500 |  4000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<=TO_DATE(:V,'ddmmyyyy'))


As for the "why" in Ian's case the CBO doesn't peek - it might be simply
because in his version/port the bind-peeking code was not smart enough
to propagate the peeked bind value through the function, hence used the default.

In the literal case, I would guess that a "SQL preprocessor"
pre-calculates, at parse
time, the date executing the function - not unlikely "where x = 2 * 2" is seen
in the library cache (predicate information) as "where x = 4".

BTW - I vaguely remember that this issue has already been discussed somewhere,
but I don't recall where of by whom. Someone remembers anything ?

HTH !
Alberto

-- 
Alberto Dell'Era
--
http://www.freelists.org/webpage/oracle-l

Other related posts: