Re: Different Plans for Literal Vs Bind Variables
- From: Ian MacGregor <ian@xxxxxxxxxxxxxxxxx>
- To: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- Date: Sun, 11 Jan 2009 14:13:45 -0800
Both were run out of SQL*PLUS.
I did not use exact lengths
variable a1 varchar2(10);
Variable a2 varchar2(20);
Begin
:a1:=SLAC;
:a2 := '2008-01-08';
End;
Now as far as variable lengths are concerned. Here is some additional
information.
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
Bind#0
oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=46 siz=64 off=0
No bind buffers allocated
Bind#1
oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=46 siz=0 off=32
No bind buffers allocated
I gather from this that 32 bytes are allocated for each variable despite the
request for 10 and 20 bytes.
I'll try exact lengths but I don't see that as a problem. 32 bytes are
going to be allocated for a2 if I change its definition to varchar2(10).
I'll answer the other questions in other mail. I want to address Karen's
questions as well.
On 1/11/09 7:15 AM, "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx> wrote:
> 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
>>
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
