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