Optimisation d'une procédure

[StringBuilder]

Plan d'exécution des update :

Sur CNT :

"Optimizer"	"Cost"	"Cardinality"	"Bytes"	"Partition Start"	"Partition Stop"	"Partition Id"	"ACCESS PREDICATES"	"FILTER PREDICATES"
"UPDATE STATEMENT"	"ALL_ROWS"	"2"	"1"	"55"	""	""	""	""	""
"UPDATE SOC1.CNP"	""	""	""	""	""	""	""	""	""
"TABLE ACCESS(BY INDEX ROWID) SOC1.CNP"	"ANALYZED"	"2"	"1"	"55"	""	""	""	""	""
"INDEX(UNIQUE SCAN) SOC1.CNP_IDX1"	"ANALYZED"	"1"	"1"	""	""	""	""	""CODSOC"=100 AND "ACHVTE"='V' AND "NUMCNT"=:4 AND "TQOI"='501' AND "CODPRO"=:5"	""

Sur EVT :

"Optimizer"	"Cost"	"Cardinality"	"Bytes"	"Partition Start"	"Partition Stop"	"Partition Id"	"ACCESS PREDICATES"	"FILTER PREDICATES"
"UPDATE STATEMENT"	"ALL_ROWS"	"12"	"1"	"75"	""	""	""	""	""
"UPDATE SOC1.EVT"	""	""	""	""	""	""	""	""	""
"TABLE ACCESS(BY INDEX ROWID) SOC1.EVT"	"ANALYZED"	"3"	"1"	"27"	""	""	""	""	""
"NESTED LOOPS"	""	"12"	"1"	"75"	""	""	""	""	""
"VIEW SYS.VW_NSO_1"	""	"9"	"1"	"48"	""	""	""	""	""
"SORT(UNIQUE)"	""	"9"	"1"	"92"	""	""	""	""	""
"NESTED LOOPS"	""	"8"	"1"	"92"	""	""	""	""	""
"NESTED LOOPS"	""	"7"	"1"	"68"	""	""	""	""	""
"TABLE ACCESS(BY INDEX ROWID) SOC1.EVE"	"ANALYZED"	"4"	"1"	"38"	""	""	""	""	""EVE"."DATEVE"<='20110816' AND ("EVE"."CODETA"='B' OR "EVE"."CODETA"='F' OR "EVE"."CODETA"='V') AND "EVE"."DATEVE">='20110629'"
"INDEX(RANGE SCAN) SOC1.W_EVE_IDX1"	"ANALYZED"	"3"	"1"	""	""	""	""	""EVE"."CODSOC"=100 AND "EVE"."ACHVTE"='V' AND "EVE"."TYPEVE"='LIV' AND "EVE"."TYPTIE"='CLI' AND "EVE"."SIGTIE"='168859'"	""
"TABLE ACCESS(BY INDEX ROWID) SOC1.EVP"	"ANALYZED"	"3"	"3"	"90"	""	""	""	""	""
"INDEX(RANGE SCAN) SOC1.EVP_IDX1"	"ANALYZED"	"2"	"3"	""	""	""	""	""EVP"."CODSOC"=100 AND "EVP"."ACHVTE"='V' AND "EVP"."TYPEVE"='LIV' AND "EVP"."NUMEVE"="EVE"."NUMEVE""	""
"TABLE ACCESS(BY INDEX ROWID) SOC1.PRO"	"ANALYZED"	"1"	"1"	"24"	""	""	""	""	""PRO"."CODZN15"='217440127'"
"INDEX(UNIQUE SCAN) SOC1.PRO_IDX1"	"ANALYZED"	"0"	"1"	""	""	""	""	""PRO"."CODSOC"=100 AND "PRO"."CODPRO"="EVP"."CODPRO""	""
"INDEX(RANGE SCAN) SOC1.EVT_IDX1"	"ANALYZED"	"2"	"1"	""	""	""	""	""EVT"."CODSOC"="CODSOC" AND "EVT"."ACHVTE"="ACHVTE" AND "EVT"."TYPEVE"="TYPEVE" AND "EVT"."NUMEVE"="NUMEVE" AND "EVT"."NUMPOS"="NUMPOS" AND "EVT"."NUMLIG"="$nso_col_6" AND "EVT"."NUMBLO"="$nso_col_7""	""EVT"."NUMBLO"="$nso_col_7""

[StringBuilder]

Envoyé par Golgotha

Tu dit plus haut que sur ton poste de DEV il n'y à pas de problème, juste pour confirmer, tu as donc bien lancé les même jeux de test avec les même requêtes et c'est beaucoup plus lent sur la prod que sur le DEV,

si oui : cela ne vient il pas de la machine ? manque de cache ? mal paramétré ? manque de perf ? processus en parallèle qui tourne ?

Oui, je confirme :

Sur la DEV, j'ai des données qui ont plusieurs mois.
Je vais donc tourner la procédure avec une date SYSDATE-140 plutôt que SYSDATE.

Je tourne donc sur environ 6000 lignes (contre 10 000 lignes en production).

En DEV, ça tourne quelques minutes.

En prod, ça met plus de 5 heures (même plus de 6 ce matin !).

Oui, il y a quelques traitements en // (notamment une mise à jour massive de CNT/CNP) mais... au moment où le traitement est lancé (pendant les deux premières heures) il n'y a rien qui devrait nous perturber.

J'ai relancé hier dans la journée (donc pas de gros traitement en //, mais des milliers de petits traitements par seconde), j'ai obtenu les mêmes temps d'exécution.


Je suis tenté de dire que la machine est correctement paramétrée, dans la mesure où personne ne se plaint de ralentissements, et que les autres traitements tournent sans problème.

[Golgotha]

selon ce que tu dis, je serais quand même tenté de dire que ce n'est pas la base ni la requête qui pose problème puisque ça tourne bien sur ta propre machine, et donc que c'est un problème extérieur, technique, matériel... non ?

[StringBuilder]

"selon"

La DEV n'est pas mon poste, il s'agit d'un environnement clône de la production.

Après, j'ai pas le détail quant à la configuration du serveur.

Je viens de trouver quelques coquilles dans mon script, en comparant les plans d'exécution requête par requête entre mes mises à jour et l'original.

Je viens de relancer en test.

Mais vu les erreurs trouvées, ça ne devrait pas changer grand chose aux performances... (j'avais inversé deux requêtes update notamment, et gourré dans une date lors d'une jointure, mais en soit, ça ne devrait pas changer grand chose au résultat).


L'ensemble de mes requêtes sont plus performantes d'après les plans d'exécution, sauf les UPDATE.
En effet, ces derniers sont passés d'un coût de 4 à 12, en contrepartie, je ne les exécute plus ligne à ligne.

[StringBuilder]

Bon, ben je pige que dalle.

Toutes les requêtes ont un plan d'exécution plus rapide maintenant (sauf les UPDATE, mais il y en a beaucoup moins), et le coût n'est que de 12, donc une broutuille...

Pourtant, l'ancien script dure 145 secondes
Et le nouveau dure 475 secondes



Bon, je capitule

Je pense qu'il y a un problème DBA qui se cache là dessous...

Mais vu qu'on va migrer l'ERP en début d'année, il y aura une réinstall toute propre sur un nouveau serveur. J'en reparlerai à l'équipe d'exploit à ce moment.

[Golgotha]

Moi je te donnerais quand même quelques conseil

Si j'ai bien retenu un enseignement depuis que je travaille dans l'informatique, c'est que ça ne sert à rien de dépenser de l’énergie et du temps à essayer de trouver une solution sur un problème tant qu'on est pas sûr est certain à 3000% que le problème est là, isolé sur notre lame du microscope, isolé sur notre table d'opération, décortiqué et analysé.

Donc, je pense que avant de commencer à chercher une solution, il faut prendre tout le temps nécessaire pour isoler le problème, à le faire devenir minuscule et sous contrôle, plutôt qu'un gros "truc" dont on ne sait pas trop comment il marche. ça m'a sauvé la vie plus d'une fois, la dichotomie, une technique infaillible ou presque pour résoudre les problème en informatique, puisque 50% du temps, le problème ne vient même pas du bout de code qu'on essaye de changer depuis 5 jours, mais du navigateur, ou autre chose...

