Friday, November 25, 2011

OID CPU performence Issue

I've spent the last six months working on a major OAS upgrade project from one client going from 10.1.2.3 -> 11.1.1.5 OID and apparently we have hit know bug that only impacts our production environment. It would appear the emagent is issuing the following query against the infrastructure database:

SELECT
UDN.ATTRVALUE,
SUM(AT1.ATTRVAL),
SUM(AT2.ATTRVAL),
SUM(AT3.ATTRVAL),
SUM(AT4.ATTRVAL),
SUM(AT5.ATTRVAL),
SUM(AT6.ATTRVAL),
SUM(AT7.ATTRVAL),
SUM(AT8.ATTRVAL),
SUM(AT9.ATTRVAL),
SUM(AT10.ATTRVAL),
SUM(AT11.ATTRVAL),
SUM(AT12.ATTRVAL),
SUM(AT13.ATTRVAL),
SUM(AT14.ATTRVAL),
SUM(AT15.ATTRVAL),
SUM(AT16.ATTRVAL),
SUM(AT17.ATTRVAL),
SUM(AT18.ATTRVAL),
SUM(AT19.ATTRVAL),
SUM(AT20.ATTRVAL),
SUM(AT21.ATTRVAL),
SUM(AT22.ATTRVAL)
FROM ODS.P1_CT_ORCLUSERDN UDN,
ODS.P1_DS_ATTRSTORE AT1,
ODS.P1_DS_ATTRSTORE AT2,
ODS.P1_D

Which completely sucks up all CPU on the server and negatively impacting system response time. You will see this statement run for ~5 min, finish and drop off for another ~5min, and then repeat again under the same PID id. It appears this is know bug:

9078688 - SQLS IN OID'S METADATA XML FILE (SQL FETCHLETS) ARE NOT PERFORMANCE

That was suppose to be fixed in 11.1.1.5...but wasn't. If you are hitting this problem as a temporary work around you can shutdown the emagent with this command:

opmnctl stopproc ias-component=emagent

and life will go back to normal...if you consider not being able to use the administrative functionality of OID as "normal". Let's hope the DEV at Oracle will get this one fixed right on the NEXT release.

No comments:

Post a Comment