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