11 octobre 2008
6
11
/10
/octobre
/2008
23:04
Bonsoir,
Que tous ceux qui pratiquent les bases de données et qui n'ont jamais eu des problèmes de performances se lèvent.
Je pense que rien qu'avec cette partie, j'aurai de quoi publier pour quelques temps !
Mais comme, il faut commencer par quelque part, j'ai décidé ce soir de m'attaquer à mes amis developpeurs à qui il arrive parfois d'écrire des requêtes que l'on qualifiera de non perforamante.
Mais est-ce vraiment leur faute ! La pluspart du temps, ils viennent du monde C, Java, Dot NET, ou que sais-je encore, et n'ont une experience du sql et d'ORACLE que très brève (voir inexistante).
Il ne faut donc pas s'étonner plus que ca d'avoir des applications "lente" ou inexploitable en accès conccurents.
Pour argumenter cela, j'ai décidé de travailler avec un exemple simple:
Une table facture (id_facture, mois_facture) et une table ligne_facture (id_ligne_facture,id_facture,montant_facture).
Je vais remplir ces tables de facon conséquente. (150 000 factures par mois avec 4 lignes de facture par facture et par mois).
CE qui nous faite 150 000 lignes dans la table facture et 600 000 * 12 = 7 200 000 de lignes !
Ci-dessous un petit script pour faire tout ca:
CREATE TABLE FACTURE (ID_FACTURE NUMBER,MOIS_FACTURE NUMBER);
CREATE TABLE LIGNE_FACTURE (ID_LIGNE_FACTURE NUMBER,ID_FACTURE NUMBER, MONTANT_FACTURE FLOAT);
CREATE SEQUENCE NEXtKEY START WITH 1 INCREMENT BY 1 CACHE 100;
ALTER TABLE FACTURE ADD CONSTRAINT PK_FACTURE PRIMARY KEY(Id_Facture);
ALTER TABLE LIGNE_FACTURE ADD CONSTRAINT PK_LIGNE_FACTURE PRIMARY KEY(Id_Ligne_Facture);
/* Remplissage table facture*/
BEGIN
FOR mois IN 1..12 LOOP
FOR i IN 1..150000 LOOP
INSERT INTO FACTURE VALUES (NEXTKEY.NEXTVAL,mois);
END LOOP;
END LOOP;
END;
/
COMMIT;
/* Remplissage de ligne_facture */
BEGIN
FOR fac in (SELECT ID_FACTURE FROM FACTURE) LOOP
FOR nb_fac IN 1..4 LOOP
INSERT INTO LIGNE_FACTURE VALUES (NEXTKEY.NEXTVAL,fac.ID_FACTURE,100*nb_fac);
END LOOP;
END LOOP;
END;
/
COMMIT;
CREATE INDEX IDX_1 ON FACTURE (MOIS);
CREATE INDEX IDX_2 ON LIGNE_FaCTURE(ID_FACTURE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,TabName=>'FACTURE',CASCADE=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,TabName=>'LIGNE_FACTURE',CASCADE=>TRUE);
Maintenant que nos tables sont bien remplies, nous voulons le montant total par mois.
Rien d'extraordinaire à écrire.
Pour info:
Version ORACLE :10.2.0.1
SGA :512 M
PGA :10 M (Faut pas pousser, en tant que dev, j'ai autre chose à faire avec ma mémoire).
SELECT
Mois_Facture,SUM(Montant_Facture)
FROM FACTURE,LIGNE_FACTURE
WHERE FACTURE.Id_Facture=LIGNE_FACTURE.Id_Facture
GROUP BY Mois_Facture
ORDER BY Mois_Facture;
Avant de lancer ma requete dans SQL +
SET TIMING ON
puis je lance la requete.
Résultat : 30 secondes (je l'ai lancé plusieurs fois)
Ce résultat me convient moyennement. ORACLE permettant differents type de jointure, je décide de forcer une jointure de type NESTED LOOP. Pour cela, j'utilise un hint qui indiquera au moteur ORACLE quel type de jointure prendre.(J'ai vu ca sur GOOGLE, parait que ca marche !)
SELECT /*+ USE_NL(FACTURE,LIGNE_FACTURE) */ Mois_Facture,SUM(Montant_Facture)
FROM FACTURE,LIGNE_FACTURE
WHERE FACTURE.Id_Facture=LIGNE_FACTURE.Id_Facture
GROUP BY Mois_Facture
ORDER BY Mois_Facture;
Résultat : 1ere fois 30 secondes, mais la seconde fois 10 secondes !!!
Génial, j'ai donc optimisé ma requete ! je laisse le hint et j'envoi l'ensemble en production !
C'est un peu caricatural, mais en cherchant au tour de soi, on peut trouver ce type de situation.
Ce qui est génant dans cette démarche, c'est qu'a aucun moment je n'ai parlé de plan d'execution ou statistiques sur l'execution de ma requete, alors que juste avec SQL PLUS on peut avoir un nombre d'informations assez sympa.
Pour cela, il suffit de taper SET AUTOTRACE ON
En plus du résultat de la requete, j'obtiens également son plan d'execution(qui nous montre bien que l'on effecture une NESTED LOOP pour la jointure) ainsi que certaines statistiques.
Or on constate que le nombre de "consistents gets" est énorme (plus de 3.5 millions).
Je décide donc d'enlever le hint ( /*+ USE_NL(FACTURE,LIGNE_FACTURE) */ ) pour voir ce qui se passe.
Je l'execute 2,3 fois (je suis toujours entre 25 & 30 secondes)
Mais ce qui est interessant c'est que je constate au niveau des statistiques que je n'ai que 23452 consistents gets. En revanche, j'ai un peu plus de 18886 accès disques !
Ce qui doit expliquer la différence de temps d'execution entre les deux requêtes.
En regardant le plan d'execution, je constate également que la jointure est maintenant de type HASH JOIN.
Et la tout s'explique, car ce type de jointure est plus gourmand en PGA (pour la création de table de haschage).
PGA, vous vous souvenez, c'est la zone mémoire que j'ai réduite à son minimum pour faire des économie de mémoire sur mon pc !
Je décide donc d'augmenter ce paramètre system d'ORACLE.
J'ouvre une session SQL PLUS en system et je modifie la valeur.
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=256M;
Et la Oh grande magie de l'informatique, ma requete ne prends plus que 3.6 secondes pour un peu moins de 23 000 consistents gets pour 0 lecture physiques.
Si des le départ de notre investigation nous avions utlisé cette option (SET AUTOTRACE ON), nous aurrions pu nous épargner des tentatives d'optimisation pas forcement judicieuces.
J'en profite pour rappeler que d'après un postulat admis par un grand nombre, 80 % des problèmes de performance sont liés à des requêtes mal écrite.
LAO.
Partager cet article
