Frequently running SQL - PublishedAssets and ApprovedAssets (Doc ID 1606853.1)

Last updated on NOVEMBER 03, 2016

Applies to:

Oracle WebCenter Sites - Version 7.6.0 and later
Information 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