Discussion :

[elitost]

Bonjour,

j'ai une table d'environ 3 millions d'enregistrements sur laquelle je souhaiterais parcourir tous les éléments pour effectuers des traitements à partir de 2 colonnes.

Quelle est la meilleures approche en terme de rapidité ?

Avez vous des exemples de code pour faire ça ?

Merci d'avance,

[miloux32]

Bonjour,

j'ai une table d'environ 3 millions d'enregistrements sur laquelle je souhaiterais parcourir tous les éléments pour effectuers des traitements à partir de 2 colonnes.

Quelle est la meilleures approche en terme de rapidité ?

Avez vous des exemples de code pour faire ça ?

Merci d'avance,

si tu veux parcourir tous les enregistrements y'a pas des masses de possibilités .... tu veux faire quoi comme traitement ?

[elitost]

Voilà le code que je souhaite améliorer :

Code sql :

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
DECLARE
  c CLOB;
  n NUMBER:=0;
BEGIN
 
  FOR i IN (SELECT doc_id,doc_bfile FROM vic) LOOP
    BEGIN
 
      Ctx_Doc.policy_filter('dgt_policy',i.doc_bfile,c);
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM);
    END;
    UPDATE VIS_DOCUMENTS_T SET doc_clob=c WHERE doc_id=i.doc_id;
    n:=n+1;
    IF MOD(n,500)=0 THEN
      DBMS_OUTPUT.PUT_LINE(n);
      COMMIT;
    END IF;
  END LOOP;
END;

Pour le moment, l'instruction SELECT doc_id,doc_bfile FROM vic de la boucle FOR prends pas mal de temps (dépendant bien sur du nombre d'enregistrement dans la table vic )

Au final, avec ce bout de code, pour 100 000 enregistrements dans la table vic cela a pris environ 5 heures cette nuit.

En attendant je vais expérimenter les différentes solutions ici :
http://www.psoug.org/reference/array_processing.html

Si vous avez d'autres propositions, conseils, etc... je suis preneur

Merci d'avance

[mnitu]

Voilà le code que je souhaite améliorer :

Code sql :

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
DECLARE
  c CLOB;
  n NUMBER:=0;
BEGIN
 
  FOR i IN (SELECT doc_id,doc_bfile FROM vic) LOOP
    BEGIN
 
      Ctx_Doc.policy_filter('dgt_policy',i.doc_bfile,c);
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM);
    END;
    UPDATE VIS_DOCUMENTS_T SET doc_clob=c WHERE doc_id=i.doc_id;
    n:=n+1;
    IF MOD(n,500)=0 THEN
      DBMS_OUTPUT.PUT_LINE(n);
      COMMIT;
    END IF;
  END LOOP;
END;

...

Code :

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

1
2
 
SELECT doc_id,doc_bfile FROM vic

Ca sélectionne tout donc il y a un seul chemin full table (ou index) scan. Ca pourrait s’optimiser par un traitement parallèle.
Ensuite l'update en boucle c'est mauvais. Il faut le faire une seule fois à la fin.
Commit en boucle c'est mauvais. Il faut le faire une seul fois à la fin et estimer le besoin d'éspace de rollback.
When Others non suivi par un Raise c'est le bug numéro 1 de la programmation en PL/SQL.

[elitost]

Code :

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

1
2
 
SELECT doc_id,doc_bfile FROM vic

Ca sélectionne tout donc il y a un seul chemin full table (ou index) scan. Ca pourrait s’optimiser par un traitement parallèle.
Ensuite l'update en boucle c'est mauvais. Il faut le faire une seule fois à la fin.
Commit en boucle c'est mauvais. Il faut le faire une seul fois à la fin et estimer le besoin d'éspace de rollback.
When Others non suivi par un Raise c'est le bug numéro 1 de la programmation en PL/SQL.

Ok, mais pourrais tu éclairer ma lanterne sur comment faire :
- Le traitement en parallèle ? je lancerais donc plusieurs procédures avec une plage de données en argument ? (si c'est ça j'ai du code pour le faire)
- Comment faire l'update en une seule fois alors que j'ai une conversion sur chaque ligne ?
- Comment estimer le besoin d'espace pour le rollback ?
- Mon when others n'est pas suivi par un raise pour ne pas interrompre la boucle et juste imprimer l'erreur...aurais tu une autre solution ?

Merci d'avance

[mnitu]

Ok, mais pourrais tu éclairer ma lanterne sur comment faire :
- Le traitement en parallèle ? je lancerais donc plusieurs procédures avec une plage de données en argument ? (si c'est ça j'ai du code pour le faire)
- Comment faire l'update en une seule fois alors que j'ai une conversion sur chaque ligne ?
...

OK. Voilà les idées de base des deux techniques détaillées dans le bouquin de Tom Kyte, Expert Oracle Database Architecture …
Au départ la procédure a la structure suivante

Code :

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

1
2
3
4
5
6
7
8
 
procedure p as
begin
  for x in (select * from t1) Loop
    traitement complex
    Update ou Insert une autre table t2
  end loop;
end;

• Procédure parallel pipelined
  L’idée est d’inverser le traitement via un procedure parallel pipelined qui prend comme paramètre d’entré un curseur. Quelque chose de type
  

  Code :

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

  1
  2
  3
  4
  5
  6
  7
   
  insert /* +append */ into t2
    Select *
      from table(parallel_p(cursor(select /* +parallel(t1) */ *
                                    From t1)
                            )
                )

• DYP (do yourself parallelisme)
  L’idée est de partitionner la table t1 en N plages des rowids et de les stocker dans une table de travail avec un job_id comme identifiant. Ces N jobs sont programmes via dbms_job pour exécuter une procédure qui a comme paramètre d’entré le job_id. La procédure interroge la table de travail pour récupérer les rowsid associés au job_id et utilise un curseur de type
  

  Code :

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

  1
  2
   
  for x in (Select * from t1 where rowid between rowdeb and rowfin)

Les exemples complètes se trouvent dans le bouquin indiqué et je pense qu’aussi sur le site de Tom Kyte. La documentation d'Oracle devrait fournir elle aussi des examples.

.../...

[mnitu]

Ok, mais pourrais tu éclairer ma lanterne sur comment faire :
...
- Mon when others n'est pas suivi par un raise pour ne pas interrompre la boucle et juste imprimer l'erreur...aurais tu une autre solution ?

Merci d'avance

.../...
When Others non suivi par un Raise c’est un bug.
Voilà l’exemple dans ton code.
Si quelque chose se passe mal dans l’appel de la procédure Ctx_Doc.policy_filter alors un appel à la procedure dbms_output.put_line serait fait en passant comme paramètre le message d’erreur.
D’abord deux remarques :

• Remarque 1 : En fonction de la version d’Oracle cette appel peut planter lui aussi parce que rien ne garanti un message d’erreur de moins de 255 caractères ! Donc perdu !
• Remarque 2 : Il se peut que l’appel de la procédure se fait dans un contexte ou il n’y pas d’output et donc l’anomalie est complètement caché.

Mais analysons ensemble la suite du traitement. Après avoir appelé la procédure dbms_output.put_line l’exécution du code continu avec la commande update. Cette commande va mettre à jour la table t2 avec la valeur de la variable c de type CLOB qui est en gros non définie. Cella veut dire que suite a ce update dans la table il aura n’importe quoi : soit une valeur d’une autre ligne, soit null soit qui sait quoi.

J’ai bien plus d’autres arguments contre ce bug. Donc ce que je te propose c’est simple et efficace: supprime-le.
Pensez que le monde des programmeurs serrait meilleur sans When Others!

[elitost]

ok, je vais voir ce que je peux faire avec tout ça.

Merci bcp pour ces informations.

[dgi77]

• Procédure parallel pipelined
  L’idée est d’inverser le traitement via un procedure parallel pipelined qui prend comme paramètre d’entré un curseur. Quelque chose de type
  

  Code :

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

  1
  2
  3
  4
  5
  6
  7
   
  insert /* +append */ into t2
    Select *
      from table(parallel_p(cursor(select /* +parallel(t1) */ *
                                    From t1)
                            )
                )

Tout ça m'intéresse fortement également, est-ce que tu as un exemple de procédure "parallel_p" à nous montrer.
J'ai l'impression qu'il faut récupérer un curseur en paramètre en entrée, et je suppose qu'il doit y avoir des paramètres en sortie assez fournis également...
Si tu as une illustration...

[mnitu]

Tout ça m'intéresse fortement également, est-ce que tu as un exemple de procédure "parallel_p" à nous montrer.
J'ai l'impression qu'il faut récupérer un curseur en paramètre en entrée, et je suppose qu'il doit y avoir des paramètres en sortie assez fournis également...
Si tu as une illustration...

Il y en a ici
[Edit]
et un autre ici
[/Edit]

[jmguiche]

Quand on veut optimiser, il faut savoir ou passe le temps.
La méthode de programmation utilisée n'est certe pas optimale, mais je pense que le temps passe dans Ctx_Doc.policy_filter.

Avant de faire une réécriture de ton code, lance ta proc en remplaçant l'appel à Ctx_Doc.policy_filter par null; et mesure le temps. Sur une machine moderne, normalement, pour 3 millions d'enregistrement cela va prendre un temps qui se compte en minutes, peut être en dizaines, mais pas plus, mais pas en heures.

Tu te rendra donc probablement compte que cet appel bouffe la majeure partie de ton temps de traitement et que tu ne changera pas d'ordre de grandeur de temps de traitement en changeant beaucoup l'architecture de ton code.
Si je ne me trompe pas, la façon la plus simple si tu est en multipro est de faire des traitements paralelles avec des plages de doc_id ou avec des modulo de doc_id (moins performant pour des problèmes de contention au début, mais assez souple pour faire varier le nombre de process).
A condition de ne pas déjà saturer le CPU avec un seul traitement.... Mais aujourd'hui les machines sont multiproc.

[elitost]

Quand on veut optimiser, il faut savoir ou passe le temps.
La méthode de programmation utilisée n'est certe pas optimale, mais je pense que le temps passe dans Ctx_Doc.policy_filter.

Avant de faire une réécriture de ton code, lance ta proc en remplaçant l'appel à Ctx_Doc.policy_filter par null; et mesure le temps. Sur une machine moderne, normalement, pour 3 millions d'enregistrement cela va prendre un temps qui se compte en minutes, peut être en dizaines, mais pas plus, mais pas en heures.

Tu te rendra donc probablement compte que cet appel bouffe la majeure partie de ton temps de traitement et que tu ne changera pas d'ordre de grandeur de temps de traitement en changeant beaucoup l'architecture de ton code.
Si je ne me trompe pas, la façon la plus simple si tu est en multipro est de faire des traitements paralelles avec des plages de doc_id ou avec des modulo de doc_id (moins performant pour des problèmes de contention au début, mais assez souple pour faire varier le nombre de process).
A condition de ne pas déjà saturer le CPU avec un seul traitement.... Mais aujourd'hui les machines sont multiproc.

J'avais fais le test au début sans et avec traitement et effectivement c'est les traitements qui plombent tout, je vais regarder du côté du parallélisme...