2

I am trying to execute a query in oracle db. When i try to run thru SQLTools the query is executing in 2 seconds and when i run the same query thru JAVA it is exectuting in more than a minute.

I am using a hint /*+ordered use_nl (a, b)*/

I am using ojdbc6.jar Is it because of any JARS? Please help whats causing this?

Improve this question
3
  • 1
    A simple example could be useful. Also, are you measuring just the time for the query or some sort of JVM startup time as well?
    – Mattias Nilsson
    Commented Jun 18, 2010 at 8:16
  • I am measuring the time of the query
    – user270885
    Commented Jun 18, 2010 at 9:16
  • 1
    hints are irrelevant unless you post the query, and perhaps the explain plan for both the unhinted and hinted versions of the query.
    – Jeffrey Kemp
    Commented Jun 18, 2010 at 10:01

2 Answers 2

Reset to default
1

For Oracle 9i or later, database engine can tune SQL automatically, few case need to specify hints.

How do you run query in Java, repeat to run in loop? use parameter? prepare before execute?

Improve this answer
2
  • I am running thru preparedstatement
    – user270885
    Commented Jun 18, 2010 at 9:14
  • Do you measure one minute time in which statement? 1. preparestatement() 2. execute() 3. while (resultSet.next()) 4. // do something cost one minute in which step?
    – user326503
    Commented Jun 18, 2010 at 16:09
0

You need to profile your Java application to identify where in the Java code before or after the SQL execution that overhead time is being spent.

Some profiling tools that you can use for that are: YourKit, JProfiler and HPROF (this one is a command line tool).

Improve this answer
1
  • Why was my answer downvoted? You really need to run a profiler to find out why it takes a minute to run the SQL via your Java app. I doubt that it is in Oracle that it is taking over a minute, since it only takes 2 seconds from SQL Tools.
    – b.roth
    Commented Jun 18, 2010 at 8:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.