RE: No bind peeking - why?

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Wed, 4 Nov 2009 11:16:46 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F2531DF87_at_AAPQMAILBX02V.proque.st>

D'oh! Nevermind.....consider this a retraction of my previous statement. My typing fingers got ahead of my brain... :-)

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark Sent: Wednesday, November 04, 2009 11:12 AM To: oracle-l_at_freelists.org
Subject: FW: No bind peeking - why?

Forwarding due bounce on bad email address.

-----Original Message-----

From: Bobak, Mark
Sent: Wednesday, November 04, 2009 11:11 AM To: 'Martin.Klier_at_klug-is.de'; oracle-l@ Subject: RE: No bind peeking - why?

Hi Martin,

What version of Oracle?? 11.1 or greater? If so, this could be the new intelligent or adaptive cursor sharing in action.

-Mark
-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier Sent: Wednesday, November 04, 2009 5:58 AM To: oracle-l_at_
Subject: 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_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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 04 2009 - 10:16:46 CST