Automatic subprogram inlining

Lorsque vous faites appel à des fonctions ou à des procédures dans vos applications PL/SQL, cela pourrait dégrader de façon significative leur performance. Cette altération de performance est d'autant plus significative lorsque l'appel d'une fonction ou d'une procédure se fait de façon répétitive dans une boucle.

"Automatic Subprogram Inlining" est un processus d'optimisation qui remédie aux problèmes de performance liée aux appels de fonctions et de procédures dans vos applications PL/SQL.

Ce processus remplace dans le même sous-programme au moment de la compilation, l'appel d'un sous-programme par sa copie (copie du sous-programme appelé) sans recourir à la modification de son code d'origine.

Le processus "Automatic Subprogram Inlining" peut être contrôlé par :

• Le paramètre PLSQL_OPTIMIZE_LEVEL
• La directive PRAGMA INLINE

Le paramètre PLSQL_OPTIMIZE_LEVEL.

Le paramètre d'initialisation PLSQL_OPTIMIZE_LEVEL spécifie le niveau d'optimisation de vos sous-programmes PL/SQL et permet également d'activer ou d'inactiver le processus "Subprogram Inlining" dans votre code PL/SQL.

a- Quand la valeur du paramètre PLSQL_OPTIMIZE_LEVEL vaut 2 (valeur par défaut), alors la directive PRAGMA INLINE détermine si la déclaration ou l'assignation apparaissant immédiatement après-elle serait ou ne serait pas éligible au processus "Automatic Subprogram Inlining".

b-Quand la valeur du paramètre PLSQL_OPTIMIZE_LEVEL vaut 3 alors l'optimiseur de PL/SQL pourrait appliquer automatiquement le processus "Automatic Subprogram Inlining". Dans ce cas, la directive PRAMA INLINE pourrait le désactiver pour un appel particulier si par exemple les performances sont altérées.

La directive " PRAGMA INLINE " indique que le processus d'optimisation doit ou non s'appliquer à un appel de sous-programme et doit apparaître immédiatement avant l'appel de ce sous-programme.

Syntaxe : PRAGMA INLINE(Identifiant,Mode)

Le paramètre Identifiant n'est d'autre que le nom du sous-programme et le Mode accepte pour valeur 'YES' ou 'NO'

- Si le mode est à 'NO', dans ce cas, aucun processus d'optimisation ne s'applique pas au sous-programme.
- Si le mode est à 'YES' et le paramètre PLSQL_OPTIMIZE_LEVEL est égal à 2, le processus d'optimisation s'applique au sous-programme.
- Si le mode est à 'YES' et PLSQL_OPTIMIZE_LEVEL est égal à 3, alors l'optimiseur placera une plus grande priorité sur le processus " Automatic Subprogram Inlining " et pourrait trouver la meilleure optimisation ne nécessitant pas l'application du processus au sous-programme.


Liste des déclarations affectées par la directive PRAGMA INLINE :
Lorsque la directive PRAGMA INLINE précède immédiatement une des déclarations suivantes, alors elle affecte chaque appel du sous-programme spécifié dans cette déclaration.

• Assignment
• Call
• Conditional
• CASE
• CONTINUE-WHEN
• EXECUTE IMMEDIATE
• EXIT-WHEN
• LOOP
• RETURN

Vous pouvez utiliser la directive PRAGMA INLINE pour :

a. Spécifier que des appels individuels de sous-programmes soient ou non éligibles au processus "Automatic Subprogram Inlining".

b. Activer ou désactiver le processus " Automatic Subprogram Inlining ".

c. Permettre au compilateur de PL/SQL d'identifier les appels de sous-programmes pour lesquels le processus "Automatic Subprogram Inlining" peut éventuellement s'appliquer en initialisant le paramètre PLSQL_OPTIMIZE_LEVEL à la valeur 3.

Remarque:

• Si plusieurs sous-programmes ont le même identifiant (surcharge), alors la directive " PRAGMA INLINE " s'applique à tous les appels de ces sous-programmes.
• Si la directive " PRAGMA INLINE(identifiant,'YES') " et " PRAGMA INLINE(identifiant,'NO') " ont le même identifiant, alors le paramètre 'NO' annule l'effet du paramètre 'YES' et ceci quelque soit le nombre d'occurrences de PRAGMA INLINE (identifiant,'YES') et peu importe l'ordre de ces PRAGMA.

