Explain Plan

**claralavraie** · 19/05/2006, 09h19

Bonjour,

j'ai une procédure PL/SQL à optimiser...15minutes au lieu de 1h30..
j'ai du boulot..;-)

pour cela j'utilise le explain Plan pour essayer d'opitmiser les requêtes que j'utilise..
Je n'ai jamais utilisé l'explain Plan...
Quand j'execute une de mes requêtes, j'ai un table Access Full.
Cela veut il dire que je dois mettre un index au niveau de la colonne de cette table ou autre chose?
Merci de votre aide.

**Nico57** · 19/05/2006, 09h31

Euh, déjà avant de tracer des plans, peut-être revoir le code de la procédure elle-même.... Est-il normal (point de vue charge à effectuer) qu'elle prenne 1h30 ? Est-elle bien développée?

Une fois seulement que tu auras ré-écrit un code propre, là, tu pourras te lancer dans l'explain plan, et effectivement créer de judicieux index là où tu te rends compte que ça accélérerait les traitements.

**claralavraie** · 19/05/2006, 09h35

Puis je te montrer une de mes requêtes et me dire ce que tu en penses?
Je ne suis pas très douée dans l'optimisation sous oracle ;-)

**orafrance** · 19/05/2006, 09h49

bien sûr

Quand au code PL/SQL assure toi d'éviter les mises à jour unitaire qui est l'erreur la plus fréquente (ie : un insert, delete ou update dans une boucle) et d'éviter l'utilisation de COUNT au lieu de NOT EXISTS pour chercher l'existence d'un enregistrement.

**claralavraie** · 19/05/2006, 09h59

ah ok alors déjà je fais 3 insert dans ma boucle et je fais 3 count aussi....
mais comment faire pour ne pas faire d'insert dans ma boucle?
je fais un count puis j'insère l'information dans une table pour chaque donnée de ma boucle...

**orafrance** · 19/05/2006, 10h00

faudrait voir le code

INSERT ... SELECT par exemple marche très bien

**claralavraie** · 19/05/2006, 10h08

Bon le plus simple est de donner mon code PL/SQL:

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
CREATEORREPLACEPROCEDURECOUNT_TABLE IS
 
code_S varchar2(20);
libelle_S varchar2(30);
code_M varchar2(20);
libelle_M varchar2(30);
code_C varchar2(20);
libelle_C varchar2(30); 
le_sql VARCHAR2(5000);
extract_S varchar2(30);
extract_M varchar2(30);
extract_C varchar2(30);
comptage number;
BEGIN
-- on vide la table
executeimmediate'TRUNCATE TABLE TABLE_COUNT';
 
SELECTcocod, colib1 INTO code_S, libelle_S
FROMcode 
WHEREcocod='S';
 
SELECT nom INTOextract_S
FROMext 
WHERE exnum ='RS';
 
 
SELECTcocod, colib1 INTO code_M, libelle_M
FROMcode 
WHEREcocod='W';

-- Selection de la table correspondant à l'extraction REFUSEMAIL 
SELECT nom INTOextract_M
FROMext 
WHERE exnum ='RE';

-- Selection des codes et libellés pour les courriers 
SELECTcocod, colib1 INTO code_C, libelle_C
FROMcode 
WHEREcocod='C';


 

SELECT nom INTOextract_C
FROMext 
WHERE exnum ='NP';

-- Boucle sur les magasins
FOR c IN(SELECT decode(length(shop_id),2,'100'||shop_id,3,'10'||shop_id,1,'1000'||shop_id,4,'1'||shop_id) shop FROMcrm_shop WHERE country_id ='FR')
LOOP
 
BEGIN
le_sql:='select count(pers) from (select pers from idet, cart, part where cadad<>''21001231'' and camot= '' '' and fdpart.patvo like ''06%''and fdpart.pacl1=''Y'' and pers = capor and idpor = papor and idagr= '':1'' minus select expor from '||extract_S||')';
EXECUTEIMMEDIATE le_sql INTO comptage USING c.shop;
INSERTINTOTABLE_COUNT(shop_id, action_type, action_type_lab, people_number)VALUES(c.shop, code_S,libelle_S, comptage); 
COMMIT;
END;
 
BEGIN
le_sql:='select count(pers) from (select pers from idet, cart, part where cadad<>''21001231'' and camot= '' '' and FDPART.PAAEM<>'' '' and FDPART.PACL2 IN(''Y'', ''A'', ''T'') and pers = capor and idpor = papor and idagr= '':1'' minus select expor from '||extract_M||')';
EXECUTEIMMEDIATE le_sql INTO comptage USING c.shop;
INSERTINTOTABLE_COUNT(shop_id, action_type, action_type_lab, people_number)VALUES(c.shop, code_M,libelle_M, comptage); 
COMMIT;
END;

 
BEGIN
le_sql:='select count(pers) from (select pers from idet, cart where cadad<>''21001231'' and camot= '' '' and pers= capor and idagr= '':1'' minus select expor from '||extract_C||')';
EXECUTEIMMEDIATE le_sql INTO comptage USING c.shop;
INSERTINTOTABLE_COUNT(shop_id, action_type, action_type_lab, people_number)VALUES(c.shop, code_C,libelle_C, comptage); 
COMMIT;
END;
ENDLOOP;
EXCEPTION
WHENOTHERSTHENNULL;
END;
/

**orafrance** · 19/05/2006, 10h11

merci de le mettre en piéce jointe, il y a des soucis d'espace

pourquoi tu fait du EXECUTE IMMEDIATE ???

