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

[Thierry F. <thi...@on...>]

Hi Christian,

No, I didn't found any solution until now.
I asked SQLAlchemy mailing list and afterwards cx_Oracle ML, which
redirected me to several Oracle links.
I made several checks (only using cx_Oracle) and the conclusion is
that :
 - if I execute the query generated by SQLAlchemy, using query
   parameters, the index is NOT used
 - but if I execute the same query using hard-coded parameters, the
   index is used and the execution time is correct :-/

Of course, I don't want to go back and update my application without
using SQLAlchemy, so I leaved the code untouched...

The problem seems to be linked to what is called "Oracle execution plan
stability", as described here:
  http://docs.oracle.com/cd/B19306_01/server.102/b14211/outlines.htm

I tried to make several tests using "stored outlines" as described in
this article (with the help of our "poor DBA" :-/ !!), but without any
success until now...

Regards,
Thierry


Le Thu, 31 May 2012 06:24:53 +0200,
  Christian Klinger <ckl...@no...> a écrit:

> Hi Thierry,
> 
> did you find a solution for that problem. It seems run in the same
> issues?
> 
> Thanks in advance
> Christian
> 
> Thierry Florac schrieb:
> > Le Thu, 05 Apr 2012 05:39:40 -0600,
> >    Doug Henderson<djh...@te...>  a écrit:
> >
> >> Look for implicit type conversions in the where clause. If columns
> >> are converted to match the parameter type, then an index on the
> >> column will not be used. You may need to include explicit type
> >> conversion around the parameters to prevent implicit conversion of
> >> indexed columns. This is a subtle problem that can be very hard to
> >> detect, but cause horrible query performance.
> >>
> >> For example, a clause like "numeric_column = :param1" may need to
> >> be coded as "numeric_column = to_number(:param1)".
> >
> > Thanks for the hint !
> > But I checked all database columns and query parameters and I only
> > use strings; that's a point where SQLAlchemy prevents you from
> > using bad query parameters types...
> >
> > Regards,
> > Thierry