Oracle query is fast from DataGrip, extremely slow within application

Question

I have the following table(s):

CREATE TABLE items (
    id          NUMBER(20, 0) NOT NULL PRIMARY KEY,
    assigned_to NUMBER(20, 0) DEFAULT NULL NULL,
    revision    NUMBER(10, 0) NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by  NUMBER(20, 0) DEFAULT NULL NULL,
    updated_at  TIMESTAMP DEFAULT NULL NULL,
    updated_by  NUMBER(20, 0) DEFAULT NULL NULL );

CREATE TABLE item_details (
    item_id NUMBER(20, 0) NOT NULL PRIMARY KEY,
    item_created_date TIMESTAMP NOT NULL,
    item_name VARCHAR2(255) DEFAULT NULL NULL,
    item_note VARCHAR2(255) DEFAULT NULL NULL );

CREATE TABLE users (
    id NUMBER(20, 0) NOT NULL PRIMARY KEY,
    email VARCHAR2(255) DEFAULT NULL NULL );

CREATE TABLE items_mv AS
SELECT i.id,
       i.assigned_to,
       d.item_created_date,
       d.item_name,
       d.item_note
FROM items i,
     item_detail d
WHERE d.item_id = d.id
  AND d.item_created_date IS NOT NULL;
  
CREATE INDEX item_created_date_idx ON items_mv (item_created_date, id);

and the following query:

SELECT i.*,
       d.*,
       u.id AS ass_to_id,
       u.email AS ass_to_email,
       NULL AS cre_by_id,
       NULL AS upd_by_id
FROM ( SELECT id, 
              item_some_date
       FROM ( SELECT i.id, 
                     i.item_created_date
              FROM items_mv i
              ORDER BY item_created_date DESC, id DESC
             )
       FETCH NEXT :limit ROWS ONLY
      ) items_table
JOIN items i ON i.id = items_table.id
JOIN item_details d ON i.id = d.item_id
ORDER BY d.item_created_date DESC, d.item_id DESC;

which runs fine through DataGrip on Oracle, with the expected query plan. However, on production, when called through Spring Boot application with NamedParameterJdbcTemplate as follows:

getNamedParameterJdbcTemplate().query(findQuery, new HashMap(){{
    put("limit", 11);
}}, myResultSetExtractor);

It generates a different SQL id from the one I'm running from DataGrip and takes too long, never finishes. There are +20m rows in items table. I've also tried to use index hint /*+INDEX(items_mv item_created_date_idx)*/ to no luck. What am I missing?

---

Update:

Replacing the :limit in SQL String in Java, basically, embedding the limit fixes the issue, speeding up the query with the correct/fast SQL ID. Making NamedParameterJdbcTemplate run with the correct parameter type:

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("limit", limit, Types.NUMERIC);
getNamedParameterJdbcTemplate().query(findQuery, params, myResultSetExtractor);

does not run the fast query. I think it's Oracle not picking up the correct execution plan/index when wrong parameter types are involved. What'd be the correct type for my limit parameter that's used in FETCH NEXT :limit ROWS ONLY?

Answer 1

The SQL_ID doesn't matter, it's just an identifier. A change merely means that the text of the parsed SQL is different than it was before.

Optimally you will want to ensure that your query uses a descending index scan on the item_created_date_idx index that avoids the SORT operation (verify by examining the execution plan). Your ORDER BY is correctly aligned with your index to accomplish this, but sometimes Oracle needs some nudging. I would explicitly add a INDEX_DESC hint:

   SELECT id, 
          item_some_date
   FROM ( SELECT /*+ INDEX_DESC(i item_created_date_idx ) */
                 i.id, 
                 i.item_created_date
          FROM items_mv i
          ORDER BY item_created_date DESC, id DESC
         )
   FETCH NEXT :limit ROWS ONLY

Next, you'll want to ensure it is using nested loops with appropriate indexes against the remainder of the tables. Examine the execution plan to see if NESTED LOOPS is being used to join in items and item_details. Most likely it will be doing so already, but if not you may need to hint with USE_NL or USE_NL_WITH_INDEX. When building a "first n" type query response time is valued over throughput, so you don't want a single hash or merge join nor a single sort anywhere. You want nested loops the whole way.

If you continue to experience instability, use v$active_session_history (or OEM or some other visualization tool that uses Oracle wait sampling data) to determine the child cursor and/or sql plan hash value and then use dbms_xplan.display_cursor to see what execution plan was in use when things went bad. Make sure the sql plan hash value is correct so that you're looking at the right plan. Most likely in this scenario I suspect it is failing to do the index descending scan and so having to read the entirety of that index and then perform a sort against the whole set before picking out the limit rows. I find that hinting to force very specific behavior in a situation like this is well worth it.

Lastly, I personally haven't actually adopted use of the FETCH NEXT syntax (hard to teach an old dog new tricks); not sure in what version that was added. It's probably better than the older method, but just in case you need to try other things, the older way would be simply WHERE ROWNUM <= :limit. If you have trouble with the bind datatype it might make a difference in the event that wrapping it with a cast to number works in one case but not the other (e.g. WHERE ROWNUM < TO_NUMBER(:limit). Just an idea worth a mention.