IBM Support

PI99672: REMOVE THE FIRST_ROWS HINT FROM ORACLE V10+ PAGINATION QUERIES

Fixes are available

9.0.0.9: WebSphere Application Server traditional V9.0 Fix Pack 9
18.0.0.3: WebSphere Application Server Liberty 18.0.0.3
9.0.0.10: WebSphere Application Server traditional V9.0 Fix Pack 10
18.0.0.4: WebSphere Application Server Liberty 18.0.0.4
19.0.0.1: WebSphere Application Server Liberty 19.0.0.1
19.0.0.2: WebSphere Application Server Liberty 19.0.0.2
19.0.0.3: WebSphere Application Server Liberty 19.0.0.3
9.0.0.11: WebSphere Application Server traditional V9.0 Fix Pack 11
19.0.0.4: WebSphere Application Server Liberty 19.0.0.4
19.0.0.5: WebSphere Application Server Liberty 19.0.0.5
9.0.5.0: WebSphere Application Server traditional Version 9.0.5 Refresh Pack
19.0.0.6: WebSphere Application Server Liberty 19.0.0.6
19.0.0.7: WebSphere Application Server Liberty 19.0.0.7
19.0.0.8: WebSphere Application Server Liberty 19.0.0.8
9.0.5.1: WebSphere Application Server traditional Version 9.0.5 Fix Pack 1
19.0.0.9: WebSphere Application Server Liberty 19.0.0.9
19.0.0.10: WebSphere Application Server Liberty 19.0.0.10
19.0.0.11: WebSphere Application Server Liberty 19.0.0.11
9.0.5.2: WebSphere Application Server traditional Version 9.0.5 Fix Pack 2
19.0.0.12: WebSphere Application Server Liberty 19.0.0.12
20.0.0.1: WebSphere Application Server Liberty 20.0.0.1
20.0.0.2: WebSphere Application Server Liberty 20.0.0.2
9.0.5.3: WebSphere Application Server traditional Version 9.0.5 Fix Pack 3
20.0.0.3: WebSphere Application Server Liberty 20.0.0.3
20.0.0.4: WebSphere Application Server Liberty 20.0.0.4
20.0.0.5: WebSphere Application Server Liberty 20.0.0.5

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When using Liberty server to use jpa-2.1 feature with Oracle
    12i, the OraclePlatform QueryHint FIRST_ROWS does not use
    max
    parameter, so the generated statement is taking a long time
    to
    complete, which is very major impact to the user.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:  All users of IBM WebSphere Application      *
    *                  Server Liberty - Java Persistence APA - JPA *
    *                  2.1 & EclipseLink                           *
    ****************************************************************
    * PROBLEM DESCRIPTION: EclipseLink adds a deprecated FIRST_ROW *
    *                      HINT for Oracle versions 10+, which     *
    *                      leads to performance degradation        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    When executing a pagination query against Oracle, EclipseLink
    generates a query similar to this:
    
        SELECT * FROM (SELECT /*+ FIRST_ROWS / a., ROWNUM rnum FROM
    (SELECT DUMMYID AS a1 FROM dummy) a WHERE ROWNUM <= ?) WHERE
    rnum > ? bind => [40, 20]
    
    However, use of the 'FIRST_ROWS' query hint leads to poorer
    performance on Oracle versions 10+. The reason for this failure
    is due to the EclipseLink binary WebSphere ships with not
    containing the 'org.eclipse.persistence.oracle' extension
    packages. The fix involves adding EclipseLink database platform
    support for Oracle versions 8+ and adding support for
    deprecating this 'FIRST_ROWS' hint.
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    PI99672

  • Reported component name

    WAS LIBERTY COR

  • Reported component ID

    5725L2900

  • Reported release

    CD0

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-06-27

  • Closed date

    2018-09-17

  • Last modified date

    2018-09-17

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    WAS LIBERTY COR

  • Fixed component ID

    5725L2900

Applicable component levels

  • RCD0 PSY

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Product":{"code":"SSD28V","label":"WebSphere Application Server Liberty Core"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"CD0","Line of Business":{"code":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
18 October 2021