FW: No bind peeking - why?
Date: Wed, 4 Nov 2009 11:12:06 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F2531DF50_at_AAPQMAILBX02V.proque.st>
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
- SNIPSNAP ===============
SQL>
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)| 00:00:01 |
--------------------------------------------------------------------------------------------
| 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
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
Received on Wed Nov 04 2009 - 10:12:06 CST