#

| Chapter 14 |

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

IV. Developer Utility Packages |  | 14.2 Capturing the Start Time

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