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

Last updated on NOVEMBER 25, 2022

Applies to:

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=
 '[targetID#]' AND ApprovedAssets.assetid=PublishedAssets.assetid AND
 ApprovedAssets.targetid='[targetID#]' AND EXISTS (
 SELECT 'x'
 FROM ApprovedAssets t0
 WHERE PublishedAssets.assetid=t0.assetid AND t0.targetid='[targetID#]'
 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='[targetID#]' 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='[targetID#]' AND t2.tstate='A' AND
 t2.locked='F' AND PubKeyTable.assetid=t2.assetid AND PubKeyTable.targetid=
 '[targetID#]' )
 

Solution

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