Performance issues with JDBC

Question

I'm currently facing an issue where a specific SQL-query is taking about 30 seconds to issue from within my Java application but <1 sec in a SQL-client (SQL Developer).

In the the question,
Slow query in Java by JDBC but not in other systems (TOAD), it is suggested that using a PreparedStatement bound to java variables could make the query execute far slower than in the SQL-client (TOAD in that case) because Oracle is getting confused about which indexes to use. Could this be an issue with a PreparedStatement without parameters as well?

What could otherwise be the issue?

The query looks something like

select 
sum(col1),
sum(col2),
max(select ...)
from view_
where time_id = get_time_id(to_date('2010-10-10','yyyy-mm-dd'))

where view_ is a complex view containing aggregations of tables and other complex views. The query is executed as a PreparedStatement but without any parameters. It doesn't seem to make a difference whether we use prepared statement or just plain statements.

Since the execution plan is quite huge I can't post all if it here, but the relevant difference seems to be:

UNION-ALL 
TABLE ACCESS FULL GVC_WH.PLAYER_FACT_DAILY TABLE 37 6717151 596,934.317 19940 240 7621178231 19502 

UNION-ALL 
TABLE ACCESS BY INDEX ROWID GVC_WH.PLAYER_FACT_DAILY TABLE 38 2657 236.120 2429 30 20544658 2428 
INDEX RANGE SCAN GVC_WH.PK_AGG_PLAYER INDEX (UNIQUE) 37 2657 16 1 638743 16 

Where the first snippet is from when running it with the JDBC Thin Client and the second from when running it inside SQL Developer. It's not picking up the correct index when running as a statement (makes no difference whether I use a prepared statement or not) with the JDBC Thin Client. The time difference i 30 seconds for the first and 0.5 seconds for the second.

Could it be that using the function get_time_id prohibits the use of the index when used though JDBC, even though it is not function on the column and even though it seems to be working in SQL Developer?

Answer 1

I would try running a trace on the database whilst using the application.

Then you should be able to see the query being run, and the actual execution plan. This will show you exactly what is going on, ie whether it is picking up the indexes or not.

Answer 2

It is very possible you might be hitting problems with bind variable peaking due to the predicates being passed in. Try running the query with the following to confirm (i.e. consistent run times)

alter session set “_optim_peek_user_binds”=false;

Are the stats up-to-date on all the objects?

As justin posted, as well, ensure you're measuring correctly as well. Without the full query, it will be difficult to provide additional insight.