Re: No bind peeking - why?
- From: Rakesh Tikku <rakesh.tikku@xxxxxxxxx>
- To: Martin.Klier@xxxxxxxxxx
- Date: Wed, 4 Nov 2009 03:27:49 -0800
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
Other related posts:
