Discussion :

[pierho34]

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]

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]

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]

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]

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]

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]

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