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
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.
CA Service Desk 17.3 and higher
Engage the database team and set the following parameters in Oracle:
From (for example):
cursor_sharing: EXACT open_cursors: 300
cursor_sharing: SIMILAR open_cursors: 1500
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