Discussion :

[nova23]

Bonjour,

Je souhaiterais votre aide svp.
J'ai écrit une procédure pl sql que je lance à partir d'un script shell. Le problème est que la requête prend énormément de temps (plus d'une journée).

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
 
PROCEDURE UPDATE_TMP_CR_FACTU IS
 
  TYPE ARRAY1 IS TABLE OF TMP_USG.TRANSAC_ID%TYPE;
  TYPE ARRAY2 IS TABLE OF TMP_USG.CONTROLE_CR_FACTU%TYPE;
 
  L_DATA_USG_TRANSAC_ID ARRAY1;
  L_DATA_USG_CR_FACTU ARRAY2;
 
  UPDATE_TMP_CR_FACTU_ERROR EXCEPTION;
 
  PRAGMA EXCEPTION_INIT(UPDATE_TMP_CR_FACTU_ERROR, -20984);
 
 
  CURSOR LISTE_USG IS
    SELECT /* +PARALLEL(TU, 8) */
      TU.TRANSAC_ID,
      (CASE WHEN TU.TYPE_USAGE = 'COMM' THEN
        (SELECT TMF.NOM_FICHIER FROM TMP_CR_FACTU TMF WHERE TMF.NO_FACTU = TU.NO_FACTURE AND TMF.NOM_FICHIER LIKE 'U%')
       WHEN TU.TYPE_USAGE = 'WIFI' THEN
        (SELECT  TMF.NOM_FICHIER FROM TMP_CR_FACTU TMF WHERE TMF.NO_FACTU = TU.NO_FACTURE AND TMF.NOM_FICHIER LIKE 'W%')
      ELSE
         (SELECT  TMF.NOM_FICHIER FROM TMP_CR_FACTU TMF WHERE TMF.NO_FACTU = TU.NO_FACTURE AND TMF.NOM_FICHIER LIKE 'S%')
      END) as fichier
    FROM
      TMP_USG TU, TMP_FACTU TF
    WHERE
      TU.NO_FACTURE = TF.NO_FACTURE AND
      TU.FACTU_PROV = 'FACTU' AND
      TU.CONTROLE_CR_FACTU IS NULL AND
      TF.EXTRACT = 'O';
 
BEGIN
 
  OPEN LISTE_USG;
    LOOP
        FETCH LISTE_USG BULK COLLECT INTO L_DATA_USG_TRANSAC_ID, L_DATA_USG_CR_FACTU LIMIT 50000;
 
        FORALL I IN INDICES OF L_DATA_USG_TRANSAC_ID
        UPDATE TMP_USG SET CONTROLE_CR_FACTU = L_DATA_USG_CR_FACTU(I) WHERE TRANSAC_ID = L_DATA_USG_TRANSAC_ID(I);
 
        COMMIT;
 
        EXIT WHEN LISTE_USG%NOTFOUND;
    END LOOP;
 
   CLOSE LISTE_USG;
 
 
  EXCEPTION
  WHEN OTHERS THEN
     ROLLBACK;
    RAISE_APPLICATION_ERROR(-20984, 'UPDATE_TMP_CR_FACTU, Erreur : ' || SQLERRM);
 
END UPDATE_TMP_CR_FACTU;

Sachant que l'exécution du SELECt dans le cursor prend à peine 5 secondes et renvoie environ 2,1 Millions de ligne, je comprends pas pourquoi le reste de la procédure prend du temps.
Quelqu'un aurait une idée svp.
Merci

[Heaven93]

bonjour,

tracez la session pour savoir exactement ce qu'elle fait.

[nova23]

tracez la session pour savoir exactement ce qu'elle fait.

Comment je peux faire ca ? Je débute un peu en fait ...
Mais la requete est elle correcte? Ya t il moyen de l'optimiser ?

[mnitu]

Il y a une quantité impressionnante des bugs et autres mauvais choses dans cette procédure donc ce n’est pas étonnant si cela prends du temps :

• La condition de sortie de la boucle est erronée (bug)
• Le choix de la limite du lot à 50000 est erroné ; au delà d’environ 1500 le coût de la gestion de la mémoire pénalise ce choix. Les valeurs acceptables sont quelque part entre 100 à 1500.
• Le fait de faire commit dans la boucle
• La gestion des exceptions avec le rollback
• L’utilisation du hint parallel.

Et sur la manière de mettre à jour une table qui contient des millions des lignes lisez l’article On Efficiency, Avoidance, and Performance

<EDIT>
La condition de sortie de la boucle est correcte quoi que vieux style.
</EDIT>

[nova23]

Comment pourrais je faire, une proposition de solution ? Merci

[mnitu]

Je vous propose de récréer la table au lieu de la mettre à jour

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
 
Create table TMP_USG_1 nologging As 
Select /*+ append */
       TU.TRANSAC_ID,
       Cast(
             Case
               When (TU.TYPE_USAGE = 'COMM' And TMF.NOM_FICHIER LIKE 'U%') Or 
                    (TU.TYPE_USAGE = 'WIFI' And TMF.NOM_FICHIER LIKE 'W%') Or
                    (TMF.NOM_FICHIER LIKE 'S%') Then TMF.NOM_FICHIER
             End
         As Varchar2(30)
       )     As fichier       
  From TMP_USG TU
       Left Outer Join
       TMP_CR_FACTU TMF
    On TMF.NO_FACTU = TU.NO_FACTURE
 Where TU.FACTU_PROV = 'FACTU' 
   And TU.CONTROLE_CR_FACTU IS NULL 
   And TF.EXTRACT = 'O'
   And Exists (Select Null
                 From TMP_FACTU TF
                Where TF.NO_FACTURE = TU.NO_FACTURE
              )

Combien de temps ça prends ?

[nova23]

Malheureusement c'est une des tables la plus importante. Elle comporte des indexs ainsi que des millions d’enregistrements et des cripts journaliers passent dessus. On peut donc pas la supprimer et la recreer à chaque update...

[ojo77]

Quand vous mettez à jour plus de 2 million de transations ce n'est plus du transactionnel qu'il faut faire mais du batch. Et dans le cadre du batch, la suppression recréation d'une table et de ses index est à priori tout à fait envisageable. Le partitionnement peut vous aider à réduire la volumétrie à gérer.

Si malgré tout cela vous ne pouvez quand même pas reconstruire votre objet, utilisez un merge plutôt qu'un update.

A lire sur le sujet:
http://blog.easyteam.fr/2011/11/22/p...ng-sql-update/
http://blog.easyteam.fr/2009/11/16/h...th-sql-tuning/
http://blog.easyteam.fr/2009/11/11/optimisation-sql-1/

[nova23]

Un truc du genre par exemple:

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
 
CREATE TABLE MATABLE AS (
SELECT /*+ append */
      TU.TRANSAC_ID,
      (CASE WHEN TU.TYPE_USAGE = 'COMM' THEN
        (SELECT TMF.NOM_FICHIER FROM TMP_CR_FACTU TMF WHERE TMF.NO_FACTU = TU.NO_FACTURE AND TMF.NOM_FICHIER LIKE 'U%')
       WHEN TU.TYPE_USAGE = 'WIFI' THEN
        (SELECT  TMF.NOM_FICHIER FROM TMP_CR_FACTU TMF WHERE TMF.NO_FACTU = TU.NO_FACTURE AND TMF.NOM_FICHIER LIKE 'W%')
      ELSE
         (SELECT  TMF.NOM_FICHIER FROM TMP_CR_FACTU TMF WHERE TMF.NO_FACTU = TU.NO_FACTURE AND TMF.NOM_FICHIER LIKE 'S%')
      END) AS FICHIER
    FROM
      TMP_USG TU, TMP_FACTU TF
    WHERE
      TU.NO_FACTURE = TF.NO_FACTURE AND
      TU.FACTU_PROV = 'FACTU' AND
      TU.CONTROLE_CR_FACTU IS NULL AND
      TF.EXTRACT = 'O')
	  ;
 
 
MERGE INTO TMP_USG TU
USING (SELECT T.TRANSAC_ID, T.FICHIER FROM MATABLE) T
ON (TU.TRANSAC_ID = T.TRANSAC_ID)
WHEN MATCHED THEN UPDATE SET TU.CONTROLE_CR_FACTU = T.FICHIER
;
 
DROP TABLE TEST;

[ora_home]

Comment je peux faire ca ? Je débute un peu en fait ...
Mais la requete est elle correcte? Ya t il moyen de l'optimiser ?

Tu peux traçer la session, puis générer un rapport TKPROF pour avoir plus d'information dur le traitement qui prend du temps.

Code :

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

1
2
3
 
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;

ton fichier traçe va porter le nom "mysqltrace..."

sinon, tu peux activer la traçe a partir du SID et serial de la session :

Code :

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

1
2
3
 
ALTER SYSTEM SET timed_statistics = true;
execute dbms_system.set_sql_trace_in_session(SID, SERIAL#, true);