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

[Hancock, D. \(DHANCOCK\) <DHA...@ar...>]

Platform stuff:

Platform: White Box Enterprise Linux 3.0 (clone of Redhat Enterprise
Linux 3.0);
Hardware: Intel dual Xeon processors, 3.0 GHz, 6GB RAM, lotsa disk=20
Oracle server version: 10.1.0.3
Oracle client version: 10g Instant Client

We'll follow up directly with Anthony on the query itself.

D.R. Boxhoorn also mentioned a similar weird case where running
hextoraw() on the bound parameter sped up the query a lot (but it was a
column with hex strings in it). I don't think that applies to our issue,
but it's good to know--thanks for that.

Cheers!
--
David Hancock | dha...@ar... | 410-266-4384=20


-----Original Message-----
From: cx-...@li...
[mailto:cx-...@li...] On Behalf Of
Anthony Tuininga
Sent: Wednesday, November 02, 2005 9:33 AM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Performance differences between bind
parameters and string substitution


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--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


-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server.
Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users