4 Replies Latest reply: Feb 2, 2013 1:36 PM by Hussein Sawwan-Oracle RSS

    wf_notification_out and wf_deferred

    937828
      Hi,
      We have EBS 11.5.10 with 10g database and I see below jobs are being executed very frequently and causing I/O issues on DB node. Any idea how we can control these?

      /* OracleOEM */
      SELECT count(*) FROM APPS.wf_notification_out WHERE state = 0

      /* OracleOEM */
      SELECT count(*) FROM APPS.wf_deferred WHERE state = 0

      Thanks in advance.
        • 1. Re: wf_notification_out and wf_deferred
          Hussein Sawwan-Oracle
          934825 wrote:
          Hi,
          We have EBS 11.5.10 with 10g database and I see below jobs are being executed very frequently and causing I/O issues on DB node. Any idea how we can control these?

          /* OracleOEM */
          SELECT count(*) FROM APPS.wf_notification_out WHERE state = 0

          /* OracleOEM */
          SELECT count(*) FROM APPS.wf_deferred WHERE state = 0

          Thanks in advance.
          Did you have any performance issues before? If not, any changes been done since then?

          Do you have the statistics collected up to date?

          Please see these docs and make sure you have all the patches applied.

          Performance Issues Caused by WF_EVENT_OJMSTEXT_QH.DEQUEUE Procedure [ID 735812.1]
          Performance Problems with Notifications When the Mailer has more Notifications to Process [ID 1392647.1]
          Troubleshooting WF_DEFERRED Agent Listeners Performance [ID 468650.1]
          Low Performance Processing Messages in WF_DEFERRED Queue [ID 334348.1]
          CPU Intensive Querries Due To FTS on WF_DEFERRED [ID 463242.1]
          Number Of Rows In WF_DEFERRED Table Is Reducing Very Slowly [ID 412856.1]
          bde_wf_deferred.sql - For Analyzing The WF_DEFERRED Queue [ID 1378953.1]

          Please also see:

          Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]
          FAQ: How to Use AWR reports to Diagnose Database Performance Issues [ID 1359094.1]
          How to Tell if the IO of the Database is Slow [ID 1275596.1

          Thanks,
          Hussein                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
          • 2. Re: wf_notification_out and wf_deferred
            EBSDBA
            Hi,

            In addition to Hussein's reply

            What's coming to mind is how frequently the program "Workflow background process" is scheduled to run and with what parameters.

            Also check if you have any other workflow programs scheduled

            You may see
            Performance Degradation when the Workflow Background Process is Running [ID 743338.1]

            Also see point 7 in
            A Guide For Troubleshooting Workflow Notification Emails - Inbound and Outbound [ID 831982.1]

            Thanks
            • 3. Re: wf_notification_out and wf_deferred
              937828
              Thanks Hussein and EBSDBA.
              How do we check these jobs scheduling? Forgot to mention that these jobs are being run as DBSNMP user.

              Edited by: 934825 on Feb 2, 2013 11:29 AM
              • 4. Re: wf_notification_out and wf_deferred
                Hussein Sawwan-Oracle
                Scheduled Concurrent Requests
                https://forums.oracle.com/forums/search.jspa?threadID=&q=Scheduled+AND+Concurrent+AND+Query&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

                Recommended Schedule For Workflow Background Process For Order Management [ID 971925.1]

                Did you check the volume of the queues as per [ID 831982.1] referenced by EBSDBA?

                Thanks,
                Hussein