Création d'applications évolutives

Concevez vos applications pour utiliser les mêmes ressources, indépendamment des populations d'utilisateurs et des volumes de données, et non pour surcharger les ressources système.

À propos des applications évolutives

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

Une application évolutive peut traiter une charge de travail 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 indisponible épuise une ressource système. Par conséquent, si vous augmentez la charge de travail de l'application, aucun débit supplémentaire n'est possible. Les applications é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, indépendamment des populations d'utilisateurs et des volumes de données, et non pour surcharger les ressources système.

Utiliser des variables de liaison pour améliorer l'évolutivité

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

Une variable de liaison est un paramètre fictif dans un énoncé SQL qui doit être remplacé par une valeur ou une adresse de valeur valide pour que l'énoncé puisse s'exécuter correctement. En utilisant des variables attachées, vous pouvez écrire une instruction SQL qui accepte les entrées ou les paramètres lors de l'exécution.

Tout comme un sous-programme peut avoir des paramètres, dont les valeurs sont fournies par l'appelant, une instruction SQL peut avoir des paramètres fictifs de variable attachée, dont les valeurs (appelées variables attachées) sont fournies au moment de l'exécution. Tout comme un sous-programme est compilé une fois, puis exécuté plusieurs fois avec des paramètres différents, une instruction SQL avec des paramètres fictifs de variable attachée est analysée une fois, puis analysée avec des variables attachées différentes.

