Créer des applications évolutives

Concevez vos applications pour utiliser les mêmes ressources, quelles que soient les populations d'utilisateurs et les volumes de données, et pour ne pas surcharger les ressources système.

A propos des applications évolutives

Une application évolutive peut traiter une charge globale plus importante avec une augmentation proportionnelle de l'utilisation des ressources système.

Une application évolutive peut traiter une charge globale plus importante avec une augmentation proportionnelle de l'utilisation des ressources système. Par exemple, si vous doublez sa charge globale, une application évolutive utilise deux fois plus de ressources système.

Une application non évolutive épuise une ressource système. Par conséquent, si vous augmentez la charge globale de l'application, aucun débit supplémentaire n'est possible. Les applications non évolutives entraînent des débits fixes et des temps de réponse médiocres.

Exemples d'épuisement des ressources :

Concevez vos applications pour utiliser les mêmes ressources, quelles que soient les populations d'utilisateurs et les volumes de données, et pour ne pas surcharger les ressources système.

Utiliser des variables attachées pour améliorer l'évolutivité

Les variables attachées, utilisées correctement, vous permettent de développer des applications efficaces et évolutives.

Une variable attachée est un espace réservé dans une instruction SQL qui doit être remplacé par une valeur ou une adresse de valeur valide pour que l'exécution de l'instruction réussisse. A l'aide de variables attachées (bind variables), vous pouvez écrire une instruction SQL qui accepte des entrées ou des paramètres lors de l'exécution.

Un sous-programme peut disposer de paramètres dont les valeurs sont fournies par l'appelant, tandis qu'une instruction SQL peut comporter des espaces réservés de variable attachée, dont les valeurs (appelées variables attachées) sont fournies par l'exécution. Tout comme un sous-programme est compilé une fois, puis exécuté plusieurs fois avec différents paramètres, une instruction SQL avec des espaces réservés de variable attachée est analysée en dur une fois, puis analysée en détail avec différentes variables attachées.

Une analyse complète, qui inclut l'optimisation et la génération de row sources, est une opération très gourmande en CPU. Une analyse partielle, qui ignore l'optimisation et la génération de row source et passe directement à l'exécution, est généralement beaucoup plus rapide qu'une analyse complète de la même instruction. (Pour une présentation du traitement SQL, qui inclut la différence entre une analyse complète et une analyse partielle, reportez-vous à Concepts Oracle Database.)

Il s'agit non seulement d'une analyse complète d'une opération consommant beaucoup de ressources CPU, mais aussi d'une opération non évolutive, car elle ne peut pas être effectuée simultanément avec de nombreuses autres opérations. Pour plus d'informations sur la simultanéité et l'évolutivité, reportez-vous à A propos de la simultanéité et de l'évolutivité.

L'exemple 8-1 illustre la différence de performances entre une requête sans variable attachée et une requête sémantiquement équivalente avec une variable attachée. Le premier est plus lent et utilise beaucoup plus de verrous (pour plus d'informations sur l'impact des verrous sur l'évolutivité, voir). Pour collecter et afficher les statistiques de performances, l'exemple utilise l'outil Runstats, décrit dans la section "Comparing Programming Techniques with Runstats".

Remarque :

Remarque :

Bien que l'analyse partielle (soft parse) soit plus efficace que l'analyse complète (hard parse), le coût de l'analyse partielle (soft parse) d'une instruction plusieurs fois reste très élevé. Pour optimiser l'efficacité et l'évolutivité de votre application, limitez l'analyse. Le moyen le plus simple de réduire l'analyse consiste à utiliser du code PL/SQL.

Exemple 8-1 : la variable attachée améliore les performances

CREATE TABLE t ( x VARCHAR2(5) );

DECLARE
  TYPE rc IS REF CURSOR;
  l_cursor rc;
BEGIN
  runstats_pkg.rs_start;  -- Collect statistics for query without bind variable

  FOR i IN 1 .. 5000 LOOP
    OPEN l_cursor FOR 'SELECT x FROM t WHERE x = ' || TO_CHAR(i);
    CLOSE l_cursor;
  END LOOP;

  runstats_pkg.rs_middle;  -- Collect statistics for query with bind variable

  FOR i IN 1 .. 5000 LOOP
    OPEN l_cursor FOR 'SELECT x FROM t WHERE x = :x' USING i;
    CLOSE l_cursor;
  END LOOP;

  runstats_pkg.rs_stop(500);  -- Stop collecting statistics
end;
/

Le résultat obtenu est similaire au texte suivant :

Run 1 ran in 740 hsec
Run 2 ran in 30 hsec
Run 1 ran in 
2466.67% of the time of run 2

Name                                   Run 1         Run 2    Difference
STAT...recursive cpu usage               729            19          -710
STAT...CPU used by this sessio           742            30          -712
STAT...parse time elapsed              1,051             4        -1,047
STAT...parse time cpu                  1,066             2        -1,064
STAT...session cursor cache hi             1         4,998         4,997
STAT...table scans (short tabl         5,000             1        -4,999
STAT...parse count (total)            10,003         5,004        -4,999
LATCH.session idle bit                 5,003             3        -5,000
LATCH.session allocation               5,003             3        -5,000
STAT...execute count                  10,003         5,003        -5,000
STAT...opened cursors cumulati        10,003         5,003        -5,000
STAT...parse count (hard)             10,001             5        -9,996
STAT...CCursor + sql area evic        10,000             1        -9,999
STAT...enqueue releases               10,008             7       -10,001
STAT...enqueue requests               10,009             7       -10,002
STAT...calls to get snapshot s        20,005         5,006       -14,999
STAT...calls to kcmgcs                20,028            35       -19,993
STAT...consistent gets pin (fa        20,013            17       -19,996
LATCH.call allocation                 20,002             6       -19,996
STAT...consistent gets from ca        20,014            18       -19,996
STAT...consistent gets                20,014            18       -19,996
STAT...consistent gets pin            20,013            17       -19,996
LATCH.simulator hash latch            20,014            11       -20,003
STAT...session logical reads          20,080            75       -20,005
LATCH.shared pool simulator           20,046             5       -20,041
LATCH.enqueue hash chains             20,343            15       -20,328
STAT...recursive calls                40,015        15,018       -24,997
LATCH.cache buffers chains            40,480           294       -40,186
STAT...session pga memory max        131,072        65,536       -65,536
STAT...session pga memory            131,072        65,536       -65,536
LATCH.row cache objects              165,209           139      -165,070
STAT...session uga memory max        219,000             0      -219,000
LATCH.shared pool                    265,108           152      -264,956
STAT...logical read bytes from   164,495,360       614,400  -163,880,960

Run 1 latches total compared to run 2 -- difference and percentage
         Run 1         Run 2          Diff       Pct
       562,092           864      -561,228  2,466.67%

PL/SQL procedure successfully completed.

Utiliser PL/SQL pour améliorer l'évolutivité

Certaines fonctionnalités PL/SQL peuvent vous aider à améliorer l'évolutivité des applications.

Réduction de l'analyse par PL/SQL

Le langage PL/SQL, optimisé pour l'accès à la base de données, met en cache silencieusement les instructions. Dans le langage PL/SQL, lorsque vous fermez un curseur, celui-ci se ferme de votre point de vue, c'est-à-dire que vous ne pouvez pas l'utiliser là où un curseur ouvert est requis, mais le langage PL/SQL garde réellement le curseur ouvert et met en cache son instruction.

Si vous utilisez à nouveau l'instruction mise en cache, le code PL/SQL utilise le même curseur, ce qui évite une analyse. (Le langage PL/SQL ferme les instructions mises en mémoire cache si nécessaire. Par exemple, si votre programme doit ouvrir un autre curseur mais que cela dépasse le paramètre init.ora OPEN_CURSORS.)

Le langage PL/SQL ne peut mettre en cache que les instructions SQL qui ne peuvent pas être modifiées lors de l'exécution.

A propos de l'instruction EXECUTE IMMEDIATE

L'instruction EXECUTE IMMEDIATE génère et exécute une instruction SQL dynamique en une seule opération.

La syntaxe de base de l'instruction EXECUTE IMMEDIATE est la suivante :

EXECUTE IMMEDIATE sql_statement

sql_statement est une chaîne qui représente une instruction SQL. Si l'instruction sql_statement a la même valeur à chaque exécution de l'instruction EXECUTE IMMEDIATE, PL/SQL peut mettre en cache l'instruction EXECUTE IMMEDIATE. Si l'instruction sql_statement peut être différente à chaque exécution de l'instruction EXECUTE IMMEDIATE, PL/SQL ne peut pas mettre en cache l'instruction EXECUTE IMMEDIATE.

Voir aussi :

À propos de OPEN FOR Statements

L'instruction OPEN FOR présente la syntaxe de base suivante.

La syntaxe de base de l'instruction OPEN FOR est la suivante :

OPEN cursor_variable FOR query

Votre application peut ouvrir la variable de curseur pour plusieurs requêtes différentes avant de la fermer. Etant donné que le code PL/SQL ne peut pas déterminer le nombre de requêtes différentes avant l'exécution, le code PL/SQL ne peut pas mettre en cache l'instruction OPEN FOR.

Si vous n'avez pas besoin d'utiliser de variable de curseur, utilisez un curseur déclaré pour améliorer les performances et faciliter la programmation. Pour plus d'informations, reportez-vous au Guide de développement Oracle Database.

Voir aussi :

A propos du package DBMS_SQL

Le package DBMS_SQL est une API permettant de créer, d'exécuter et de décrire des instructions SQL dynamiques. Vous devez utiliser le package DBMS_SQL au lieu de l'instruction EXECUTE IMMEDIATE si le compilateur PL/SQL ne peut pas déterminer au moment de la compilation le nombre ou les types de variables hôte de sortie (sélectionner des éléments de liste) ou de variables attachées d'entrée.

Le package DBMS_SQL est une API permettant de créer, d'exécuter et de décrire des instructions SQL dynamiques. L'utilisation du package DBMS_SQL demande plus d'efforts que l'utilisation de l'instruction EXECUTE IMMEDIATE, mais vous devez utiliser le package DBMS_SQL si le compilateur PL/SQL ne peut pas déterminer au moment de la compilation le nombre ou les types de variables hôte de sortie (sélectionner des éléments de liste) ou de variables attachées d'entrée.

Voir aussi :

A propos du langage SQL en masse

Le langage SQL en masse réduit le nombre d'allers-retours entre PL/SQL et SQL, ce qui permet d'utiliser moins de ressources.

Sans le langage SQL en masse, vous extrayez une ligne à la fois de la base de données (SQL), la traitez (PL/SQL) et la renvoyez à la base de données (SQL). Avec le langage SQL en masse, vous extrayez un ensemble de lignes de la base de données, traitez l'ensemble de lignes, puis renvoyez l'ensemble à la base de données.

Oracle recommande d'utiliser le langage SQL en masse lorsque vous extrayez plusieurs lignes de la base de données et que vous les renvoyez à la base de données, comme dans l'Example 8-2. Vous n'avez pas besoin de SQL en masse si vous extrayez plusieurs lignes mais que vous ne les renvoyez pas ; par exemple :

FOR x IN (SELECT * FROM t WHERE ... )  -- Retrieve row set (implicit array fetch)
  LOOP
    DBMS_OUTPUT.PUT_LINE(t.x);          -- Process rows but do not return them
  END LOOP;

L'exemple 8-2 parcourt une table t avec une colonne object_name, extrait des ensembles de 100 lignes, les traite et les renvoie à la base de données. (La limitation de l'instruction FETCH en masse à 100 lignes nécessite un curseur explicite.)

L'exemple 8-3 effectue le même travail que l'exemple 8-2, sans instruction SQL en masse.

Comme le montrent ces rapports TKPROF pour l'Example 8-2 et l'Example 8-3, l'utilisation de SQL en masse pour ce travail utilise près de 50 % de temps CPU en moins :

SELECT ROWID RID, OBJECT_NAME FROM T T_BULK

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      721      0.17       0.17          0      22582          0       71825
********************************************************************************
UPDATE T SET OBJECT_NAME = :B1 WHERE ROWID = :B2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    719     12.83      13.77          0      71853      74185       71825
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      720     12.83      13.77          0      71853      74185       71825


SELECT ROWID RID, OBJECT_NAME FROM T T_SLOW_BY_SLOW

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      721      0.17       0.17          0      22582          0       71825
********************************************************************************
UPDATE T SET OBJECT_NAME = :B2 WHERE ROWID = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  71824     21.25      22.25          0      71836      73950       71824
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    71825     21.25      22.25          0      71836      73950       71824

Toutefois, l'utilisation d'instructions SQL en masse pour ce travail utilise plus de temps CPU et plus de code que l'utilisation d'une seule instruction SQL, comme le montre cet état TKPROF :

UPDATE T SET OBJECT_NAME = SUBSTR(OBJECT_NAME,2) || SUBSTR(OBJECT_NAME,1,1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.30       1.44          0       2166      75736       71825
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.30       1.44          0       2166      75736       71825

Exemple 8-2 SQL en masse

CREATE OR REPLACE PROCEDURE bulk AS
  TYPE ridArray IS TABLE OF ROWID;
  TYPE onameArray IS TABLE OF t.object_name%TYPE;

  CURSOR c is SELECT ROWID rid, object_name  -- explicit cursor
              FROM t t_bulk;

  l_rids    ridArray;
  l_onames  onameArray;
  N         NUMBER := 100;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT
    INTO l_rids, l_onames LIMIT N;   -- retrieve N rows from t

    FOR i in 1 .. l_rids.COUNT
      LOOP                           -- process N rows
        l_onames(i) := substr(l_onames(i),2) || substr(l_onames(i),1,1);
      END LOOP;

      FORALL i in 1 .. l_rids.count  -- return processed rows to t
        UPDATE t
        SET object_name = l_onames(i)
        WHERE ROWID = l_rids(i);
        EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
END;
/

Exemple 8-3 sans instructions SQL en masse

CREATE OR REPLACE PROCEDURE slow_by_slow AS
BEGIN
  FOR x IN (SELECT rowid rid, object_name FROM t t_slow_by_slow)
    LOOP
      x.object_name := substr(x.object_name,2) || substr(x.object_name,1,1);

      UPDATE t
      SET object_name = x.object_name
      WHERE rowid = x.rid;
    END LOOP;
END;

Voir aussi :

A propos de la simultanéité et de l'évolutivité

Concurrency est l'exécution simultanée de plusieurs transactions. Plus votre application gère la simultanéité d'accès aux données, plus elle est évolutive. Une application évolutive peut traiter une charge globale plus importante avec une augmentation proportionnelle de l'utilisation des ressources système.

Concurrency est l'exécution simultanée de plusieurs transactions. Les relevés des transactions simultanées peuvent mettre à jour les mêmes données. Plus votre application gère la simultanéité d'accès aux données, plus elle est évolutive. Une application évolutive peut traiter une charge globale plus importante avec une augmentation proportionnelle de l'utilisation des ressources système. Par exemple, si vous doublez sa charge globale, une application évolutive utilise deux fois plus de ressources système.

Les transactions simultanées doivent produire des résultats significatifs et cohérents. Par conséquent, une base de données multiutilisateur doit fournir les éléments suivants :

Oracle Database maintient la cohérence des données à l'aide d'un modèle de cohérence multiversion et de divers types de verrous et niveaux d'isolement des transactions. Pour une présentation du mécanisme de verrouillage d'Oracle Database, reportez-vous à Concepts relatifs à Oracle Database. Pour obtenir une présentation des niveaux d'isolement des transactions Oracle Database, reportez-vous à Concepts Oracle Database.

Pour décrire un comportement de transaction cohérent lorsque les transactions sont exécutées simultanément, les chercheurs en base de données ont défini une catégorie d'isolement de transaction appelée sérialisable. Une transaction sérialisable fonctionne dans un environnement qui semble être une base de données monoutilisateur. Les transactions sérialisables sont souhaitables dans des cas spécifiques, mais pour 99 % de la charge de travail, l'isolement validé en lecture est parfait.

Oracle Database propose des fonctionnalités qui améliorent la simultanéité et l'évolutivité, telles que les séquences, les verrous internes (latches), les lectures et écritures non bloquantes et les instructions SQL partagées.

Voir aussi : Concepts Oracle Database pour plus d'informations sur la simultanéité et la cohérence des données

A propos des séquences et de la simultanéité

Les séquences éliminent la sérialisation, ce qui améliore la simultanéité et l'évolutivité de votre application.

Une séquence est un objet de schéma à partir duquel plusieurs utilisateurs peuvent générer des entiers uniques, ce qui est très utile lorsque vous avez besoin de clés primaires uniques.

Sans séquences, des valeurs de clé primaire uniques doivent être générées par programmation. Un utilisateur obtient une nouvelle valeur de clé primaire en sélectionnant la dernière valeur produite et en l'incrémentant. Cette technique nécessite un verrouillage pendant la transaction et oblige plusieurs utilisateurs à attendre la prochaine valeur de clé primaire, c'est-à-dire la sérialisation des transactions. Les séquences éliminent la sérialisation, ce qui améliore la simultanéité et l'évolutivité de votre application.

Voir aussi :

A propos des verrous internes et de la concomitance

Une augmentation des verrous internes implique davantage d'attentes basées sur la simultanéité, et donc une diminution de l'évolutivité.

Un verrou est un mécanisme de sérialisation simple de bas niveau qui coordonne l'accès multiutilisateur aux structures de données partagées. Les verrous internes (latches) protègent les ressources de mémoire partagée contre l'altération lorsque plusieurs processus y accèdent.

Une augmentation des verrous internes implique davantage d'attentes basées sur la simultanéité, et donc une diminution de l'évolutivité. Si vous pouvez utiliser une approche qui s'exécute un peu plus rapidement pendant le développement ou une approche qui utilise moins de verrous internes, utilisez cette dernière.

Voir aussi :

A propos des lectures et écritures non bloquantes et de la simultanéité d'accès aux données

Dans Oracle Database, les lectures et écritures non bloquantes permettent aux interrogations de s'exécuter simultanément avec les modifications apportées aux données qu'elles lisent, sans blocage ni arrêt. Les lectures et écritures non bloquantes permettent à une session de lire des données alors qu'une autre session modifie ces données.

A propos des instructions SQL partagées et de la simultanéité d'accès aux données

Oracle Database compile une instruction SQL dans un objet exécutable une seule fois, puis d'autres sessions peuvent réutiliser l'objet tant qu'il existe. Cette fonctionnalité d'Oracle Database, appelée SQL partagé, permet à la base de données d'effectuer des opérations très gourmandes en ressources : compiler et optimiser les instructions SQL une seule fois, au lieu de chaque fois qu'une session utilise la même instruction SQL.

Voir aussi : Concepts Oracle Database pour plus d'informations sur les instructions SQL partagées

Limitation du nombre de sessions simultanées

Plus vous avez de sessions simultanées, plus vous avez d'attentes basées sur la simultanéité et plus votre temps de réponse est lent.

Si votre ordinateur dispose de n coeurs de processeur, n sessions au maximum peuvent être actives simultanément. Chaque session "concurrente" supplémentaire doit attendre qu'un coeur de CPU soit disponible pour pouvoir devenir actif. Si certaines sessions en attente n'attendent que des E/S, l'augmentation du nombre de sessions simultanées à un peu plus de n peut légèrement améliorer les performances d'exécution. Toutefois, l'augmentation excessive du nombre de sessions simultanées réduit considérablement les performances d'exécution.

Le paramètre d'initialisation SESSIONS détermine le nombre maximum d'utilisateurs simultanés dans le système. Pour plus de détails, reportez-vous à Référence Oracle Database.

Voir aussi : http://www.youtube.com/watch?v=xNDnVOCdvQ0 pour une vidéo qui montre l'effet de la réduction du nombre de sessions simultanées sur un ordinateur avec 12 coeurs de processeur de milliers à 96

Comparaison des techniques de programmation et des statistiques d'exécution

L'outil Runstats vous permet de comparer les performances de deux techniques de programmation pour voir laquelle est la meilleure.

À propos de Runstats

L'outil Runstats vous permet de comparer les performances de deux techniques de programmation pour voir laquelle est la meilleure.

Les statistiques d'exécution mesurent les valeurs suivantes :

Parmi les mesures précédentes, la plus importante est le verrouillage (reportez-vous à la section "A propos des verrous internes et de la simultanéité").

Voir aussi : Exemple 8-1, qui utilise des statistiques d'exécution

Configurer les statistiques d'exécution

L'outil Runstats est implémenté en tant que package qui utilise une vue et une table temporaire.

Remarque : Pour l'étape 1 de la procédure suivante, vous devez disposer du privilège SELECT sur les vues de performances dynamiques V$STATNAME, V$MYSTAT et V$LATCH. Si vous ne pouvez pas obtenir ce privilège, demandez à une personne disposant de ce privilège de créer la vue à l'étape 1 et d'y accorder le privilège SELECT.

Etapes de configuration de l'outil Runstats :

  1. Créez la vue utilisée par les statistiques d'exécution :

     CREATE OR REPLACE VIEW stats
 AS SELECT 'STAT...' || a.name name, b.value
 FROM V$STATNAME a, V$MYSTAT b
 WHERE a.statistic# = b.statistic#
 UNION ALL
 SELECT 'LATCH.' || name, gets
 FROM V$LATCH;

  2. Créez la table temporaire utilisée par Runstats :

     DROP TABLE run_stats;

 CREATE GLOBAL TEMPORARY TABLE run_stats
 ( runid VARCHAR2(15),
   name VARCHAR2(80),
   value INT )
 ON COMMIT PRESERVE ROWS;

  3. Créez la spécification du package suivante :

     CREATE OR REPLACE PACKAGE runstats_pkg
 AS
   PROCEDURE rs_start;
   PROCEDURE rs_middle;
   PROCEDURE rs_stop( p_difference_threshold IN NUMBER DEFAULT 0 );
 end;
 /

    Le paramètre p_difference_threshold contrôle la quantité de statistiques et de données de verrouillage affichées par Runstats. Les statistiques d'exécution affichent les données uniquement lorsque la différence entre les deux techniques est supérieure à p_difference_threshold. Par défaut, Runstats affiche toutes les données.

  4. Créez le corps de package suivant :

     CREATE OR REPLACE PACKAGE BODY runstats_pkg
 AS
   g_start NUMBER;
   g_run1 NUMBER;
   g_run2 NUMBER;

   PROCEDURE rs_start
   IS
   BEGIN
     DELETE FROM run_stats;

     INSERT INTO run_stats
     SELECT 'before', stats.* FROM stats;

     g_start := DBMS_UTILITY.GET_TIME;
   END rs_start;

   PROCEDURE rs_middle
   IS
   BEGIN
     g_run1 := (DBMS_UTILITY.GET_TIME - g_start);

     INSERT INTO run_stats
     SELECT 'after 1', stats.* FROM stats;

     g_start := DBMS_UTILITY.GET_TIME;
   END rs_middle;

   PROCEDURE rs_stop( p_difference_threshold IN NUMBER DEFAULT 0 )
   IS
   BEGIN
     g_run2 := (DBMS_UTILITY.GET_TIME - g_start);

     DBMS_OUTPUT.PUT_LINE
       ('Run 1 ran in ' || g_run1 || ' hsec');

     DBMS_OUTPUT.PUT_LINE
       ('Run 2 ran in ' || g_run2 || ' hsec');

     DBMS_OUTPUT.PUT_LINE
       ('Run 1 ran in ' || round(g_run1/g_run2*100, 2) || '% of the time of run 2');

     DBMS_OUTPUT.PUT_LINE( CHR(9) );

     INSERT INTO run_stats
     SELECT 'after 2', stats.* FROM stats;

     DBMS_OUTPUT.PUT_LINE
       ( RPAD( 'Name', 30 ) ||
         LPAD( 'Run 1', 14) ||

         LPAD( 'Run 2', 14) ||

         LPAD( 'Difference', 14)
       );

     FOR x IN
     ( SELECT RPAD( a.name, 30 ) ||
             TO_CHAR( b.value - a.value, '9,999,999,999' ) ||
             TO_CHAR( c.value - b.value, '9,999,999,999' ) ||
             TO_CHAR( ( (c.value - b.value) - (b.value - a.value)),
               '9,999,999,999' ) data
       FROM run_stats a, run_stats b, run_stats c
       WHERE a.name = b.name
         AND b.name = c.name
         AND a.runid = 'before'
         AND b.runid = 'after 1'
         AND c.runid = 'after 2'
         AND (c.value - a.value) > 0
         AND abs((c.value - b.value) - (b.value - a.value)) >
           p_difference_threshold
     ORDER BY ABS((c.value - b.value) - (b.value - a.value))
     ) LOOP
         DBMS_OUTPUT.PUT_LINE( x.data );
     END LOOP;

     DBMS_OUTPUT.PUT_LINE( CHR(9) );

     DBMS_OUTPUT.PUT_LINE(
       'Run 1 latches total compared to run 2 -- difference and percentage' );

     DBMS_OUTPUT.PUT_LINE
       ( LPAD( 'Run 1', 14) ||

         LPAD( 'Run 2', 14) ||

         LPAD( 'Diff', 14) ||

         LPAD( 'Pct', 10)
       );

     FOR x IN
     ( SELECT TO_CHAR( run1, '9,999,999,999' ) ||
             TO_CHAR( run2, '9,999,999,999' ) ||
             TO_CHAR( diff, '9,999,999,999' ) ||
             TO_CHAR( ROUND( g_run1/g_run2*100, 2), '99,999.99' ) || '%' data
       FROM ( SELECT SUM (b.value - a.value) run1,
                     SUM (c.value - b.value) run2,
                     SUM ( (c.value - b.value) - (b.value - a.value)) diff
             FROM run_stats a, run_stats b, run_stats c
             WHERE a.name = b.name
               AND b.name = c.name
               AND a.runid = 'before'
               AND b.runid = 'after 1'
               AND c.runid = 'after 2'
               AND a.name like 'LATCH%'
           )
     ) LOOP
         DBMS_OUTPUT.PUT_LINE( x.data );
     END LOOP;

   END rs_stop;

 END;
 /

Voir aussi :

Utiliser Runstats

Cette rubrique fournit la syntaxe d'utilisation de l'outil Runstats.

Pour utiliser Runstats pour comparer deux techniques de programmation, appelez les procédures runstats_pkg à partir d'un bloc anonyme, en utilisant la syntaxe suivante :

[ DECLARE local_declarations ]
BEGIN
  runstats_pkg.rs_start;
  code_for_first_technique
  runstats_pkg.rs_middle;
  code_for_second_technique
  runstats_pkg.rs_stop(n);
END;
/

Voir aussi : Exemple 8-1, qui utilise des statistiques d'exécution

Techniques de traitement des données et des performances réelles

Dans les applications de base de données d'un environnement de data warehouse, il est courant d'interroger ou de modifier un ensemble de données volumineux. Le problème pour les développeurs d'applications est de savoir comment obtenir des performances élevées lors du traitement d'ensembles de données volumineux.

Les techniques de traitement se divisent en deux catégories : itérative et set-based. Au fil des années de tests, le groupe Real-World Performance a découvert que les techniques de traitement basées sur un ensemble de données améliorent considérablement les performances des applications de base de données qui traitent des ensembles de données volumineux.

Cette rubrique comprend les sous-rubriques principales suivantes :

A propos du traitement itératif des données

Dans le traitement itératif, les applications utilisent une logique conditionnelle pour effectuer une boucle sur un ensemble de lignes.

En règle générale, bien que pas nécessairement, le traitement itératif utilise un modèle client/serveur comme suit :

  1. Transférez un groupe de lignes du serveur de base de données vers l'application client.

  2. Traitez le groupe dans l'application client.

  3. Transférez le groupe traité vers le serveur de base de données.

Vous pouvez implémenter des algorithmes itératifs à l'aide de trois techniques principales : le traitement ligne par ligne, le traitement de tableau et le parallélisme manuel.

Traitement itératif : ligne par ligne

Dans le traitement ligne par ligne, un processus unique effectue une boucle dans un ensemble de données et opère sur une seule ligne à la fois. Dans une implémentation standard, l'application extrait chaque ligne de la base de données, la traite au niveau intermédiaire, puis la renvoie à la base de données, qui exécute des opérations LMD et effectue des validations (commit).

Supposons que votre besoin fonctionnel consiste à interroger une table externe nommée ext_scan_events, puis à insérer ses lignes dans une table intermédiaire organisée en tas nommée stage1_scan_events. Le bloc PL/SQL suivant utilise une technique ligne par ligne pour répondre à cette exigence :

declare
  cursor c is select s.* from ext_scan_events s;
  r c%rowtype;
begin
  open c;
  loop
    fetch c into r;
    exit when c%notfound;
    insert into stage1_scan_events d values r;
    commit;
  end loop;
  close c;
end;

La technique ligne par ligne présente les avantages suivants :

La technique ligne par ligne présente les inconvénients suivants :

Voir aussi : RWP #7 Traitement basé sur un ensemble

Traitement itératif : tableaux

Le traitement de tableau est identique au traitement ligne par ligne, sauf qu'il traite un groupe de lignes dans chaque itération plutôt qu'une seule ligne.

Supposons que votre besoin fonctionnel soit le même que dans l'exemple X-X : interrogez une table externe nommée ext_scan_events, puis insérez ses lignes dans une table intermédiaire organisée en tas nommée stage1_scan_events. Le bloc PL/SQL suivant utilise une technique de tableau pour répondre à cette exigence :

declare
  cursor c is select s.* from ext_scan_events s;
  type t is table of c%rowtype index by binary_integer;
  a t;
  rows binary_integer := 0;
begin
  open c;
  loop
    fetch c bulk collect into a limit array_size;
    exit when a.count = 0;
    forall i in 1..a.count
      insert into stage1_scan_events d values a(i);
    commit;
  end loop;
  close c;
end;

Le code précédent diffère du code équivalent ligne par ligne en utilisant un opérateur BULK COLLECT dans FETCH STATEMENT, qui est limité par la valeur array_size de type PLS_INTEGER. Par exemple, si array_size est défini sur 100, l'application extrait les lignes par groupes de 100.

La technique de tableau présente les avantages suivants par rapport à la technique ligne par ligne :

Les inconvénients de cette technique sont les mêmes que pour le traitement ligne par ligne. Le temps de traitement peut être inacceptable pour les jeux de données volumineux. En outre, l'application doit s'exécuter en série sur un seul coeur de processeur et ne peut donc pas exploiter le parallélisme natif d'Oracle Database.

Traitement itératif : parallélisme manuel

Le parallélisme manuel utilise le même algorithme itératif que le traitement ligne par ligne et tableau, mais permet à plusieurs processus serveur de diviser le travail et de l'exécuter en parallèle.

Supposons que l'exigence fonctionnelle est la même que dans les exemples ligne par ligne et tableau. Les principales différences sont les suivantes :

Le code PL/SQL suivant utilise le parallélisme manuel :

declare
  sqlstmt varchar2(1024) := q'[
-- BEGIN embedded anonymous block
  cursor c is select s.* from ext_scan_events_${thr} s;
  type t is table of c%rowtype index by binary_integer;
  a t;
  rows binary_integer := 0;
begin
  for r in (select ext_file_name from ext_scan_events_dets where ora_hash(file_seq_nbr,${thrs}) = ${thr})
  loop
    execute immediate
      'alter table ext_scan_events_${thr} location' || '(' || r.ext_file_name || ')';
    open c;
    loop
      fetch c bulk collect into a limit ${array_size};
      exit when a.count = 0;
      forall i in 1..a.count
        insert into stage1_scan_events d values a(i);
      commit;
--  demo instrumentation
      rows := rows + a.count; if rows > 1e3 then exit when not sd_control.p_progress('loading','userdefined',rows); rows := 0; end if;
    end loop;
    close c;
  end loop;
end;
-- END   embedded anonymous block
]';

begin
  sqlstmt := replace(sqlstmt, '${array_size}', to_char(array_size));
  sqlstmt := replace(sqlstmt, '${thr}', thr);
  sqlstmt := replace(sqlstmt, '${thrs}', thrs);
  execute immediate sqlstmt;
end;

La fonction ORA_HASH divise la table ext_scan_events_dets en 32 buckets répartis équitablement, puis l'instruction SELECT extrait les noms de fichier du bucket 0. Pour chaque nom de fichier dans le bucket, le programme définit l'emplacement de la table externe sur ce nom de fichier. Le programme utilise ensuite le traitement par lots pour interroger la table externe, l'insérer dans la table intermédiaire, puis la valider.

Pendant l'exécution du travail 1, les 31 autres travaux Oracle Scheduler s'exécutent en parallèle. Ainsi, chaque travail lit simultanément un sous-ensemble différent des fichiers d'événements d'analyse et insère les enregistrements de son sous-ensemble dans la même table intermédiaire.

La technique de parallélisme manuel présente les avantages suivants par rapport aux techniques itératives alternatives :

La technique de parallélisme manuel présente les inconvénients suivants :

Voir aussi : RWP #8 : Traitement parallèle basé sur un ensemble

A propos du traitement basé sur un ensemble

Le traitement basé sur un ensemble est une technique SQL qui traite un ensemble de données dans la base.

Dans un modèle basé sur un ensemble, l'instruction SQL définit le résultat et permet à la base de données de déterminer le moyen le plus efficace de l'obtenir. En revanche, les algorithmes itératifs utilisent une logique conditionnelle pour extraire chaque ligne ou groupe de lignes de la base de données vers l'application client, traiter les données sur le client, puis renvoyer les données à la base de données. Le traitement basé sur un ensemble élimine la surcharge de l'API réseau et de la base de données car les données ne quittent jamais la base de données.

Supposons la même exigence fonctionnelle que dans les exemples précédents. Les instructions SQL suivantes répondent à cette exigence à l'aide d'un algorithme basé sur un ensemble :

alter session enable parallel dml;
insert /*+ APPEND */ into stage1_scan_events d
  select s.* from ext_scan_events s;
commit;

Etant donné que l'instruction INSERT contient une sous-interrogation de la table ext_scan_events, une instruction SQL unique lit et écrit toutes les lignes. En outre, l'application exécute une instruction COMMIT unique une fois que la base de données a inséré toutes les lignes. En revanche, les applications itératives exécutent une instruction COMMIT après l'insertion de chaque ligne ou de chaque groupe de lignes.

La technique basée sur un ensemble présente des avantages significatifs par rapport aux techniques itératives :

Le traitement basé sur un ensemble présente certains inconvénients potentiels :

Voir aussi : RWP #7 Set-Based Processing, RWP #8 : Set-Based Parallel Processing, RWP #9 : Set-Based Processing–Data Deduplication, RWP #10 : Set-Based Processing–Data Transformations, et RWP #11 : Set-Based Processing–Data Aggregation