Amélioration d'un process d'écriture [Résolu]

[Waldar]

Bonjour à tous,


Dans le cadre d'un développement je dois extraire depuis une base de données des fichiers XML, afin de les envoyer à des tiers.

Pour générer le XML, j'ai codé ce qu'il faut dans une vue en passant pas des objets créé pour le besoin : pas de soucis de ce côté là, ça répond plutôt bien.

L'envoi des fichiers est piloté par une table tierce qui contient les méta-données.

Là où je ne suis pas satisfait de mon process, c'est sur l'extraction de ces XML vers les fichiers.

Certaines limitations me sont imposées :

• dans ma table d'envoi figure un code et je dois déclarer un fichier par code.
• un fichier ne doit pas dépasser 10.000 objets.

Mon environnement est Oracle Database Enterprise Edition 11gR1 11.1.0.7, 64 bits qui opère sur Windows Server 2003.

J'ai créé un jeu de test relativement simple pour illustrer tout ceci :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE directory TST AS 'c:\TEMP\TST';
 
CREATE TABLE donnees
(
    col1      number(1)         NOT NULL,
    col2      varchar2(10 char) NOT NULL,
    constraint pk_donnees
      PRIMARY KEY (col1)
      USING INDEX
);
 
INSERT INTO donnees (col1, col2) VALUES (1, 'ABC');
INSERT INTO donnees (col1, col2) VALUES (2, 'DEF');
INSERT INTO donnees (col1, col2) VALUES (3, 'GHI');
INSERT INTO donnees (col1, col2) VALUES (4, 'JKL');
INSERT INTO donnees (col1, col2) VALUES (5, 'MNO');
INSERT INTO donnees (col1, col2) VALUES (6, 'PQR');
INSERT INTO donnees (col1, col2) VALUES (7, 'STU');
INSERT INTO donnees (col1, col2) VALUES (8, 'VWX');
INSERT INTO donnees (col1, col2) VALUES (9, 'YZ' );
 
commit;
 
CREATE VIEW v_donnees_xml
AS
SELECT col1, XMLElement("SuperBalise", col2) AS col_XML
  FROM donnees;
 
CREATE TABLE envoi
(
    col1      number(1)         NOT NULL,
    code      varchar2(1 char)  NOT NULL,
    chk       varchar2(1 char)      NULL,
    constraint pk_envoi
      PRIMARY KEY (col1)
      USING INDEX
);
 
INSERT INTO envoi (col1, code, chk) VALUES (1, 'A', 'X');
INSERT INTO envoi (col1, code, chk) VALUES (2, 'A', 'X');
INSERT INTO envoi (col1, code, chk) VALUES (3, 'A', 'X');
INSERT INTO envoi (col1, code, chk) VALUES (4, 'A', 'X');
INSERT INTO envoi (col1, code, chk) VALUES (5, 'A', 'X');
INSERT INTO envoi (col1, code, chk) VALUES (6, 'B', 'X');
INSERT INTO envoi (col1, code, chk) VALUES (7, 'B', 'X');
INSERT INTO envoi (col1, code, chk) VALUES (8, 'B', 'X');
INSERT INTO envoi (col1, code, chk) VALUES (9, 'C', 'X');
 
commit;

D'après ce que j'ai lu ici (probablement un lien posté par mnitu, comme souvent ), le plus rapide sans parallélisation pour exporter des données dans un fichier plat sont de passer par un CLOB et de ne l'écrire qu'une fois.

Dans le code PL/SQL qui va suivre, ce qui me déplaît c'est que j'ai créé un premier curseur pour parcourir les éléments à envoyer, et à partir des données de celui-ci je vais chercher le bout de XML qui me convient.
Avec ma volumétrie, effectuer la jointure à ce niveau en pur SQL est malheureusement trop coûteux, j'annule le teste après plus de trente minutes sans rien voir arriver.

Dans ce code j'ai simulé la limitation à trois objets par fichier :

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
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
declare
    v_file                  CLOB;
    v_clob                  CLOB;
    v_date_extract          VARCHAR2(30);
    v_nb_rows               NUMBER(10) DEFAULT 0;
    v_file_name             VARCHAR2(50);
    v_file_dir     CONSTANT VARCHAR2( 3) DEFAULT 'TST';
    v_header                VARCHAR2(100);
    v_footer                VARCHAR2(100);
 
begin
 
    FOR c_query IN
    (
    SELECT col1, code,
           lag(code, 1, ' ') over(ORDER BY code ASC) AS code_prec
      FROM envoi
     WHERE chk = 'X'
    )
    loop
 
      IF (c_query.code <> c_query.code_prec OR v_nb_rows = 3)
      then -- Nouveau fichier 
 
        IF c_query.code_prec <> ' '
        then -- Écriture du fichier précédent
          v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
          -- Rajout du footer
          DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
          DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
          DBMS_LOB.FREETEMPORARY(v_file);
        end IF;
 
        -- Initialisation du CLOB 
        DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
        v_nb_rows      := 0;
        v_date_extract := to_char(systimestamp, 'yyyymmdd_hh24missff');
        v_file_name    := 'Fichier_' || c_query.code || '_' || v_date_extract || '.xml';
 
        v_header       := '<MonFichier>' || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<MonCode>'   || c_query.code   || '</MonCode>'   || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<DateEnvoi>' || v_date_extract || '</DateEnvoi>' || chr(13) || chr(10);
 
        -- Rajout du header
        DBMS_LOB.WRITEAPPEND(v_file, length(v_header), v_header);
 
      end IF;
 
      -- Sélection du XML dans la vue 
      SELECT lpad(' ', 8, ' ') || x.col_xml.getClobVal() || chr(13) || chr(10) INTO v_clob
        FROM v_donnees_xml x
       WHERE x.col1 = c_query.col1;
 
      -- Concaténation du XML dans le CLOB
      DBMS_LOB.APPEND(v_file, v_clob);
 
      v_nb_rows := v_nb_rows + 1;
 
    end loop;
 
    -- Écriture du dernier fichier
    v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
    -- Rajout du footer
    DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
    DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
    DBMS_LOB.FREETEMPORARY(v_file);
 
end;
/

Évidemment sur ce genre de jeu de test, tout est instantané.

Voici le contenu d'un fichier, qui est bien celui escompté :

Code :

1
2
3
4
5
6
7
<MonFichier>
    <MonCode>A</MonCode>
    <DateEnvoi>20110329_151939417000000</DateEnvoi>
        <SuperBalise>JKL</SuperBalise>
        <SuperBalise>MNO</SuperBalise>
    <NbItem>2</NbItem>
</MonFichier>

Sur mon environnement de développement, j'écris environ 1Go de données en quinze minutes.
Ce n'est pas tellement l'écriture qui est lente, c'est vraiment d'aller chercher les bouts de XML un par un.

C'est sur ce point qu'à mon avis le code peut être améliorer, je suis quasiment sûr que ce temps de traitement est divisible par deux ou trois.
J'ai imaginé stocker tous mes identifiants dans une collection et la passer d'un coup à la vue, avec du XMLAgg afin de ne faire plus qu'un seul appel à ce niveau, mais je n'ai pas réussi à l'implémenter.

Ayant relativement peu de pratique en pur PL/SQL, toutes expériences et idées sont les bienvenues !

Merci d'être parvenu au terme de ce long sujet.

PS : Pour nettoyer votre environnement :

Code :

1
2
3
4
5
6
7
DROP VIEW v_donnees_xml;
 
DROP TABLE envoi;
 
DROP TABLE donnees;
 
DROP directory TST;

[mnitu]

Salut Waldar,
Oui travailler ligne par ligne n’est pas bon pour les performances.
Si j’ai bien compris ton problème il te faut grouper les donnés des le départ et parcourir les données dans un seul passage.

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
26
27
28
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> 
SQL> SELECT code, XmlAgg(col_XML)
  2    FROM (
  3          SELECT t.*, trunc(t.rn/4) grp
  4          FROM (
  5            SELECT e.code, x.col_xml, row_number() over (partition BY code ORDER BY rownum)rn
  6              FROM v_donnees_xml x
  7                   JOIN
  8                   envoi e
  9                ON e.col1 = x.col1
 10            WHERE  e.chk = 'X'
 11          ) t
 12  )
 13  GROUP BY code, grp
 14  /
 
CODE XMLAGG(COL_XML)
---- --------------------------------------------------------------------------------
A    <SuperBalise>ABC</SuperBalise><SuperBalise>DEF</SuperBalise><SuperBalise>GHI</Su
A    <SuperBalise>JKL</SuperBalise><SuperBalise>MNO</SuperBalise>
B    <SuperBalise>PQR</SuperBalise><SuperBalise>STU</SuperBalise><SuperBalise>VWX</Su
C    <SuperBalise>YZ</SuperBalise>
 
SQL>

PS. Ce n’était pas moi. J’ai simplement participé à la discussion pour signaler que l’utilisation du Clob n’est pas meilleure que l’utilisation correcte de l’UTL_FILE

[Waldar]

Tout simplement le trunc du row_number(), je n'y ai pas pensé.

Merci mnitu, je reviens avec le différentiel de temps de traitement.

[Waldar]

Aïe, même si l'idée est excellente, elle a mis le serveur sur les rotules.
Mon temps de référence est de treize minutes et après une demie-heure je n'ai toujours rien qui vient.

Les CPU sont à 100%, je pense que le XMLAgg sur toutes les lignes est trop violent.

Je vais regarder si je peux implémenter une solution intermédiaire, peut-être essayer de passer les id 100 par 100, mais là je retombe dans ma connaissance trop faible des collections.

Ou alors si une autre idée surgit !

[mnitu]

C'est le fait du chercher ligne à ligne qui prends le plus du temps ou la concaténation des lobs ? C'est dans ce cas que le profil du traitement devrait indiquer dans où le temps passe et par conséquence comment il faut intervenir.
Quelle est la volumétrie de la table ?

[skuatamad]

Salut Waldar,

Alors effectivement nous ne savons pas encore lequel, du select dans la boucle ou de la concaténation des lobs, est le plus lent, mais au cas où le select dans la boucle soit en cause je propose une autre approche (mais franchement je ne sais pas ce que ça vaut, et vu que vous êtes tous les deux plus compétent que moi... j'ai de gros doutes...)

Mon idée est, plutôt que de générer du XML en SQL et découper les fichiers en PL/SQL, pourquoi ne pas faire l'inverse... découper en SQL et générer le XML dans le code PL/SQL (à la manière du header et du footer déjà codés), au moins il n'y a plus de select dans une boucle.

J'ai utilisé une variable globale pour "simuler" le paramètre v_nb_rows tel qu'il était utilisé dans le découpage, mais une modification assez simple te l'épargnera si tu préfères.

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
26
CREATE OR REPLACE package param AS
  g_nb_rows NUMBER(10) DEFAULT 3;
  FUNCTION getNbRows RETURN number;
end;
/
CREATE OR REPLACE package body param AS
  FUNCTION getNbRows RETURN number IS
    begin
      RETURN g_nb_rows;
    end;
end;
/
CREATE OR REPLACE VIEW v_donnees_per_file AS
WITH rows_per_files AS (
  SELECT param.getNbRows AS nb_rows FROM dual
)
  SELECT t.code,t.col2,
         mod(t.nb_rows - 1,rpf.nb_rows) AS new_file
    FROM (SELECT e.code,d.col2,
                 count(*) over (partition BY e.code ORDER BY d.col2) AS nb_rows
            FROM envoi e
            JOIN donnees d ON e.col1 = d.col1
           WHERE e.chk = 'X'
         ) t
   CROSS JOIN rows_per_files rpf
/

et la procédure (très proche de la tienne):

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
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
declare
    v_file                  CLOB;
    v_clob                  CLOB;
    v_date_extract          VARCHAR2(30);
    v_nb_rows               NUMBER(10) DEFAULT 0;
    v_file_name             VARCHAR2(50);
    v_file_dir     CONSTANT VARCHAR2( 3) DEFAULT 'TST';
    v_header                VARCHAR2(100);
    v_footer                VARCHAR2(100);
    v_first_row             VARCHAR2(1) DEFAULT 'T';
 
begin
 
    FOR c_query IN
    (
    SELECT code,col2,new_file FROM v_donnees_per_file
    )
    loop
 
      IF (c_query.new_file = 0)
      then -- Nouveau fichier 
 
        IF v_first_row <> 'T'
        then -- Écriture du fichier précédent
          v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
          -- Rajout du footer
          DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
          DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
          DBMS_LOB.FREETEMPORARY(v_file);
        end IF;
        v_first_row := 'F';    
        -- Initialisation du CLOB 
        DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
        v_nb_rows      := 0;
        v_date_extract := to_char(systimestamp, 'yyyymmdd_hh24missff');
        v_file_name    := 'Fichier_' || c_query.code || '_' || v_date_extract || '.xml';
 
        v_header       := '<MonFichier>' || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<MonCode>'   || c_query.code   || '</MonCode>'   || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<DateEnvoi>' || v_date_extract || '</DateEnvoi>' || chr(13) || chr(10);
 
        -- Rajout du header
        DBMS_LOB.WRITEAPPEND(v_file, length(v_header), v_header);
 
      end IF;
 
      -- Sélection du XML dans la vue 
      -- Plus utile maintenant
      -- SELECT lpad(' ', 8, ' ') || x.col_xml.getClobVal() || chr(13) || chr(10) INTO v_clob
      --  FROM v_donnees_xml x
      -- WHERE x.col1 = c_query.col1;
 
      v_clob := lpad(' ', 8, ' ') || '<SuperBalise>'||c_query.col2||'</SuperBalise>' || chr(13) || chr(10);
 
      -- Concaténation du XML dans le CLOB
      DBMS_LOB.APPEND(v_file, v_clob);
 
      v_nb_rows := v_nb_rows + 1;
 
    end loop;
 
    -- Écriture du dernier fichier
    v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
    -- Rajout du footer
    DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
    DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
    DBMS_LOB.FREETEMPORARY(v_file);
 
end;
/

Mais je ne sais pas dans quelle mesure il sera simple d'adapter mon approche à tes données, notamment quand tu dis :

j'ai codé ce qu'il faut dans une vue en passant par des objets créé pour le besoin

[Waldar]

mnitu, je pensais perdre du temps sur l'appel un par un dans la vue XML à partir du curseur, mais justement ton test m'amène à penser qu'il faut jongler entre les deux coûts : l'appel récurrent ou la construction du XML directement par le moteur SQL.

J'ai lancé des tests unitaires en utilisant ton code, le XMLAgg sur le premier bloc dure environ cinq minutes alors que la sélection unitaire dans un CLOB global ne prend qu'une ou deux minutes.

J'ai exécuté une trace sur mon traitement de référence, j'attends que le DBA sorte de réunion pour que je puisse l'analyser, puis j'en relancerai une sur le test unitaire du XMLAgg.

Au niveau de la volumétrie, c'est plutôt faible au départ.
Dans le modèle relationnel dédié, tables et index occupent 250 Mo.

Le XML étant un langage verbeux, à partir de ce volume je génère un peu plus d'un Go de données.

skuatamad, dans le test c'est faisable, malheureusement en vrai ma vue pour générer le XML fait plus de 200 lignes et possède sa petite complexité, ça va être difficile à réaliser.

[skuatamad]

J'ai probablement été un peu violent en retirant tout le XML de la vue, mais si la requête "prédécoupe un peu" en fichier, la concaténation oppéré par le XMLAgg se fera facilement dans le PL/SQL et il n'y aura pas de requête dans la boucle.

Code :

1
2
3
4
5
6
7
8
SELECT t.code,t.col_xml,
       mod(t.nb_rows - 1,3) AS new_file
  FROM (SELECT e.code, x.col_xml, 
               count(*) over (partition BY e.code ORDER BY rownum) AS nb_rows
          FROM v_donnees_xml x
          JOIN envoi e ON e.col1 = x.col1
         WHERE e.chk = 'X'
        ) t

[McM]

A mon avis, ce qui est coûteux, c'est l'affectation sur CLOB et la concaténation. J'avais ce problème sur de la génération de SOAP XML.

Tant que tu peux utiliser du VARCHAR2 pour concaténer des chaines, fais le.
Le Append sur ton CLOB global quand tu en as besoin.
Sinon, tu as essayé uniquement en varchar2 pour remplir ton fichier ?

Dernière ou première action : Mettre des traces (dbms_utility.get_time) entre chaque partie pour calculer le temps total de chaque instruction. C'est comme ça que j'avais vu que les append de CLOB étaient super longs.

[mnitu]

Salut Waldar,
Je pense que t’as peut être, un peut trop simplifié ton problème. Dans ce qui suit la table big (N fois select * from all_objects) contient presque 800 000 enregistrements. Une colonne id a été ajoutée et valorisée à partir du rownum pour devenir la clé primaire. La table big_pilote simule la table envoie (id, owner, object_type), clé primaire id. La vue donnes XML est

Code :

1
2
3
4
 
CREATE OR REPLACE VIEW DONNEES_XML AS
SELECT id, XMLElement("SuperBalise", object_name) AS col_XML
  FROM big

La requête suivante ramène 17 enregistrements en 6 secondes

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
26
27
28
29
30
31
32
33
34
 
SELECT object_type, XmlAgg(col_XML).extract('/') xml, count(*)
  FROM (
    SELECT t.*, trunc((rn - 1)/10000) grp
      FROM (
            SELECT b.owner, b.object_type, a.id, a.col_XML,
                   row_number() over(partition BY b.owner, b.object_type ORDER BY b.id) rn
              FROM donnees_xml a
                   JOIN
                   big_pilote b
                ON a.id = b.id
             WHERE b.owner = 'TOTO'
    ) t   
)
GROUP BY object_type, grp
/
   	OBJECT_TYPE	XML	COUNT(*)
1	TYPE		<CLOB>	320
2	VIEW		<CLOB>	8448
3	INDEX		<CLOB>	10000
4	INDEX		<CLOB>	5168
5	TABLE		<CLOB>	10000
6	TABLE		<CLOB>	4560
7	PACKAGE		<CLOB>	1344
8	SYNONYM		<CLOB>	64
9	TRIGGER		<CLOB>	10000
10	TRIGGER		<CLOB>	3344
11	FUNCTION 	<CLOB>	1024
12	SEQUENCE 	<CLOB>	1120
13	PROCEDURE	<CLOB>	10000
14	PROCEDURE	<CLOB>	10000
15	PROCEDURE	<CLOB>	10000
16	PROCEDURE	<CLOB>	4560
17	PACKAGE BODY	<CLOB>	1344

Chaque clob contient count(*) enregistrements de type

Code :

1
2
3
 
<SuperBalise>JKL</SuperBalise>
<SuperBalise>MNO</SuperBalise>

[Waldar]

Oui c'est fort possible, là où j'ai simplifié le problème c'est dans la vue qui retourne le XML, en réalité je récupère par objet entre 5000 et 150.000 caractères.

Je n'ai pas encore essayé de les découper en varchar2, c'est la prochaine étape.

J'ai pu récupérer mes traces en attendant.

Traitement de référence
Requête du curseur :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    88175      1.75       2.07        964       1008          0       88174
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    88177      1.75       2.07        964       1008          0       88174
 
Misses IN library cache during parse: 1
Optimizer mode: ALL_ROWS
 
Elapsed times include waiting ON following events:
  Event waited ON                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered READ                         37        0.04          0.47
  db file sequential READ                         8        0.00          0.01
********************************************************************************

Requêtes qui vont chercher le XML un par un :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  88174      8.35       8.91         76        759          0           0
Fetch    88174    429.15     473.41      14418    1243061     705119       88174
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   176349    437.51     482.33      14494    1243820     705119       88174
 
Misses IN library cache during parse: 1
Misses IN library cache during execute: 1
 
Elapsed times include waiting ON following events:
  Event waited ON                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential READ                     15607        0.44         38.74
********************************************************************************

Sur le reste du traitement :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1    132.57     247.87       6434    1527790    9074615           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    132.57     247.87       6434    1527790    9074615           1
 
Misses IN library cache during parse: 1
Optimizer mode: ALL_ROWS
 
Elapsed times include waiting ON following events:
  Event waited ON                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential READ                      4383        0.32          4.45
  db file scattered READ                       1022        0.18          1.49
  latch: object queue header operation            1        0.02          0.02
  SQL*Net message TO client                       1        0.00          0.00
  SQL*Net message FROM client                     1        0.01          0.01
********************************************************************************

Traitement allégé cf. mnitu
J'ai conservé l'idée de construire le fichier directement dans le curseur, et je fais un XMLAgg en utilisant ma plage d'identifiants récupérés directement à partir du curseur.
Cette fois-ci je parviens à un résultat, mais environ deux fois moins rapide que le traitement de référence.
Requête du curseur :

Code :

1
2
3
4
5
6
7
8
9
10
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       13      0.00       0.31          0       1008          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.00       0.31          0       1008          0          12
 
Misses IN library cache during parse: 1
Optimizer mode: ALL_ROWS

Requêtes qui vont chercher les XMLAgg :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     12      0.06       0.06          0        783          0           0
Fetch       12   1086.32    1347.93     147634    9884451   72114827          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25   1086.39    1348.00     147634    9885234   72114827          12
 
Misses IN library cache during parse: 1
Misses IN library cache during execute: 1
Optimizer mode: ALL_ROWS
 
Elapsed times include waiting ON following events:
  Event waited ON                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  WRITE complete waits                            4        0.99          3.96
  db file sequential READ                     73312        1.32        154.43
  latch: cache buffers chains                     1        0.00          0.00
  db file scattered READ                      37029        2.26         63.08
  latch: object queue header operation            1        0.00          0.00
  latch: cache buffers lru chain                  1        0.00          0.00
********************************************************************************

Sur le reste du traitement :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00     252.44      40451    1760811   11465481           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00     252.44      40451    1760811   11465481           1
 
Misses IN library cache during parse: 1
Optimizer mode: ALL_ROWS
 
Elapsed times include waiting ON following events:
  Event waited ON                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential READ                     40450        0.24         28.33
  SQL*Net message TO client                       1        0.00          0.00
  SQL*Net message FROM client                     1        0.01          0.01
********************************************************************************

J'ai suivi le conseil de McM pour mesurer les temps de DBMS_LOB.APPEND, ça m'a l'air plutôt correct.
Les temps pour un fichier (les autres sont dans les mêmes ordre de grandeur) en centièmes de secondes :

Code :

1
2
3
4
5
header           0
XMLAgg       13322
append clob    658
footer          37
WRITE disk    1521

[mnitu]

Requêtes qui vont chercher le XML un par un :

Code :

1
2
3
4
5
6
 
call     disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Fetch    14418    1243061     705119       88174
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   14494    1243820     705119       88174

Requêtes qui vont chercher les XMLAgg :

Code :

1
2
3
4
5
6
call     disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Fetch       147634    9884451   72114827          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       147634    9885234   72114827          12

Que est-ce qu'il explique cette différence ?
Que voit-on dans les plans d'exécution ?

[Waldar]

L'explain plan me paraît correct.

Sur le traitement un à un, il fait ce qui est attendu, index unique scan + rowid access.
Sur le traitement ensembliste, il fait un index full scan + rowid access + hash join...
Par contre sur une de mes tables de mon modèle (que j'appelle aussi en XMLAgg dans ma vue), je me retrouve avec cette interprétation dans l'EXPLAIN PLAN de la trace :

Code :

1
2
   9817  SORT AGGREGATE (cr=18728 pr=0 pw=0 time=0 us)
 142105   INDEX RANGE SCAN PK_TABLE2 (cr=17408 pr=0 pw=0 time=62281 us cost=3 size=1674 card=18)

Je n'ai pas réussi à interpréter le time=62281, mais j'imagine que ce n'est pas bon (j'ai 0 partout ailleurs).

J'ai pu améliorer le traitement initial (edit : en fait temps similaire, mais code relativement plus simple) avec les idées récupérées ici + l'utilisation d'une collection.
J'ai un peu moins de code pour la gestion des fichiers en créant dès le départ les bonnes plages :

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
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
CREATE type nt_col1 AS TABLE of number(1);
 
declare
    v_file                  CLOB;
    v_clob                  CLOB;
    v_date_extract          VARCHAR2( 30);
    v_file_name             VARCHAR2( 50);
    v_file_dir     CONSTANT VARCHAR2(  3) DEFAULT 'TST';
    v_header                VARCHAR2(100);
    v_footer                VARCHAR2(100);
 
begin
 
    FOR c_query IN
    (
    WITH sr AS
    (
    SELECT col1, code,
           trunc((row_number() over (partition BY code ORDER BY col1 ASC)-1)/3) AS grp
      FROM envoi
     WHERE chk = 'X'
    )
      SELECT code, grp,
             count(*) AS nb_rows,
             cast(collect(col1) AS nt_col1) AS tt
        FROM sr
    GROUP BY code, grp
    ORDER BY code, grp
    )
    loop
 
      -- Initialisation du CLOB 
      DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
      v_date_extract := to_char(systimestamp, 'yyyymmdd_hh24missff');
      v_file_name    := 'Fichier_' || c_query.code || '_' || v_date_extract || '.xml';
 
      v_header       := '<MonFichier>' || chr(13) || chr(10) ||
                        lpad(' ', 4, ' ') || '<MonCode>'   || c_query.code   || '</MonCode>'   || chr(13) || chr(10) ||
                        lpad(' ', 4, ' ') || '<DateEnvoi>' || v_date_extract || '</DateEnvoi>' || chr(13) || chr(10);
 
      -- Rajout du header
      DBMS_LOB.WRITEAPPEND(v_file, length(v_header), v_header);
 
      FOR i IN c_query.tt.first..c_query.tt.last
      loop
 
        -- Sélection du XML dans la vue 
        SELECT lpad(' ', 8, ' ') || x.col_xml.getClobVal() || chr(13) || chr(10) INTO v_clob
          FROM v_donnees_xml x
         WHERE x.col1 = c_query.tt(i);
 
        -- Concaténation du XML dans le CLOB
        DBMS_LOB.APPEND(v_file, v_clob);
 
      end loop;
 
      -- Écriture du fichier
      v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(c_query.nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
 
      -- Rajout du footer
      DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
      DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
      DBMS_LOB.FREETEMPORARY(v_file);
 
    end loop;
 
end;
/

Malheureusement mon crédit-temps sur ce projet touchant à sa fin, je vais me contenter de ces améliorations.

Merci à tous, toutes vos propositions m'ont bien aidées.

[mnitu]

Je continu à penser que parcourir les données une seul fois devrait donner un meilleurs temps de réponse. Mais si le temps de réponse est acceptable c’est OK.
Par contre la solution qui détermine d’abord les plages des identifiants à extraire permet une optimisation plus intéressante encore qui via la parallélisassions des traitements d’écriture des fichiers.