Une analyse complète, qui inclut l'optimisation et la génération de la source de rangées, est une opération très gourmande en UC. Une analyse flexible, qui ignore l'optimisation et la génération du row source et passe directement à l'exécution, est généralement beaucoup plus rapide qu'une analyse complète du même énoncé. (Pour un aperçu du traitement SQL, qui inclut la différence entre une analyse complète et une analyse partielle, voir Concepts d'Oracle Database.)

Non seulement une analyse complète est une opération nécessitant beaucoup d'UC, mais il s'agit également d'une opération impossible à mettre à l'échelle, car elle ne peut pas être effectuée simultanément avec de nombreuses autres opérations. Pour plus d'informations sur l'accès simultané et l'évolutivité, voir "À propos de l'accès simultané et de l'évolutivité".

Exemple 8-1 affiche la différence de performance entre une interrogation sans variable de liaison et une interrogation sémantiquement équivalente avec une variable de liaison. Le premier est plus lent et utilise beaucoup plus de verrous (pour plus d'informations sur l'incidence des verrous sur l'évolutivité, voir). Pour collecter et afficher des statistiques de performance, l'exemple utilise l'outil Runstats, décrit dans "Comparaison des techniques de programmation avec Runstats".

Note :

Note :

Bien que l'analyse partielle soit plus efficace que l'analyse complète, le coût de l'analyse partielle d'une instruction est souvent très élevé. Pour optimiser l'efficacité et l'évolutivité de votre application, réduisez les analyses. Le moyen le plus simple de réduire l'analyse consiste à utiliser du code PL/SQL.

Exemple 8-1 : La variable de liaison 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 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.

Comment PL/SQL réduit l'analyse

Le langage PL/SQL, optimisé pour l'accès à la base de données, met en cache les instructions en mode silencieux. Dans le langage PL/SQL, lorsque vous fermez un curseur, celui-ci se ferme dans votre perspective, c'est-à-dire que vous ne pouvez pas l'utiliser lorsqu'un curseur ouvert est requis, mais le langage PL/SQL le maintient ouvert et met en cache son instruction.

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

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

À propos de la déclaration EXECUTE IMMEDIATE

L'instruction EXECUTE IMMEDIATE crée 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 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 sql_statement peut être différent à 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 a 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 cursor_variable pour plusieurs requêtes différentes avant de la fermer. Comme PL/SQL ne peut pas déterminer le nombre d'interrogations différentes avant l'exécution, PL/SQL ne peut pas mettre en cache l'instruction OPEN FOR.

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

Voir aussi :

À propos du paquetage 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 nécessite 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 :

À propos des énoncés SQL en masse

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

Sans SQL en masse, vous extrayez une ligne à la fois de la base de données (SQL), la traitez (PL/SQL) et la retournez à 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 l'énoncé SQL en masse lorsque vous extrayez plusieurs rangées de la base de données et les retournez à la base de données, comme dans Exemple 8-2. Vous n'avez pas besoin d'instructions SQL en masse si vous extrayez plusieurs lignes mais que vous ne les retournez 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;

Exemple 8-2 effectue une boucle sur une table t avec un nom_objet de colonne, en extrayant des jeux de 100 rangées, en les traitant et en les retournant à la base de données. (Limiter l'instruction FETCH en masse à 100 lignes nécessite un curseur explicite.)

Exemple 8-3 effectue la même tâche que Exemple 8-2, sans SQL en masse.

Comme le montrent les rapports TKPROF pour Exemple 8-2 et Exemple 8-3, l'utilisation d'énoncés SQL en masse pour cette tâche utilise presque 50 % moins de temps UC :

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'énoncés SQL en masse pour cette tâche utilise plus de temps d'UC et plus de code que l'utilisation d'un seul énoncé SQL, comme le montre ce rapport 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 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 :

À propos des accès simultanés et de l'évolutivité

Devise est l'exécution simultanée de plusieurs transactions. Plus votre application gère les accès simultanés, plus elle est évolutive. Une application évolutive peut traiter une charge de travail plus importante avec une augmentation proportionnelle de l'utilisation des ressources système.

Devise est l'exécution simultanée de plusieurs transactions. Les relevés des transactions concurrentes peuvent mettre à jour les mêmes données. Plus votre application gère les accès simultanés, plus elle est évolutive. Une application évolutive peut traiter une charge de travail 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 concurrentes 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 assure la cohérence des données à l'aide d'un modèle à versions multiples et de différents types de verrouillage et niveaux d'isolement des transactions. Pour un aperçu du mécanisme de verrouillage d'Oracle Database, voir Concepts relatifs à Oracle Database. Pour un aperçu des niveaux d'isolement des transactions Oracle Database, voir Concepts relatifs à Oracle Database.

Pour décrire le comportement cohérent des transactions exécutées simultanément, les chercheurs de base de données ont défini une catégorie d'isolement des transactions appelée sérialisable. Une transaction sérialisable fonctionne dans un environnement qui semble être une base de données à utilisateur unique. 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 offre des fonctionnalités qui améliorent la simultanéité d'accès et l'évolutivité, telles que les séquences, les verrous internes (latches), les lectures et les écritures non bloquantes et le code SQL partagé.

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

À propos des séquences et des accès simultanés

Les séquences éliminent la sérialisation, améliorant ainsi 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 produites par programmation. Un utilisateur obtient une nouvelle valeur de clé primaire en sélectionnant la valeur produite la plus récente et en l'incrémentant. Cette technique nécessite un verrouillage pendant la transaction et entraîne l'attente par plusieurs utilisateurs de la prochaine valeur de clé primaire, c'est-à-dire la sérialisation des transactions. Les séquences éliminent la sérialisation, améliorant ainsi la simultanéité et l'évolutivité de votre application.

Voir aussi :

À propos des verrous et des accès simultanés

Une augmentation des verrous internes (latches) entraîne davantage d'attentes basées sur la simultanéité d'accès, et donc une diminution de l'évolutivité.

Un verrouillage 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 protègent les ressources de mémoire partagées contre la corruption lorsqu'elles sont accessibles par plusieurs processus.

Une augmentation des verrous internes (latches) entraîne davantage d'attentes basées sur la simultanéité d'accès, et donc une diminution de l'évolutivité. Si vous pouvez utiliser soit une approche qui s'exécute légèrement plus rapidement pendant le développement, soit une approche qui utilise moins de verrous internes, utilisez cette dernière.

Voir aussi :

À propos des lectures et des écritures non bloquantes et des accès simultanés

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

À propos du code SQL partagé et des accès simultanés

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 fonction Oracle Database, appelée SQL partagé, permet à la base de données de compiler et d'optimiser des énoncés SQL très gourmands en ressources une seule fois, au lieu de chaque fois qu'une session utilise le même énoncé SQL.

Voir aussi : Concepts relatifs à Oracle Database pour plus d'informations sur les énoncés SQL partagés

Limiter le nombre de sessions concurrentes

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

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

Le paramètre d'initialisation SESSIONS détermine le nombre maximal d'utilisateurs simultanés dans le système. Pour plus de détails, voir Informations de référence sur 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 concurrentes sur un ordinateur avec 12 coeurs d'UC, passant de milliers à 96

Comparaison des techniques de programmation avec Runstats

L'outil Runstats vous permet de comparer les performances de deux techniques de programmation pour voir lesquelles sont les meilleures.

À propos de Runstats

L'outil Runstats vous permet de comparer les performances de deux techniques de programmation pour voir lesquelles sont les meilleures.

Runstats mesure les valeurs suivantes :

Parmi les mesures précédentes, la plus importante est le verrouillage (voir "À propos des verrous et des accès simultanés").

Voir aussi : Exemple 8-1, qui utilise Runstats

Définition des statistiques d'exécution

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

Note : Pour l'étape 1 de la procédure suivante, vous avez besoin du privilège SELECT sur les vues dynamiques du rendement V$STATNAME, V$MYSTAT et V$LATCH. Si vous ne pouvez pas obtenir ce privilège, demandez à quelqu'un qui a le privilège de créer la vue à l'étape 1 et de vous accorder le privilège SELECT dessus.

Étapes de configuration de l'outil Runstats :

  1. Créez la vue utilisée par Runstats :

     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 cette spécification de package :

     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. Runstats affiche les données uniquement lorsque la différence pour les deux techniques est supérieure à p_difference_threshold. Par défaut, Runstats affiche toutes les données.

  4. Créez le corps du 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 :

Utilisation de Runstats

Cette rubrique présente 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, à l'aide de 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 Runstats

Performances réelles et techniques de traitement des données

Une tâche courante dans les applications de base de données d'un environnement d'entrepôt de données consiste à interroger ou à modifier un jeu de données volumineux. Le problème pour les développeurs d'applications est de savoir comment atteindre des performances élevées lors du traitement de jeux de données volumineux.

Les techniques de traitement se divisent en deux catégories : itérative et basée sur des ensembles. Au fil des années de tests, le groupe Real-World Performance a découvert que les techniques de traitement basées sur un jeu de données offrent de meilleures performances pour les applications de base de données qui traitent des jeux de données volumineux.

Cette rubrique comprend les principaux sous-thèmes suivants :

À propos du traitement itératif des données

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

En général, 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 : Rangée par rangée

Dans le traitement ligne par ligne, un seul traitement effectue une boucle dans un jeu de données et s'exécute sur une seule ligne à la fois. Dans une implémentation classique, l'application extrait chaque ligne de la base de données, la traite au niveau intermédiaire, puis renvoie la ligne à la base de données, qui exécute les opérations LMD et les validations (commits).

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 "heaps" 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 : Traitement par jeu RWP #7

Traitement itératif : Tableaux

Le traitement des tableaux 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 "heaps" 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 ligne par ligne équivalent 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 réglé à 100, l'application extrait des rangées dans des groupes de 100.

La technique du 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. De plus, 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 le traitement de tableau, mais permet à plusieurs processus serveur de diviser le travail et de s'exécuter en parallèle.

Supposons que l'exigence fonctionnelle soit 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 seaux répartis uniformément, puis l'instruction SELECT extrait les noms de fichier du seau 0. Pour chaque nom de fichier dans le seau, le programme définit l'emplacement de la table externe à 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 valider.

Pendant l'exécution du travail 1, les 31 autres travaux Oracle Scheduler s'exécutent en parallèle. De cette façon, 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 du parallélisme manuel présente les inconvénients suivants :

Voir aussi : RWP #8 : Traitement parallèle basé sur des ensembles

À propos du traitement par jeu

Le traitement par jeu de données est une technique SQL qui traite un jeu de données dans la base de données.

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 par jeu élimine la surcharge liée à l'aller-retour réseau et à l'API de 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;

Comme l'énoncé INSERT contient une sous-interrogation de la table ext_scan_events, un énoncé SQL unique lit et écrit toutes les rangées. De plus, l'application exécute un COMMIT unique après que la base de données a inséré toutes les rangées. En revanche, les applications itératives exécutent un 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 par jeu de tables 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