CODEWITHSUNDEEP
oracle-database
Java Geo
Java Geo

Reputation: 149

Monitor stored procedure execution time in Oracle

Is there a way to monitor stored procedure execution time? Also to do some operations if execution time takes more than some fixed time

Upvotes: 0

Views: 4274

Answers (2)

Artem Kharkunov
Artem Kharkunov

Reputation: 66

For this is dbms_profiler package. Previously it should be set up for use. It creates a table service. More details can be found in the documentation: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_profil.htm#BJEFDBBC

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59652

I don't know a direct way but you can use PL/SQL package DBMS_APPLICATION_INFO

Procedure MY_Procedure is
begin

DBMS_APPLICATION_INFO.SET_MODULE('MY_Procedure', 'Starting');
...

DBMS_APPLICATION_INFO.SET_ACTION('Still working, please be patient'); 
...

DBMS_APPLICATION_INFO.SET_ACTION('Finished');

END;

While the procedure is running you can query (and perform some actions if needed) by this:

SELECT SID, serial#, username,  module, action, sql_exec_start
FROM v$session;

If needed you can also put a timestamp or execute time, e.g. DBMS_APPLICATION_INFO.SET_ACTION('Started at '||systimestamp)

In case you are working with Scheduler jobs you can monitor and stop the jobs directly:

BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
        NAME      => 'MY_JOB',
        ATTRIBUTE => 'MAX_RUN_DURATION',
        VALUE     => INTERVAL '10' MINUTE);
END;

and the call this frequently:

DECLARE

    CURSOR Jobs IS
    SELECT JOB_NAME, LAST_START_DATE, MAX_RUN_DURATION
    FROM USER_SCHEDULER_JOBS
    WHERE JOB_NAME = 'MY_JOB'
        AND STATE = 'RUNNING'
        AND SYSTIMESTAMP - LAST_START_DATE > MAX_RUN_DURATION;

BEGIN

    FOR aJob IN Jobs LOOP
        DBMS_SCHEDULER.STOP_JOB('MY_JOB', FORCE => TRUE);
    END LOOP;

END;

Upvotes: 1

Related Questions