oracle query performance in java

Question

My Scenario is i have big query with lot of joins and lot of decode/case calls in select and i am passing one param to where condition from java and i see for 150000 rows java fetch is very slow but query is running faster in SQL developer client interface.

i thought of creating or replacing a view which takes one parameter and call that view from java.

Did not find resource to know how to pass prams to create or replace view statement from java ?

Any one suggest other approach that fetches rows quickly ?

Using oracle 12c and driver is jdbc7 and jdk8

• Pavel Smirnov: How did you measure that?
• Alex Poole: Are you sure the difference is really execution and not fetch? What fetch size are you using from Java? And are you actually retrieving all rows in SQL Developer or just the default first 50 in the grid view? Check that you are passing (binding, hopefully) the parameter as the correct data type - if not you might be introducing implicit conversion what prevents index use, for instance.
• mwarren: Have you added any indexes?
• 9000: (1) SQL Developer just does not fetch 150k result rows at once; it only fetches the first few (like 100-200) (2) Ask Oracle to optimize for ALL_ROWS to have the best performance fetching large numbers of rows; SQL Developer likely optimizes for FIRST_ROWS. (3) Your deserialization / ORM code may be the slowest part; are you measuring the raw JDBC performance?

Answer 1

First (and easiest):

Set the JDBC fetch size to a high number in your statement. There is a setFetchSize(int) method on Statement, PreparedStatement, CallableStatement, and ResultSet objects. This defaults to something small like 10 rows. Set that to a reasonably high number, such as 500 or more.

This is a setting that will definitely slow down a query that pulls back hundreds of thousands of records.

Second:

Verify that the query is indeed running fast in SQL Developer, to the last row. You can export to a file or try wrapping the query in a PL/SQL statement that will loop through all records.

If you wish, you can use AUTOTRACE in SQL*Plus to your advantage:

SET TIMING ON
SET AUTOTRACE TRACEONLY
<your query>

This will run the query to the end, pulling all records over the network but not displaying them. The goal here is to prove that your SQL statement is indeed returning all records as quickly as needed.

If not, then you have a standard tuning exercise. Get it running to completion quickly in SQL Developer first.