Re: No bind peeking - why?
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: Martin.Klier@xxxxxxxxxx, rakesh.tikku@xxxxxxxxx
- Date: Wed, 4 Nov 2009 06:30:52 -0800 (PST)
from:
http://optimizermagic.blogspot.com/2008/02/displaying-and-reading-execution-plans.html
variable pcat varchar2(50)
exec :pcat := 'Women'
select PROD_CATEGORY, avg(amount_sold)
from sales s, products p
where p.PROD_ID = s.PROD_ID
and prod_category != :pcat
group by PROD_CATEGORY;
select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));
------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'
--- On Wed, 11/4/09, Rakesh Tikku <rakesh.tikku@xxxxxxxxx> wrote:
> From: Rakesh Tikku <rakesh.tikku@xxxxxxxxx>
> Subject: Re: No bind peeking - why?
> To: Martin.Klier@xxxxxxxxxx
> Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
> Date: Wednesday, November 4, 2009, 6:27 AM
> 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:
