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.

Document ID : KB000004846
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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