Re: [cx-oracle-users] Strange problem with query execution...

[Andreas M. <and...@we...>]

Hi Thierry,

as far as I know Oracle DB's query planner can take 
into account how selective a value in a where clause
is. That can cause the described phaenomena:
Slow execution with bind parameters, fast with literal
values in sql statement.

Look at the query plan for both cases.
(Buzzword: Query Plan Stability)

Best regards
Andreas Mock


> -----Original Message-----
> From: Thierry Florac [mailto:thi...@on...]
> Sent: Thursday, April 05, 2012 9:08 AM
> To: cx-...@li...
> Subject: [cx-oracle-users] Strange problem with query execution...
> 
> 
> Hi,
> 
> I'm using cx_Oracle (actually in release 5.1.1, but with the same
> symptoms in 5.1 and 5.0.4) through SQLAlchemy to access an Oracle 10g
> database (release 10.2.0.4).
> 
> I'm building a web application querying a quite big table (nearly 3.5
> millions records), which is indexed correctly.
> 
> After activating SQLAlchemy "echo SQL" feature, I noticed that :
>  - an SQLAlchemy request takes between 10 and 15 seconds
>  - the same request executed from an SQL worksheet (in Tora) is nearly
>    immediate.
> 
> So I made a few tests using cx_Oracle without SQLAlchemy and the
> results are that :
>  - if I execute the same query with parameters (via
>    "cursor.execute("select...", param1=value1,...)", which is the way
>    SQLAlchemy works), it still takes between 10 and 15 seconds;
>  - but if I execute the same request with "hard-coded" parameters, the
>    result is immediate.
> 
> Any idea (perhaps on the DB side) ?
> 
> Thanks for any help,
> Thierry
> --
>   Chef de projets internet/intranet
>   Office National des Forêts
>   Direction des Systèmes d'Information
>   2, Avenue de Saint Mandé
>   75570 PARIS Cedex 12
> 
>   Tél. : 01 40 19 59 64
>   Fax. : 01 40 19 59 85
>   Mél. : thi...@on...
>   WWW  : http://www.onf.fr
> 
> ------------------------------------------------------------------------------
> Better than sec? Nothing is better than sec when it comes to
> monitoring Big Data applications. Try Boundary one-second
> resolution app monitoring today. Free.
> http://p.sf.net/sfu/Boundary-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users