Optimisation requête MERGE

**pierho34** · 16/05/2022, 16h20

Bonjour,

J'aurais besoin d'un petit coup de main pour optimiser ma requête qui prend plus de jours à s'exécuter.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
26
27
28
29
30
31
32
33
34
35
36
37
MERGE
        INTO    PDD01400B.DD01405_COMPETENCE dest
            USING   (
                SELECT cpt.rowid AS RID,mig1.new_code_ogr FROM PDD01400B.DD01405_COMPETENCE cpt inner join
                PDD01400B.mig_competences_4_0 mig1 on mig1.old_code_ogr = cpt.code
                where
                    mig1.action = 'R'
            ) src
            ON      (dest.rowid = src.rid)
            WHEN MATCHED THEN UPDATE
                SET dest.code = new_code_ogr;
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3731241056
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT       |                      |    80M|  2304M|       |  4033K  (1)| 00:02:38 |
|   1 |  MERGE                | DD01405_COMPETENCE   |       |       |       |            |          |
|   2 |   VIEW                |                      |       |       |       |            |          |
|*  3 |    HASH JOIN          |                      |    80M|  8450M|  3610M|  4033K  (1)| 00:02:38 |
|*  4 |     HASH JOIN         |                      |    80M|  2688M|       |   816K  (1)| 00:00:32 |
|*  5 |      TABLE ACCESS FULL| MIG_COMPETENCES_4_0  |  2247 | 35952 |       |     8   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL| DD01405_COMPETENCE   |   507M|  9204M|       |   815K  (1)| 00:00:32 |
|   7 |     TABLE ACCESS FULL | DD01405_COMPETENCE   |   539M|    37G|       |   818K  (1)| 00:00:32 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEST".ROWID="CPT".ROWID)
   4 - access("MIG1"."OLD_CODE_OGR"="CPT"."CODE")
   5 - filter("MIG1"."ACTION"='R')
   6 - filter("CPT"."CODE" IS NOT NULL)

Merci pour votre aide

**Ikebukuro** · 16/05/2022, 16h41

Salut,
As-tu le SQL Tuning Pack? Si oui, te prends pas la tête, lancxe le SQL Tuning Advisor et le SQL Access Advisor sur ta requête, tu devrais avoir de bons conseils de leur part.

**vanagreg** · 16/05/2022, 17h28

Bonjour,

D'après ton plan tu as plus de 500 millions de lignes à mettre à jour, ce qui représente 37Go de données. Vu que des FULL SCAN sont effectués, je suppose que quasiment toute la table va être mise à jour? Si oui, alors il vaut mieux partir sur un CTAS q'un MERGE, surtout que vu le nombre de lignes, énormément de redo sera généré.

**pierho34** · 17/05/2022, 10h05

Envoyé par Ikebukuro

Salut,
As-tu le SQL Tuning Pack? Si oui, te prends pas la tête, lancxe le SQL Tuning Advisor et le SQL Access Advisor sur ta requête, tu devrais avoir de bons conseils de leur part.

Bonjour,
SQL tuning advisor pas possible car il me réponds :
Le code SQL était trop ancien et a été sorti du cache de curseur.

**tbc92** · 17/05/2022, 10h54

J'ai l'impression que cette autre requête donnerait le même résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
MERGE
        INTO    PDD01400B.DD01405_COMPETENCE dest
            USING   (
                SELECT mig1.old_code_ogr   , mig1.new_code_ogr 
                FROM PDD01400B.mig_competences_4_0 mig1 
                where
                    mig1.action = 'R'
            ) src
            ON     (dest.code = src.old_code_ogr  )
            WHEN MATCHED THEN UPDATE
                SET dest.code = new_code_ogr;

Ou encore celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
update PDD01400B.DD01405_COMPETENCE cpt
set cpt.code = ( select mig1.new_code_ogr  from PDD01400B.mig_competences_4_0 mig1 where  mig1.old_code_ogr = cpt.code  and      mig1.action = 'R' )
where exists ( select mig1.new_code_ogr  from PDD01400B.mig_competences_4_0 mig1 where  mig1.old_code_ogr = cpt.code  and      mig1.action = 'R' )
;

S'il y a un index sur la table PDD01400B.mig_competences_4_0 , colonne old_code_ogr , cette dernière requête devrait être très rapide.
Enfin, tout est relatif, un update ou un merge n'est JAMAIS très rapide. Si tu as la possibilité de créer une nouvelle table avec le résultat de cet update, ce sera beaucoup plus rapide.

Edit : J'avais vu le message de Vanagreg, et je me disais : faut que je regarde cette commande CTAS ... et c'est fait : CTAS=Create Table As Select ! on est aligné, un Create d'une nouvelle table sera beaucoup plus rapide qu'un merge ou un update.

**pierho34** · 17/05/2022, 11h37

Je fais étudier et surtout tester tes propositions.
Pour info : la colonne old_code_ogr de la table PDD01400B.mig_competences_4_0 est indexé.

**Mohamed.Houri** · 17/05/2022, 14h16

Bonjour,

C’est étonnant qu’une requête en HASH JOIN prenne plusieurs jours sans tomber en erreur de TEMP. Mais admettons cela ; puisque cette requête a duré plusieurs jours elle a donc été monitorée. Vérifier cela avec ceci en adaptant le sql_id

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SQL> select sql_id, sql_exec_id, status from gv$sql_monitor where sql_id = '6g7h65pzab861';
 
SQL_ID        SQL_EXEC_ID STATUS
------------- ----------- -------------------
6g7h65pzab861    16777225 DONE (ERROR)
6g7h65pzab861    16777230 DONE (ERROR)
6g7h65pzab861    16777221 DONE (ERROR)
6g7h65pzab861    16777219 DONE (ERROR)
6g7h65pzab861    16777222 DONE (ERROR)
6g7h65pzab861    16777216 DONE (ERROR)
6g7h65pzab861    16777218 DONE (ERROR)
6g7h65pzab861    16777217 DONE (ERROR)
6g7h65pzab861    16777231 DONE (ERROR)
6g7h65pzab861    16777226 DONE (ERROR)
6g7h65pzab861    16777227 DONE (ERROR)
 
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '6g7h65pzab861',
  sql_exec_id  => 16777225,
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual;

Si votre MERGE a bien été monitoré mais il a déjà été flushé de la mémoire alors tentez votre change avec la requête suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SELECT
    report_id,
    key1  sql_id,
    key2  sql_exec_id    
FROM
    dba_hist_reports
WHERE
        component_name = 'sqlmonitor'
    AND key1 = '6g7h65pzab861';
 
REPORT_ID SQL_ID            SQL_EXEC_ID
---------- ----------------- --------------
       284 6g7h65pzab861     16777218
       285 6g7h65pzab861     16777219
       287 6g7h65pzab861     16777221
       288 6g7h65pzab861     16777222
       289 6g7h65pzab861     16777225
       290 6g7h65pzab861     16777226
       292 6g7h65pzab861     16777227
       294 6g7h65pzab861     16777230
       296 6g7h65pzab861     16777231
 
SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => 284, TYPE => 'text')
       FROM dual;

Au moins avec ça on pourra voir plus clair.

Bien Cordialement
Mohamed Houri

**pierho34** · 19/05/2022, 16h17

Bonjour,

Avec l'équipe de développeurs, nous avons élaborer ce pPL/SQL, celui-ci prend une heure de traitement qui est plus qu'honorable :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
-- Version : 1.0
SET HEADING OFF
 
SET PAGES 0
 
SET SERVEROUT ON SIZE UNLIMITED
 
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- Sauvegarde de la table
DECLARE
    conf_if_debug BOOLEAN :=true;
    pseudoBoolean NUMBER;
 
    --Fonction LOG INFO
    PROCEDURE loginfo (
        v_txt IN VARCHAR2
    ) IS
    BEGIN
        dbms_output.put_line('INFO : ' || v_txt);
    END;