Exemples :
Nous allons illustrer ce concept par un exemple simple en demandant à Oracle d'appliquer le processus d'optimisation "Subprogram Inilining" sur l'appel d'une fonction nommée fnc_calc_sm qui calcule la somme de deux nombres.
Ceci va s'avérer très utile lorsque l'appel de notre fonction se fera de façon répétitive dans une boucle.

Pour notre exemple, l'appel se fera dans un bloc anonyme.
1er cas : Le paramètre PLSQL_OPTIMIZE_LEVEL est initialisé à 1.

ALTER SESSION
SET plsql_optimize_level = 1
/
SQL> 
 
Session altered

L'optimiseur de PL/SQL n'applique aucune optimisation sur le code.

SQL> DECLARE
  2    V_deb_apl NUMBER;
  3    V_sm_2   NUMBER := 0;
  4    FUNCTION fnc_calc_sm(P1 NUMBER, P2 NUMBER) RETURN NUMBER IS
  5    BEGIN
  6      RETURN(P1 + P2);
  7    END fnc_calc_sm;
  8  BEGIN
  9    V_deb_apl := DBMS_UTILITY.GET_TIME;
 10    FOR I IN 1 .. 1000000 LOOP
 11    PRAGMA inline (FNC_CALC_SM,'YES');
 12      V_sm_2 := V_sm_2 + fnc_calc_sm(1, I);
 13    END LOOP;
 14    DBMS_OUTPUT.PUT_LINE('Durée Exécution  :' ||
 15                         TO_CHAR(DBMS_UTILITY.GET_TIME - V_deb_apl));
 16  END;
 17  /
 
Durée Exécution  :57
 
PL/SQL procedure successfully completed

2^ème cas : PLSQL_OPTIMIZE_LEVEL = 2 et PRAGMA INLINE (FNC_CALC_SM,'YES')

ALTER SESSION
SET plsql_optimize_level = 2
/
 
SQL> 
 
Session altered

Dans ce cas, le processus "Subprogram Inilining" s'applique seulement sur les appels pour lesquels le développeur aura spécifié la directive PRAGMA INLINE (FNC_CALC_SM,'YES').

SQL> DECLARE
  2    V_deb_apl NUMBER;
  3    V_sm_2   NUMBER := 0;
  4    FUNCTION fnc_calc_sm(P1 NUMBER, P2 NUMBER) RETURN NUMBER IS
  5    BEGIN
  6      RETURN(P1 + P2);
  7    END fnc_calc_sm;
  8  BEGIN
  9    V_deb_apl := DBMS_UTILITY.GET_TIME;
 10    FOR I IN 1 .. 1000000 LOOP
 11    PRAGMA inline (FNC_CALC_SM,'YES');
 12      V_sm_2 := V_sm_2 + fnc_calc_sm(1, I);
 13    END LOOP;
 14    DBMS_OUTPUT.PUT_LINE('Durée Exécution  :' ||
 15                         TO_CHAR(DBMS_UTILITY.GET_TIME - V_deb_apl));
 16  END;
 17  /
 
Durée Exécution  :30
 
PL/SQL procedure successfully completed

3^ème cas : PLSQL_OPTIMIZE_LEVEL = 2 et PRAGMA INLINE (FNC_CALC_SM,'NO')

SQL> DECLARE
  2    V_deb_apl NUMBER;
  3    V_sm_2   NUMBER := 0;
  4    FUNCTION fnc_calc_sm(P1 NUMBER, P2 NUMBER) RETURN NUMBER IS
  5    BEGIN
  6      RETURN(P1 + P2);
  7    END fnc_calc_sm;
  8  BEGIN
  9    V_deb_apl := DBMS_UTILITY.GET_TIME;
 10    FOR I IN 1 .. 1000000 LOOP
 11    PRAGMA inline (FNC_CALC_SM,'NO');
 12      V_sm_2 := V_sm_2 + fnc_calc_sm(1, I);
 13    END LOOP;
 14    DBMS_OUTPUT.PUT_LINE('Durée Exécution  :' ||
 15                         TO_CHAR(DBMS_UTILITY.GET_TIME - V_deb_apl));
 16  END;
 17  /
 
Durée Exécution  :57
 
PL/SQL procedure successfully completed

4^ème cas : Le paramètre PLSQL_OPTIMIZE_LEVEL est initialisé à 3.

SQL> ALTER SESSION
  2  SET plsql_optimize_level = 3
  3  /
 
Session altered

SQL> DECLARE
  2    V_deb_apl NUMBER;
  3    V_sm_2   NUMBER := 0;
  4    FUNCTION fnc_calc_sm(P1 NUMBER, P2 NUMBER) RETURN NUMBER IS
  5    BEGIN
  6      RETURN(P1 + P2);
  7    END fnc_calc_sm;
  8  BEGIN
  9    V_deb_apl := DBMS_UTILITY.GET_TIME;
 10    FOR I IN 1 .. 1000000 LOOP
 11   -- PRAGMA inline (FNC_CALC_SM,'YES');
 12      V_sm_2 := V_sm_2 + fnc_calc_sm(1, I);
 13    END LOOP;
 14    DBMS_OUTPUT.PUT_LINE('Durée Exécution  :' ||
 15                         TO_CHAR(DBMS_UTILITY.GET_TIME - V_deb_apl));
 16  END;
 17  /
 
Durée Exécution  :31
 
PL/SQL procedure successfully completed

5^ème cas : Nous allons tester la surcharge de notre fonction fnc_calc_sm et le processus " Subprogram Inilining " .
Nous allons assigner dans un premier temps la valeur de fnc_calc_sm (p1,p2) à la variable V_sm_2, puis dans un deuxième temps la valeur de fnc_calc_sm (p1,p2,p3) qui elle, calcule la somme de trois nombres à la variable V_sm_3.

SQL> ALTER SESSION SET plsql_optimize_level = 2
  2  /
 
Session altered

SQL> DECLARE
  2    V_deb_apl NUMBER;
  3    V_sm_2   NUMBER := 0;
  4  
  5    FUNCTION fnc_calc_sm(P1 NUMBER, P2 NUMBER)
  6    RETURN NUMBER IS
  7    BEGIN
  8      RETURN(P1 + P2);
  9    END fnc_calc_sm;
 10  
 11    FUNCTION fnc_calc_sm(P1 NUMBER, P2 NUMBER, P3 NUMBER)
 12    RETURN NUMBER IS
 13    BEGIN
 14      RETURN(P1 + P2 + P3);
 15    END fnc_calc_sm;
 16  
 17  BEGIN
 18    V_deb_apl := DBMS_UTILITY.GET_TIME;
 19    FOR I IN 1 .. 1000000 LOOP
 20    PRAGMA inline (FNC_CALC_SM,'YES');
 21      V_sm_2 := V_sm_2 + fnc_calc_sm(1, I)+fnc_calc_sm(1,2,I);
 22    END LOOP;
 23    DBMS_OUTPUT.PUT_LINE('Durée Exécution  :' ||
 24                         TO_CHAR(DBMS_UTILITY.GET_TIME - V_deb_apl));
 25  END;
 26  /
 
Durée Exécution  :52
 
PL/SQL procedure successfully completed

Pour vérifier que le compilateur PL/SQL applique le processus pour fnc_calc_sm(1,2,I), nous avons besoin du nouveau package nommé DBMS_HPROF.

PL/SQL Hierarchical Profiler est une nouvelle application fournie par Oracle 11g, implantée par le package DBMS_HPROF qui enregistre et reporte les statistiques de l'exécution de votre code sql et pl/sql organisés par sous programmes (fonction hiérarchique et appels de procédures)

DBMS_HPROF est un package fourni par Oracle pour permettre de tracer l'exécution de vos applications PL/SQL. Vous pouvez accéder et analyser les données hiérarchiques du profil d'exécution de votre programme.

Pour commencer, nous devons installer le package DBMS_HPROF.

• Exécuter le script dbmshptab.sql se trouvant le répertoire rdbms/admin pour créer les tables requises pour enregistrer de façon persistante les résultats de la fonction ANALYZE.
• L'exécution de dbmshptab.sql supprime toutes les tables créées par une exécution précédente de ce script.

Sous-programme :
Analyze : Analyse le fichier de sortie et produit l'information hiérarchique du profil d'exécution dans les tables de la base de données.
Start_profiling : Commence la collecte des données du profil hiérarchique dans la session de l'utilisateur.
Stop_profiling : Met fin à la collecte des données du profil hiérarchique dans votre session.
Remarque :
L'exécution du script dbmshptab.sql supprime toutes les tables du profil hiérarchique précédemment créées.

Pour tracer notre exécution, commençons par spécifier un répertoire (LOG_DIR), dans lequel notre fichier inline_1.trc doit être crée au moment de l'exécution de notre bloc anonyme.

Le répertoire doit exister physiquement dans notre système d'exploitation et l'utilisateur doit avoir le droit de lecture sur ce répertoire.

Nous allons utiliser la fonction la fonction ANALYZE du package DBMS_HPROF.

Cette fonction analyse le fichier trace généré lors de l'exécution en produisant l'information hiérarchique dans les tables de la base de données.

Nous allons particulièrement nous servir de la table DBMSHP_FUNCTION_INFO pour valider nos affirmations précédentes.

Étape 1 :

Le package DBMS_HPROF est installé par défaut mais pour l'utiliser, nous avons besoin d'avoir les privilèges d'exécution sur ce package, puis de créer un répertoire (physique) dans lequel sera stocké notre fichier de données relatives au profil d'exécution.

conn SYSTEM/salem@ORA11 AS SYSDBA
/
SQL> 
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
Connected as SYS
 
SQL>
 
 
GRANT EXECUTE ON dbms_hprof TO salem
/
Grant succeeded
 
SQL>

CREATE OR REPLACE DIRECTORY LOG_DIR AS 'D:\PUBLICATIONS_2010\SUBPRGINLINE\LOG_DIR';
 
 
GRANT READ, WRITE ON DIRECTORY LOG_DIR TO SALEM
/
SQL> 
 
Grant succeeded

Exécution du script dbmshptab.sql :

Assurez-vous d'avoir les privilèges sur le tablespace USERS avant de lancer le script dbmshptab.sql.

alter user SALEM quota unlimited on users
/
SQL> 
 
User altered

@C:\app\ORA\product\11.1.0\db_1\RDBMS\ADMIN\dbmshptab.SQL
/

Vérification :

Création des tables

SELECT TABLE_NAME FROM USER_TABLES US 
WHERE US.TABLE_NAME LIKE 'DBMSHP%'
/
 
SQL> 
 
TABLE_NAME
------------------------------
DBMSHP_FUNCTION_INFO
DBMSHP_PARENT_CHILD_INFO
DBMSHP_RUNS

Création de la séquence

SELECT SEQUENCE_NAME FROM USER_SEQUENCES
/
SQL> 
 
SEQUENCE_NAME
------------------------------
DBMSHP_RUNNUMBER

Nous pouvons maintenant exploiter le package DBMS_HPROF pour identifier les cas d'application du processus "Automatic Subprogram inlining" sur les appels de sous-programmes qui ont été "surchargés".

SQL> ALTER SESSION
  2  SET plsql_optimize_level = 2
  3  /
 
Session altered

1^er cas : Si plusieurs sous-programmes ont le même identifiant (surcharge), alors la directive " PRAGMA INLINE " s'applique à tous les appels de ces sous-programmes.
a- Test du processus(Inlining Subprogram) sur des appels de fonctions dans le cas de surcharge.

Exemple 1▲

SQL> DECLARE
  2  
  3    V_SOM_2 NUMBER := 0;
  4  
  5    FUNCTION FNC_SOMME_2(P1 NUMBER, P2 NUMBER)
  6    RETURN NUMBER
  7    IS
  8    BEGIN
  9      RETURN(P1 + P2);
 10    END FNC_SOMME_2;
 11  
 12    FUNCTION FNC_SOMME_2(P1 NUMBER, P2 NUMBER, P3 NUMBER)
 13    RETURN NUMBER
 14    IS
 15    BEGIN
 16      RETURN(P1 + P2) / P3;
 17    END FNC_SOMME_2;
 18  
 19  BEGIN
 20  
 21    DBMS_HPROF.START_PROFILING(LOCATION => 'LOG_DIR',
 22                               FILENAME => 'profiler.txt');
 23    FOR I IN 1 .. 1000000 LOOP
 24      -- PRAGMA INLINE(FNC_SOMME_2, 'YES');
 25      V_SOM_2 := V_SOM_2 + FNC_SOMME_2(1, I) + FNC_SOMME_2(1, I, 2);
 26    END LOOP;
 27    DBMS_HPROF.STOP_PROFILING;
 28  END;
 29  /
 
