3 Replies Latest reply: Sep 23, 2010 4:13 AM by user13312864 RSS

    Best way to execute / monitor long running custom conc request with slaves

    Nigel C.L. Thomas
      (11.5.10 on Solaris 10, running on 9iR2).

      I am planning to take an existing custom PL/SQL program and rework it to run with a number of parallel slave processes. If this was a stand-alone PL/SQL I would use DBMS_JOB to launch the slaves; I understand that with EBS I should use FND_REQUEST.SUBMIT_REQUEST and FND_CONCURRENT.WAIT_FOR_REQUEST.

      Should I define an additional concurrent executable and program for the slaves, or should I reuse the same definition as the original master (and have the PL/SQL procedure know from the parameters supplied whether it is the master or one of the slaves)?

      I'm also interested in monitoring. Again, in a regular app I would use DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS to get information back to a DBA via V$SESSION_LONGOPS. Is there any roughly equivalent supported mechanism in Apps to feed back progress - eg percent complete - of the master and (several) concurrent request slaves to the user who initiated the process? I want more than simply the regular "phase" (PENDING, RUNNING, COMPLETE) and "status" (eg NORMAL, WAITING, TERMINATED etc) that could be got from FND_REQUEST.GET_STATUS.

      Thanks in advance

      Nigel
        • 1. Re: Best way to execute / monitor long running custom conc request with slaves
          Hussein Sawwan-Oracle
          Hi,

          For the monitoring part, please see the following documents.

          Note: 187504.1 - bde_request.sql - Process and Session info for one Concurrent Request (11.5)
          Note: 735119.1 - How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running.
          Note: 97810.1 - Scripts for Finding OS and ORACLE Information for Given Concurrent Request ID
          Note: 280391.1 - How to Retrieve SID Information For a Running Request

          Regards,
          Hussein
          • 2. Re: Best way to execute / monitor long running custom conc request with slaves
            Nigel C.L. Thomas
            Hussein

            Thanks for reply. All these scripts / notes appear to be guidance to an Oracle DBA/developer on how to trace/monitor concurrent requests using V$ views etc.

            As I explained, that's not what I am trying to do. I am trying to find the Apps equivalent of monitoring long operations. Of course, I could (and probably wlll) use DBMS_APPLICATION_INFO and the DBA/developer will be able to monitor the progress of the master and slave requests - but the professional user who initiated the request won't have access to that from the EBS screens. I want to know whether there is any concurrent request field that I can populate using a supported API to be able to feedback progress information along the lines:

            09:10: 1,000 employees processed out of 138,000 in 0:38 - expected finish 16:28
            09:32: 2,000 employees processed out of 138,000 in 1:04 - expected finish 16:15
            ...
            which the professional user would be able to query on. Currently the process takes > 100 hours; even reworked into a parallel master / slave it will take 8-12 hours.

            Regards Nigel
            • 3. Re: Best way to execute / monitor long running custom conc request with slaves
              user13312864
              Is anyone have scripts for history of long running forms/concurrent program , high logical IO , high physical IO , cost , users .