After upgrading Oracle to 12c, the database is experiencing high CPU load and Service Desk Manager is experiencing Delayed Server Responses due to slow Oracle DB.

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

After upgrading to Oracle 12c the database server has high CPU usage.

Environment

Service Desk Manager running on Oracle 12c

Cause

There is a new feature in Oracle 12c called Adaptive Query Optimization that will in most cases make the Oracle database perform better and is enabled by default. However due to the high number of small queries that have be parsed by this new feature it ends up adding a lot of overhead causing high CPU usage on the Oracle database server.

Resolution

To disable the feature run the following:
alter system set optimizer_adaptive_features=FALSE scope = both;
alter system set optimizer_adaptive_reporting_only=TRUE scope = both;

Additional Information

Adaptive Query Optimization

 

The information in this article has been included in our product documentation. You can find further details here:

https://docops.ca.com/ca-service-management/17-0/en/troubleshooting/troubleshooting-ca-service-desk-manager/delayed-ca-sdm-server-response-after-upgrading-to-oracle-12c