Re: How to detect the completion of stored procedures?

To track the progress, you might have to code this explicitly into the procedures
i.e. sending a status message (e.g. iteration 131 of 200).

One way of doing this is to send a message to

    DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS Alternatively, if you start the procedures running in parallel via DBMS_JOBS, you can count the procedures which are currently executing, which have finished etc.

Alternatively, I think you can use DBMS_ALERT to send alert messages around to indicate when a process has started/finished etc. (The DBMS_ALERT package provides support for the asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can cause itself to be notified whenever values of interest in the database are changed. )

If you do not want to modify the packages/procedures etc., then you can monitor the number of blocks used, CPU used by the session etc. This will at least let you know that the process is probably running and has not hung etc.

Another way is to check the SQL which is currently being executed e.g. by checking the contents of the SGA.

Hope it helps.
Best regards,
Malcolm

Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to record names of executing modules or transactions in the database for later use when tracking the performance of various modules.

Registering the application allows system administrators and performance tuning specialists to track performance by module. System administrators can also use this information to track resource use by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views.

Your applications should set the name of the module and name of the action automatically each time a user enters that module. The module name could be the name of a form in an Oracle Forms application, or the name of the code segment in an Oracle Precompilers application. The action name should usually be the name or description of the current transaction within a module.

If you want to gather you own statistics based on module, then you can implement a wrapper around this package by writing a version of this package in another schema that first gathers statistics and then calls the SYS version of the package. The public synonym for DBMS_APPLICATION_INFO can then be changed to point to the DBA's version of the package.

"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message news:8nmnph$8ve2j$1_at_ID-41926.news.cis.dfn.de...

> Dear all,
>
> Some stored procedures may run for a long time. If we have several stored
> procedures jobs to run asynchronously, how do we detect the completion of
> these stored procedures? Can we somehow track the progress of these jobs?
> Thanks in advance.
>
> Regards,
> Dino
>
>
>
Received on Sun Aug 20 2000 - 06:14:52 CDT