Timesten : Query Performance for ROWNUM query is low (Doc ID 2098714.1)

Last updated on JULY 03, 2017

Applies to:

Oracle TimesTen In-Memory Database - Version 11.2.2.7.0 and later
Information in this document applies to any platform.

Symptoms

The following query is taking 53 seconds to complete

The execution time through ttisql -

SELECT * FROM (
  SELECT KEY, ID, CODE, RINT, PRIORITY, EKEY
FROM
XXXXXX
WHERE PRIORITY < 9999 ORDER BY TRANS_DATE, PRIORITY, ID, CODE )
 WHERE
    ROWNUM < 10 + 1;


< XXXXXX, 153472733, CLCGRREP, 311683708609, 50, >
< XXXXXX, 161352825, B, 0, 50, >
< XXXXXX, 161352834, IGNORE, 0, 50, >

.......
< XXXXXX, 153649796, IGNORE, 327507012053, 50, >
10 rows found.
Execution time (SQLExecute + Fetch Loop) = 53.838433 seconds.

When taken trace we saw after fetch, the close cursor is taking
more time

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms