Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 8 sur 8
  1. #1
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    65
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 65
    Points : 23
    Points
    23

    Par défaut Optimisation d'une requete MERGE via HINT d'index

    Bonjour,

    J'essaie d'optimiser une requête SQL qui est passé de 15 minutes à plus de 150 minutes.

    La requête update certains champs de la table T_CIBLE par les champs de la table T_SOURCE.
    La table T_CIBLE contient 4 millions de lignes et est indexer sur les champs ID_1 et ID_2.
    La table T_SOURCE 2 Millions de lignes.

    le merge est le suivant :

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    merge INTO T_CIBLE A USING
    (SELECT * FROM T_SOURCE WHERE CHAMPS_1='U') B
    ON (
    A.ID_1 = B.ID_1 AND
    A.ID_1 = B.ID_1)
    when matched then
    UPDATE
    SET A.CHAMPS_2 = B.CHAMPS_2,
    A.CHAMPS_3 = B.CHAMPS_3,
    A.CHAMPS_4 = B.CHAMPS_4,
    A.CHAMPS_5 = B.CHAMPS_5;
    Le plan d'exécution est celui ci :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
     
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation                 | Name             | Rows  | Bytes | Cost  |    TQ  |IN-OUT| PQ Distrib |
    -----------------------------------------------------------------------------------------------------------
    |   0 | MERGE STATEMENT           |                  |  2536K|  2704M| 42188 |        |      |            |
    |   1 |  MERGE                    | T_CIBLE          |       |       |       |        |      |            |
    |   2 |   PX COORDINATOR          |                  |       |       |       |        |      |            |
    |   3 |    PX SEND QC (RANDOM)    | :TQ10002         |  2536K|  1765M| 42188 |  Q1,02 | P->S | QC (RAND)  |
    |   4 |     VIEW                  |                  |       |       |       |  Q1,02 | PCWP |            |
    |   5 |      HASH JOIN BUFFERED   |                  |  2536K|  1765M| 42188 |  Q1,02 | PCWP |            |
    |   6 |       BUFFER SORT         |                  |       |       |       |  Q1,02 | PCWC |            |
    |   7 |        PX RECEIVE         |                  |  2536K|   459M|  7779 |  Q1,02 | PCWP |            |
    |   8 |         PX SEND HASH      | :TQ10000         |  2536K|   459M|  7779 |        | S->P | HASH       |
    |   9 |          TABLE ACCESS FULL| T_SOURCE         |  2536K|   459M|  7779 |        |      |            |
    |  10 |       PX RECEIVE          |                  |  4073K|  2097M| 12261 |  Q1,02 | PCWP |            |
    |  11 |        PX SEND HASH       | :TQ10001         |  4073K|  2097M| 12261 |  Q1,01 | P->P | HASH       |
    |  12 |         PX BLOCK ITERATOR |                  |  4073K|  2097M| 12261 |  Q1,01 | PCWC |            |
    |  13 |          TABLE ACCESS FULL| T_CIBLE          |  4073K|  2097M| 12261 |  Q1,01 | PCWP |            |
    -----------------------------------------------------------------------------------------------------------
    En forçant la lecture l'index de T_CIBLE, la requête mets toujours autant de temps mais l'explain plan semble beaucoup plus adéquat :

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    -----------------------------------------------------------------------------------
    | Id  | Operation                      | Name             | Rows  | Bytes | Cost  |
    -----------------------------------------------------------------------------------
    |   0 | MERGE STATEMENT                |                  |  2536K|  2704M|  3397K|
    |   1 |  MERGE                         | TB_CUST          |       |       |       |
    |   2 |   VIEW                         |                  |       |       |       |
    |   3 |    HASH JOIN                   |                  |  2536K|  1765M|  3397K|
    |   4 |     TABLE ACCESS FULL          | TI_BCCSTFIN_CUST |  2536K|   459M|  7779 |
    |   5 |     TABLE ACCESS BY INDEX ROWID| TB_CUST          |  4073K|  2097M|  3300K|
    |   6 |      INDEX FULL SCAN           | PK_TB_CUST       |  4073K|       |  4250 |
    -----------------------------------------------------------------------------------
    Auriez-vous une idée d'optimisation de cette requête ?

    Merci par avance !

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 066
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 066
    Points : 29 225
    Points
    29 225

    Par défaut

    Avez vous essayé avec un simple UPDATE ? parce que là je ne voit pas l'intérêt d'un merge !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    65
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 65
    Points : 23
    Points
    23

    Par défaut

    Merci pour ta réponse SQLPro !

    Dans ma tête, depuis la version 10G, un Merge était toujours plus efficace qu'un update.
    Mais en faisant comme tu m'as dit, voici le merge transformé en update :

    Code :
    1
    2
    3
    4
    UPDATE T_CIBLE A
    SET A.champs_1, A.champs_2,A.champs_3, A.champs_4,A.champs_5
    = (SELECT B.champs_1, B.champs_2,B.champs_3, B.champs_4,B.champs_5 FROM T_SOURCE B WHERE a.ID_1=b.ID_1 AND a.ID_2=b.ID_2) 
    WHERE  EXISTS (SELECT 1 FROM T_SOURCE B WHERE a.ID_1=b.ID_1 AND a.ID_2=b.ID_2);
    Et ce qui me donne comme Explain plan :


    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    --------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    --------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT         |                  |     1 |   268 | 19507   (5)| 00:05:52 |        |      |            |
    |   1 |  UPDATE                  | T_CIBLE          |       |       |            |          |        |      |            |
    |   2 |   PX COORDINATOR         |                  |       |       |            |          |        |      |            |
    |   3 |    PX SEND QC (RANDOM)   | :TQ10001         |     1 |   268 | 19507   (5)| 00:05:52 |  Q1,01 | P->S | QC (RAND)  |
    |*  4 |     HASH JOIN RIGHT SEMI |                  |     1 |   268 | 19507   (5)| 00:05:52 |  Q1,01 | PCWP |            |
    |   5 |      BUFFER SORT         |                  |       |       |            |          |  Q1,01 | PCWC |            |
    |   6 |       PX RECEIVE         |                  |  2536K|    48M|  7222   (2)| 00:02:10 |  Q1,01 | PCWP |            |
    |   7 |        PX SEND BROADCAST | :TQ10000         |  2536K|    48M|  7222   (2)| 00:02:10 |        | S->P | BROADCAST  |
    |   8 |         TABLE ACCESS FULL| T_SOURCE         |  2536K|    48M|  7222   (2)| 00:02:10 |        |      |            |
    |   9 |      PX BLOCK ITERATOR   |                  |  4073K|   963M| 12261   (6)| 00:03:41 |  Q1,01 | PCWC |            |
    |  10 |       TABLE ACCESS FULL  | T_CIBLE          |  4073K|   963M| 12261   (6)| 00:03:41 |  Q1,01 | PCWP |            |
    |  11 |   TABLE ACCESS FULL      | T_SOURCE         |  2536K|   459M|  7701   (8)| 00:02:19 |        |      |            |
    --------------------------------------------------------------------------------------------------------------------------
    .... et un temps d'update toujours aussi long .

  4. #4
    Expert Confirmé Sénior Avatar de mnitu
    Homme Profil pro Marius Nitu
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    4 476
    Détails du profil
    Informations personnelles :
    Nom : Homme Marius Nitu
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : octobre 2007
    Messages : 4 476
    Points : 8 903
    Points
    8 903

    Par défaut

    La première requête est exécutée en parallèle mais dans son ensemble le plan d’exécution est le même : merge via un hash join entre les deux tables accédées en full. Ce n’est pas forcement un mauvais plan.

    Vous dite que la requête est passée du 15 minute à 150 mais que est-ce que a changé entre les deux exécutions ? Avez-vous une idée ? Est vous certain que les enregistrements à mettre à jour ne sont pas verrouillé par un autre processus ?

    Pour l’instant essayez de tester la requête sans exécution en parallèle. Activez une trace sql étendue, laissez tourner la requête un certains temps et ensuite tuez le processus qui l’exécutez. Analysez le fichier de trace pour voir sur quoi elle attente.

    Une autre piste : parfois au lieu de mettre à jour une grosse table il est plus intéressant de créer une nouvelle table via create as select et de supprimer la ancienne table et la remplacer par la nouvelle. Du ce point de vue quelle est le temps d’exécution de la requête qui ramène les données des la jointure entre la table T_Source et T_Cible. Quelle est son plan d’exécution ?

  5. #5
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    65
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 65
    Points : 23
    Points
    23

    Par défaut

    Bonjour mnitu,
    Tout d'abord j m'excuse pour le retard de ma réponse. J'ai bien essayé d'avancer sur le sujet mais vraiment je sèche...

    Vous dite que la requête est passée du 15 minute à 150 mais que ? Avez-vous une idée ? Est vous certain que les enregistrements à mettre à jour ne sont pas verrouillé par un autre processus ?
    Justement rien du tout ... le temps d'exécution a explosé de lui même. J'ai vérifié pour savoir si le temps d'attente n'est pas trop long mais ... je ne sais pas comment faire.

    Activez une trace sql étendue, laissez tourner la requête un certains temps et ensuite tuez le processus qui l’exécutez. Analysez le fichier de trace pour voir sur quoi elle attente
    Est-il possible de faire cela sans être dba , As tu une doc sur le sql trace étendue ?

    Une autre piste : parfois au lieu de mettre à jour une grosse table il est plus intéressant de créer une nouvelle table via create as select et de supprimer la ancienne table et la remplacer par la nouvelle. Du ce point de vue quelle est le temps d’exécution de la requête qui ramène les données des la jointure entre la table T_Source et T_Cible. Quelle est son plan d’exécution ?
    Je vais essayer cette piste d'optim .

  6. #6
    Expert Confirmé Sénior Avatar de mnitu
    Homme Profil pro Marius Nitu
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    4 476
    Détails du profil
    Informations personnelles :
    Nom : Homme Marius Nitu
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : octobre 2007
    Messages : 4 476
    Points : 8 903
    Points
    8 903

    Par défaut

    Code :
    1
    2
    3
    dbms_monitor.session_trace_enable(waits => true);
    UPDATE ...
    dbms_monitor.session_trace_disable;
    ou
    Code :
    1
    2
    3
    ALTER session SET 'events 10046 trace name context forever, level 8';
    UPDATE ...
    ALTER session SET events '10046 trace name context off'
    ça génère un fichier ".trc" dans le répertoire user_dump_dest qui peut être analysé avec tkprof (voir tutoriel sur site).

  7. #7
    Membre chevronné Avatar de jkofr
    Homme Profil pro Jacques Kostic
    Senior Consultant DBA (Trivadis SA)
    Inscrit en
    octobre 2006
    Messages
    484
    Détails du profil
    Informations personnelles :
    Nom : Homme Jacques Kostic
    Âge : 45
    Localisation : Suisse

    Informations professionnelles :
    Activité : Senior Consultant DBA (Trivadis SA)
    Secteur : Conseil

    Informations forums :
    Inscription : octobre 2006
    Messages : 484
    Points : 714
    Points
    714

    Par défaut

    Hello Twixi

    Tu peux essayer ceci et mettre le plan d'éxécution?

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
     
    UPDATE ( SELECT A.champs_1, A.champs_2, A.champs_3, A.champs_4, A.champs_5, B.champs_1 ps_1, B.champs_2 ps_2, B.champs_3 ps_3, B.champs_4 ps_4, B.champs_5 ps_5
              FROM T_CIBLE A JOIN T_SOURCE B ON A.ID_1 = B.ID_1 AND A.ID_2 = B.ID_2
            )
     SET champs_1 = ps_1,
         champs_2 = ps_2,
         champs_3 = ps_3, 
         champs_4 = ps_4,
         champs_5 = ps_5;
    Et le plan correspondant, sans parallélisme.
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
     
     
     
    --------------------------------------------------------------------------------
    | Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT    |          |  2852 |   161K|   161   (2)| 00:00:01 |
    |   1 |  UPDATE             | T_CIBLE  |       |       |            |          |
    |*  2 |   HASH JOIN         |          |  2852 |   161K|   161   (2)| 00:00:01 |
    |*  3 |    TABLE ACCESS FULL| T_SOURCE |  2852 | 42780 |    19   (0)| 00:00:01 |
    |*  4 |    TABLE ACCESS FULL| T_CIBLE  | 10170 |   427K|   141   (1)| 00:00:01 |
    --------------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
     
       2 - access("A"."ID_1"="B"."ID_1" AND "A"."ID_2"="B"."ID_2")
       3 - filter("B"."ID_2" IS NOT NULL)
       4 - filter("A"."ID_2" IS NOT NULL)
    Versus le plan initial toujours sans parallèlisme.

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
     
     
    UPDATE T_CIBLE A SET (A.champs_1, A.champs_2, A.champs_3, A.champs_4, A.champs_5) = 
    (SELECT B.champs_1, B.champs_2,B.champs_3, B.champs_4,B.champs_5 FROM T_SOURCE B WHERE a.ID_1=b.ID_1 AND a.ID_2=b.ID_2)
    WHERE EXISTS (SELECT 1 FROM T_SOURCE B WHERE a.ID_1=b.ID_1 AND a.ID_2=b.ID_2);
     
    -------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT             |            |  2853 |   142K| 42938  (34)| 00:00:02 |
    |   1 |  UPDATE                      | T_CIBLE    |       |       |            |          |
    |   2 |   NESTED LOOPS SEMI          |            |  2853 |   142K|   143   (3)| 00:00:01 |
    |*  3 |    TABLE ACCESS FULL         | T_CIBLE    | 10170 |   427K|   141   (1)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN         | INDTSOURCE |   800 |  6400 |     0   (0)| 00:00:01 |
    |   5 |   TABLE ACCESS BY INDEX ROWID| T_SOURCE   |     1 |    39 |     2   (0)| 00:00:01 |
    |*  6 |    INDEX UNIQUE SCAN         | INDTSOURCE |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
     
       3 - filter("A"."ID_2" IS NOT NULL)
       4 - access("A"."ID_1"="B"."ID_1" AND "A"."ID_2"="B"."ID_2")
           filter("B"."ID_2" IS NOT NULL)
       6 - access("B"."ID_1"=:B1 AND "B"."ID_2"=:B2)
    Attention, ceci implique que tu as une clé primaire sur id_1,Id2...
    Sinon: ORA-01779: cannot modify a column which maps to a non key-preserved table

    jko
    OCM 11g, RAC and Performance & Tuning Expert 11g
    RMAN Backup & Recovery, Data Guard and Grid Control

  8. #8

    Inscrit en
    juillet 2012
    Messages
    21
    Détails du profil
    Informations forums :
    Inscription : juillet 2012
    Messages : 21
    Points : -2
    Points
    -2

    Par défaut

    hey

    Pour reprendre le premier post de mnitu, moi aussi le plan je le trouve pas mal.

    Peux-tu nous dire ce que tu as comme machine, le nombre de processeurs, mémoire, serveur dédié ou mutualisé? on est passé de 15min à 150min toutes choses étant égales d'ailleurs? (identité de volume traité, code, définition de structures, environnement, mémoire et cpu dispo??)

    moi j'essaierais de :
    • désactiver tes indexes sur la table cible, ainsi que les fk et autres contraintes éventuelles

    • passer ta table cible en nologging

    • m'assurer que tu es en full partout (source et cible)

    • paralléliser autant que faire se peut (source et cible)

    ça te donnera une référence de temps de traitement.

    Joyeux Noël, et comme dit un collègue, si vous avez les boules en ce moment, arrêtez-vous, et accrochez-les au sapin...

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •