Service Desk is taking a long to start up and allow users to log in

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

After moving the MDB to another ORACLE infrastructure, CA Service Desk started taking a long time to start up and allow users to log in. When available, after around 20 minutes, users can log in but still noticing slowness to navigate through the application.

It happens because the kt_daemon process is not completely available, and this process takes action when users log into CA Service Desk.

While kt_daemon is not completely available, users are not able to log into SDM.

It is possible to notice many queries (using the same agent) to the database:

04/28 09:58:04.17 TESTING     sqlagt:select18      1792 MILESTONE    orclclass.c           1401 The following statement took 47 milliseconds: SELECT O_INDEXES.ALLOW_QA , O_INDEXES.AUTHOR_ID , O_INDEXES.CAPTION , O_INDEXES.CR_CAT , O_INDEXES.DESCRIPTION , O_INDEXES.DOC_TEMPLATE , O_INDEXES.HAS_CHILDREN , O_INDEXES.HAS_DOCS , O_INDEXES.ISS_CAT , O_INDEXES.KEYWORDS , O_INDEXES.OWNER_ID , O_INDEXES.PARENT_ID , O_INDEXES.PERMISSION_INDEX_ID , O_INDEXES.READ_PGROUP , O_INDEXES.RELATIONAL_ID , O_INDEXES.SUBJECT_EXPERT_ID , O_INDEXES.WF_TEMPLATE , O_INDEXES.WRITE_PGROUP , O_INDEXES.last_mod_by , O_INDEXES.LAST_MOD_DT , O_INDEXES.tenant FROM O_INDEXES WHERE O_INDEXES.ID = 407671

In normal conditions, the entire chain goes much faster and using multiple agents.

Message when trying to log into CA Service Desk:

04/27 17:08:25.85 TESTING     kt_daemon            6988 SIGNIFICANT  KTDaemon.c            1479 Method pgroups_value ignored until kt_daemon initialization is complete.  Table Cache Done: 0 , P Groups Cache Done: 1

Environment

CA Service Desk 17.3 and higher

Oracle database

Cause

On every SELECT SQL statement, CA Service Desk needs to ask Oracle for the list of columns and the type of each column. For that, there is an OCI call "OCIStmtExecute" passing "OCI_DESCRIBE_ONLY" for Oracle to describe the SQL statement.

Because of performance issues in Oracle, a long time is spent asking Oracle for the SQL Statement information.

Resolution

Engage the database team and set the following parameters in Oracle:

From (for example):

cursor_sharing: EXACT
open_cursors: 300

To:

cursor_sharing: SIMILAR
open_cursors: 1500

Additional Information

CA Service Desk Manager (SDM) performance is poor when using Oracle Database Management System (DBMS) to host the MDB

Its also possible that this issue was caused by Windows Update patches

Reference the first occurrence of the issue and if Windows Update patches were installed around the same time

Try rolling back the Windows patches and see if the issue is still occurring