Stored Procedure SSP_select_waiting_events are Being Called many Times in a Particular Day (Doc ID 1494324.1)

Last updated on MARCH 23, 2019

Applies to:

Symptoms

Database side can see SSP_select_waiting_events being called many times in a particular Day

During that time, DBA's found out that the following query was being executed more frequently than at other times. On a particular day, the same query was executed around 500 times, while the same query has never been executed on the next day, which suggested it could have been the reason for the Slowness.

This query was running from SARM module.

SELECT SRP_ID , EVENT_TYPE , WO_ID , EVENT_UNID , ESTIMATE , MISC , REV_FLAG , EXCEPTIONS , MACH_CLLI , HOST_CLLI , REASON , CSDL_SEQ_NO , CSDL_ID , TIMEOUT_STATUS , QUEUE_TM , XACTION_TYPE , OLD_WO_STAT , NEW_WO_STAT , STATUS , EXTSYS_ID , EVENT_DTS , ROLLBACK_EXCEPTIONS FROM TBL_WO_EVENT_QUEUE WHERE SRP_ID = :B2 AND EVENT_UNID > :B1 AND EVENT_STATUS = 301 ORDER BY EVENT_UNID

Checked the database and following query is executed from Stored Procedure "SSP_SELECT_WAITING_EVENTS" .

Now we need to know in what cases will the SARM execute this Stored Procedure and why multiple times, even when the SARM was not recycled?

Changes

Cause

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