# Optimization of Loop Invariants

• Script Name Optimization of Loop Invariants
• Description This script demonstrates how the PL/SQL optimizer automatically detects and optimizes loop invariants (operations executed within a loop that do not change for each iteration of the loop). What does it do? Pulls the invariant expression out of the loop, executes it once, and then replaces the invariant expression with a variable. All transparent to the programmer. Want more details? Check out this whitepaper: http://www.oracle.com/technetwork/database/features/plsql/codeorder-133512.zip
• Category PL/SQL General
• Contributor Steven Feuerstein (Oracle)
• Created Tuesday May 03, 2016
• Statement 1
``CREATE OR REPLACE TYPE ls_numbers_t IS TABLE OF NUMBER``
Type created.
• Statement 2
The parts_in array is read-only. So the expression involving the first three elements of the array always evaluates to the same value. Why put it inside the loop? Well, you might want to do so for readability. But do you pay a price in performance?

Why Calculate That Over and Over Again?

``````CREATE OR REPLACE PROCEDURE ls_loop1 (parts_in IN ls_numbers_t)
IS
l_result   NUMBER := 0;
BEGIN
FOR indx IN 1 .. 10000000
LOOP
l_result := l_result +
(parts_in (1) + parts_in (2)) / parts_in (3);
END LOOP;

DBMS_OUTPUT.put_line (l_result);
END;``````
Procedure created.
• Statement 3
In this procedure, I have recognized that I included loop invariant code, so I pulled it out, assigned it to a variable (could also be a constant) outside of the loop, and referenced the variable within the loop. All good stuff to do, but do I need to do this? Should I have to pay attention to such details in my code? Not if your optimization level is set to at least 2!

Manual Extraction of Loop Invariant Code

``````CREATE OR REPLACE PROCEDURE ls_loop2 (parts_in IN ls_numbers_t)
IS
l_computation   NUMBER :=
(parts_in (1) + parts_in (2)) / parts_in (3);
l_result        NUMBER := 0;
BEGIN
FOR indx IN 1 .. 10000000
LOOP
l_result := l_result + l_computation;
END LOOP;

DBMS_OUTPUT.put_line (l_result);
END;``````
Procedure created.
• Statement 4
Another variation on the theme. Still, I end up with (seeming) repeated execution of invariant logic.

Intermediate Assignment Inside Loop

``````CREATE OR REPLACE PROCEDURE ls_loop3 (parts_in IN ls_numbers_t)
IS
l_computation   NUMBER;
l_result        NUMBER := 0;
BEGIN
FOR indx IN 1 .. 10000000
LOOP
l_computation := (parts_in (1) + parts_in (2)) / parts_in (3);
l_result := l_result + l_computation;
END LOOP;

DBMS_OUTPUT.put_line (l_result);
END;``````
Procedure created.
• Statement 5

Let's Analyze Performance! A Timer Package

``````CREATE OR REPLACE PACKAGE ls_timer
IS
PROCEDURE start_timer;

PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL);
END ls_timer;``````
Package created.
• Statement 6
I use the built-in DBMS_UTILITY.GET_CPU_TIME to do all the timing work for me.
``````CREATE OR REPLACE PACKAGE BODY ls_timer
IS
/* Package variable which stores the last timing made */
last_timing   NUMBER := NULL;

PROCEDURE start_timer
IS
BEGIN
last_timing := DBMS_UTILITY.get_cpu_time;
END;

PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL)
IS
BEGIN
DBMS_OUTPUT.put_line (
'"'
|| message_in
|| '" completed in: '
|| (DBMS_UTILITY.get_cpu_time - last_timing) / 100
|| ' seconds');
END;
END ls_timer;``````
Package Body created.
• Statement 7
And the elapsed time is roughly the same for all three! Hurry, optimizer!

Now Easy to Compare Different Approaches

``````DECLARE
l_numbers   ls_numbers_t := ls_numbers_t (4, 5, 6);
BEGIN
ls_timer.start_timer;
ls_loop1 (l_numbers);
ls_timer.show_elapsed_time ('Loop1 Opt Level 2');

ls_timer.start_timer;
ls_loop2 (l_numbers);
ls_timer.show_elapsed_time ('Loop2 Opt Level 2');

ls_timer.start_timer;
ls_loop3 (l_numbers);
ls_timer.show_elapsed_time ('Loop3 Opt Level 2');
END;``````
15000000
"Loop1 Opt Level 2" completed in: .32 seconds
15000000
"Loop2 Opt Level 2" completed in: .32 seconds
15000000
"Loop3 Opt Level 2" completed in: .32 seconds
• Statement 8
But is it the optimizer helping us here? Let's find out! I will ratchet down the optimizer level from its default of 2 down to 1. Still does some optimizations, but is not as aggressive.

Throttle Back the Optimizer

``ALTER PROCEDURE ls_loop1 COMPILE plsql_optimize_level = 1``
Procedure altered.
• Statement 9
Wow. That's quite a degradation of performance.

Order of Magnitude Slow Down

``````DECLARE
l_numbers   ls_numbers_t := ls_numbers_t (4, 5, 6);
BEGIN
ls_timer.start_timer;
ls_loop1 (l_numbers);
ls_timer.show_elapsed_time ('Loop1 Opt Level 1');
END;``````
15000000
"Loop1 Opt Level 1" completed in: 2.3 seconds
• Statement 10
Now I turn off optimization all together.

Let's Make It Worse

``ALTER PROCEDURE ls_loop1 COMPILE plsql_optimize_level = 0``
Procedure altered.
• Statement 11

Yep, Even Slower

``````DECLARE
l_numbers   ls_numbers_t := ls_numbers_t (4, 5, 6);
BEGIN
ls_timer.start_timer;
ls_loop1 (l_numbers);
ls_timer.show_elapsed_time ('Loop1 Opt Level 0');
END;``````
15000000
"Loop1 Opt Level 0" completed in: 2.52 seconds
• Statement 12
``DROP PACKAGE ls_timer``
Package dropped.
• Statement 13
``DROP PROCEDURE ls_loop1``
Procedure dropped.
• Statement 14
``DROP PROCEDURE ls_loop2``
Procedure dropped.
• Statement 15
``DROP PROCEDURE ls_loop3``
Procedure dropped.
• Statement 16
``DROP TYPE ls_numbers_t``
Type dropped.