#

| Chapter 14 PLVtmr: Analyzing Program Performance |

## 14.3 Retrieving and Displaying the Elapsed Time

PLVtmr offers several different programs to retrieve and display the elapsed time: `elapsed`, `elapsed_message`, and `show_elapsed`. To get a non-NULL result from these programs, you must first have called the `capture` procedure.

The `elapsed` function returns the number of hundredths of seconds since the last call to `capture`. Its header is:

>     FUNCTION elapsed RETURN NUMBER;

You will want to use `elapsed` when you wish to store the elapsed time, rather than display it, or when you wish to display it in a format not supported by the other programs of PLVtmr.

### 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.

14.2 Capturing the Start Time |  | 14.4 Using PLVtmr in Scripts

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.