Any PL/SQL program unit involving looping logic is a strong candidate for performance improvements. Potential improvements for these types of programs can be accomplished in two ways. The first is to reduce the number of iterations by restructuring the logic to accomplish the same functional result. The second is to reduce the time per iteration. Either reduction often improves performance dramatically.
To bring this point into perspective, think of the following scenario: You need to process 9000 employee records in a PL/SQL routine, and to process each employee takes 2 seconds. This equates to 18,000 seconds, which equates to 5 hours. If the processing per employee is reduced to 1 second, the time to process the 9000 employees is reduced by 9000 seconds, or 2.5 hours . . . quite a difference!
The following example shows a minor restructuring of a PL/SQL program unit to illustrate reducing per-loop processing and overall processing. The program unit processes a loop 1,000,000 times. Each iteration adds to the incremental counter used to display a message each 100,000 iterations and adds to the total counter used to check for loop exiting. To view DBMS_OUTPUT, make sure you issue the SET SERVEROUTPUT ON command first.
The package has been created.
The package body has been created.
By changing the program to only add to the LV_TOTAL_COUNTER_NUM variable each time the incremental counter reaches 100,000, overall execution time is reduced:
The DBMS_OUTPUT.PUT_LINE output for each batch of processed records was not included in the following output:
The preceding example illustrates the performance difference achieved by changing the iteration logic to reduce the timing per iteration. The example is basic and shows a 34 percent increase on 1 million iterations. Based on the restructuring and the iterations, this improvement can make a huge difference.
TIP
When a PL/SQL program unit involves extensive looping or recursion, concentrate on reducing the execution time per iteration. The benefits add up fast, and it is easy to do the math to determine the overall improvement potential. Also review the looping or recursion for restructuring to reduce the number of iterations, while keeping the functionality. With the extreme flexibility of PL/SQL and SQL, a variety of ways typically exist to accomplish the same result. If a PL/SQL program unit is not performing optimally, sometimes you have to rewrite the logic another way.
Leave a Reply