Re: Different Plans for Literal Vs Bind Variables

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: