LSW: How to Collect Information to Troubleshoot Oracle Advanced Queuing Performance Event (Doc ID 2693253.1)

Last updated on JULY 13, 2023

Applies to:

Goal

During a performance issue related to jobs execution, the following symptoms were observed:

• Install and execution jobs remain in 'Executing' status for a long time and they taking longer than usual to complete.
• New submitted jobs remain in 'Pending' status and they are not progressing.
• There are no messages triggered in OWB logs about these long running jobs.
• The database alert logs were including "Global Enqueue Services Deadlock detected" messages.
• Job queue restart did not have any impact.
• De-queuing (from CDR_JOB_PROCESSING_QTB table) is extremely slow. A large number of records were accumulated in CDR_JOB_PROCESSING_QTB table. Impacted queue name: CDR_JOB_PROCESSING_Q:
  
  

LSH job processing queue CDR_JOB_PROCESSING_Q (listed under CDR_JOB_PROCESSING_QTB table) depends on the DBMS_AQ database package behind scenes.

If the de-queuing is hitting performance issues, then perform the steps listed below for collecting the information required by RDBMS AQ (Advanced Queuing) Team for their investigation.

Solution

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