Infocenter UI (With IMDB on SQL Server, Not Oracle DB) Became Not-Responding/Frozen Under Heavy Load With Thread Stuck at "UPDATE CONTENTMETRICS" SQL Statement (Doc ID 2066806.1)

Last updated on OCTOBER 15, 2015

Applies to:

Oracle Knowledge - Version 8.4.2.2 and later
Information in this document applies to any platform.

Symptoms

Infocenter UI (With IMDB on SQL Server, Not Oracle DB) became not-responding/frozen under heavy load with thread stuck at "UPDATE CONTENTMETRICS" SQL statement.

And it is co-relating/pending on the stuck/long-running SQL query at the back-end SQL Server DB:

SELECT DISTINCT TOP 1000 d.COUNT, a.*, c.REFERENCEKEY as CHANNEL
FROM CONTENTTEXTPUB a, CONTENT b, CONTENTCHANNEL c, CONTENTMETRICS d , PUBSITE ps, SITE i, PUBSECURITYTAG f
WHERE a.CONTENTID = b.RECORDID
AND b.CONTENTCHANNELID=c.RECORDID
AND b.CONTENTCHANNELID IN ( @P0 )
AND b.RECORDID=d.CONTENTID AND (d.ISDEFAULT='Y' )
AND b.ALIASID is NULL AND a.LOCALEID = @P1
AND (a.DISPLAYSTARTDATE <= @P2 AND a.DISPLAYENDDATE >= @P3 )
AND a.RECORDID = ps.PUBID
and ps.SITEID = i.RECORDID
AND ( i.OBJECTID= @P4 OR i.OBJECTID LIKE @P5 OR i.OBJECTID= @P6 OR i.OBJECTID LIKE @P7 OR i.OBJECTID= @P8 OR i.OBJECTID LIKE @P9 OR i.OBJECTID= @P10 OR i.OBJECTID LIKE @P11 OR i.OBJECTID= @P12 OR i.OBJECTID LIKE @P13 OR i.OBJECTID= @P14 OR i.OBJECTID LIKE @P15 OR i.OBJECTID= @P16 OR i.OBJECTID LIKE @P17 OR i.OBJECTID= @P18 OR i.OBJECTID LIKE @P19 OR i.OBJECTID= @P20 OR i.OBJECTID LIKE @P21 OR i.OBJECTID= @P22 OR i.OBJECTID LIKE @P23 OR i.OBJECTID= @P24 OR i.OBJECTID LIKE @P25 OR i.OBJECTID= @P26 OR i.OBJECTID LIKE @P27 OR i.OBJECTID= @P28 OR i.OBJECTID LIKE @P29 )
AND f.PUBID = a.RECORDID
AND (f.TAGID IN ( @P30 , @P31 , @P32 , @P33 , @P34 ))
AND (a.DISPLAYSTARTDATE >= @P35 OR a.DISPLAYSTARTDATE IS NULL )
ORDER BY a.DISPLAYSTARTDATE desc

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms