Performance Issue With High CPU Usage After Upgrade to 12c RDBMS

Asset ID:	1-72-2139151.1
Update Date:	2016-05-31
Keywords:

Solution Type Problem Resolution Sure

Solution 2139151.1 : Performance Issue With High CPU Usage After Upgrade to 12c RDBMS

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Exadata X5-2 Full Rack - Version All Versions to All Versions [Release All Releases]
Information in this document applies to any platform.

Symptoms

Performance issue after Database upgraded to 12c.
Customer upgraded from 11g to 12c.

All the queries are taking multifold time after upgrade, compared to previous version. No change in the underlying hardware or any other parameters.

AWR report Analysis:

Increased DB CPU (For example, in a customer case it is 5,374sec to 20,705sec for 1 hour duration)
All other waits are fairly similar.

Number of parses (Hard and Soft) are similar between the 2 versions.

Changes

RDBMS upgrade to 12.1.0.2.

Cause

Looking at the application sessions running the queries, they are consuming high CPU at OS level.

Taking pstack at OS level shows this:

[root@sessexa2db04 ~]# pstack 88914
#0 0x000000000cd00c70 in kksllbp ()
#1 0x000000000ccc8a1a in kkfdtParallel ()
#2 0x000000000ccc32ac in kkfdiPaPrm ()
#3 0x000000000ccc30cc in kkfdPaPrm ()
#4 0x000000000cc17173 in qksfroGetPara ()
#5 0x000000000cb8f26a in kkoppar ()
#6 0x0000000001736ce4 in kkebmtac ()
#7 0x0000000001692cbe in kkombn ()
#8 0x000000000cb6c37c in kkobmp ()
#9 0x000000000cb5b2c1 in kkotap ()
#10 0x000000000167530b in kkoiqb ()
#11 0x000000000166d154 in kkooqb ()
#12 0x0000000001654321 in kkoqbc ()
#13 0x00000000029ea7f6 in apakkoqb ()
#14 0x00000000029ea388 in apaqbdDescendents ()
#15 0x00000000029ea686 in apaqbdDescendents ()
#16 0x00000000029ea686 in apaqbdDescendents ()
#17 0x00000000029ea4cd in apaqbdDescendents ()
#18 0x00000000029e9b47 in apadrv ()
#19 0x00000000030f472a in opitca ()
#20 0x00000000026f0c96 in kksSetBindType ()
#21 0x000000000ccf645c in kksfbc ()
#22 0x000000000cc96a0c in opiexe ()
#23 0x00000000021ce5b9 in kpoal8 ()

[root@sessexa2db04 ~]# pstack 2334
#0 0x000000000cd00c7d in kksllbp ()
#1 0x000000000ccc8a1a in kkfdtParallel ()
#2 0x000000000ccc32ac in kkfdiPaPrm ()
#3 0x000000000ccc30cc in kkfdPaPrm ()
#4 0x000000000cc17173 in qksfroGetPara ()
#5 0x000000000cb8f26a in kkoppar ()
#6 0x0000000001736ce4 in kkebmtac ()

[root@sessexa2db04 ~]# pstack 62874
#0 0x000000000cd00c59 in kksllbp ()
#1 0x000000000ccc8a1a in kkfdtParallel ()
#2 0x000000000ccc32ac in kkfdiPaPrm ()
#3 0x000000000ccc30cc in kkfdPaPrm ()
#4 0x000000000cc17173 in qksfroGetPara ()
#5 0x000000000cb8f26a in kkoppar ()
#6 0x0000000001736ce4 in kkebmtac ()

kkfdtParallel() invoked kksllbp() for every session.

SQL> sho parameter cursor

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing string
FORCE

This is caused by unpublished Bug 21632821 - CURSOR SHARING=FORCE PERFORMANCE DEGRADATION IN 12C.

Solution

Fix for bug 21632821 is included in 12.1.0.2.160119DBBP.

For lower versions, apply one-off patch.

Potential workaround would be to change the CURSOR_SHARING setting to EXACT. This may lead to a bad performance of other queries. So, this needs to be tested.

References

<BUG:21632821> - CURSOR SHARING=FORCE PERFORMANCE DEGRADATION IN 12C

Attachments

This solution has no attachment