Re: Different Plans for Literal Vs Bind Variables
Date: Tue, 13 Jan 2009 20:45:05 +0100
Message-ID: <4ef2fbf50901131145l52be4461lb8c5419868aa9e22_at_mail.gmail.com>
> 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-lReceived on Tue Jan 13 2009 - 13:45:05 CST