Advanced Oracle PL/SQL Programming with Packages

14.4. Using PLVtmr in Scripts

In most situations, you will not place calls to PLVtmr inside your production code. Instead, you will extract specific elements of your application which you wish to focus on and understand their performance implications. You will usually write a SQL*Plus script that executes your code one or more times. If you do place the code within a loop, you should use the set_factor procedure to let PLVtmr know that it is timing multiple iterations of the code.

The following anonymous block, for example, calculates how long it takes to calculate totals. It also computes an average execution time over the specified number of iterations (passed in as a SQL*Plus argument) by calling the set_factor procedure:

BEGIN
    PLVtmr.set_factor (&1);
    PLVtmr.capture;
    FOR rep IN 1 .. &1
    LOOP
       calc_totals;
    END LOOP;
    PLVtmr.show_elapsed ('calc_totals');
 END;
 /

The PLVgen package will generate a loop like the one you see above. In fact, that script was generated with the following call in SQL*Plus:

SQL> exec PLVgen.timer ('calc_totals');

14.4.1. Comparing Performance of Different Implementations

Another common operation with PLVtmr is to compare two or more implementations of the same business rule or function. One example of this approach is shown below. In this script, I see which of my implementations of an "is number" function is most efficient. The first version is based on the TO_NUMBER builtin, while the second uses the LTRIM function.

SET SERVEROUTPUT ON SET VERIFY OFF ...