No bind peeking - why?

From: Martin Klier <Martin.Klier_at_klug-is.de>
Date: Wed, 4 Nov 2009 11:58:23 +0100
Message-ID: <OF3AB07AD9.C72AA821-ONC1257664.00399379-C1257664.003C46B1_at_klug-is.de>

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_at_klug-is.de
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

Received on Wed Nov 04 2009 - 04:58:23 CST