Question & Answer
Question
Why does the application fire additional YFS_INBOX queries in 7.5SP1 and newer versions for getAlertStatisticsForUser API call?
Answer
Issue Symptoms:
Observation of constant time-gaps in SCA 7.x and newer versions with getAlertStatisticsForUser API invocation. No information available in the logs even after enabling VERBOSE level tracing.
Issue Diagnosis:
The application fires the following queries in most of its 7.x versions, and the latest versions including MCF 8.x, for getAlertStatisticsForUser API.
User assigned to a queue:
1. SELECT /* YANTRA */ COUNT (*), QUEUE_KEY FROM YFS_INBOX WHERE (((QUEUE_KEY = 'DEFAULT') AND ACTIVE_FLAG='Y')) GROUP BY QUEUE_KEY;
2. SELECT /* YANTRA */ COUNT (*), QUEUE_KEY FROM YFS_INBOX WHERE (((QUEUE_KEY = 'DEFAULT') AND ACTIVE_FLAG='Y'))) AND ASSIGNED_TO_USER_KEY='sUserKey' GROUP BY QUEUE_KEY;
User not assigned to a queue:
1. SELECT /* YANTRA */ COUNT (*), QUEUE_KEY FROM YFS_INBOX WHERE ((ACTIVE_FLAG='Y')) GROUP BY QUEUE_KEY;
2. SELECT /* YANTRA */ COUNT (*), QUEUE_KEY FROM YFS_INBOX WHERE ((ACTIVE_FLAG='Y')) AND ASSIGNED_TO_USER_KEY='' GROUP BY QUEUE_KEY;
The logs in 7.5SP1 or earlier do not show these queries, as database classes do not fire these queries. Hence, even with a VERBOSE level of trace enabled, the logs do not show them.
If getAlertStatisticsForUser API affects performance and there is no pointed evidence to the cause of slowness, then the queries above may be a reason.
These queries could affect performance as seen on many occasions when one does not run TASK and INBOX Purges as required.
Cause for the slowness:
The queries have GROUP BY clause. In Oracle, this can result in the query failing to use an index, or the index used may be inefficient. If there are a huge number of records in the YFS_INBOX table, then the system takes a long to execute these queries, causing slowness.
Issue Resolution:
While troubleshooting API slowness issues, and to identify the root cause, knowing these queries up-front will help one understand the problem and take necessary action.
INBOX purge is the solution suggested for the above scenario.
[{"Product":{"code":"SS6PEW","label":"IBM Sterling Order Management"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":"Data management - Purge","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SS6PEW","label":"IBM Sterling Order Management"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":" Data management - house keeping","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
Historical Number
FAQ3650
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21517693