Slow query in Java by JDBC but not in other systems (TOAD)

Question

Hello i have a query to an Oracle System which involves a view which joins other tables by apliying an TO_NUMBER() to the tables primary key.

If i do the query using TOAD the query is very fast (1 sec for 800 regs). If i do the same query in a java program by JDBC with a String literal (not a parametrized query), the time is good too.

But if i use a parametriced query by an PreparedStatement the query takes 1 min to fetch the same registers. I know that using constant values produce distinct execution plan than using parameters... but if i remove the TO_NUMBER funcions in the joins of the view, the parametriced query is fast too.

• Is the parameters / TO_NUMBER() union preventing to use the PK index of the joined tables?
• is there a workaround to solve this (i need the parameters on the query and also the TO_NUMBER function)?

P.D. sry for my bad english

Leger

Answer 1

without additional information, we can only assume that an index isn't being used with the to_number() function is applied to the column. As shown in this SO question, a type conversion can prevent the optimizer from using an index.

In general:

• when you add a function to a column (i-e: to_number(id)) the optimizer won't be able to use the regular indexes on that column,
• if it is possible, you should use the column raw. For exemple: instead of WHERE trunc(col) = DATE '2009-08-27' you should use: WHERE col >= DATE '2009-08-27' AND col < DATE '2009-08-28'
• if you really have to apply a function to a column, you can use a function-based index

Chryso

i know that apliying an function on a column can prevent the use of the column index, but the extrange thing is that it only happens when the query is parametrized – Leger

@Telcontar: What might happen is that the datatype of your constant (with Toad or with a dynamicly built sql) and the datatype of your parameter are different. This could lead to a situation where the plan for a parametrized query is suboptimal because of implicit conversion. Can you give us a simple exemple of a query where you encouter this issue ? – Chryso

The query is SELECT * FROM V_DAT_AJPR_CON_DESCRIP_C WHERE ENTIDAD=7777 AND TO_NUMBER(CFPERIOD)=TO_NUMBER(251) AND CFSUBGRP='S0000' AND ID_VERSION=6 where the four values are dinamic the view use multiple joins with other tables of the form CFPERIOD, ID, FIELD_1, ... FIELD_N and the pk is (CFPERIOD,ID) and the join with the view is TO_NUMBER(V.CFPERIOD)=TO_NUMBER(T.CFPERIOD) AND V.ID=T.ID – Leger

Answer 2

Check that the data type of the Java variable passed in the parameter is compatible with the Oracle data type. I have seen symptoms similar to yours when passing Java TIMESTAMP's through a bind variable that was being compared to Oracle DATE columns - literal string query OK, test case in PL/SQL with (date) bind OK, Java code w/ mismatch not OK.

[Edit] I think you've provided some additional information since the original posting. The best way to understand what is happening with the slightly different forms (binds vs. literals) of the query from different environments (Java vs. Toad) is to enable tracing during the execution and compare the execution paths from the resulting trace files. This will require that you have access to the database host to retrieve the files.

• In Toad, open an interactive SQL window (I don't use Toad but I'm sure you'll understand what I mean) and issue the SQL command "alter session set sql_trace=true"
• Run your query - it would be a good idea to add a comment to the query such as "/* Toad with literals */"
• For the Java test, build a test case that issues the "alter session..." statement and then runs the query. Again, add a comment to the query to identify it as coming from the Java test.
• Don't worry about turning the tracing off - this will happen when the sessions are disconnected and in some cases the disconnection method of stopping the trace is preferred.
• Find out where your trace files on the database host are by "select value from v$parameter where name like 'user_dump_dest' "
• Find the .trc files by searching for the query comment strings
• Use the TKPROF utility from the OS command line to process the trace file - " tkprof filename.trc tkprof filename.out "
• Examine/post the execution paths and times that you see.

Marotta

the datatype comparation is between numbers and strings, some of the strings start with 0's which i can't remove from the tables of the view (i can`t write to them) – Leger

I've added some information in my original answer about tracing the execution which should help you/us analyze what's actually happening. – Marotta

Sry i have forgotten this question (which is already unanswerd), but i can't do the test you suggest, i 'm not the dba and i can only create tables/views, procedures, packages... but not trace querys. I pass it to the DBA but i don't think he do anything – Leger

Answer 3

Check to make sure someone hasn't set the property oracle.jdbc.defaultNChar=true

This is sometimes done to resolve unicode problems but it means all columns are treated as nvarchars. If you have an index on a varchar column, it won't be used because oracle has to use a function to convert the character encoding.

Photoactive

Answer 4

This actual an issue relate with ORACLE server side configuration, just change Cursor_Sharing to EXACT if origin is FORCE

Flung