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.
Get Advanced Oracle PL/SQL Programming with Packages now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.