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 -- //www.freelists.org/webpage/oracle-l