Prepare Statement very slow compare with direct query | Oracle DB

Question

I have a prepared statement in my application and it takes 3 minutes to give an results. However, same query i have executed in sql developer and it only takes less than 0.1 seconds to give the results. I have done research on this throughout last week and I couldn't find a proper solution. Here is my code.

public List<ResponseDto> loadData(RequestDto request) throws SQLException {
        List<ResponseDto> responseDto = new ArrayList<>();
        int sortBy = request.getSortBy();
        String sql = "SELECT *" +
                "FROM (SELECT r.*, ROWNUM RNUM, COUNT(*) OVER () RESULT_COUNT " +
                "      FROM (SELECT *" +
                "FROM" +
                "  (SELECT r.VALUE_4," +
                "    r.DATE," +
                "    r.ID," +
                "    r.AMOUNT," +
                "    r.TO_AGENT_ID," +
                "    r.FROM_AGENT_ID," +
                "    a.NAME," +
                "    r.VALUE_2," +
                "    r.VALUE_1," +
                "    r.STATUS," +
                "    r.VALUE_3," +
                "    r.TEXT" +
                "  FROM MY_TABLE r" +
                " INNER JOIN AGENT a " +
                " ON a.AGENT_ID=r.TO_AGENT_ID" +
                "  WHERE r.STATUS        = 1 " +
                "  AND r.ID IN" +
                "    (SELECT T.ID FROM TEST_TABLE T" +
                "    INNER JOIN AGENT af" +
                "    ON af.AGENT_ID = T.FROM_AGENT_ID " +
                "    INNER JOIN AGENT at" +
                "    ON at.AGENT_ID=T.TO_AGENT_ID" +
                "    WHERE T.FROM_AGENT_ID=?";
        StringBuilder sbQuery = new StringBuilder(sql);

        if (request.getToAgentId() != 0) {
            sbQuery.append(" AND T.TO_AGENT_ID = ? ");
        } else if (request.getQueryParam() != null && !request.getQueryParam().equalsIgnoreCase("")) {
            sbQuery.append(" AND UPPER(at.NAME) like UPPER( ? ) ");
        }

        String secondPart =
                "    AND T.STATUS = 1" +
                "    AND TO_DATE(T.DATE) BETWEEN TO_DATE(?, 'yyyy-MM-dd') AND TO_DATE(?, 'yyyy-MM-dd')" +
                "    ) " +
                "    or r.VALUE_3=?";

        sbQuery.append(secondPArt);

        if (sortBy == 1) {
            sbQuery.append("  ORDER BY a.NAME ");
        } else if (sortBy == 2) {
            sbQuery.append("  ORDER BY r.AMOUNT ");
        } else if (sortBy == 3) {
            sbQuery.append("  ORDER BY r.VALUE_4 ");
        }
        if (request.getSortingOrder() == 1) {
            sbQuery.append("DESC ");
        } else if (request.getSortingOrder() == 2) {
            sbQuery.append("ASC ");
        }
        sbQuery.append("  )) R)" +
                "WHERE RNUM between ? and ?");
        String sqlq = sbQuery.toString();
        log.info(sqlq);
        try(Connection con = dataSource.getConnection(); PreparedStatement pstmt = con.prepareStatement(sbQuery.toString()) ) {
            con.setAutoCommit(false);
            String nameParam = "%" + request.getQueryParam() + "%";
            pstmt.setLong(1, request.getFromAgentId());
            if (request.getToAgentId() != 0) {
                pstmt.setLong(2, request.getToAgentId());
            } else if(request.getQueryParam() != null && !request.getQueryParam().equalsIgnoreCase("")) {
                pstmt.setString(2, request.getQueryParam());
            }
            pstmt.setString(3, request.getFromDate());
            pstmt.setString(4, request.getToDte());
            pstmt.setString(5, request.getQueryParam());
            pstmt.setLong(6, request.getFromIndex());
            pstmt.setLong(7, request.getToIndex());
            responseDto = helperMethod(pstmt);
            con.commit();
        } catch (SQLException e) {
            log.error(e.getMessage());
            throw e;
        }
        return responseDto;
    }


    public List<MyDto> helperMethod(PreparedStatement pstmt) throws SQLException {
        List<MyDto> myDtoList = new ArrayList<>();
        try( ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                MyDto myDto = new MyDto();
                myDto.setValue4(rs.getLong("VALUE_4"));
                myDto.setDate(rs.getDate("DATE"));
                myDto.setTransactionId(rs.getLong("ID"));
                myDto.setAmount(rs.getLong("AMOUNT"));
                myDto.setToAgentId(rs.getLong("TO_AGENT_ID"));
                myDto.setFromAgentId(rs.getLong("FROM_AGENT_ID"));
                myDto.setName(rs.getString("NAME"));
                myDto.setValue2(rs.getLong("VALUE_2"));
                myDto.setValue1(rs.getLong("VALUE_1"));
                myDto.setStatus(rs.getInt("STATUS"));
                myDto.setValue3(rs.getString("VALUE_3"));
                myDto.setText(rs.getString("TEXT"));
                myDtoList.add(myDto);
            }
        }catch (Exception ex){
            log.error(ex.getMessage());
            throw ex;
        }
        return myDtoList;
    }

As I said, same query works with in milliseconds. I really don't know what i am doing wrong here.

Any help would be grateful !

Answer 1

This is not a direct answer, but may hopefully point you in the right direction. First off, depending on your conditionals, there are different variations of what SQL is executed. I would try the following:

1. Edit the select string and embed a unique comment in it so we can find it in the next step. Example : "select /*mytest*/ * from ..."
2. Execute your program. Then locate the query in the v$sqlarea such as: select sql_id from v$sqlarea where instr(sql_fulltext,'mytest') > 0;
3. using the sql_id value from Step #2, execute SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('sql_id',0));

this will show you the execution plan, and hopefully you will see the difference maybe a full table scan is happening or index not getting used. etc. Do similar steps for the direct sql query that is faster and see what the differences are.