Je pense que tu peux résoudre ton problème, en y allant petit à petit :

- Matériel ou Logiciel ?
- Code ou Environnement ?
- Quel bout de code ?
- Quelle table ?
- Quelle pièce, processeur, mémoire ?

En étant sûr à 100% à chaque fois de ton avancée, tu arriveras inéluctablement à mettre le doigt sur ton problème.

Avec cette façon de procéder, je pense que bon nombre de problèmes peuvent être réduits, simplifiés et résolus.

[ORA-007]

Envoyé par StringBuilder

C'est plus lent que le full scan, et le coût du plan d'exécution passe de... 649 à 4778 grmpf !

Cela veut dire que l'index n'est pas assez sélectif et oracle préfère faire un FULL SCAN plutôt que de multiplier les accès lecture .

De manière générale : INDEX SCAN c'est mieux mais pas tout le temps

Sinon les stats des index sont ils OK ?

Recalcule les au cas où :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
begin
DBMS_STATS.gather_table_stats
			(	 tabname 		=> 'CNP'
				,ownname 		=> "schéma de CNP"
				,cascade 		=> TRUE
				,estimate_percent 	=> 25
			);
DBMS_STATS.gather_table_stats
			(	 tabname 		=> 'EVT'
				,ownname 		=> "schéma de EVT"
				,cascade 		=> TRUE
				,estimate_percent 	=> 25
			);
end;
/

Dans ton script tu ouvres un curseur puis tu boucles sur les updates,

combien d'enregistrements sur DEV/PROD de

Code :

1
2
3
4
5
6
7
8
9
10
11
12
		SELECT c1.codsoc, c1.sigtie, c1.datapp, c1.datech, c1.numcnt, cnp.codpro, c1.codzn5, pru.coefuv
		FROM cnt c1
		INNER JOIN cnt c2 ON c2.codsoc = c1.codsoc AND c2.achvte = c1.achvte AND c2.numcnt = c1.codzn5 AND c2.codzn4 = 'ENS'
		INNER JOIN cnp ON cnp.codsoc = c1.codsoc AND cnp.achvte = c1.achvte AND cnp.numcnt = c1.numcnt AND cnp.tqoi = '501'
		INNER JOIN pru ON pru.codsoc = cnp.codsoc AND pru.codpro = cnp.codpro AND pru.coduni = cnp.coduni
		WHERE c1.codsoc = 100
		AND c1.achvte = 'V'
		AND c1.typeve = 'CNT'
		AND c1.codzn4 = 'PDL'
		AND c1.datrev <= V_Date
		AND c1.datech >= to_char(to_date(V_Date, 'YYYYMMDD') - 15, 'YYYYMMDD')
		ORDER BY c1.codzn5, cnp.codpro

peut expliquer 5 heures de traitement : si 1 occurrence de ta boucle met 1s * 18000 boucles = 5 heures !.

[StringBuilder]

Justement, les statistiques en production ne sont pas vraiment à jour...

Elles sont calculées toutes les semaines, le dimanche.

Mais dès le lundi matin, elles sont pourries : l'ERP passe son temps à se synchroniser avec d'autres systèmes, et donc une grosse partie des données sont mises à jour en masse tous les matins.

Je m'en suis notamment rendu compte en faisant un
select count(*) from eve
=> Normalement, chez tous les autres clients, ça met un pouillème de secondes. Là, il a fallu entre 5 et 10 minutes pour me dire qu'il y avait 9 millions de lignes. Certes, c'est gros, mais j'ai vu pire ailleurs, et ça n'en rendait pas moins cette requête instantanée.

J'ai donc comparé le résultat de la requête avec la valeur stockée dans les stats... Il y avait près de 1 million d'écart !

Sinon, la requête que tu indiques fait < 6000 lignes en DEV et < 10000 lignes en PROD.

Pourtant, sur la DEV, le script tourne moins de 5 minutes.

[Mohamed.Houri]

