Remedy - Server - After upgrading Oracle to 12.2.0.1 AR System performance degraded [HPD:IncidentInterface]

Version 5
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    AR System


    APPLIES TO:

    Remedy AR System Server with Oracle 12.2.0.1



    PROBLEM:

    After upgrading Oracle to 12.2.0.1 it was noticed that some SQL statements were performing a Full Table Scan on large tables causing a significant performance degradation.
    EXADATA and Oracle Case Insensitivity is being used but these do not appear to be related to the problem being reported.


    CAUSE:

    Oracle Optimizer problems


    SOLUTION:

    Note: This has been identified when the Join formHPD:IncidentInterface is being used and so far not in other forms however please contact Support if a similar behavior is observed in other forms.

    To put context:
    1- Form: HPD:IncidentInterface is a Join form:

              User-added image

    2- This Join form uses the same form, the main HPD:Help Desk using the same field in the join's qualification:

              User-added image

    The field ID for "Incident Number" is: 1000000161, so in the database will be the column C1000000161

    The observed SQL statement using the join form, for example:
    ..................
    SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM (SELECT T2525.C1000000161, T2525.C1
    FROM T2525 WHERE (T2525.C1000000161 = 'INC000000940529') ORDER BY T2525.C1 ASC NULLS FIRST ) AR_SQL_Alias$1)
    WHERE ((AR_RowNumber_Alias$1 > 0) AND (AR_RowNumber_Alias$1 < 103) AND (rownum < 103))
    .................


    Legend for the above query:
         T2525 = HPD:IncidentInterface 
         T1447 = HPD:Help Desk
         - T2525(HPD:IncidentInterface) is a self-join of T1447 (HPD:Help Desk) on column C1000000161 (Incident Number)

    Note that the schemaid issues in the Table ID (T2525) will be different on each database.
    To know the Table ID in your database run the following query:
    SELECT schemaid FROM arschema where name = 'HPD:IncidentInterface';

    The same can be done for the HPD:Help Desk form.

    Running an Execution Plan, and the SQL run time, improves tremendously with the parameter "optimizer_features_enable" set to a previous version of Oracle e.g. 12.1.0.1 or older
    The two plans are shown below.

    If you notice SQL statements that are showing a "degraded" Execution Plan after upgrading to 12.2.0.1 you might want to set the above parameter at the system level  (after ensuring that other SQL statements/use cases are not adversely affected) or at the statement level, via a hint, using SQL Patch.

    Examples:

    PLAN ON 12.2.0.1

    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                    |     1 |    39 | 45635   (1)| 00:00:02 |
    |*  1 |  COUNT STOPKEY                    |                    |       |       |            |          |
    |*  2 |   VIEW                            |                    |     1 |    39 | 45635   (1)| 00:00:02 |
    |   3 |    COUNT                          |                    |       |       |            |          |
    |   4 |     VIEW                          |                    |     1 |    26 | 45635   (1)| 00:00:02 |
    |   5 |      SORT ORDER BY                |                    |     1 |    65 | 45635   (1)| 00:00:02 |
    |   6 |       NESTED LOOPS                |                    |     1 |    65 | 45634   (1)| 00:00:02 |
    |
      7 |        TABLE ACCESS BY INDEX ROWID| T1447              |     1 |    33 |     3   (0)| 00:00:01 |
    |
    *  8 |         INDEX UNIQUE SCAN         | I1447_0_1000000161 |     1 |       |     2   (0)| 00:00:01 |
    |
    *  9 |        TABLE ACCESS FULL          | T1447              |     1 |    32 | 45631   (1)| 00:00:02 |
    --------------------------------------------------------------------------------------------------------



    PLAN ON 12.1.0.1
    The  DBA issued the following SQL command to use the 12.1.0.1 optimizer mode:
    alter session set optimizer_features_enable='12.1.0.1';


    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |                    |     1 |    39 |     5   (0)| 00:00:01 |
    |*  1 |  COUNT STOPKEY                   |                    |       |       |            |          |
    |*  2 |   VIEW                           |                    |     1 |    39 |     5   (0)| 00:00:01 |
    |   3 |    COUNT                         |                    |       |       |            |          |
    |   4 |     VIEW                         |                    |     1 |    26 |     5   (0)| 00:00:01 |
    |   5 |      NESTED LOOPS                |                    |     1 |    65 |     5   (0)| 00:00:01 |
    |
      6 |       TABLE ACCESS BY INDEX ROWID| T1447              |     1 |    33 |     3   (0)| 00:00:01 |
    |
    *  7 |        INDEX UNIQUE SCAN         | I1447_0_1000000161 |     1 |       |     2   (0)| 00:00:01 |
    |
      8 |       TABLE ACCESS BY INDEX ROWID| T1447              |     1 |    32 |     2   (0)| 00:00:01 |
    |
    *  9 |        INDEX UNIQUE SCAN         | I1447_0_1000000161 |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------


    Note: You will need to set the parameter at the system level after testing to determine its value to your environment



     


    Article Number:

    000164869


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles