Fetching Oracle DB LONG type through jdbc works very slow

Issue

This happens only when the statement return LONG column.
Even if i don't do anything with it such like rs.getString, just execute.

sql1: That was: 7593 ms
sql2: That was: 530 ms

As you can see it's not about volume of data.
When i use OracleConnection:OracleStatement:OracleCachedResultSet result is the same.
Also i tried to use OracleCachedRowSet instead of ResultSet.
Problem shows up in OracleCachedRowSet.execute() before OracleCachedRowSet.next().
When i set setFetchSize(1):

sql1: That was: 7474 ms
sql2: That was: 7153 ms

UPDATE 1:
I had tested other tools:

sql1# Toad For Oracle : execution time < 1 second
sql1# Oracle SQL Developer thin: 7.5 second(same as my result)
sql1# Oracle SQL Developer OCI: 7.5 second(same as my result)
sql2# Oracle SQL Developer thin: 0.36 second(same as my result)
Rows fethed one by one in sql1 case in java app, so look like setFetchSize() dosn't work.

Sample code:

public class Test {

    public static void main(String[] args) throws SQLException {

        final String sql1 = "select HIGH_VALUE from sys.DBA_TAB_PARTITIONS WHERE rownum < 20";
        final String sql2 = "select PARTITION_NAME, HIGH_VALUE_LENGTH,TABLESPACE_NAME,NUM_ROWS,BLOCKS,\n"
                + "EMPTY_BLOCKS,LAST_ANALYZED,AVG_SPACE,SUBPARTITION_COUNT,COMPRESSION from sys.DBA_TAB_PARTITIONS WHERE rownum < 20";

        OracleDataSource ods = new OracleDataSource();
        ods.setDriverType("thin");
        ods.setUser("vr");
        ods.setPassword("oracle");
        ods.setDatabaseName("ORCL");
        ods.setServerName("192.172.18.1");
        ods.setPortNumber(1521);

        Connection conn = null;
        conn = ods.getConnection();

        Statement stmt = conn.createStatement();
        stmt.setFetchSize(1000);

        // Start time
        long startTime = System.currentTimeMillis();

        ResultSet rs = stmt.executeQuery(sql1);
        rs.setFetchSize(1000);
        while (rs.next()){
            //System.out.println(rs.getString(1) + " " +  rs.getString(2));
            System.out.println("row#: " + rs.getRow());
        }

        // Finish time        
        long endTime = System.currentTimeMillis();
        System.out.println("That was: " + (endTime - startTime) + " ms");
    } 
}

Solution

Results with new ojdbc7.jar(before i used ojdbc6.jar from 11.2.0.2 bundle) and OracleJDBCRowSet.
19 rows:

sql1 : That was: 1470 ms // before 7-8 seconds
sql2 : That was: 1140 ms

99 rows:

sql1 : That was: 1491 ms
sql2 : That was: 1158 ms

As you can see its much faster, but still slower than query with even much more data.
Adding more column almost make no changes in execution and fetching time, so the problem partially solved.
But seems that the improvements comes to RowSets only.
OracleResultSet still work extremly slow.
Also if you link new driver with Oracle SQL Developer it takes no effect.

Sample code:

public class Test {

    public static void main(String[] args) {

        final String sql1 = "select HIGH_VALUE from sys.DBA_TAB_PARTITIONS WHERE rownum < 100";
        final String sql2 = "select PARTITION_NAME, HIGH_VALUE_LENGTH,TABLESPACE_NAME,NUM_ROWS,BLOCKS,\n"
                + "EMPTY_BLOCKS,LAST_ANALYZED,AVG_SPACE,SUBPARTITION_COUNT,COMPRESSION "
                + "from sys.DBA_TAB_PARTITIONS WHERE rownum < 100";

        OracleDataSource ods = null;
        try {
            ods = new OracleDataSource();
        } catch (SQLException ex) {
            System.exit(2);
        }
        ods.setDriverType("thin");
        ods.setUser("vr");
        ods.setPassword("oracle");
        ods.setDatabaseName("ORCL");
        ods.setServerName("192.172.18.1");
        ods.setPortNumber(1521);

        try (Connection conn = ods.getConnection();) {
            try (OracleJDBCRowSet rs = new OracleJDBCRowSet(conn);) {
                rs.setFetchSize(200);
                rs.setReadOnly(true);
                rs.setCommand(sql2);

                // Start time
                long startTime = System.currentTimeMillis();

                rs.execute();
                while (rs.next()) {
                        System.out.println("row#: " + rs.getRow() + " " + rs.getString(1));
                }

                // Finish time        
                long endTime = System.currentTimeMillis();
                System.out.println("That was: " + (endTime - startTime) + " ms");
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}

Answered By - gotozero