My Oracle Support Banner

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

Last updated on JUNE 11, 2019

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.

Changes

 

Cause

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
Symptoms
 Description
 Impact
Changes
Cause
Solution
 Resolution

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