Re: Different Plans for Literal Vs Bind Variables

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
>> 
>> 
>> 
>> --
>> http://www.freelists.org/webpage/oracle-l
> 
> Regards
> 
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
> 

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

Other related posts: