Explain Plan

Version imprimable

19/05/2006, 09h19
claralavraie

Explain Plan

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.
19/05/2006, 09h31
Nico57

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.
19/05/2006, 09h35
claralavraie

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 ;-)
19/05/2006, 09h49
orafrance

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.
19/05/2006, 09h59
claralavraie

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...
19/05/2006, 10h00
orafrance

faudrait voir le code ;)

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

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

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
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;
/

19/05/2006, 10h11
orafrance

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

pourquoi tu fait du EXECUTE IMMEDIATE ???
19/05/2006, 10h15
claralavraie

1 pièce(s) jointe(s)

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.:cry:
19/05/2006, 10h26
Nico57
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:

1 2 3 4 5 6 7 SELECT nom INTO extract_S FROM ext WHERE exnum ='RS'; le_sql:= ... from '||extract_S||')';
19/05/2006, 10h29
claralavraie

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

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

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
   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 :?

19/05/2006, 10h51
claralavraie

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 :-)
22/05/2006, 09h15
claralavraie

Comment faire pour éviter de faire les insert dans la boucle?
je me demande si c'est possible?
22/05/2006, 09h37
orafrance

C'est pas possible ainsi ?

Code:

INSERT ... SELECT <la requête du curseur>
22/05/2006, 10h12
claralavraie

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?
22/05/2006, 10h16
orafrance

Non, dans ce cas tu ne fais plus de boucle ;)

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

Code:

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:

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?

22/05/2006, 10h38
orafrance

Et alors ? Tu fais un insert dynamique ;)

Oui bien sur lol...
donc cela sera ainsi

Code:

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

22/05/2006, 11h09
claralavraie

Je ne comprends pas, je suis obligée de faire un loop?
22/05/2006, 11h15
orafrance

j'ai pas le temps de me pencher plus sur le cas mais le LOOP ne me parait pas nécessaire ;)

Note que je peux me tromper :oops:
22/05/2006, 14h45
claralavraie

Je pense que la boucle est indispensable puisque j'ai une curseur.
donc pour parcourir ce curseur, je dois faire une boucle.
22/05/2006, 15h19
orafrance
sauf si le select du cursor est déplacé dans une SELECT de l'insert :
Code:

1 2 3 4 5 FOR c IN (SELECT * FROM tab) LOOP insert into tab2 value (c.col1, c.col2); END LOOP;
devient :

Code:

insert into tab2 SELECT * from tab;

Ok d'accord.
donc j'ai ecrit la requeête suivante:
Code:

1 2 3 4 le_sql:='INSERT INTO COUNT(shop_id, action_type, action_type_lab, people_number) select shop_id, ''SMS'',''SMS'', count(idpor) from mkt.fdidet, mkt.fdcart, mkt.fdpart, ref.crm_shop where cadad<>''21001231'' and camot= '' '' and fdpart.patvo like ''06%'' and fdpart.pacl1=''Y'' and shop_id in (''359'',''59'') and idpor = capor and idpor = papor and idagr= decode(length(shop_id),2,''100''||shop_id, 3, ''10''||shop_id,1,''1000''||shop_id,4,''1''||shop_id) and country_id=''FR'' and sign_id=''01'' group by shop_id'; EXECUTEIMMEDIATE le_sql;
je n'ai aucune valeur qui est insérée dans ma table.
Quand je le fais manuellement, cette reque^te fonctionne bien.
Aurais-je fait une erreur dans mon sql dynamique?
pourtant j'ai vérifier la syntaxe et il me semble que c'est ok.

22/05/2006, 17h04
orafrance

t'as pensé au COMMIT ?

ok j'ai changé mon code, et je l'ai fait ainsi:

Code:

1
2
3
executeimmediate'INSERT INTO COUNT(shop_id, action_type,people_number) select shop_id, '||code_action_S||',count(idpor) from mkt.fdidet, mkt.fdcart, mkt.fdpart, ref.crm_shop where cadad<>''21001231'' and camot= '' '' and fdpart.patvo like ''06%'' and fdpart.pacl1=''Y'' and shop_id in (''359'',''59'') and idpor = capor and idpor = papor and idagr= decode(length(shop_id),2,''100''||shop_id, 3, ''10''||shop_id,1,''1000''||shop_id,4,''1''||shop_id) and country_id=''FR'' and sign_id=''01'' group by shop_id';

suivi d'un COMMIT ;-)

cela fonctionne donc maintenant.
Par contre quand j'appelle la variable code_action_s, cela ne fonctionne plus snif:cry:

22/05/2006, 17h31
claralavraie

ok tout va bien...
lol je commence à tout comprendre lol
22/05/2006, 17h52
claralavraie

en tout cas merci pour votre aide...
je pense que ma requ^te ne va plus prendre que quelques minutes maintenant.