J'ai lu très rapidement les différentes interventions et me suis concentré (très rapidement aussi) sur le script public.sql. Je me permets de vous dire que ce n'est pas du code destiné à être performant. C'est du code de traitement enregistrement par enregistrement. Rappelez vous ce que dit Tom Kyte " Ce que vous pouvez faire en SQL faites le en SQL". La première des choses à faire serait de voir si votre traitement ne peut pas se faire en pur SQL (update/select plutot que LOOP/update/END LOOP). Deuxième problème: pourquoi du sql dynamique? Si vous laissez votre traitement en SQL statique, vous n'auriez plus à vous soucier de l'utilisation des "bind variables". Le SQL statique le fait pour vous. Mais comme vous êtes passé en SQL dynamique vous devriez faire attention à la bonne utilisation des bind variables. Troisièmement, pourquoi ces commits à l'intérieur de la loop? Ceci représente un risque majeur de l'apparition de l'erreur ORA-01555 rollback segment snapshot too old; il faut toujours commiter à l'extérieur de la loop et commiter uniquement lorsque c'est nécessaire (commiter tous les 100 updates ne fera que détériorer la performance).

Enfin, vous vous concentrez sur la performance de quelques requêtes sans être sûr si vraiment ce sont ces requêtes qui consomment le plus de temps. D'une manière générale, lorsqu'on a un problème de performance dans l'exécution d'une procédure ou d’un batch de nuit comme le votre faisant plusieurs traitements PL/SQL, afin de localiser exactement la répartition du temps, il faudra tracer la session qui exécute cette procédure avec le 10046 trace events

Code :

1
2
3
4
 
ALTER session SET events '10046 trace name context forever, level 12';
   executez la procedure
ALTER session SET events '10046 trace name context off';

Et utilisez un profiler comme orasrp http://www.orasrp.com/ afin de "profiler" le fichier trace généré. Vous auriez une image claire pour bien avancer dans votre processus d'amélioration de la performance

PS : j'ajoute quelque chose. A quoi sert l'order by suivant

Code :

1
2
 
ORDER BY c1.codzn5, cnp.codpro

les données seront insérées là où oracle trouvera de l'espace libre. Et au mieux vous ne feriez qu'améliorer le clustering factor d'un de vos indexes. Ce qui n'est pas votre but ici. Pensez à le supprimer si vous pouvez

[mnitu]

Actuellement ce n’est pas de l’optimisation que vous faite mais une activité chaotique avec peu de chances de réussite. Commencez donc par une trace sql étendue du traitement ce que vous permettra d’identifier le point chauds. Mieux encore utilisez un outil comme TVD$XTAT pour faire le profil de votre traitement.

[Mohamed.Houri]

Envoyé par mnitu

Actuellement ce n’est pas de l’optimisation que vous faite mais une activité chaotique avec peu de chances de réussite. Commencez donc par une trace sql étendue du traitement ce que vous permettra d’identifier le point chauds. Mieux encore utilisez un outil comme TVD$XTAT pour faire le profil de votre traitement.

Marius,

Exactement. j'allais proposer tvd$xtat de Christian antognoni également. Orasrp fera l'affaire aussi.

[mnitu]

Mohamed,

Je ne connaissais pas Orasrp mais finalement peu importe l’outil. Nous sommes d’accord que dans ce cas il y a un problème de méthode et de bonnes habitudes de programmation plutôt que d’outil.

[StringBuilder]

Le order by est obligatoire, dans la mesure où j'ai des regroupements :

Pour chaque contrat, je recherche les CDE et LIV qui correspondent, que je flag, puis je mets à jour le contrat avec les quantités commandées et livrées.

MAIS, je dois aussi mettre à jour la quantité des contrats "enseigne", qui sont des contrat qui regroupement des contrats.

D'où ce tri par enseigne/produit, afin de pouvoir mettre à jour ces contrats enseigne à chaque changement de produit/enseigne.

Pour ce qui est des commit, je suis obligé d'en faire régulièrement (à l'origine, c'était toutes les 2000 updates, mais maintenant mes updates traitant en moyenne 20 lignes, je fais tous les 100, simplement parceque sinon je me tape un rollback segment fault...