14. PLVtmr: Analyzing Program Performance

Contents:
Toggling the Timer
Capturing the Start Time
Retrieving and Displaying the Elapsed Time
Using PLVtmr in Scripts

In Oracle PL/SQL Programming , I explored the implementation of the sp_timer package, which provides an easy-to-use interface to the DBMS_UTILITY.GET_TIME builtin function. GET_TIME gives us a mechanism for calculating the elapsed time of PL/SQL code execution down to the hundredth of a second. Since the publication of that book, I have enhanced sp_timer and it has evolved into the PLVtmr (PL/Vision TiMeR) package. The following sections show how to use each of the different elements of PLVtmr. For more information about how this package was developed, see Oracle PL/SQL Programming .

Now you have the following options when analyzing PL/SQL performance:

Turn the timer on or off. You can keep your timers embedded in your application. They will not do anything if you explicitly turn off PLVtmr.
Retrieve and display the elapsed time. You have many options for the format of the elapsed time data. These options allow you to use PLVtmr in SQL*Plus, as well as Oracle Developer/2000 and any other environment which supports PL/SQL program execution.
Execute prebuilt performance comparison procedures. These programs allow you to get a sense of the difference in performance of implicit and explicit cursors and of the overhead of a function call.

14.1 Toggling the Timer

PLVtmr supplies a toggle so that you can leave your timing program calls in your code, but only have the timings execute when desired.

To turn on the activity of PLVtmr, call the turn_on program; its header is:

PROCEDURE turn_on;

To turn off the activity of PLVtmr, call the turn_on program; its header is:

PROCEDURE turn_off;

When turned off, PLVtmr will not add any overhead to your code execution and will not display any timing information.