PRAGMA INLINE in Subprogram Calling

Introduction

Subprogram is procedure or function that is declare in declaration section like below

 DECLARE  
   PROCEDURE XXX (IN_PARAMATER NUMBER)  
   IS  
   BEGIN  
    --CODE OF PROCEDURE  
   END;  
   
   FUNCTION YYY (IN_PARAMETER NUMBER)  
    RETURN NUMBER  
   IS  
   BEGIN  
    --CODE OF FUNCTION  
   END;  
 BEGIN  
   --CODE OF ANONYMOUS BLOCK  
 END;  

   
When you call subprogram(procedure or function) in your code multiple time, in every call it will be loaded again so it will take more time to execute.
To enhance previous drawback we use inline to replace the subprogram call with the copy of already called subprogram before.


PRAGMA INLINE directive is new feature provided since oracle database 11g.
The PRAGMA INLINE directive is used to determine if subprogram call is inline or not.

The PRAGMA INLINE is controlled by PLSQL_OPTIMIZE_LEVEL parameter value
You can change PLSQL_OPTIMIZE_LEVEL by any of below commands

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;

1-If PLSQL_OPTIMIZE_LEVEL is equal to 2, then the inline is done regarding PRAGMA INLINE Value, IF PRAGMA INLINE value is YES, it will be inlined otherwise not inlined

2-If PLSQL_OPTIMIZE_LEVEL is equal to 3, then the inline is done with high priority  regarding PRAGMA INLINE Value, IF PRAGMA INLINE value is YES, it will be inlined with high priority otherwise not inlined

How to use PRAGMA INLINE

You can use it anywhere in your code and it has below syntax

PRAGMA INLINE(subprogram_name,'YES');

PRAGMA INLINE(subprogram_name,'NO');

You can use PRAGMA INLINE in  precedence  of the  following statements

1- Assignment
2- Call
3- Conditional
4- CASE
5- CONTINUE-WHEN
6- EXECUTE IMMEDIATE
7- EXIT-WHEN
8- LOOP
9- RETURN

Demo

1- I will create anonymous block for calling subprogram without using PRAGMA INLINE and print the time used to execute the code

 DECLARE  
   LN$START_TIME  NUMBER;  
   LN$END_TIME   NUMBER;  
   LN$RESULT    NUMBER;  
   
   FUNCTION ADD_FIVE (IN_NUMBER1 NUMBER)  
    RETURN NUMBER  
   IS  
   BEGIN  
    RETURN NVL (IN_NUMBER1, 0) + 5;  
   END;  
 BEGIN  
   LN$START_TIME := DBMS_UTILITY.GET_TIME;  
   
   FOR I IN 1 .. 10000000  
   LOOP  
    LN$RESULT := ADD_FIVE (I);  
   END LOOP;  
   
   LN$END_TIME := DBMS_UTILITY.GET_TIME;  
   DBMS_OUTPUT.  
   PUT_LINE ('Time Elapsed is ' || TO_CHAR (LN$END_TIME - LN$START_TIME));  
 END;  

The output in DBMS Output Console

Time Elapsed is 318

2- Add PRAGMA INLINE to my previous anonymous block.

 DECLARE  
   LN$START_TIME  NUMBER;  
   LN$END_TIME   NUMBER;  
   LN$RESULT    NUMBER;  
   
   FUNCTION ADD_FIVE (IN_NUMBER1 NUMBER)  
    RETURN NUMBER  
   IS  
   BEGIN  
    RETURN NVL (IN_NUMBER1, 0) + 5;  
   END;  
 BEGIN  
   LN$START_TIME := DBMS_UTILITY.GET_TIME;  
   
   FOR I IN 1 .. 10000000  
   LOOP  
    PRAGMA INLINE (ADD_FIVE, 'YES');  
    LN$RESULT := ADD_FIVE (I);  
   END LOOP;  
   
   LN$END_TIME := DBMS_UTILITY.GET_TIME;  
   DBMS_OUTPUT.  
   PUT_LINE ('Time Elapsed is ' || TO_CHAR (LN$END_TIME - LN$START_TIME));  
 END;  

The output in DBMS Output Console

Time Elapsed is 160

I posted  Timer in PLSQL and SQL, you can use it for tracking timers.

Conclusion

Executing calling subprogram with PRAGMA INLINE takes 160 and without it takes 318, So using using PRAGMA INLINE provide us with high performance.

Thanks