Antwort: Re: No bind peeking - why?
- From: "Martin Klier" <Martin.Klier@xxxxxxxxxx>
- To: rakesh.tikku@xxxxxxxxx
- Date: Wed, 4 Nov 2009 12:52:05 +0100
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'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
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 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
|------------>
| Von: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|Rakesh Tikku <rakesh.tikku@xxxxxxxxx>
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| An: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|Martin.Klier@xxxxxxxxxx
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Kopie: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|"oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Datum: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|04.11.2009 12:30
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Betreff: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|Re: No bind peeking - why?
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Gesendet |
| von: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|oracle-l-bounce@xxxxxxxxxxxxx
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
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@xxxxxxxxxx>
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))
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Antwort: Re: No bind peeking - why? - Martin Klier
