Re: Different Plans for Literal Vs Bind Variables
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx, Ian MacGregor <ian@xxxxxxxxxxxxxxxxx>
- Date: Tue, 13 Jan 2009 20:45:05 +0100
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
