My Oracle Support Banner

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

Last updated on FEBRUARY 22, 2021

Applies to:

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


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(*)
 (SELECT DISTINCT PublishedAssets.assetid AS assetid,
 PublishedAssets.assettype AS assettype, ApprovedAssets.tstate AS tstate
 FROM PubKeyTable,PublishedAssets,ApprovedAssets
 WHERE AND PubKeyTable.targetid=
 '[targetID#]' AND ApprovedAssets.assetid=PublishedAssets.assetid AND
 ApprovedAssets.targetid='[targetID#]' AND EXISTS (
 FROM ApprovedAssets t0
 WHERE PublishedAssets.assetid=t0.assetid AND t0.targetid='[targetID#]'
 AND (PublishedAssets.assetversion!=t0.assetversion OR
 PublishedAssets.assetdate<t0.assetdate)) AND EXISTS (
 FROM ApprovedAssets t1
 WHERE PubKeyTable.assetid=t1.assetid AND t1.targetid='[targetID#]' AND
 t1.tstate='A' AND t1.locked='F') UNION
 SELECT t2.assetid AS assetid, t2.assettype AS assettype, t2.tstate AS
 FROM PubKeyTable,ApprovedAssets t2
 WHERE newkey!='D' AND t2.targetid='[targetID#]' AND t2.tstate='A' AND
 t2.locked='F' AND PubKeyTable.assetid=t2.assetid AND PubKeyTable.targetid=
 '[targetID#]' )


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.