Long running operations

Sometimes we write procedures, functions or pieces of code that run slow or just take a long time to complete. When that happens it is usually because of some repeating operations implemented using a loop. These loops are very easy to monitor using PL/SQL, the DBMS_APPLICATION_INFO package has procedures and functions we can use to monitor our code.

When implemented the monitoring details can be found in the v$session_longops view. Below is a sample of how you could implement a longops metric when doing a loop operation.

DECLARE
    rindex    BINARY_INTEGER;
    slno      BINARY_INTEGER;
    totalwork number;
    sofar     number;
    obj       BINARY_INTEGER;

    BEGIN
    rindex := dbms_application_info.set_session_longops_nohint;
    sofar := 0;
    totalwork := 10;

    WHILE sofar < 10 LOOP
        -- update obj based on sofar
        -- perform task on object target

        sofar := sofar + 1;
        dbms_application_info.set_session_longops(
            rindex,  --A token which represents the v$session_longops row to update. Set this to set_session_longops_nohint to start a new row. Use the returned value from the prior call to reuse a row.
            slno, --Saves information across calls to set_session_longops: It is for internal use and should not be modified by the caller.
            "Operation X", --Specifies the name of the long running task. It appears as the OPNAME column of v$session_longops. The maximum length is 64 bytes.
            obj, --	Specifies the object that is being worked on during the long running operation. For example, it could be a table ID that is being sorted. It appears as the TARGET column of v$session_longops. Normally we just leave this blank
            0, -- Any number the client wants to store. It appears in the CONTEXT column of v$session_longops.
            sofar, -- Any number the client wants to store. It appears in the SOFAR column of v$session_longops. This is typically the amount of work which has been done so far.
            totalwork, --Any number the client wants to store. It appears in the TOTALWORK column of v$session_longops. This is typically an estimate of the total amount of work needed to be done in this long running operation.
            "table", --Specifies the description of the object being manipulated in this long operation. This provides a caption for the target parameter. This value appears in the TARGET_DESC field of v$session_longops. The maximum length is 32 bytes.
            "tables" --Specifies the units in which sofar and totalwork are being represented. It appears as the UNITS field of v$session_longops. The maximum length is 32 bytes.
            );
    END LOOP;
    END;

In the package API.SPIN_EXPORT_PKG we use this method of logging quite a lot since the package is handling exports of tables and these operations are rather slow. Logging the total number of rows to process and the number currently processed also allows us to show these types of metrics to users, currently these types of metrics are not shown to users and only used by developers. However they could quite easily be shown in some of the functions where they are used.