PL/SQL procedure successfully completed
 
SQL>

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2    L_runid NUMBER;
  3  BEGIN
  4    L_runid := DBMS_HPROF.analyze(LOCATION=>'LOG_DIR',
  5                                FILENAME=>'profiler.txt',
  6                                RUN_COMMENT=>'Test du processus(Inlining Subprogram) sur des appels de fonctions dans le cas de surcharge' );
  7    DBMS_OUTPUT.PUT_LINE('L_runid = ' || L_runid);
  8  END;
  9  /
 
L_runid = 1
 
PL/SQL procedure successfully completed

SQL> SET SERVEROUTPUT ON;
SQL> SET LINESIZE 200;
SQL> COLUMN RUNID FORMAT 999;
SQL> COLUMN FUNCTION FORMAT A50;
SQL> COLUMN LINE# FORMAT 9999;
SQL> COLUMN CALLS FORMAT 9999999;
SQL> SELECT FI.RUNID,
  2         FI.FUNCTION,
  3         FI.LINE#,
  4         FI.CALLS
  5  FROM DBMSHP_FUNCTION_INFO FI
  6  WHERE FI.RUNID = 1
  7  /
 
RUN FUNCTION                                           LINE   CALLS
--- -------------------------------------------------- ---- -------
  1 __anonymous_block.FNC_SOMME_2                         5 1000000
  1 __anonymous_block.FNC_SOMME_2                        12 1000000
  1 STOP_PROFILING                                       53       1

Exemple 2▲

SQL> DECLARE
  2  
  3    V_SOM_2 NUMBER := 0;
  4  
  5    FUNCTION FNC_SOMME_2(P1 NUMBER, P2 NUMBER)
  6    RETURN NUMBER
  7    IS
  8    BEGIN
  9      RETURN(P1 + P2);
 10    END FNC_SOMME_2;
 11  
 12    FUNCTION FNC_SOMME_2(P1 NUMBER, P2 NUMBER, P3 NUMBER)
 13    RETURN NUMBER
 14    IS
 15    BEGIN
 16      RETURN(P1 + P2) / P3;
 17    END FNC_SOMME_2;
 18  
 19  BEGIN
 20  
 21    DBMS_HPROF.START_PROFILING(LOCATION => 'LOG_DIR',
 22                               FILENAME => 'profiler.txt');
 23    FOR I IN 1 .. 1000000 LOOP
 24      PRAGMA INLINE(FNC_SOMME_2, 'YES');
 25      V_SOM_2 := V_SOM_2 + FNC_SOMME_2(1, I) + FNC_SOMME_2(1, I, 2);
 26    END LOOP;
 27    DBMS_HPROF.STOP_PROFILING;
 28  END;
 29  /
 
PL/SQL procedure successfully completed

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2    L_runid NUMBER;
  3  BEGIN
  4    L_runid := DBMS_HPROF.analyze(LOCATION=>'LOG_DIR',
  5                                FILENAME=>'profiler.txt',
  6                                RUN_COMMENT=>'Test du processus(Inlining Subprogram) sur des appels de fonctions dans le cas de surcharge' );
  7    DBMS_OUTPUT.PUT_LINE('L_runid = ' || L_runid);
  8  END;
  9  /
 
L_runid = 2
 
PL/SQL procedure successfully completed

SQL> SET SERVEROUTPUT ON;
SQL> SET LINESIZE 200;
SQL> COLUMN RUNID FORMAT 999;
SQL> COLUMN FUNCTION FORMAT A50;
SQL> COLUMN LINE# FORMAT 9999;
SQL> COLUMN CALLS FORMAT 9999999;
SQL> SELECT FI.RUNID,
  2         FI.FUNCTION,
  3         FI.LINE#,
  4         FI.CALLS
  5  FROM DBMSHP_FUNCTION_INFO FI
  6  WHERE FI.RUNID = 2
  7  /
 
RUN FUNCTION                                           LINE   CALLS
--- -------------------------------------------------- ---- -------
  2 STOP_PROFILING                                       53       1
 
SQL>