Oracle not liking the index ... sometimes
- From: "Don Seiler" <don@xxxxxxxxx>
- To: oracle_l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Apr 2007 16:36:11 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
