CODEWITHSUNDEEP
sqloracle-databasespringjdbcjdbctemplate
Rajesh Balan
Rajesh Balan

Reputation: 643

Oracle SQL Query takes too long in JdbcTemplate & SimpleJdbcTemplate

The oracle SQL query available in our application that uses a SimpleJdbcTemplate never (almost) comes out. The query is very long that connects nearly 15 tables, selects approximately 100 fields and so on.

The query is nearly 8500 characters, 6 bind variables added to it using MapSqlParameterSource instance). I have put enough log messages in the Row Mapper method calls, though the messages are never printed.

The same SQL query after replacing the bind variables with actual values produces the result in 5-7 seconds in SQL*Plus & SQL Developer. The result is only 15 records since I use the logic to implement the pagination support in SQL query itself.

I tried both JDBCTemplate & SimpleJdbcTemplate. Any Idea what could be going wrong??

UPDATE ::

I was able to narrow down the issue. My query contained one synonym as a LEFT OUTER JOIN (which referred to a different schema table) was slowing down the entire query. This was happening when I used this join along with bind variables. When I hard coded the values for bind variables in SQL Developer & SQL*Plus, query was running fine. But when I used the bind variables the same thing (hanging situation) happened in those tools as well.

Currently I am referring to the following link to correct my dblink/synonym usage, Getting a ResultSet/RefCursor over a database link

Thanks,

Upvotes: 1

Views: 1948

Answers (1)

Devolus
Devolus

Reputation: 22084

If you have a different performance between two different connect methods make sure that they don't alter the session optimizer setting.

Maybe your jdbc code somehow specifies the optmizer mode and thus is different form when you connect via sqlplus.

alter session set OPTIMIZER_MODE = RULE;
alter session set OPTIMIZER_MODE = choose;

I had such a problem as well in our environment, so I made sure that all connections use the same optimiziation setting.

Upvotes: 1

Related Questions