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

[Anthony T. <ant...@gm...>]

No bells ringing here. :-) We haven't used 10g much yet but haven't
noticed anything of this nature. Could you give some additional
information on what platform you are running Oracle on and what
version of Oracle you are running -- both client and server. Could you
also send a sample query so that I can try this for myself on a 10g
database? We are considering moving to 10g fairly soon so I'd like to
know about these sort of problems up front. :-)

On 11/1/05, Hancock, David (DHANCOCK) <DHA...@ar...> wrote:
>
>
> We're using Oracle 10g, Python 2.3, cx_Oracle "HEAD" (sorry no better
> version number than that, but we picked up the latest version--4.1--earli=
er
> this year and compiled against 10g headers).
>
> We're seeing counterintuitive behavior with one of our queries (we can't
> reproduce it with other "biggish" queries). If we construct the query by
> substituting strings instead of using bind parameters, the query runs 100
> times faster! For the same dataset, that's 30 seconds with bind parameter=
s
> and 0.3 seconds with string substitution. We're holding as much constant =
as
> possible (arraysize, for example, is staying at 20). This is repeatable. =
The
> furthest we've gotten towards isolating the problem is to learn that we c=
an
> reproduce the "fix" by only string-substituting the date fields.
>
> A few more data points: When we "explain plan" on both versions of the
> query, we get identical results. When we run both queries in SQL*Plus, we
> get consistent fast results (< 1 second). But when we run both queries fr=
om
> Python via cx_Oracle, we get the huge discrepancy. We only started notici=
ng
> this a couple weeks ago, but that doesn't mean it wasn't happening before=
,
> we just didn't notice.
>
> We've worked around this by changing just this one query to do string
> substitution, but I'd REALLY like to know what the cause is and fix it.
>
> Does this ring a bell for anyone? I'd expect bind-parameter queries to ru=
n
> faster, not slower, and this one is a LOT slower.
>
> Cheers!
> --
> David Hancock | dha...@ar... | 410-266-4384