servicedesk application respoding slow while cpu usage is low about 5 or 10%, lot of free memory, no much activity observed on SDM server

Products

• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

servicedesk application respoding slow while cpu usage is low about 5 or 10%, lot of free memory, no much activity observed on SDM server

In the cloned environment, the ServiceDesk application is not responding fast, the cpu usage is low, we have lot of memory. Activity on the system is low.
Oracle is the database server.

Environment

ServiceDesk 12.9
ServiceDesk 14.1
ServiceDesk 17.1
Windows 2012

Resolution

Execute at the command prompt pdm_vdbinfo we would see these informations
.
Num Requests pending = 900
Actual num idle = 0 
..

-> This information show lot of queries are on pending, there is no free dbagent to process query. We could be in situation of bottleneck on the database.

1/ Verify on the local SDM server to see if we have many long queries.
Search "milliseconds" in the local NX_ROOT\log\stdlog.* , we would see the tables with long queries.

2/ If we do not have long query on the local SDM server, verify if we are in AA mode and the database is slow due to queries from other Application servers. Then search on the other application servers for long queries.

3/ Check if any index is missing on the slow table (not created due to error on cloning the mdb)
This query could be executed with your dba via sqlplus

set pagesize 0
set long 90000
set linesize 9000
set feedback off
set echo off 
spool mdbadmin_schema.sql 
connect mdbadmin/[email protected]_alias;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;
spool off;

This will generate the create index in mdbadmin_schema.sql 
Do the same for the mdb source and compare the create index from source and target mdb. Would help to identify missing index