ORA-01013 only on the first SELECT execution

Question

I have a java program that performs various SELECT queries on my Oracle database, using the JdbcTemplate class from the Spring framework.

Every time I run this program after a long pause (1-2 days), it remains blocked to a specific query and I'm getting a timeout exception after ten minutes of waiting with a ORA-01013 error. If I run the program again it will perform fine without any error.

The specific query is:

SELECT ca.ARTICLE as article, r.COUNTRY as country, count(1) as total
    FROM CLIENT_ARTICLES ca 
      INNER JOIN ARTICLES aa ON a.ID = substr(ca.ARTICLE, 0, 8) 
      INNER JOIN SEAZON_P sp ON sp.PARAM = 'paramS1' AND substr(a.id, 1, 1) = sp.seazon 
      INNER JOIN REGISTER r ON r.id = ca.CLIENT_ID 
    WHERE ca.ACTION_DATE > sysdate - 5 AND ca.ACTION = 'I' 
    GROUP BY ca.ARTICLE, r.COUNTRY 
    ORDER BY ca.ARTICLE, r.COUNTRY;

The explain plan of this query:

Plan hash value: 3771965889

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |    11 |   759 | 71934   (2)| 00:14:24 |       |       |
|   1 |  SORT ORDER BY                   |                            |    11 |   759 | 71934   (2)| 00:14:24 |       |       |
|   2 |   HASH GROUP BY                  |                            |    11 |   759 | 71934   (2)| 00:14:24 |       |       |
|   3 |    NESTED LOOPS                  |                            |    11 |   759 | 71932   (2)| 00:14:24 |       |       |
|   4 |     NESTED LOOPS                 |                            |    40 |  1920 | 71932   (2)| 00:14:24 |       |       |
|   5 |      NESTED LOOPS                |                            |     2 |    78 | 71928   (2)| 00:14:24 |       |       |
|   6 |       PARTITION RANGE ITERATOR   |                            |     2 |    58 | 71924   (2)| 00:14:24 |   KEY |1048575|
|*  7 |        TABLE ACCESS FULL         | CLIENT_ARTICLES            |     2 |    58 | 71924   (2)| 00:14:24 |   KEY |1048575|
|   8 |       TABLE ACCESS BY INDEX ROWID| REGISTER                   |     1 |    10 |     2   (0)| 00:00:01 |       |       |
|*  9 |        INDEX UNIQUE SCAN         | PK_REGISTER                |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |      INDEX RANGE SCAN            | PK_ARTICLES                |    20 |   180 |     2   (0)| 00:00:01 |       |       |
|* 11 |     INDEX UNIQUE SCAN            | SEAZON_P_PK                |     1 |    21 |     0   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

"   7 - filter(""CA"".""ACTION_DATE"">SYSDATE@!-3 AND ""CA"".""ACTION""='I')"
"   9 - access(""R"".""ID""=""CA"".""CLIENT_ID"")"
"  10 - access(""A"".""ID""=SUBSTR(""CA"".""ARTICLE"",0,8))"
"  11 - access(""SP"".""SEAZON""=SUBSTR(""CA"".""ID"",1,1) AND ""A"".""PARAM""='paramS1')"

The java code who does the query:

public List<ArticleDTO> select(final String param) {
        List<ArticleDTO> articleList;
        String[] queryArgs = { param};
        super.jdbcTemplate.setFetchSize(20000);

        articleList = super.jdbcTemplate.query(SELECT_ARTICLES, queryArgs, this.articleRowMapper);

        return articleList;
    }

The query returns over 80,000 results, but the tables are very large (> 10 million rows).

Any idea that can be the problem?

Thank you!

Answer 1

"I'm getting a timeout exception after 10 min of waiting with a ORA-01013 error. If I run the program again will perform fine without any error"

The meaning of ORA-01013 is user requested cancel of current operation. So most likely explanation is your Spring layer is configured to timeout requests after ten minutes. But it succeeds the second time because the relevant data is stored in database memory and/or OS caches.

So what can you do to improve matters? The blunt instrument is to check the timeout parameter and increase it. That would be the quickest win but possibly controversial. Reducing the execution time is preferable but will take longer, because you'll need to undertake some investigation and testing.

The notable point of your question is this:

"The query returns over 80.000 results, but the tables are very large (> 10 milion rows)."

The rows figures in your explain plan are nowhere near this magnitude. The optimiser thinks your filter on CLIENT_ARTICLE wil return two rows per partition which seems suspiciously low for such large tables. So first thing to check is the freshness of your tables' statistics. Accurate stats help the optimizer to find a good execution plan.

Another thing is that the query is running a full table scan on the partitions in scope (bounded by ca.ACTION_DATE > sysdate - 5 so five or six partitions). If the table is wide (lots of columns) that is an expensive operation when all you need is the ARTICLE in the projection and the ACTION in the filter. If this is a query you run a lot you may derive benefit from a local partitioned index on (ARTICLE, ACTION).