Optimisation d'une requete MERGE via HINT d'index

[twixi]

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 !

[SQLpro]

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

A +

[twixi]

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 .

[mnitu]

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 ?

[twixi]

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 .

[mnitu]

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).

[jkofr]

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

[thepierre]

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...