14.3.1 elapsed_message Function

The elapsed_message function returns the elapsed time displayed within a standard format that incorporates the iteration factor (see Section 14.4, "Using PLVtmr in Scripts" ). The header for elapsed_message follows:

FUNCTION elapsed_message
   (prefix_in IN VARCHAR2 := NULL,
    adjust_in IN NUMBER := 0,
    reset_in IN BOOLEAN := TRUE,
    reset_context_in IN VARCHAR2 := NULL)
RETURN VARCHAR2;

The first argument, prefix_in , is another "context" string. In addition to the string provided in the call to capture , you can pass additional information to the PLVtmr package to display with the elapsed timing message. The value of the adjust_in argument allows you adjust or calibrate your timing with any overhead computations you may have made. The reset_in argument indicates whether or not the capture procedure should be called to "reset" the starting time of the next elapsed timing. The final argument, reset_context_in , provides a starting context for that reset call to capture .

Notice that default values are provided for every argument to elapsed_message . I can, therefore, execute it as simply as this:

v_howmuch := PLVtmr.elapsed_message;

The impact of this call to elapsed_message will be to return a message that contains the elapsed time without any prefix string, unadjusted for any overhead, with the start time that is maintained by PLVtmr reset by a call to the PLVtmr.capture procedure.

If you want to obtain the elapsed timing information without automatically calling capture to reset the start time, pass FALSE for reset_in as shown below:

v_howmuch := PLVtmr.elapsed_message (reset_in => FALSE);

This next call to elapsed_message supplies a full set of arguments:

v_howmuch := PLVtmr.elapsed_message
                (TO_CHAR (v_empid), v_compcalc, TRUE, 'Profit Share');

In this call, a prefix of the current employee ID number is provided to be placed in the message string. The timing is also adjusted by the amount of time it took to calculate the compensation ( v_compcalc ). I request that capture be called to start the clock ticking again from a new start time. Finally, I associate the string "Profit Share" with the new timing session.

For examples of the format of the output from elapsed_message , see the next session on the show_elapsed procedure.

14.3.2 show_elapsed Procedure

The show_elapsed procedure is the highest-level mechanism for displayed elapsed timings. It relies on the DBMS_OUTPUT.PUT_LINE procedure (through the p package) to display the information. If you want to use another mechanism for displaying the elapsed timing (such as MESSAGE in Oracle Forms), you will simply call elapsed or elapsed_message and display that information.

The header for show_elapsed is:

PROCEDURE show_elapsed
   (prefix_in IN VARCHAR2 := NULL,
    adjust_in IN NUMBER := 0,
    reset_in IN BOOLEAN := TRUE);

The first argument, prefix_in , is another "context" string. In addition to the string provided in the call to capture , you can pass additional information to the PLVtmr package to display with the elapsed timing message. The adjust_in value allows you adjust or calibrate your timing with any overhead computations you may have made. The reset_in argument indicates whether or not the capture procedure should be called to "reset" the starting time of the next elapsed timing.

As with elapsed_message , all arguments have defaults, so you can simply specify:

PLVtmr.show_elapsed;

to see the number of hundredths of seconds elapsed since the last call to capture without any adjustment. You will also reset the start time.

The following SQL*Plus-based calls to the PLVtmr programs will give you a sense of the format used to display the timing information:

SQL> exec PLVtmr.capture ('calc totals');
SQL> exec PLVtmr.show_elapsed;
Elapsed since calc totals: 5.28 seconds.
SQL>  exec PLVtmr.capture ('calc totals');
SQL>  exec PLVtmr.show_elapsed ('year 1995');
year 1995 Elapsed since calc totals: 9.45 seconds.

14.3.3 Setting the Timing Factor

In many cases (and as demonstrated in Section 14.4 ), you will want to calculate the performance of an element of code which executes very quickly. In order to gain a true sense of its performance, you will put this code inside a loop and execute it multiple times.

You can use the factoring programs of PLVtmr to tell PLVtmr the number of times you are executing your code. It will then factor that number into its presentation of elapsed time.

The factoring programs are as follows:

PROCEDURE set_factor (factor_in IN NUMBER);
FUNCTION factor RETURN NUMBER;

The set_factor procedure sets the factoring value. The factor function returns the current setting.

Here is an example of how the use of the factoring value affects the output from the PLVtmr package:

SQL> exec PLVtmr.set_factor(100);
SQL> exec PLVtmr.capture ('calc totals');
SQL> exec PLVtmr.show_elapsed ('year 1995');
year 1995 Elapsed since calc totals: 3.79 seconds. Factored: .0379 seconds.