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.
To turn on the activity of PLVtmr, call the turn_on program; its header is:
To turn off the activity of PLVtmr, call the turn_on program; its header is:
When turned off, PLVtmr will not add any overhead to your code execution and will not display any timing information.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.