1 2 Previous Next 23 Replies Latest reply on Jul 6, 2020 3:09 AM by Samo Puharic

    Remedy slow with functional indexes in version 19.08 & 20.02

    Samo Puharic
      Share This:

      We were doing upgrade from 9.1.04 to the 19.08 and later to the 20.02.

       

      DB: Oracle 12c (12.1.0.2.0)

      Some key tables:

      count(*) on HPD_Help_desk - 340131 records

      count(*) on SRM_Request - 331052 records

       

      On 9.1.04 we have functional indexes in place and cursor_sharing set to EXACT in ar.cfg and in database.

       

      During upgrade to the 19.08 we have had following symptoms:

      - AR 19.08 upgrade failed -> needed hotfix from support and installer in pause mode to finish installation

      - timeouts during deployment ITSM apps via deployment console -> needed to raise ARAPI timeout on OS level

      - installer took more time to finish as before in previous upgrades

      -> so, we finally managed to upgrade our DEV/TEST system

       

      After upgrade - various performance issues:

      - midtier slowness, especially during Preload (took >30min)

      - despite Preload done, very long time for form first load (from 1-2min to >10min)

      - when cache in place, look normal but with slight overhead in all actions

      - save on INC form on change status about 6-8sec

       

      Working with BMC support on various cases for different symptoms. No use.

      Finally we gave up - because we suspect root cause of all above issues is case insensitivity and functional indexes - we did revert to normal indexes.

       

      Next attempt to upgrade was to the 20.02 with normal indexes. With cursor_sharing set to EXACT.

      No any issues - normal upgrade with normal installation times (as in previous upgrades)

       

      Then we switch to the functional indexes again.

      And again performance issues.

      - very slow Preload

      - Sync Cache failed

      - save on INC form on change status about 6-8sec

       

      Analysis via LogAnalyzer for 20.02 and comparison of logs before and with functional indexes - clearly shows different pattern of executing SQL queries.

      Our senior DBA with help of this comparisons took assumption maybe cursor_sharing parameter is wrong.

       

      Changing to cursor_sharing = FORCE we finally got almost "normal" performance - as with normal indexes - but with case insensitivity, what is our goal of course.

      - normal speed of  Preload

      - save on INC form on change status about 2-3sec (2sec with normal indexes)

      - sometimes on first status change on INC form took more time e.g. >6sec

      -> good enough we could go in production like this

       

      And now we have problem - our findings is in opposite what is in BMC docs and KB articles

      https://docs.bmc.com/docs/brid2002/tuning-the-oracle-database-server-908971707.html

      REMEDY 9.x/18xx/19xx/20xx Release - CONFIGURATION CHECKLIST from the BMC R&D PERFORMANCE TEAM

      and KB article about

      Remedy - Server - Enabling case-insensitive searches with an AR System server 8.1 or 9.x with Oracle

      link to the 19.02 docs page

      Please check the following link to get this information: Tuning the Oracle server - Cursor sharing

      with same prescription

      cursor_sharing = EXACT

       

      I remember BMC docs point out cursor_sharing  should be FORCE for Oracle case insensitivity.

      But now documentation is cleared of such hint - reason explained in article

      Remedy 9.x, Oracle Case Insensitivity and SQL with LIKE conditions

       

      Please experts - advise. What to do now ?!

      Our system is better working with cursor_sharing = FORCE but it seems in contrary to the BMC recommendations.

       

      It looks like bug or BMC code reverted to the code before 9.1.04 patch 002...

        • 1. Re: Remedy slow with functional indexes in version 19.08 & 20.02
          Andreas Mitterdorfer

          Which setting have you used for Db-Case-Insensitive ? I assume it is T as mentioned in the KB?

           

          Were you able to crosscheck execution plan of slow queries with sqlplus or sqldeveloper using ar server session parameters and executing the query with variables and without?

          • 2. Re: Remedy slow with functional indexes in version 19.08 & 20.02
            Samo Puharic

            Hi Andreas,

             

            Yes, parameters in ar.cfg are T as mentioned in KB:

            Remedy - Server - Enabling case-insensitive searches with an AR System server 8.1 or 9.x with Oracle

            Db-Case-Insensitive: T

            Db-Functional-Index: T

             

            There are no slow SQL queries as such (>0.5sec). It could not be found. It was confusing for BMC support, as well.

            LogAnalyzer is not in help in such situation - so, by manual analysis in Notepad++ for use case INC saving we found out some queries take longer to run cca 0.3sec instead normal 0.001sec for indexed query. Which, at the end gives 6-8sec for SGE API call -> saving INC ticket.

             

            Yes. In SQL Devloper we execuded:

            ALTER SESSION SET NLS_COMP=LINGUISTIC
            ALTER SESSION SET NLS_SORT=BINARY_CI

            And tested some of "0.3sec" queries - execution plan in SQL developer is telling us that linguistic index are used. Not skilled enough to test SQL queries with variables.

             

            We found out cursor_sharing = FORCE could help and give us normal performance. -> It means not problem with SQL queries as such.

            • 3. Re: Remedy slow with functional indexes in version 19.08 & 20.02
              Andreas Mitterdorfer

              If you want to test with variables in sqlplus you just need to define them first, set the values and then execute the sql with the variables.

               

              Example:

              Original sql:

              SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM (SELECT T582.C200000005, T582.C200000001, T582.C260000104, T582.C200000004, T582.C200000003, T582.C1 FROM T582 WHERE

              (((T582.C800000014 = '00') OR ('00' = ' ')) AND ((T582.C800000015 = '117') OR ('117' = ' ')) AND (T582.C700020000 = 1)) ORDER BY T582.C200000005 ASC, T582.C1 ASC) AR_SQL_Alias$1) WHERE ((AR_RowNumber_Alias$1 > 0) AND (AR_RowNumber_Alias$1 < 302) AND (rownum < 302))

               

              =>

               

              VARIABLE b7 NUMBER;

              VARIABLE b8 NUMBER;

              VARIABLE b9 NUMBER;

              VARIABLE b10 NUMBER;

              VARIABLE b1 Varchar2(10);

              VARIABLE b2 Varchar2(10);

              VARIABLE b3 Varchar2(10);

              VARIABLE b4 Varchar2(10);

              VARIABLE b5 Varchar2(10);

              VARIABLE b6 Varchar2(10);

               

              exec :b1 := '00';

              exec :b2 := '00';

              exec :b3 := ' ';

              exec :b4 := '117';

              exec :b5 := '117';

              exec :b6 := ' ';

              exec :b7 := 1;

              exec :b8 := 0;

              exec :b9 := 302;

              exec :b10 := 302;

               

              SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM (

              SELECT T582.C200000005, T582.C200000001, T582.C260000104,T582.C200000004, T582.C200000003, T582.C1 FROM T582

              WHERE (((T582.C800000014 = :b1 ) OR (:b2  = :b3 )) AND ((T582.C800000015 = :b4 )OR (:b5  = :b6 )) AND

              (T582.C700020000 = :b7 )) ORDER BY T582.C200000005 ASC, T582.C1 ASC) AR_SQL_Alias$1) WHERE ((AR_RowNumber_Alias$1 > :b8 ) AND (AR_RowNumber_Alias$1 < :b9 ) AND (rownum < :b10 ));

               

              Maybe there is a difference between in runtime of query with variables or without.

              • 4. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                Randy Evans-Strum

                Samo Puharic,

                 

                in your post it sounded like you had to revert your indexes to 'Normal' non functional indexes, before getting a successful upgrade.  Did you use the unsupported script to convert your indexes?  Were upgrading from 18.8 to 19.8 and are having issues that my gut tells me are related to case insensitivity.  Was your upgrade to a server group?  Are you using the SID or the service for your oracle connection?  if it was a server group you were upgrading did you have any issues with installer picking up the server group name as the service name?  Any helpful hints on what helped you be successful would be appreciated.

                 

                Thanks

                Randy

                • 5. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                  Samo Puharic

                  Hi Randy Evans-Strum,

                   

                  Yes, our DBA did revert to the normal indexes via script provided in KB article

                  Remedy - Server - Enabling case-insensitive searches with an AR System server 8.1 or 9.x with Oracle

                  Be careful - you should check for errors - creating some unique indexes could failed because of duplicates - e.g. jon and Jon are not the same any more.

                  We spend a lot o time to detect functional indexes could be reason of our problems :-/

                  It seems AR metadata could not be updated properly because there are no indexes in PK constraints.

                  Anyway - problem solved reverting to the normal indexes. And after successful upgrade reverting back to the functional.

                   

                  We have server group and only one AR server. Because of server group is mandatory from some 9.1.x version upwards.

                  We use Server Name parameter as server group name "xyz". Not using LB. Set IP for "xyz" in hosts file.

                  We did change Server Name to the name of AR server hostname - before upgrade. And reverting to the "xyz" after finishing upgrade.

                  Btw, installer is getting AR server name from XML of previous install. You should change to the AR hostname if different.

                   

                  We have had problem because of Server-Connect-Name - before upgrade we added multiple entries in ar.cfg  - with AR short name and IP address -> according some old recommendation for server group settings.

                  But it should be only one Server-Connect-Name with FQDN of AR server - the same as Configuration-Name.

                  If you have multiple Server-Connect-Name - installer could add them in Ranking form - and expect they should be upgraded as well. But could not find them.

                  So your upgrade stays in Pending status...

                  We did revert/restore - delete multiple Server-Connect-Name - and do upgrade with only FQDN Server-Connect-Name - with no problems.

                   

                  For Oracle using SID only.

                   

                  And if you have Smart Reporting ask for hotfix ASAP. There is major bug - running query in loop - produce overhead on DB.

                   

                  I wish you have happy upgrade ;-)

                  Samo

                  2 of 2 people found this helpful
                  • 6. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                    Randy Evans-Strum

                    Samo Samo Puharic,

                     

                    Thanks for the information.  Our sandbox was case insensitive, but had issues with a long startup time, so the upgrade would fail, along with the system passwords being too long.  When i looked at the indexes i found that there were only about 150 functional ones, compared to the Dev which was installed the same way had a couple orders of magnitude more.  I converted it back to case sensitive, and upgrade worked without issue.  Then i used the scripts to convert back to case insensitive. Support is going to give us a fix so we don't have to do that.  Hopefully it works upgrades are so much fun.

                     

                    Randy

                    • 7. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                      Kobus Duminy

                      Have a similar situation where we have non-prod environments on 20.02 with the cursor as FORCE on 12c.

                      Getting quite a bit of mixed messages around what is best (FORCE vs EXACT) from BMC like you indicate in your comments. We will keep it FORCE as overall speed appear similar to pre-20.02, but will only know for sure when we upgrade our production.

                      One thing to note - when analysing queries it was found that we need to use a mix of index types for best performance. Make sure your Oracle SQL client's NLS settings are correct (In line with DB & AR) as that can skew your analysis with tuning advisor.

                      Will follow your question as any feedback may help.

                      • 8. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                        Samo Puharic

                        Thnx Kobus Duminy for valuable feedback.

                         

                        For testing queries in SQL Devloper we execuded:

                        ALTER SESSION SET NLS_COMP=LINGUISTIC
                        ALTER SESSION SET NLS_SORT=BINARY_CI

                         

                        Is it enough, to get execution plan equivalent as from AR system ?

                         

                        How do you achieve to use a mix of index types ?!

                         

                        It is said to me that

                        alter session set nls_comp=LINGUISTIC;

                        would force using functional/linguistic indexes.

                         

                         

                        When putting Db-Case-Insensitive: T in ar.cfg we do not have any control after nls_comp parameter.

                        At least, that is how I understand it.

                         

                        Update:

                        Remedy - Server - Enabling case-insensitive searches with an AR System server 8.1 or 9.x with Oracle

                        While running in case-insensitive mode, Oracle does not use the regular indexes that the AR System server creates. Instead, Oracle uses functional indexes (also called linguistic indexes).  You can create a functional index based on a specific language or on a multilingual option.  This ensures that the indexed entries are sorted in the correct order for the specific language or language set.

                        1 of 1 people found this helpful
                        • 9. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                          Stefan Hall

                          A mix of index types is not possible, means is not used by the AR server.

                           

                          You can have several different types in the DB - regular, binary_ci, binary_ai - but the session parameter set determines which type is used.  Keep in mind that indexes take time to write, less is often more

                          1 of 1 people found this helpful
                          • 10. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                            Stefan Hall

                            No chance to use a mix of index types.

                             

                            For the test your two alter session statements.

                             

                            If you don't want to use binary_ci, you have to set an alter session statement at AR server level

                            • 11. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                              Kobus Duminy

                              Sounds like you nailed it on a session level, other place you can set it is under Tools -> Preferences -> Database -> NLS. Just use one of the later clients, one of the old clients was ignoring the NLS.

                              As for the other indexes mentioned - Shadow indexes on a DB level as it can't be generated by AR but is picked up by Oracle plans. To be used with care - as Stefan indicated - bad or too much indexes can cause degradation .

                              To see if they work you can use AWR or even direct query through AR with timings before & after.

                              One thing I forgot to mention - After upgrade we found EXEC DBMS_STATS.gather_schema_stats with around 15% useful, chat to your DBA on that one as there may be a housekeeping job for it already.

                              • 12. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                                Kobus Duminy

                                Samo Puharic, are you able to run the following through AR process itself?

                                SELECT DB.PARAMETER, DB.VALUE "DATABASE", I.VALUE "INSTANCE", S.VALUE "SESSION"

                                FROM   NLS_DATABASE_PARAMETERS DB, NLS_INSTANCE_PARAMETERS I, NLS_SESSION_PARAMETERS S

                                WHERE  DB.PARAMETER=I.PARAMETER(+) AND DB.PARAMETER=S.PARAMETER(+)

                                • 13. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                                  Samo Puharic

                                  Hot to run the following through AR process itself?

                                   

                                  SQL Developer 19.2.1.247 as ARADMIN

                                  env.var: NLS_LANG=CROATIAN_CROATIA.AL32UTF8

                                  ALTER SESSION SET NLS_COMP=LINGUISTIC;

                                  ALTER SESSION SET NLS_SORT=BINARY_CI;

                                   

                                  • 14. Re: Remedy slow with functional indexes in version 19.08 & 20.02
                                    Kobus Duminy

                                    Looks like DB NLS settings may be a problem. I would have expected following:

                                    NLS_COMP should be LINGUISTIC on a instance level

                                    NLS_SORT should be BINARY_CI on a instance level

                                    Looks like these was not set at all. You may need to review with DBA and apply - test if it helps in a non-prod environment.

                                    - I think it is documented in Prepare your oracle DB part of installtion.

                                     

                                    Note the highlighted ones below from one of our systems-

                                    1 2 Previous Next