Pragma Inline

Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram.

Pragma inline could be of considerable performance improvement.

Let's check out this with example

Without using Pragme Inline

 DECLARE
   l_loops  NUMBER := 10000000;
   l_start  NUMBER;
   l_return NUMBER;
  
 FUNCTION add_numbers(p_1 IN NUMBER, p_2 IN NUMBER) RETURN NUMBER AS
   BEGIN
     RETURN p_1 + p_2;
   END add_numbers;
 
 BEGIN
   l_start := DBMS_UTILITY.get_time;
 
   FOR i IN 1 .. l_loops LOOP
     
     l_return := add_numbers(1, i);
   END LOOP;
 
   DBMS_OUTPUT.put_line('Elapsed Time: ' ||
                        (DBMS_UTILITY.get_time - l_start) || 'secs');
 END;
 -------------------------------------------------------------------
 Here the function Add number is being called multiple times it is executed in arround 90 sec

With Pragma Inline

 DECLARE
   l_loops  NUMBER := 10000000;
   l_start  NUMBER;
   l_return NUMBER;
 
   FUNCTION add_numbers(p_1 IN NUMBER, p_2 IN NUMBER) RETURN NUMBER AS
   BEGIN
     RETURN p_1 + p_2;
   END add_numbers;
 
 BEGIN
   l_start := DBMS_UTILITY.get_time;
   FOR i IN 1 .. l_loops LOOP
     PRAGMA INLINE(add_numbers, 'YES');
     l_return := add_numbers(1, i);
   END LOOP;
 
   DBMS_OUTPUT.put_line('Elapsed Time: ' ||
                        (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 END;
 -------------------------------------------------------------------
 It is now taking only 40 sec!!

For optimizing level 3 The pragma inline is the default.

The INLINE pragma only affects the type of the following statement.

· Assignment

· Cal

· Conditional

· CASE

· CONTINUE-WHEN

· EXECUTE IMMEDIATE

· EXIT-WHEN

· LOOP

· RETURN

Please also check pragma serially reusable

#Oracledevelopemnt #PLSQL #Pragma_Inline #Pragma_serially reusable#Oracle