Applies to:

Oracle WebCenter Sites - Version 7.6.0 and laterInformation in this document applies to any platform.

Goal

We have seen a SQL running all the time even when system is not getting used. It takes a long time to run, and runs very often. What is this for and is there a way to reduce its frequency or tune it?

select count(*) from (SELECT DISTINCT PublishedAssets.assetid AS assetid, PublishedAssets.assettype AS assettype, ApprovedAssets.tstate AS tstate FROM PubKeyTable,PublishedAssets,ApprovedAssets WHERE PubKeyTable.id=PublishedAssets.pubkeyid AND PubKeyTable.targetid= '1277985490077' AND ApprovedAssets.assetid=PublishedAssets.assetid AND ApprovedAssets.targetid='1277985490077' AND EXISTS ( SELECT 'x' FROM ApprovedAssets t0 WHERE PublishedAssets.assetid=t0.assetid AND t0.targetid='1277985490077' AND (PublishedAssets.assetversion!=t0.assetversion OR PublishedAssets.assetdate<t0.assetdate)) AND EXISTS ( SELECT 'x' FROM ApprovedAssets t1 WHERE PubKeyTable.assetid=t1.assetid AND t1.targetid='1277985490077' AND t1.tstate='A' AND t1.locked='F') UNION SELECT t2.assetid AS assetid, t2.assettype AS assettype, t2.tstate AS tstate FROM PubKeyTable,ApprovedAssets t2 WHERE newkey!='D' AND t2.targetid='1277985490077' AND t2.tstate='A' AND t2.locked='F' AND PubKeyTable.assetid=t2.assetid AND PubKeyTable.targetid= '1277985490077' )

Solution

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