Re: Different Plans for Literal Vs Bind Variables

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sun, 11 Jan 2009 08:15:46 -0700
Message-Id: <200901111515.n0BFFmp0006959_at_mail96c0.megamailservers.com>

We would need a bit more of the 10053 trace than just the bind peeking information and index cost calculations. At least everything from "BASE STATISTICAL INFORMATION" to "GENERAL PLANS".

How did you run these sql? Both in sqlplus or the one with the literals in sqlplus and the one with the binds in the application? One thing I noticed is that both bind variables are defined as varchar2(32) whereas the lterals obviously are only 4 and 10 characters long. I'd be curious what the bind plan ( and 10053 trace ) look like when you run it in sqlplus and declare the variables with their correct lengths:

var a1 varchar2(4)
var a2 varchar2(10)
exec :a1 := 'SLAC'; :a2 := '2008-01-08;
...

At 11:34 PM 1/10/2009, Ian MacGregor wrote:

>I ran the 10053 traces.
>
>Here is the peeked bind variable information.
>
>Peeked Binds
>============
> Bind variable information
> position=1
> datatype(code)=1
> datatype(string)=VARCHAR2(32)
> char set id=46
> char format=1
> max length=32
> value=SLAC
> Bind variable information
> position=2
> datatype(code)=1
> datatype(string)=VARCHAR2(32)
> char set id=46
> char format=1
> max length=32
> value=2008-01-08
>--------------------------------------
>
>The costs differ. Here is the information from the query using the
>literals.
>
>
>Index: IDX$$_7D3D0001 Col#: 2 1 3
> LVLS: 1 #LB: 4 #DK: 894 LB/K: 1.00 DB/K: 1.00 CLUF: 737.00
>
>
>Access Path: index (index (FFS))
> Index: IDX$$_7D3D0001
> resc_io: 3.00 resc_cpu: 225140
> ix_sel: 0.0000e+00 ix_sel_with_filters: 1
> Access Path: index (FFS)
> Cost: 3.03 Resp: 3.03 Degree: 1
> Cost_io: 3.00 Cost_cpu: 225140
> Resp_io: 3.00 Resp_cpu: 225140
>
>
>And the query using the bind variables
>
>
>Index: IDX$$_7D3D0001 Col#: 2 1 3
> LVLS: 1 #LB: 4 #DK: 894 LB/K: 1.00 DB/K: 1.00 CLUF: 737.00
>
>Access Path: index (index (FFS))
> Index: IDX$$_7D3D0001
> resc_io: 3.00 resc_cpu: 585001
> ix_sel: 0.0000e+00 ix_sel_with_filters: 1
> Access Path: index (FFS)
> Cost: 3.07 Resp: 3.07 Degree: 1
> Cost_io: 3.00 Cost_cpu: 585001
> Resp_io: 3.00 Resp_cpu: 585001
>
>-----------------------------------------------------------
>
>I think this shows that the access paths were given different costs.
>
>Ian
>
>
>
>--
>http://www.freelists.org/webpage/oracle-l

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jan 11 2009 - 09:15:46 CST