• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBC and optimizer query hints - are they recognised?

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi folks,

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?

Thanks,
Mike
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Mike Hayes
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Paul,

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.

Mike
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic