Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Oracle not liking the index ... sometimes

Oracle not liking the index ... sometimes

From: Don Seiler <don_at_seiler.us>
Date: Mon, 2 Apr 2007 16:36:11 -0500
Message-ID: <716f7a630704021436k28d45c20wecb156c141d7bafc@mail.gmail.com>


Yet another "why is my query doing this?!?!?!" email.

Running Oracle 10.2.0.2. A dev sent me a note that his java app was hanging. Sure enough he had a query that was doing a full scan on a large table. The problem was, his where clause was all equality lines on every field of the index that the query was supposed to use. Stats on both the table and index were up-to-date (and re-gathered as I watched).

I ran the same query (uses bind vars) in Oracle SQL Developer from my workstation, and it came out great. Both the explain plan and autotrace looked great and the query returned in less than a second. Similar results when he ran it in TOAD. But when he ran it via his java app, it would do the full scan.

I went into EM dbconsole and the tuning advisor came up with "HEY! there's a better query plan". Of course it was the query plan that uses the index, and so I created a SQL profile for it and everything worked great. When I remove the profile, things to sour again.

He is using ojdbc14.jar from 10.2.0.2. I've downloaded the 10.2.0.3 jar but haven't put it on the dev box yet. Of course my Oracle SQL Developer is also JDBC and I don't see the problem.

Any ideas?

--

Don Seiler
http://seilerwerks.blogspot.com
--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 02 2007 - 16:36:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US