Bind variables slowing the query?

[ravisekhark]

I have a wierd query that is generated by a third party tool(this is an generated query to talk to siebel).

This is a big query, but ends like.

(T7.ROW_ID LIKE :1) AND
(T4.ASSET_ID = :2)

and the :1 will be replaced by % and :2 is replaced by a valid row_id at runtime.

This query takes about 3 min to execute and return one row of values. But when we directly place the values in the query like

(T7.ROW_ID LIKE '%') AND
(T4.ASSET_ID = 'XYZ' )
The execution takes a few milliseconds. We are using oracle 9.2.0.1

any ideas why this could happen?

[davey23uk]

histograms? how skewed is your data

[slimdave]

There's surely a different execution plan going on here. It seems like it would be worth investigating optimizer plan stability

[ravisekhark]

hi slimdave,
I ran explain plan for both the types and its different. and that explains the problem. I did not think if that could be the case as i am pretty new at this Oracle performance stuff.

I will try to check about optimizer plan stability.
thanks a ton.