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

[D.R. B. <da...@as...>]

Hoi David,

What you describe does ring a bell, but whether that will be of any help ...
We noticed that queries with "WHERE OBJECT_ID=:1" performed _much_ worse than
"WHERE OBJECT_ID=HEXTORAW(:1)", where the bind variable contains a
hexadecimal string.
In our case the execution plan differed between both cases. A full table
scan was performed when the HEXTORAW() was not used. We have never tried
to find out what was the cause of this difference in behaviour, though.
We had a suspicion that it could be due to the way string/RAW conversion
is handled by OCI and the cx_Oracle module. If this is the kind of
conversion your query has to perform, then maybe there is a connection.

Also note that the cost-based optimizer can choose different execution plans
for bind variables on one hand and for hard-coded constants on the other
hand if your data is skewed. From your explanation I understand that this
is not the case.

Hoping that this helps in finding the cause of the strange behaviour,

                                                   Danny

On Tue, Nov 01, 2005 at 03:36:51PM -0500, Hancock, David (DHANCOCK) 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--earlier 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
> parameters 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 can 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 from Python via cx_Oracle, we get the huge discrepancy. We only
> started noticing 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
> run faster, not slower, and this one is a LOT slower.
> 
> Cheers!
> --
> David Hancock | dha...@ar... | 410-266-4384 
>