Performance problem with slow VIEW from JDBC (fast from SQL Developer)

blama

Hi all,

I'm experiencing following problem and would like to know if someone else also hit this one before and has a suggestion how to solve it:

I have a pretty complicated SELECT statement that per definition returns only a few rows (~30). With no further optimization it takes ~20 seconds to return the full dataset in Oracle SQL Developer. If you add the */+ PUSH_PRED(name_of_some_inner_view) /* hint (hint is correct, stars got eaten by the OTN-forum syntax), the statement takes less than 0.5s to execute (still in SQL Developer). I saved the statement with the hint as VIEW. Selecting from the VIEW in SQL Developer is also fast.
Now if I call the statement from JDBC (Tomcat webapp), I can see from the server console that the statement is 1:1 100% the same as the one I execute in SQL Developer. Nevertheless it takes about 20 seconds to complete.

Here my details:

SELECT banner FROM v$version;

BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production              
PL/SQL Release 11.2.0.2.0 - Production                                           
CORE	11.2.0.2.0	Production                                                         
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                          
NLSRTL Version 11.2.0.2.0 - Production                                           

JDBC Driver used: some old odbc14.jar as well as current odbc6.jar for 11.2.0.2.0 from http://www.oracle.com/technetwork/da...10-090769.html
SQL Developer: current version 3.2.20.09

From my reading this could go wrong:
- JDBC doesn't know the VIEW's column data types and Oracle behaves mysterious because of this (=there must be more to the SELECT than just the string, some meta-information)
- For some reason the hint inside the VIEW is not used (unlikely)

I also tried a Table Function/Pipelined table and selected from it as a workaround, but the result is the same: Selecting from Function is fast from SQL Developer, but slow from JDBC. All other statements that come from JDBC are as fast as they should be. I really don't know what to think of this and where the error might be.

Is there some setting that tells Oracle not to use hints when called from JDBC?

Thank you & Best regards,
Blama

Bawer

how long does it take in sql developer without hint ???

blama

Hi,

the exact times are:
Without Hint, Program(JDBC) as well as SQL Developer: 160s (also for 2nd and 3rd run)
With Hint, Program(JDBC): 160s (also for 2nd and 3rd run)
With Hint, SQL Developer: 7s (1st run), 0.5s (2nd and 3rd run)

Bawer

blama wrote:
the exact times are:
Without Hint, Program(JDBC) as well as SQL Developer: 160s (also for 2nd and 3rd run)

this means you didn't pass the hint (or has been ignored)
post your java code.

blama

Hi Bawer,

that's what I'm thinking. Unfortunately I can't post it, as it is library code (not my lib). But in the debug-output I can see the SQL-String sent to the DB (which does include the hint).

But I find the 2nd option you mention more likely anyway: Even if I put the hint into a VIEW and select from the view, the time-difference is there (it's even there if I use Table Functions/Pipelined table and select from the function).
So I'd think it is more likely that something else is happening (e.g. Oracle is configured in a way that it does not use hints when called from JDBC or similar. Or the library sets some session options in order to prevent the usage of hints). But I don't know if there is even the possibility of doing so.
Does the Oracle JDBC driver have the option to set these options?
Does the Oracle DB have the option to set sth. like "ALTER SESSION SET dontUseHints = 'Y';"

JustinCave

There is no session-level parameter that would tell Oracle to disregard hints. I suppose it is possible that if you were to set some ancient optimizer_features_enable setting that pre-dated a particular hint that the hint might no longer be valid but I've never had occasion to try that. Setting the OPTIMIZER_MODE to RULE might also have some impact. Neither of these things seem particularly likely.

Do you actually get the same SQL_ID when you run the query from JDBC? If you are getting a different SQL_ID, I would tend to suspect that it is much more likely that you have an outline and/or profile attached to the "good" SQL_ID that isn't getting used for the "bad" SQL_ID (assuming that there really isn't some subtle difference other than whitespace between the queries).

Justin

blama

Hi Justin,

thanks for your answer. How can I find out about the SQL_ID? Especially when running the JDBC Query?
As I'm just starting to build the application I'm still on 11.2 XE, so I might not have all features of the full version.

Best regards,
Blama

JustinCave

Assuming you can find the session that executed the query in V$SESSION, SQL_ID will give you the currently executing SQL_ID and PREV_SQL_ID will give you the previously executed SQL_ID. You can also query the V$SQL view to find the SQL statement(s) in question.

Justin