Antwort: Re: No bind peeking - why?
From: Martin Klier <Martin.Klier_at_klug-is.de>
Date: Wed, 4 Nov 2009 12:52:05 +0100
Message-ID: <OF42431D43.E2F8C4B7-ONC1257664.00400B45-C1257664.00413194_at_klug-is.de>
Hi Rakesh,
SQL_ID 74tmatw0d035b, child number 0
select /*+ gather_plan_statistics */ * from willi where status=1
SQL> select /*+ gather_plan_statistics */ * from willi where status=:a; <stuff>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT
SQL_ID b4hxcszax9ft0, child number 0
select /*+ gather_plan_statistics */ * from willi where status=:a
SQL> EXECUTE :a := 99
SQL> select /*+ gather_plan_statistics */ * from willi where status=:a; <stuff>
SQL_ID b4hxcszax9ft0, child number 0
select /*+ gather_plan_statistics */ * from willi where status=:a
| 0 | SELECT STATEMENT | | 1 | | 7999K|00:00:20.51 | 1099K| 19708 |
| 1 | TABLE ACCESS BY INDEX ROWID| WILLI | 1 | 1000 | 7999K|00:00:20.51 | 1099K| 19708 |
Date: Wed, 4 Nov 2009 12:52:05 +0100
Message-ID: <OF42431D43.E2F8C4B7-ONC1257664.00400B45-C1257664.00413194_at_klug-is.de>
Hi Rakesh,
you are right:
- SNIPSNAP ==================
SQL>
SQL> select /*+ gather_plan_statistics */ * from willi where status=1; <stuff> SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID 74tmatw0d035b, child number 0
select /*+ gather_plan_statistics */ * from willi where status=1
Plan hash value: 1854384652
| Id | Operation | Name | Starts | E-Rows |A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 141 | | 1 | TABLE ACCESS BY INDEX ROWID| WILLI | 1 | 1000 | 1000 |00:00:00.01 | 141 | |* 2 | INDEX RANGE SCAN | WILLI_STATUS | 1 | 1000 | 1000 |00:00:00.01 | 71 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("STATUS"=1)
19 Zeilen ausgewahlt.
SQL>
SQL> select /*+ gather_plan_statistics */ * from willi where status=:a; <stuff>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT
SQL_ID b4hxcszax9ft0, child number 0
select /*+ gather_plan_statistics */ * from willi where status=:a
Plan hash value: 1854384652
| Id | Operation | Name | Starts | E-Rows |A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 141 | | 1 | TABLE ACCESS BY INDEX ROWID| WILLI | 1 | 1000 | 1000 |00:00:00.01 | 141 | |* 2 | INDEX RANGE SCAN | WILLI_STATUS | 1 | 1000 | 1000 |00:00:00.01 | 71 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("STATUS"=:A)
19 Zeilen ausgewahlt.
SQL> -- and, of course, the curse of bind peeking works as well!!!!!!!!!!! SQL>
SQL> EXECUTE :a := 99
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL>
SQL> select /*+ gather_plan_statistics */ * from willi where status=:a; <stuff>
7999000 Zeilen ausgewahlt.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT
SQL_ID b4hxcszax9ft0, child number 0
select /*+ gather_plan_statistics */ * from willi where status=:a
Plan hash value: 1854384652
| Id | Operation | Name | Starts | E-Rows |A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | 7999K|00:00:20.51 | 1099K| 19708 |
| 1 | TABLE ACCESS BY INDEX ROWID| WILLI | 1 | 1000 | 7999K|00:00:20.51 | 1099K| 19708 |
|* 2 | INDEX RANGE SCAN | WILLI_STATUS | 1 | 1000 | 7999K|00:00:07.32 | 547K| 0 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("STATUS"=:A)
19 Zeilen ausgewahlt.
SQL>
================== SNIPSNAP ==================
Thanks a lot!!
-- Mit freundlichem Gruß Martin Klier Senior Oracle Database AdministratorReceived on Wed Nov 04 2009 - 05:52:05 CST
------------------------------------------------------------------------------
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 |------------> | Von: | |------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| |Rakesh Tikku <rakesh.tikku_at_gmail.com> | >--------------------------------------------------------------------------------------------------------------------------------------------------| |------------> | An: | |------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| |Martin.Klier_at_klug-is.de | >--------------------------------------------------------------------------------------------------------------------------------------------------| |------------> | Kopie: | |------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| |"oracle-l_at_freelists.org" <oracle-l_at_freelists.org> | >--------------------------------------------------------------------------------------------------------------------------------------------------| |------------> | Datum: | |------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| |04.11.2009 12:30 | >--------------------------------------------------------------------------------------------------------------------------------------------------| |------------> | Betreff: | |------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| |Re: No bind peeking - why? | >--------------------------------------------------------------------------------------------------------------------------------------------------| |------------> | Gesendet | | von: | |------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| |oracle-l-bounce_at_freelists.org | >--------------------------------------------------------------------------------------------------------------------------------------------------| Hi Martin, As per my understanding, bind peeking falls under the category of runtime optimization, and does not happen if you are just doing an explain plan. Can you change your experiment to run the sql instead of doing an explain plan? You can also enable cbo trace (event 10053) before running the sql, and the trace file will confirm if it peeked or not. Rakesh Managing Principal DB Perf Inc. On Wed, Nov 4, 2009 at 2:58 AM, Martin Klier <Martin.Klier_at_klug-is.de> wrote: > > 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)) > -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l