Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..
1391 views

SM 9.34 P5 performance issues after upgrading to Oracle 12c

We are running Service Manager 9.34 P5 and attempting to upgrade our database from Oracle 11.2 to 12.2. After the upgrade to Oracle 12, we are experiencing performance issues. 

An example of a performance hit is upon logging in.  It went from taking between 2-4 seconds to 10-17 seconds. See the below examples from the log, where almost all of the time is spent on the database.


Oracle 12:  Base login Response: 10.874 -- RAD: 0.000  JS: 1.405 Log:0.000 Database: 9.469(09663)  LDAP: 0.000 (CPU 1.481)
Oracle 11:  Base login Response: 2.765 -- RAD: 0.000  JS: 0.702 Log:0.000 Database: 2.063(04296)  LDAP: 0.000 (CPU 0.968)


Could anyone offer any advice on how to determine what is causing the issue?

0 Likes
5 Replies
Highlighted
Knowledge Partner
Knowledge Partner

Was the upgrade on the same DB Server or did you change to a new DB server? 

With such difference I'd bet that your DB server is not in the same HPSM Servers network or DB has a serious lack of resource, the upgrade should not cause this.... The second line of investigation could also be to check if something changed in terms of collation or other oracle parameters... HPSM has not any specific requirement to run, the only one I can remember now is to set the NLS to Binary for better performance.. details on it you can find on help or installation guide. You probably also upgraded the HPSM server oracle client, so make sure you are using the same configuration otherwise conversion will occur and this cause delays..

To check the network you can ping DB Servers from HPSM server, the response should be constant and <=1ms...

If it's a shared database, check if other applications also noticed something new...
Also, you may want to enable a higher debug in order to identify the time of each query running during the login, this will help you to identify if this is due a specific a query or not..

Regards,
Breno Abreu

If you feel this was helpful please click the KUDOS! thumb below!
0 Likes
Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Thanks for the response.

It is on the same DB server, the database was only upgraded and made pluggable.

We have checked the oracle params and everything is in line with our case-insensitive setup. 

We did not upgrade the oracle client on the SM server because the versions are still compatible.

The pings always result in <1ms.

 

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

 

Interesting, so seems it's something related to Oracle alone...
I just googled "poor performance after upgrade to Oracle 12.2" and found several pages relating same issues, most of them related to OPTIMIZER parameters.. this can be a good lead to your DBA do some test with you. In parallel, I'd also open a support ticket asking for specific guidelines for Oracle 12.2 parameters for HPSM since there is this known issue......
Bellow some links I found particularly interesting.

http://blog.solvaria.com/performance-hit-after-migrating-to-oracle-12c

https://communities.ca.com/thread/241788872-oracle-12-performance-issues

I'm also migrating to 12c... probably next week. If you find the final solution please share with us and If face the same problem and find the final solution I'll post here.

Regards,
Breno Abreu

If you feel this was helpful please click the KUDOS! thumb below!
0 Likes
Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Did you ever got around to migrating to 12c?  If so, what was your experience?

We are still struggling with this. We recently tried enabling Automatic SQL Tuning Advisor, but have not seen much imporvement.

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

Yes, we started to use it on test environment and so far no issues.
There is a specific consideration about oracle client configuration when using Oracle higher then a specific version (don't have the doc now)... . did you follow it ?
Regards,
Breno Abreu

If you feel this was helpful please click the KUDOS! thumb below!
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.