[cx-oracle-users] Re: Performance differences between bind parameters and string su bstitution

[Henning v. B. <H.v...@t-...>]

Perhaps it would help if you'd send the query SQL text.
How does it use your bind variables?

I mean, are you doing (using scott/tiger as an example)
"where ENAME like :P_ENAME"
or
"where ENAME = :P_ENAME" ?

This can be quite a difference in 10g, AFAIK.
That's because 10g has a new feature called
bind-variable inspection (or whatever).

When the query is first executed, the optimizer
checks the actual value of P_ENAME and then treats
the query as if it used a constant.

Thus, if your very first value is "%SMITH", for example,
Oracle will decide that it cannot use an index 
(even if ENAME was a primary key) and use a full table scan.

Never tested this, anyway.
Perhaps your query is a good candidate for Tom Kyte's asktom web site ?

Henning