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

[Mark H. <mh...@pi...>]

On 5/31/12 12:27 AM, Thierry Florac wrote:
>
> 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 :-/


short version:

The solution for us was to simply regather statistics using
DBMS_STATS.GATHER_TABLE_STATS.

long version:

I had a similar problem... I'm no expert, but here's the basic idea:

- execution plans get reused
- it's possible to have a "bad" execution plan which gets reused

For example, suppose you have a column FOO with a yes/no value and a million
rows, all of which have FOO = 1.  It's likely that if you have WHERE FOO = 1,
the planner will know that a full table scan will be more efficient than
an index scan.

But, suppose the data changes, so that only 100 rows have FOO = 1.  Then
obviously an index scan will be more efficient.

But if the query has parameters, (FOO = :1), then [handwaving, coz I
wasn't quite following the explanation], the execution planner doesn't
get reinvoked since it's not obvious to oracle that the row percentages
has changed.

Regathering statistics invalidated the plans based on old statistics
and made our query faster.

HTH!