This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
An Oracle hotshot at a customer site has identified a problem query that can be improved by adding optimizer hints of the form "select /*+ index(.... etc".
The query is executed using a regular JDBC PreparedStatement. The database is oracle 9i and we're using the classes12.zip (not sure of exact revision) for the JDBC driver.
It's a legacy app and the code is relatively old stuff that I've inherited and that I usually don't have to go near very often.
I don't have the necessary Oracle skills to properly interpret EXPLAIN output yet (working on it) so I'm clueless as to whether the hints make any difference to the query. That and the development database is nowhere near as big as the customers, so there's no observable difference between the results of the query with or without the hints.
Does the oracle driver do anything to optimizer hints? I think a prepared statement parses the query and I'm wondering if the driver ignores or discards the optimizer hints in the process. Does anyone have any experience of this?
The JDBC driver should just pass the hints to the database.
Its your customer's call (and they'll have much better visibility of the problem than I have), but optimiser hints are usually the last thing to try. If they are using the cost based optimiser there is loads that can be done before reaching the using hints stage. If they are still using the (legacy) rule based optimiser - which they may we'll be since this is Oracle 9 - you might suggest trying CBO first. Its very useful because then your Oracle hotshot can tune your queries to his/her hearts content without changing any code. Just an idea.
Thanks for the reply. There is a performance problem there, and the query in question doesn't seem to be the best in the world. I suspect the oracle analyst did his thing and found a dodgy query. But my expereicen is that tweaking one query meerly shifts the problem somewhere else.
Thanks again for the help, I'll see if I can persuade them to looking into using a different optimizer.