**claralavraie** · 19/05/2006, 10h15

Ah oui en effet, je vais l'ajouter en pièce jointe.

j'ai déjà beaucoup modifier ce code mais il met toujours autant de temps.

**Nico57** · 19/05/2006, 10h26

Pour répondre à Fred, je pense qu'elle fait du EXECUTE Immediate car le nom des tables d'où elle sélectionne les données n'est pas fixe...

Code :

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

1
2
3
4
5
6
7
 
 
SELECT nom INTO extract_S
FROM ext 
WHERE exnum ='RS';
 
le_sql:= ... from '||extract_S||')';

**claralavraie** · 19/05/2006, 10h29

Oui je fais du EXECUTE IMMEDIATE car tous les jours le nom des tables change...

**orafrance** · 19/05/2006, 10h39

Commence par mettre çà pour trouver les libellés :

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
   FOR r_code IN ( SELECT cocod, r_code
                               FROM code
                                 WHERE cocod IN ('C','S','W'))
   LOOP
   CASE r_code.cocod
       WHEN 'C' THEN 
                               code_c := r_code.cocod;
                               libelle_c := r_code.colib1;
       WHEN 'S' THEN 
                               code_m := r_code.cocod;
                               libelle_m := r_code.colib1;
       WHEN 'W' THEN 
                               code_s := r_code.cocod;
                               libelle_s := r_code.colib1;
   END LOOP;
 
   FOR r_ext IN ( SELECT nom,exnum
                                  FROM ext
                                 WHERE exnum IN ('RS','RE','NP'))
   LOOP
   CASE r_code.exnum
       WHEN 'RE' THEN 
                               extract_m := r_ext.exnum;
                               extract_m := r_ext.nom;
       WHEN 'RS' THEN 
                               extract_s := r_code.exnum;
                               extract_s := r_code.nom;
       WHEN 'NP' THEN 
                               extract_c := r_code.exnum;
                               extract_c := r_code.nom;
   END LOOP;

pour le reste, tu vas devoir nous expliquer le but de la manoeuvre

**claralavraie** · 19/05/2006, 10h51

OK je vais essayer ce code...
Voici quelques explications sur mon programme PL_SQL:
Les 3 tables qui changent de noms tous les jours contiennent des personnes correspondant à 3 extractions différents.

Mon but est de selectionner pour chaque requête, toutes les personnes avec diverses critères en exlcuant, pour le 1 ère requête la 1ère extraction, le 2 ème requête la 2ème extraction et le 3 ème requête la 3 ème extraction.
ensuite j'insère dans une table récapitulative, le nombre de personnes que je trouve pour chaque requête.
j'espère que c'est un peu plus clair :-)

**claralavraie** · 22/05/2006, 09h15

Comment faire pour éviter de faire les insert dans la boucle?
je me demande si c'est possible?

**orafrance** · 22/05/2006, 09h37

C'est pas possible ainsi ?

Code :

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

INSERT ... SELECT <la requête du curseur>

**claralavraie** · 22/05/2006, 10h12

Vous m'avez dit que les insert dans une boucle prenez du temps.
Si je suis votre mtéthode, mes insert se feront aussi dans la boucle non?

**orafrance** · 22/05/2006, 10h16

Non, dans ce cas tu ne fais plus de boucle

**claralavraie** · 22/05/2006, 10h30

ok donc si je comprends bien au lieu de faire comme suit:

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
-- Boucle sur les magasins
FOR c IN(SELECT decode(length(shop_id),2,'100'||shop_id,3,'10'||shop_id,1,'1000'||shop_id,4,'1'||shop_id) shop FROMcrm_shop WHERE country_id ='FR')
LOOP
 
BEGIN
le_sql:='select count(pers) from (select pers from idet, cart, part where cadad<>''21001231'' and camot= '' '' and fdpart.patvo like ''06%''and fdpart.pacl1=''Y'' and pers = capor and idpor = papor and idagr= '':1'' minus select expor from '||extract_S||')';
EXECUTEIMMEDIATE le_sql INTO comptage USING c.shop;
INSERT INTO TABLE_COUNT(shop_id, action_type, action_type_lab, people_number)VALUES(c.shop, code_S,libelle_S, comptage); 
COMMIT;
END;

END LOOP;

je vais faire comme suit :

Code :

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

1
2
3
INSERT INTO TABLE_COUNT(shop_id, action_type, action_type_lab, people_number)
SELECT....

Mais mon select est du sql dynamique.
Alors je ne sais pas si c'est possible?

**orafrance** · 22/05/2006, 10h38

Et alors ? Tu fais un insert dynamique

**claralavraie** · 22/05/2006, 10h50

Oui bien sur lol...
donc cela sera ainsi

Code :

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

1
2
3
4
5
6
7
le_sql:='INSERT INTO CUSTOMER_COUNT (shop_id, action_type, action_type_lab, people_number)
SELECT c.shop, code_action_SMS, libelle_action_SMS, count(idpor)
FROM ((select idpor from mkt.fdidet, mkt.fdcart, mkt.fdpart where cadad<>''21001231'' and camot= '' '' and fdpart.patvo like ''06%''and fdpart.pacl1=''Y'' and idpor = capor and idpor = papor and idagr= :1 minus select expor from mkt.'||nom_extract_SMS||')
groupby c.shop, code_action_S, libelle_action_S'
EXECUTEIMMEDIATE le_sql INTO comptage USING c.shop

et je n'aurais pas besoin de faire une boucle?
c'est exact

Explain Plan

Oracle

Discussions similaires

Partager

Partager