This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.
PRODUCT:
Remedy with Smart IT
COMPONENT:
Remedy with Smart IT
APPLIES TO:
Slowness, GLEWF calls, Dashboard Console, SMT:WorklogView This also applies to Smart IT 18.08
QUESTION:
Environment Details:
OS: Windows Server 2016 Standard.
DB: MSSQL 2016
ARS, ITSM, CMDB, RKM, SRM, SLM: 1805 patch 5
SmartIT: {"buildNumber":"78","buildTime":1537339920000,"smartITVersion":"18.05.00.005","smartITPreviousVersion":null,"changelist":"4c2f60ee264b3941e4c04deebe99a93c3fa31b0d","ucBuildNumber":"70"}
ANSWER:
Solution when culprit is SMT:Social_WorklogView:
-
Suggest the customer to apply indexes as per doc https://docs.bmc.com/docs/display/smartit1805/Adding+database+indexes+for+performance+improvement
-
Make sure you are on latest cumulative hotfix patch for Smart IT 18.05/18.08 for more on this review https://bmcsites.force.com/casemgmt/sc_KnowledgeArticle?sfdcid=000163544
Analyzing the API +FILTER+SQL logs we realized that there is lot of slowness reported for an GLEWF API call on SMT:Social_WorklogView and it takes around 2 min.
Based on the observation, it looks like the query is running on T2118, which is SMT:Social_WorkLogView. This view refers to the database view U_SmartIT_Social_Worklogs. This query is generated when the user is accessing the Smart IT dashboard.
(LA screencapture)
Query:
==================================================
WITH AR_SQL_Alias$1 AS (SELECT T2118.C1, T2118.C1000000182, T2118.C179, T2118.C1000005261, T2118.C2, T2118.C1000000151, T2118.C1000000159, T2118.C1000000761, T2118.C1000000218, T2118.C1000000655, T2118.C1000001563, T2118.C10001959, T2118.C304309590, T2118.C304309600, T2118.C302308591, ROW_NUMBER() OVER (ORDER BY T2118.C1000005261 DESC, T2118.C1 ASC) AS 'AR_RowNumber_Alias$1' FROM T2118 WHERE (((T2118.C1000000182 IN (SELECT FVALUE FROM #AR704NVARCHAR415)) AND (T2118.C1000005261 > ?)) OR ((T2118.C1000001563 = ?) AND (T2118.C1000000182 = ?) AND (T2118.C1000005261 > ?)))) SELECT AR_SQL_Alias$1.C1, AR_SQL_Alias$1.C1000000182, AR_SQL_Alias$1.C179, AR_SQL_Alias$1.C1000005261, AR_SQL_Alias$1.C2, AR_SQL_Alias$1.C1000000151, AR_SQL_Alias$1.C1000000159, AR_SQL_Alias$1.C1000000761, AR_SQL_Alias$1.C1000000218, AR_SQL_Alias$1.C1000000655, AR_SQL_Alias$1.C1000001563, AR_SQL_Alias$1.C10001959, AR_SQL_Alias$1.C304309590, AR_SQL_Alias$1.C304309600, AR_SQL_Alias$1.C302308591 FROM AR_SQL_Alias$1 WHERE (AR_SQL_Alias$1.AR_RowNumber_Alias$1 BETWEEN ? AND ?)
==================================================
Server-Temp-Table-Threshold: 1000 and then proceed to restart the AR Server
(with this parameter be aware of the below defect/considerations)
USE CASE 2 (CPU performance AR and at DB side it is also high)
If above things have been applied and there is still some performance issues, you may want to double check the SQL logs you may notice that the SQL query has a lot of 'OR' operator as below snippet:
SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM
(SELECT T7801.C1, T7801.C1000000182, T7801.C179, T7801.C1000005261, T7801.C2, T7801.C1000000151, T7801.C1000000159, T7801.C1000000761, T7801.C1000000218, T7801.C1000000655, T7801.C1000001563, T7801.C10001959,
T7801.C304309590, T7801.C304309600, T7801.C302308591
FROM T7801 WHERE (((T7801.C1000000182 IN (SELECT FVALUE FROM ARFTS707201276_401_0)) OR ((T7801.C1000001563 = :1 ) AND (T7801.C1000000182 = :2 )))
AND ((T7801.C2 = :3 ) OR exists ( select /*+ NO_EXPAND */ 1 from dual where ((T7801.C112 LIKE :4 ) OR (T7801.C112 LIKE :5 ) OR (T7801.C112 LIKE :6 ) OR (T7801.C112 LIKE :7 ) OR (T7801.C112 LIKE :8 ) OR
(T7801.C112 LIKE :9 ) OR (T7801.C112 LIKE :10 ) OR (T7801.C112 LIKE :11 ) OR (T7801.C112 LIKE :12 ) OR (T7801.C112 LIKE :13 ) OR (T7801.C112 LIKE :14 ) OR (T7801.C112 LIKE :15 ) OR (T7801.C112 LIKE :16 ) OR
(T7801.C112 LIKE :17 ) OR (T7801.C112 LIKE :18 ) OR (T7801.C112 LIKE :19 ) OR (T7801.C112 L IKE :20 ) OR (T7801.C112 LIKE :21 ) OR (T7801.C112 LIKE :22 ) OR (T7801.C112 LIKE :23 ) OR (T7801.C112 LIKE :24 ) OR
(T7801.C112 LIKE :25 ) OR (T7801.C112 LIKE :26 ) OR (T7801.C112 LIKE :27 ) OR (T7801.C112 LIKE :28 ) OR (T7801.C112 LIKE :29 ) OR (T7801.C112 LIKE :30 ) OR (T7801.C112 LIKE :31 ) OR (T7801.C112 LIKE :32 ) OR
(T7801.C112 LIKE :33 ) OR (T7801.C112 LIKE :34 ) OR (T7801.C112 LIKE :35 ) OR (T7801.C112 LIKE :36 ) OR (T7801.C112 LIKE :37 ) OR (T7801.C112 LIKE :38 ) OR (T7801.C112 LIKE :39 ) OR (T7801.C112 LIKE :40 ) OR
(T7801.C112 LIKE :41 ) OR (T7801.C112 LIKE :42 ) OR (T7801.C112 LIKE :43 ) OR (T7801.C60900 LIKE :44 ) OR (T7801.C60900 LIKE :45 ) OR (T7801.C60900 LIKE :46 ) OR (T7801.C60900 LIKE :47 ) OR (T7801.C60900 LIKE :48 ) OR
(T7801.C60900 LIKE :49 ) OR (T7801.C60900 LIKE :50 ) OR (T7801.C60900 LIKE :51 ) OR (T7801.C60900 LIKE :52 ) OR (T7801.C60900 LIKE :53 ) OR (T7801.C60900 LIKE :54 ) OR (T7801.C60900 LIKE :55 ) OR (T7801.C60900 LIKE :56 ) OR
(T7801.C60900 LIKE :57 ) OR (T7801.C60900 LIKE :58 ) OR (T7801.C60900 LIKE :59 ) OR (T7801.C60900 LIKE :60 ) OR (T7801.C60900 LIKE :61 ) OR (T7801.C60900 LIKE :62 ) OR (T7801.C60900 LIKE :63 ) OR (T7801.C60900 LIKE :64 ) OR
(T7801.C60900 LIKE :65 ) OR (T7801.C60900 LIKE :66 ) OR (T7801.C60900 LIKE :67 ) OR (T7801.C60900 LIKE :68 ) OR (T7801.C60900 LIKE :69 ) OR (T7801.C60900 LIKE :70 ) OR (T7801.C60900 LIKE :71 ) OR (T7801.C60900 LIKE :72 ) OR......
On this use case we added Disable-New-RLS-Implementation F
(on the docs it says it defaults it to T)
This increased performance dramatically and reduced CPU performance
Note: Disable-New-RLS-Implementation has impact only on the rls query. If true, the query is made using or and when false, the query uses regex for dynamic group permissions.
**When the culprit is the SMT:Social:FollowConfig form, please review this article 000170189
(with this parameter be aware of the below defect/considerations)
| SW00551614 | Value of Server-Temp-Table-Threshold should be less than 2100 for SQL Server and less than 1000 for ORACLE. if you set something higher than above values then you will get below errors. SQL Server: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL ; SQL state S0001; error code 8003; The incoming request has too many parameters. The server supports a maximum of 2100 parameters. ORACLE: com.bmc.arsys.domain.etc.ARException: ERROR (552): The SQL database operation failed.; ORA-01795: maximum number of expressions in a list is 1000 |
USE CASE 2 (CPU performance AR and at DB side it is also high)
If above things have been applied and there is still some performance issues, you may want to double check the SQL logs you may notice that the SQL query has a lot of 'OR' operator as below snippet:
SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM
(SELECT T7801.C1, T7801.C1000000182, T7801.C179, T7801.C1000005261, T7801.C2, T7801.C1000000151, T7801.C1000000159, T7801.C1000000761, T7801.C1000000218, T7801.C1000000655, T7801.C1000001563, T7801.C10001959,
T7801.C304309590, T7801.C304309600, T7801.C302308591
FROM T7801 WHERE (((T7801.C1000000182 IN (SELECT FVALUE FROM ARFTS707201276_401_0)) OR ((T7801.C1000001563 = :1 ) AND (T7801.C1000000182 = :2 )))
AND ((T7801.C2 = :3 ) OR exists ( select /*+ NO_EXPAND */ 1 from dual where ((T7801.C112 LIKE :4 ) OR (T7801.C112 LIKE :5 ) OR (T7801.C112 LIKE :6 ) OR (T7801.C112 LIKE :7 ) OR (T7801.C112 LIKE :8 ) OR
(T7801.C112 LIKE :9 ) OR (T7801.C112 LIKE :10 ) OR (T7801.C112 LIKE :11 ) OR (T7801.C112 LIKE :12 ) OR (T7801.C112 LIKE :13 ) OR (T7801.C112 LIKE :14 ) OR (T7801.C112 LIKE :15 ) OR (T7801.C112 LIKE :16 ) OR
(T7801.C112 LIKE :17 ) OR (T7801.C112 LIKE :18 ) OR (T7801.C112 LIKE :19 ) OR (T7801.C112 L IKE :20 ) OR (T7801.C112 LIKE :21 ) OR (T7801.C112 LIKE :22 ) OR (T7801.C112 LIKE :23 ) OR (T7801.C112 LIKE :24 ) OR
(T7801.C112 LIKE :25 ) OR (T7801.C112 LIKE :26 ) OR (T7801.C112 LIKE :27 ) OR (T7801.C112 LIKE :28 ) OR (T7801.C112 LIKE :29 ) OR (T7801.C112 LIKE :30 ) OR (T7801.C112 LIKE :31 ) OR (T7801.C112 LIKE :32 ) OR
(T7801.C112 LIKE :33 ) OR (T7801.C112 LIKE :34 ) OR (T7801.C112 LIKE :35 ) OR (T7801.C112 LIKE :36 ) OR (T7801.C112 LIKE :37 ) OR (T7801.C112 LIKE :38 ) OR (T7801.C112 LIKE :39 ) OR (T7801.C112 LIKE :40 ) OR
(T7801.C112 LIKE :41 ) OR (T7801.C112 LIKE :42 ) OR (T7801.C112 LIKE :43 ) OR (T7801.C60900 LIKE :44 ) OR (T7801.C60900 LIKE :45 ) OR (T7801.C60900 LIKE :46 ) OR (T7801.C60900 LIKE :47 ) OR (T7801.C60900 LIKE :48 ) OR
(T7801.C60900 LIKE :49 ) OR (T7801.C60900 LIKE :50 ) OR (T7801.C60900 LIKE :51 ) OR (T7801.C60900 LIKE :52 ) OR (T7801.C60900 LIKE :53 ) OR (T7801.C60900 LIKE :54 ) OR (T7801.C60900 LIKE :55 ) OR (T7801.C60900 LIKE :56 ) OR
(T7801.C60900 LIKE :57 ) OR (T7801.C60900 LIKE :58 ) OR (T7801.C60900 LIKE :59 ) OR (T7801.C60900 LIKE :60 ) OR (T7801.C60900 LIKE :61 ) OR (T7801.C60900 LIKE :62 ) OR (T7801.C60900 LIKE :63 ) OR (T7801.C60900 LIKE :64 ) OR
(T7801.C60900 LIKE :65 ) OR (T7801.C60900 LIKE :66 ) OR (T7801.C60900 LIKE :67 ) OR (T7801.C60900 LIKE :68 ) OR (T7801.C60900 LIKE :69 ) OR (T7801.C60900 LIKE :70 ) OR (T7801.C60900 LIKE :71 ) OR (T7801.C60900 LIKE :72 ) OR......
On this use case we added Disable-New-RLS-Implementation F
(on the docs it says it defaults it to T)
This increased performance dramatically and reduced CPU performance
Note: Disable-New-RLS-Implementation has impact only on the rls query. If true, the query is made using or and when false, the query uses regex for dynamic group permissions.
**When the culprit is the SMT:Social:FollowConfig form, please review this article 000170189
Article Number:
000163021
Article Type:
Add the below configuration parameter in the Centralized Configuration Form for AR Server: com.bmc.arsys.server.shared. This parameter must be added in every AR Server (facing/backend)