Oracle Database Discussions

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

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)

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

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';"

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

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

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