BEGIN
    -- Sauvegarde de la table compétences dans une table temporaire
    SELECT count(*) INTO pseudoBoolean FROM user_tables WHERE TABLE_NAME = 'SAVE_DD01405_COMPETENCE';
    IF ( pseudoboolean = 0 )THEN
        loginfo('SAUVEGARDE DE LA TABLE COMPETENCE');
        execute immediate 'CREATE TABLE SAVE_DD01405_COMPETENCE AS SELECT * FROM DD01405_COMPETENCE';
    END IF;
    COMMIT;
    -- table temporaire
    SELECT count(*) INTO pseudoBoolean FROM user_tables WHERE TABLE_NAME = 'DD01405_COMPETENCE_TMP';
    IF ( pseudoboolean = 0 ) THEN
        loginfo('CREATION TABLE TEMPORAIRE');
        execute immediate 'CREATE TABLE DD01405_COMPETENCE_TMP AS SELECT * FROM DD01405_COMPETENCE WHERE 1=0';
    ELSE
        loginfo('VIDAGE TABLE TEMPORAIRE');
        execute immediate 'truncate table DD01405_COMPETENCE_TMP';
    END IF;
    -- setup table final ou debug
    IF conf_if_debug THEN
        loginfo('ACTION DEBUG');
        SELECT count(*) INTO pseudoBoolean FROM user_tables WHERE TABLE_NAME = 'DD01405_COMPETENCE_DEBUG';
        IF ( pseudoboolean = 0 ) THEN
            execute immediate 'CREATE TABLE DD01405_COMPETENCE_DEBUG AS SELECT * FROM DD01405_COMPETENCE WHERE 1=0';
        END IF;
    ELSE
        loginfo('ACTION PROD');
        --action pour la table final
        -- TODO supprimer les contraintes et index de la table principale dans la version final
        -- TODO vidé la table original dans la version final
        -- TODO 'truncate table DD01405_COMPETENCE'
    END IF;
    COMMIT;
END;
/
-- SELECTION COMPETENCE A METTRE A JOUR
DECLARE
    pseudoBoolean NUMBER;
BEGIN
    SELECT count(*) INTO pseudoBoolean FROM user_tables WHERE TABLE_NAME = 'COMPETENCE_A_MODIFIE';
    IF ( pseudoboolean = 0 ) THEN
        execute immediate 'CREATE TABLE COMPETENCE_A_MODIFIE AS SELECT MIN(CPT.ID_COMPETENCE) AS "ID_COMPETENCE" FROM SAVE_DD01405_COMPETENCE CPT INNER JOIN MIG_COMPETENCES_4_0 MIG ON mig.old_code_ogr = CPT.CODE
        WHERE DECODE(CPT.TYPE,''L'',NULL,TO_CHAR(CPT.ID_RCI)||NVL(CPT.TYPE,''-'')||NVL(MIG.NEW_CODE_OGR,''-'')) NOT IN (
        SELECT DECODE(CPT2.TYPE,''L'',NULL,TO_CHAR(CPT2.ID_RCI)||NVL(CPT2.TYPE,''-'')||NVL(CPT2.CODE,''-''))
        FROM SAVE_DD01405_COMPETENCE CPT2 INNER JOIN MIG_COMPETENCES_4_0 MIG2 ON MIG2.new_code_ogr = CPT2.CODE)
        AND MIG.ACTION = ''R''
        GROUP BY DECODE(CPT.TYPE,''L'',NULL,TO_CHAR(CPT.ID_RCI)||NVL(CPT.TYPE,''-'')||NVL(MIG.NEW_CODE_OGR,''-''))';
    ELSE
        execute immediate 'TRUNCATE TABLE COMPETENCE_A_MODIFIE';
        execute immediate 'INSERT INTO COMPETENCE_A_MODIFIE SELECT MIN(CPT.ID_COMPETENCE) AS "ID_COMPETENCE" FROM SAVE_DD01405_COMPETENCE CPT INNER JOIN MIG_COMPETENCES_4_0 MIG ON mig.old_code_ogr = CPT.CODE
        WHERE DECODE(CPT.TYPE,''L'',NULL,TO_CHAR(CPT.ID_RCI)||NVL(CPT.TYPE,''-'')||NVL(MIG.NEW_CODE_OGR,''-'')) NOT IN (
        SELECT DECODE(CPT2.TYPE,''L'',NULL,TO_CHAR(CPT2.ID_RCI)||NVL(CPT2.TYPE,''-'')||NVL(CPT2.CODE,''-''))
        FROM SAVE_DD01405_COMPETENCE CPT2 INNER JOIN MIG_COMPETENCES_4_0 MIG2 ON MIG2.new_code_ogr = CPT2.CODE)
        AND MIG.ACTION = ''R''
        GROUP BY DECODE(CPT.TYPE,''L'',NULL,TO_CHAR(CPT.ID_RCI)||NVL(CPT.TYPE,''-'')||NVL(MIG.NEW_CODE_OGR,''-''))';
    END IF;
    COMMIT;
END;
/
 
DECLARE
--------------------------------------- CONF GLOBALE -------------------------------------------------------------
 
    conf_realiser_les_commits  BOOLEAN := true;  --/!\ pour la PROD, à mettre à TRUE (si FALSE, un ROLLBACK est effectue apres les INSERT BULK)
    conf_if_debug BOOLEAN :=true;
 
 
--------------------------------------- TEMPS D'EXECUTION --------------------------------------------------------
 
    --temps de traitement du script complet
    v_start_total              NUMBER;
    v_stop_total               NUMBER;
--------------------------------------- FONCTIONS TECHNIQUES -----------------------------------------------------
 
    --Fonction LOG INFO
    PROCEDURE loginfo (
        v_txt IN VARCHAR2
    ) IS
    BEGIN
        dbms_output.put_line('INFO : ' || v_txt);
    END;
 
    --Fonction de recuperation du temps systeme
    FUNCTION getsystime RETURN NUMBER AS
    BEGIN
        RETURN dbms_utility.get_time();
    END;
 
    --Fonction de calcul et formattage du temps d'execution
    FUNCTION calcultempsexecution (
        p_start IN NUMBER,
        p_stop  IN NUMBER
    ) RETURN VARCHAR2 AS
    BEGIN
        RETURN ( ( p_stop - p_start ) * 10 )|| ' ms';
    END;
 
--------------------------------------- FONCTIONS RG -----------------------------------------------------
 
 
BEGIN
    loginfo('---------------------------');
    loginfo('------- DEBUT BATCH -------');
    loginfo('---------------------------');
 
    --
    v_start_total := getsystime();--start temps exec total
 
    INSERT INTO DD01405_COMPETENCE_TMP (
        id_rci,
        id_competence,
        type,
        code,
        date_ajout,
        code_statut,
        code_niveau,
        libelle_competence,
        json_liaison,
        origine_donnee,
        visibilite_donnee,
        date_modification,
        code_acteur,
        code_origine,
        date_validite,
        vu_candidat,
        ref_externe
    )
    SELECT
        cpt.id_rci,
        cpt.id_competence,
        cpt.type,
        mig.new_code_ogr,
        cpt.date_ajout,
        cpt.code_statut,
        cpt.code_niveau,
        cpt.libelle_competence,
        cpt.json_liaison,
        cpt.origine_donnee,
        cpt.visibilite_donnee,
        cpt.date_modification,
        cpt.code_acteur,
        cpt.code_origine,
        cpt.date_validite,
        cpt.vu_candidat,
        cpt.ref_externe
    FROM
    SAVE_DD01405_COMPETENCE cpt
        INNER JOIN mig_competences_4_0 mig ON cpt.code = mig.old_code_ogr
        INNER JOIN COMPETENCE_A_MODIFIE cptm ON cptm.ID_COMPETENCE = cpt.ID_COMPETENCE
    WHERE
        mig.action = 'R';
 
    loginfo(SQL%rowcount || ' Compétences mise à jour');
 
    IF ( conf_realiser_les_commits ) THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
 
    EXECUTE IMMEDIATE 'INSERT INTO DD01405_COMPETENCE_DEBUG SELECT * FROM DD01405_COMPETENCE_TMP';
 
    v_stop_total := getsystime();--start temps exec total
 
    loginfo('> Temps d''execution total = ' || calcultempsexecution(v_start_total, v_stop_total));
    loginfo('---------------------------');
    loginfo('-------- FIN BATCH --------');
    loginfo('---------------------------');
END;
/
 
EXIT SUCCESS;

**dell68** · 20/05/2022, 11h06

Pourquoi tu ne teste pas l'approche TRIGGER entre ces deux tables ? ??????

Optimisation requête MERGE [12c]

SQL Oracle

Discussions similaires

Partager

Partager