Query taking over 30 hours to execute in SARM database (Doc ID 441874.1)

Last updated on OCTOBER 12, 2016

Applies to:

Oracle Communications ASAP - Version 4.6.5 to 4.6.5 [Release 4.6]
Oracle Solaris on SPARC (32-bit)
***Checked for relevance on 18-APR-2013***

Symptoms

Description

I received the following message our from database administrator regarding the ASAP SARM database.The following sentence is being reported at the oracle log: -----------------------------------------------------------------------------------------

Wed Apr 20 20:42:32 2005ORA-01555 caused by SQL statement below (Query Duration=96516 sec, SCN: 0x0000.4e3af49c): Wed Apr 20 20:42:32 2005 DELETE FROM TBL_SRQ_LOG WHERE SRQ_ID NOT IN (SELECT SRQ_ID FROM TBL_SRQ)

-----------------------------------------------------------------------------------------

Since operation (as it is) takes a long time (27 hrs) and either one or another (or both) are changing too much (beyond our undo-retention of 3hrs) operation is bound to fail every time it runs. I have a suggestion to improve this operation. Change DELETE statement as:delete from TBL_SRQ_LOG a WHERE not exists (SELECT null FROM TBL_SRQ b where b.SRQ_ID = a.SRQ_ID)

I ran query below (ran in less than 30 seconds) and looks like there only are 441 records out of 2754908 to delete. 1 select count(*) FROM TBL_SRQ_LOG a 2* WHERE not exists (SELECT null FROM TBL_SRQ b where b.SRQ_ID = a.SRQ_ID)SQL> / COUNT(*)

Impact

Causes a cron job to fail, query is taking too long to execute, impacting other part of ASAP processing.

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