Advanced Oracle PL/SQL Programming with Packages

3.10. Choosing the Best Performer

If there is a difference in execution time between the performance of my two string-repeaters, it will not be a big one. I will need to execute the functions many times to compare the cumulative difference. The best way to calculate the elapsed time of PL/SQL code execution is with the GET_TIME function of the DBMS_UTILITY builtin package. I have encapsulated GET_TIME inside the PLVtmr package (PL/Vision TiMeR) to make it easier to use. Example 3.9 shows the kind of script I used.^[4]

^[4] By the way, this code was for the most part generated for me with the PLVgen package to compare the performance of repeated, rep_rpad, and also the recursion-based implementation of repeated (see sidebar).

This SQL*Plus script (stored in the file timerep.sql on the disk; see http://examples.oreilly.com/advoracle) takes three arguments. The first, &1, accepts the number of times to execute each function. The second, &2, accepts a string that is to be duplicated. The third, &3, accepts the number of repetitions of the string. I ran the script several times as shown below:

SQL> @timerep 100 abc 1
duprpad Elapsed: .77 seconds. Factored: .0077 seconds.
duploop Elapsed: .66 seconds. Factored: .0066 seconds.
recrep Elapsed: .71 seconds. Factored: .0071 seconds.

SQL> @timerep 100 abc 10
duprpad Elapsed: .71 seconds. Factored: .0071 seconds.
duploop Elapsed: .99 seconds. Factored: .0099 seconds.
recrep Elapsed: 1.54 seconds. Factored: .0154 seconds.

I ran each of these ...