Advanced Oracle PL/SQL Programming with Packages

Chapter 14. PLVtmr: Analyzing Program Performance

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.