No bind peeking - why?

Hi list,

I risk to ask a question with an obvious answer, but I can't find it by
myself. I've got a statement that simply DOES NOT bind-peek as far as I can
see.

Here my example, it's an example schema and a simplified query
- ALTER SYSTEM FLUSH SHARED_POOL; has done immediately before executing
- no other sessions on this DB are active/possible

================== SNIPSNAP ===============
SQL>
--------------------------------------------------------------------------------------
SQL> set lines 1000
SQL> set pages 100
SQL>
--------------------------------------------------------------------------------------
SQL> desc willi;
         Name    Null?           Typ
------------- -------- -------------
ID            NOT NULL NUMBER
DESCRIPTION            VARCHAR2(100)
STATUS                 NUMBER

SQL> -- ID is a ascending primary key, starting with 1, ending with 8
million
SQL> -- DESCRIPTION is a random 3-byte string
SQL> -- STATUS is "1" for IDs <= 1000, above it's STATUS=99
SQL>
--------------------------------------------------------------------------------------
SQL> select status, count(*) from willi group by status;

    STATUS   COUNT(*)
---------- ----------
         1       1000
        99    7999000

SQL>
--------------------------------------------------------------------------------------
SQL> VAR a number
SQL> EXECUTE :a := 1

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL>
--------------------------------------------------------------------------------------
SQL> explain plan for select * from willi where status=:a;

EXPLAIN PLAN ausgefuhrt.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1772530392

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  4000K|    49M|  5435   (2)| 00:01:06 |
|*  1 |  TABLE ACCESS FULL| WILLI |  4000K|    49M|  5435   (2)| 00:01:06 |
---------------------------------------------------------------------------

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

   1 - filter("STATUS"=TO_NUMBER(:A))

13 Zeilen ausgewahlt.

SQL>
--------------------------------------------------------------------------------------
SQL> -- just to make sure that the bind variable has had the right value
SQL> select count(*) from willi where status=:a;

  COUNT(*)
----------
      1000

SQL>
--------------------------------------------------------------------------------------
SQL> explain plan for select * from willi where status=1;

EXPLAIN PLAN ausgefuhrt.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1854384652

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost
(%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1000 | 13000 |     7
(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WILLI        |  1000 | 13000 |     7
(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | WILLI_STATUS |  1000 |       |     4
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("STATUS"=1)

14 Zeilen ausgewahlt.

SQL>
================== SNIPSNAP ===============

Why are the plans different if oracle peeks into that bind variable at
parse time? If it was a range predicate, I would understand. But this is
EQUAL....

Any ideas are greatly appreciated, if you need further details, feel free
to ask immediately.
Thanks in advance!
--
Mit freundlichem Gruß


Martin Klier
Senior Oracle Database Administrator
------------------------------------------------------------------------------

Klug GmbH integrierte Systeme
Lindenweg 13, D-92552 Teunz
Tel.:  +49 9671/9216-245
Fax.: +49 9671/9216-112
mailto: martin.klier@xxxxxxxxxx
www.klug-is.de
------------------------------------------------------------------------------

Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg

--
http://www.freelists.org/webpage/oracle-l

Other related posts: