Discussion :

[dark_mouette]

Bonjour à tous, et voici mon interrogation.

J'ai une table de statistiques qui est mise à jour tous les 3 mois. Cette dernière est très très longue (entre 48 et 72h).

On m'a demandé de l'optimiser.
La procédure lançait une multitude d'UPDATE avec des données venant de vues assez complexe.

La première chose que j'ai fait, c'est de modifier les vues en vues matérialisées. En testant l'un des 20 UPDATE, je constatais déjà une formidable avancée.

Maintenant j'ai plusieurs choix pour mettre à jour ma table de statistiques globale. Cette dernière est constituée de 20 champs (1 par UPDATE) pour un champ composé des numéros INSEE du territoire que nous couvrons.

Soit je fais comme avant : 1 UPDATE par champ, vis à vis de sa table matérialisée, le tout mis dans une procédure.
Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
DBMS_OUTPUT.PUT_LINE('DEBUT de la MAJ champ 1 : '||SYSDATE);
UPDATE table_stat SET champ1 = 0 ;
COMMIT;
DBMS_MVIEW.REFRESH('stat1');
UPDATE table_stat
SET table_stat.champ1 =
  (SELECT stat1.champ1
  FROM stat1
  WHERE stat1.cd_insee = table_stat.cd_insee
  );
COMMIT;
DBMS_OUTPUT.PUT_LINE('FIN   de la MAJ champ1 : '||SYSDATE);

Je répète cela 20 fois, et j'obtiendrai le résultat escompté.
De plus, afin d'avoir un suivi de la procédure, j'ajouterais un INSERT INTO vers un table faisant le suivi de la procédure (je verrais ainsi où j'en suis, et le temps que ça prend pour chaque parties).
J'avais lu sur divers site qu'il y avait aussi le DBMS_ALERT, mais je ne le trouve pas satisfaisant, de même pour le UTL_FILE.
Je ne vois pas plus simple qu'une table spécifique au suivi, si vous avez des idées, je suis preneur.

Mais je reviens à mes moutons (enfin mes stats).
Voyant la puissance des vues matérialisées, je me suis dit "pourquoi ne pas faire de cette table une vue matérialisée".
Or, je constate que je n'arrive pas à faire d'UPDATE, il faut absolument un SELECT.

Alors voilà, je viens là pour voir s'il y a une autre solution qu'une procédure qui UPDATE tout, ou alors s'il y a possibilité de faire une vue matérialisée (tapant sur 20 autres tables matérialisées) et ainsi pouvoir la refresh avec une ligne de commande.
A voir aussi si l'une des solutions est plus rapide et moins lourde que l'autre.

Je vous remercie d'avance pour votre aide
PS : j'espère avoir bien formulé ma demande.


EDIT 1 :

Je viens de penser à une autre solution.

I) Je fais un REFRESH de toutes mes 20 vues matérialisées
II) Je fais une table matérialisée avec une SELECT de ces 20 tables, pour obtenir mes 20 champs.

Pour faire la liaison, je prends le CD_INSEE d'une table de nomenclature, et je fais des jointures externes avec toutes les tables.

Avec ce SELECT, je crée ma vue matérialisée générale (avec les 20 champs)

Pour mettre à jour cette dernière, je crée une procédure qui va faire un REFRESH de mes 20 vues matérialisées, puis enfin, je fais un REFRESH de ma vue matérialisée générale (en toute fin de procédure).
Dans celle ci, je fais des INSERT INTO dans une table de suivi de la procédure.

Qu'en pensez vous?

Merci ^^

[McM]

Pour les traces, en effet, une table de trace est le meilleur moyen. Ne pas oublier de faire ta procédure de trace en autonomous, ça permet de savoir où ça en est même sans commit (et surtout en cas d'erreur) de la procédure appelante.

Ensuite pourquoi veux tu faire une VM qui pour regrouper les 20 champs dans un select.
Tu peux très bien faire

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
FOR i IN 1..20
LOOP
 DBMS_MVIEW.REFRESH('stat'|| i);
END LOOP;
UPDATE table_stat a
SET champ1 = (SELECT b.champ1 FROM stat1 b WHERE b.cd_insee = a.cd_insee),
champ2 = (SELECT b.champ1 FROM stat2 b WHERE b.cd_insee = a.cd_insee),
champ3 = (SELECT b.champ1 FROM stat3 b WHERE b.cd_insee = a.cd_insee),
champ4 = (SELECT b.champ1 FROM stat4 b WHERE b.cd_insee = a.cd_insee),
...
champ20 = (SELECT b.champ1 FROM stat20 b WHERE b.cd_insee = a.cd_insee)
COMMIT;

[dark_mouette]

Oui, si le nom des champs étaient bien champ1, champ2 etc...
Mais je les ai nommé ainsi pour simplifier ma demande, voici les vrais noms des champs :

CD_INSEE
NB_DATA_AP90
NB_DATA_AV90
NB_REF_HAB
NB_REF_REL
NB_ESP_COM
NB_ESP_COM_AP90
NB_ESP_COM_AV90
NB_ESP_COM_AVAP90
NB_ESP_NAT_COM
NB_ESP_NAT_COM_AP90
NB_ESP_NAT_COM_AV90
NB_ESP_NAT_COM_AVAP90
NB_ESP_PNPR_COM
NB_ESP_PNPR_COM_AP90
NB_ESP_PNPR_COM_AV90
NB_ESP_PNPR_COM_AVAP90
NB_ESP_ZNIEFF_COM
NB_ESP_ZNIEFF_COM_AP
NB_ESP_ZNIEFF_COM_AV
NB_ESP_ZNIEFF_COM_AVAP

[lola06]

Oui, si le nom des champs étaient bien champ1, champ2 etc...
Mais je les ai nommé ainsi pour simplifier ma demande, voici les vrais noms des champs :

Rien ne t'empèche d'utiliser la solution préconisée par McM, il suffit de remplacer champ1, ... par tes noms réels

[dark_mouette]

J'avais lu un peu rapidement.
En effet, mais je ne pourrais pas utiliser la boucle, mais faire un enchainement de REFRESH (comme je l'avais prévu dans un premier temps).

Puis un enchainement d'UPDATE.

Mais entre le refresh et cette procédure, lequel des 2 serait le plus rapide?

[McM]

1 update avec 20 sous-select est préférable à 1 truncate + 1 insert avec 20 sous-select (refreshVM) + 1 update (un seul sous select)

Bien sur il y a des cas où ce n'est pas vrai (entre 2 bases par dblink par exemple).

[dark_mouette]

Bonjour,
Alors, j'ai opté pour la solution d'encapsulation de vues matérialisées.

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
 
BEGIN
 
Insert INTO table suivi ('0/21 Début procédure', sysdate); COMMIT;
 
Insert INTO table suivi ('1/21 DEBUT refresh VM1', sysdate); COMMIT;
DBMS_MVIEW.REFRESH('VM1');
Insert INTO table suivi ('1/21 FIN refresh VM1', sysdate); COMMIT;
 
Insert INTO table suivi ('2/21 DEBUT refresh VM2', sysdate); COMMIT;
DBMS_MVIEW.REFRESH('VM2');
Insert INTO table suivi ('2/21 FIN refresh VM2', sysdate); COMMIT;
 
...
 
Insert INTO table suivi ('20/21 DEBUT refresh VM20', sysdate); COMMIT;
DBMS_MVIEW.REFRESH('VM20');
Insert INTO table suivi ('20/21 FIN refresh VM20', sysdate); COMMIT;
 
DBMS_MVIEW.REFRESH('VM_stats');
Insert INTO table suivi ('21/21 FIN procédure', sysdate); COMMIT;
 
END;

Voici en gros ce que ça donne.
Et là je me suis dit "et pourquoi ne pas faire la différence entre le début et la fin d'une étape afin d'avoir la durée de cette étape".

Pour cela, j'aimerais donc faire la différence enter sysdate_n-2 et sysdate_n-1.

Est ce que ce n'est pas trop complexe pour la procédure (ce qui rallongerait la durée du traitement)?

[McM]

Soit tu utilises dbms_utility.get_time (donne un ticks en centième de sec)
Soit un sysdate.

Ensuite il suffit de faire une différence dessus. Pour le premier cas, c'est facile ce sont des centièmes de secondes, pour les dates, la différence donne un nombre de jours (donc faut multiplier par 24 pour avoir en heure, puis par 3600 pour avoir en secondes).


PS : pas d'inquiétude, le rajout de ces calculs ne va pas augmenter ta durée globale de traitement de plus d'une seconde.

[dark_mouette]

Ah oui, j'avais oublié le dbms_utility.get_time
Cela peut être utile, mais comme mon champ est de type date, je ne pense pas qu'il l'accepte :/

Pour le sysdate, j'aimerais faire la différence entre le début et la fin assez simplement, mais je ne vois pas comment (sachant que cette table de suivi, je ne la truncate pas :/)

Alors je ne sais pas s'il y a un moyen pou récupérer juste les 2 derniers enregistrements

[McM]

Ce n'est pas clair. Tu veux afficher la durée avec un select from suivi, ou tu veux l'insérer dans la table suivi durant ton traitement ?

[dark_mouette]

Je veux l'insérer

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
 
INSERT INTO suivi VALUES (' 1/21 : DEBUT REFRESH : mv_stat_nb_data_ap90 du '||TO_CHAR(SYSDATE,'DD/MM/YYYY'),SYSDATE); COMMIT;
 
DBMS_MVIEW.REFRESH('mv_stat_nb_data_ap90');
 
INSERT INTO suivi VALUES (' 1/21 : FIN   REFRESH : mv_stat_nb_data_ap90 du '||TO_CHAR(SYSDATE,'DD/MM/YYYY'),SYSDATE); COMMIT;
 
INSERT INTO suivi (etape,heure)
SELECT ' 1/21 : DUREE DE L ETAPE DU '||TO_CHAR(SYSDATE,'DD/MM/YYYY'), ((t1.heure - t2.heure)*24)*3600
FROM 
(
SELECT MAX(heure) AS heure
FROM suivi
WHERE etape = ' 1/21 : FIN   REFRESH : mv_stat_nb_data_ap90 du '||TO_CHAR(SYSDATE,'DD/MM/YYYY')
)t1,
(
SELECT MAX(heure) AS heure
FROM suivi
WHERE etape = ' 1/21 : DEBUT REFRESH : mv_stat_nb_data_ap90 du '||TO_CHAR(SYSDATE,'DD/MM/YYYY')
)t2

Problème, ce qui ressort ce sont des secondes, et dans ma table suivi, le champ heure est en format date.
Après, je peux faire en sorte de changer le format du champ, et ça ne devrait pas poser de problème je pense.

Mais je doute que j'arrive à avoir ma durée de cette façon : 'heure-minute-seconde'.