Overblog
Suivre ce blog Administration + Créer mon blog
21 octobre 2008 2 21 /10 /octobre /2008 21:56

Performance (3) - Indexation

Bonsoir,

J'avais prévu de parler grosse table et indexes mais malheureusement j'ai un peu surestimé mon PC pour la création de la table en question.
Je vais cependant commcer par poser la problématique et je reviendrai à la charge dans les prochains jours...

Une table sert à récolter des enquêtes téléphonique dans 25 pays différents.
Il y a 100 questionnaires.  Chaque questionnaire comporte 10 questions auxquelles on peut mettre une note de 0 à 10.

Cette enquête à eu lieu sur tout le mois de décembre (samedi & dimanche compris).
Pour des raisons de simplicité la date est sous le forme d'un nombre (ex :le 15 décembre 2007 devient 20071215)

Maintenant que nous disposons de notre table; une équipe d'ananlystes veut comprendre les résultats et pour cela va donc requeter sur notre table.

Script de création de la table:

CREATE TABLE LAO.REPONSE_1
(
  Q1                  NUMBER,
  Q2                  NUMBER,
  Q3                  NUMBER,
  Q4                  NUMBER,
  Q5                  NUMBER,
  Q6                  NUMBER,
  Q7                  NUMBER,
  Q8                  NUMBER,
  Q9                  NUMBER,
  Q10                 NUMBER,
  ID_PAYS             NUMBER,
  ID_FORMULAIRE       NUMBER,
  DATE_QUESTIONNAIRE  NUMBER
);

Script de remplissage: 


remarque : Je vous conseille de mettre la table en NOLOGGING et de passer votre base (si il s'agit d'une base de test qui vous est propre en mode no archivelog)

BEGIN
      FOR i IN 1..25 LOOP
      FOR j IN 1..5000000 LOOP
         INSERT /*+APPEND*/ INTO Reponse_1 VALUES (
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(0,10)),
            ROUND(DBMS_RANDOM.VALUE(1,25)),
            ROUND(DBMS_RANDOM.VALUE(1,100)),
            ROUND(DBMS_RANDOM.VALUE(20071201,20071231)));
      END LOOP;
      COMMIT;
   END LOOP;
END; 
 
Une fois que ce script aura terminé d'inserer des lignes de façon aléatoire, nous aurrons une table de  125 millions de lignes.
-1 / Ca va prendre de la place !
-2 / Les indexes que je vais créer vont également prendre de la place (donc j'ai interêt à bien les choisir)
-3 / J'ai interet de faire attention aux requêtes que je vais faire, si je ne veux pas monopoliser toutes les resources.

Globalement, l'idée est de pouvoir requeter sur toute les colonnes en se posant des questions metaphysiques du genre.
  • Combien de personnes on mis 10 aux trois premières questions ou 0 aux trois premières questions and 5 à toutes les autres (Je vous laisse imaginer les variantes) pour une période donnée (soit sur une journée ou plusieurs) et eventuellement pour un pays, voir un formulaire donné (ou un ensemble).
  • On se positionne quand même dans un esprit Datawarehouse chargé. Pas de mise à jour prévus.


J'aurai bien voulu commencer à élaborer une stratégie mais mon script tourne depuis deux bonnes heures et j'en suis à 60 millions de lignes.

Alors si jamais vous voulez faire tourner le script je vous conseille:

  • De prévoir un espace disque suffisant (7 GO pour la table et prévoir de l'espace pour les indexes.
  • Après la création de la table faire :ALTER TABLE Reponse_1 NOLOGGING;
  • Avant le script d'insertion , ajouter 3 fichiers redo de 512M ou 1 G chacun pour éviter des "Checkpoint not complete" qui ralentissent l'insertion.
  • De passer par une insertion de masse (plutot que la pauvre boucle que j'ai crée)=> Ca m'apprendra à ne pas réfléchir.
  • Eventuellement de diminuer la volumétrie. Mais si on veut un exemple parlant c'est mieux d'avoir quelques millions de lignes.

Remarque : Comme tout le monde le sait, si on veut qu'Oracle choisisse le bon plan d'execution, il peut être interessant d'avoir les stats à jours (après la création d'indexes) . Je vous propose de faire dans sql +

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,tabname=>'REPONSE_1',CASCADE=>TRUE,
 ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE); 
 

Alors maintenant si vous êtes prêt, j'attends votre aide pour par exemple que la requête suivante s'execute le plus rapidement en utilisant le moins de resources possible:


SELECT id_pays,COUNT(*) FROM Reponse_1
WHERE ((q1=10 AND q2=10 AND q3=10) OR (q1=0 AND q2=0 AND q3=0)) AND (q4=5 AND q5=5 AND q6=5)
GROUP BY id_pays; 

Bon courage, et j'éspère avoir des propositions à tester !

LAO.

Partager cet article
Repost0
Published by LAO - dans PERFORMANCE
commenter cet article …
19 octobre 2008 7 19 /10 /octobre /2008 18:48

Statspack (1) - Installation

Bonsoir,

Allons y pour l'installation de STATSPACK. J'ai presque envie de dire que normalement peu d'entre vous devraient lire cet article, car à mon sens, il devrait déjà être installé sur toutes vos bases ORACLE...
On peut même se demander pourquoi il n'est pas installé avec ORACLE par défaut. Surement pour faire travailler le service "consulting" de ORACLE ou d'autres sociétés en tout genre.

 Alors tout d'abord a quoi sert Statspack ? C'est un ensemble de scripts qui va collecter de facçn manuelle ou automatique un grand nombre d'informations qui seront très utile pour vous aider à résoudre des problèmes de performance.

Pour résoudre des problèmes (quels qu'ils soient) encore faut-il les comprendre et pouvoir comparer deux situations.

Ou est-ce que je veux en venir en disant cela ?

Certains utilisent statspack à partir du moment ou l'on signale des problèmes de performances.
Avoir un rapport à un instant t va peut être nous donner des informations sur des requetes couteuses, mais si l'utilitaire n'a pas été installé et configuré des le départ nous ne pourrons pas COMPARER deux situations et cela est bien dommage !

En effet, la comparaison de deux périodes (une ou tout allait bien, et celle ou ca part en vrille) permet de comprendre ce qui se passe. ex: Augementation du nombre de transaction, d'accès disques, modification de paramètre systeme...

Installation de STATSPACK:

 Toutes les manipulations suivantes on été effectuées dans un environnement windows avec la version 10.2.0.1 d'ORACLE.

Rien de plus simple, il suffit de vous connecter en SYSDBA avec SQL+ sur votre serveur ORACLE.

SQL > connect / as sysdba
SQL > @?\rdbms\admin\spcreate.sql

Remarque: En fait ce script va lui même lancer trois scripts:
  • spcusr.sql ==> Création de l'utilisateur PERFSTAT et accorder droits nécessaires
  • spctab.sql ==> Création des tables
  • spcpkg.sql ==> création du package

Et voila c'est terminé !!! (ou presque)


 Durant l'execution de ce script ORACLE va vous mettre à contribution en vous posant 3 questions :
  1. Vous devez indiquer un mot de passe pour le USER PERFSTAT qui va être creé par le script.
  2. Indiquer un tablespace pour l'emplacement des objets (Tables,Indexes,...) de PERFSTAT. Par défaut sous ORACLE 10, le tablespace SYSAUX est proposé.
  3. Indiquer le nom d'un tablespace temporaire. Par défaut le tablespace TEMP est proposé.

Remarque : Sous ORACLE 9, le tablespace SYSAUX n'existe pas. Il est alors conseillé de créer un tablespace dédié pour le user PERFSTAT.

Une fois l'installation terminée, il est fortement conseillé d'aller jeter un oeil (pas trop loin) dans le fichiers de log.
Pour l'info, il y a trois fichier de log (et 1 par script)

  • spcusr.lis
  • spctab.lis
  • spcpkg.lis 

Le script appelant uniquement un SPOOL sans chemin, les fichiers de logs se sont retrouvés à la racine du disque contenant mon ORACLE_HOME (c:\ dans mon cas).
 
Attention : En cas d'erreur il est préférable de desinstaller Statspack et de recommencer l'installation.
Pour cela rien de plus simple...
On se connecte à la base via SQL+ en SYS DBA

SQL >  @?\rdbms\admin\spdrop.sql

 Si vous disposez des mots de passe et des accès nécessaire aux serveurs ORACLE, il ne vous faudra pas plus de 3 minutes pour installer STATPACK.
Et ensuite ...

Comme indiqué au départ Statspack sert à collecter des informations qui nous seront bien utile pour comprendre pourquoi notre base préférée bat de l'aile.

Prendre un cliché:

 On se connecte (toujours avec SQL+) sous le user PERFSTAT fraichement crée.

SQL > CONNECT PERFSTAT/xxxxxx
SQL > execute statspack.snap; 
 

Petit rappel : L'idée est quand même d'automatiser la prise de clichés et donc on peut utiliser le petit script ci-dessous pour programmer une prise de cliché toutes les trentes minutes.

 DECLARE X NUMBER;
 BEGIN
    SYS.DBMS_JOB.SUBMIT
  (
   job => X,
   what => 'statspack.snap;',
   next_date => SYSDATE+30/1440,
   interval => 'SYSDATE+30/1440',
   no_parse => FALSE
  );
  END;
 /
C'est bien beau de collecter de l'information... mais à un moment donnée il serait bon de pouvoir la restituer.
Encore une fois, rien de bien compliqué
Toujours notre ami SQL + , on se connecte avec l'utilisateur PERFSTAT.

SQL > connect PERFSTAT/xxxxxx
SQL > @?\rdbms\admin\spreport.sql
 
La on obtient la liste des clichés avec le SNAP Id et la date et heure du cliché. Ce qui peut être pratique.. Le jour ou l'on vous dit que "ca a ramé" le 12 entre 15heures et 16 heure, il suffira d'identifier les snap id correspondants.

Pour faire un rapport, il faut deux clichés.
On réponds à la question posé:

Entrez une valeur pour begin_snap:
Entrez une valeur pour end_snap :
Entrez une valeur pour report_name : Il faut en fait indique chemin + nom (ex:c:\temp\report.log)


Et voila, y a plus qu'a lire le rapport !!!!

Comme le titre de l'article s'appelle "STATSPACK (1) - Installation", on va s'arreter la.
Vous savez maintenant 
  • Installer Statspack
  • Créer un cliché manuel
  • Programmer une prise de clichés.
  • Obtenir un rapport

Ca n'est pas forcement suffisant, mais c'est un bon début.
Pour vous donner une petite idée de ce qui reste, je pense faire au moins deux autres articles sur le sujet;

  • Statspack (2) - Administation et configuration
  • Statspack (3) - Description du rapport (Et oui y a boire & manger la dedans).


Remarques : Pour ceux qui pratiquent l'anglais, vous pouvez également lire le fichier spdoc.txt qui se trouve  à l'emplacement suivant: %ORACLE_HOME%\rdbms\admin\

Et encore mieux le chapitre le chapitre consacré à Statspack par la documentation (en ligne et gratuite d'ORACLE)
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm#PFGRF019

 Il s'agit de la documentation d'ORACLE 9. En effet en version 10 et 11, il est plus souvent question de AWR (Automatic Workload Repository). Le seul soucis, c'est que ce bel outil d'ORACLE nécessite une license supplémentaire.  


LAO. 

Partager cet article
Repost0
Published by LAO - dans PERFORMANCE
commenter cet article …
15 octobre 2008 3 15 /10 /octobre /2008 19:45

PERFORMANCE(2) - Autotrace

Bonsoir, 

Dans l'article précédent, j'ai fait mention de la possibilité d'activer une trace pertinente en executant la commande suivante

SET AUTOTRACE ON

 Peut-être certains ont-ils essayé et ont la désagrable surprise d'avoir l'erreur suivante:

SP2-0618 : Impossible de trouver l'identificateur de session. Le rôle PLUSTRACE doit être activé.
SP2-0611 : Erreur lors de l'activation de l'état STATISTICS

Et oui notre ami ORACLE, n'est pas que sympathique, il lui arrive d'être farceur. Allez savoir pourquoi ce rôle n'est pas activé par défaut.

La procédure pour l'activer est relativement simple.
Il faut se connect en sys as sysdba dans une session SQL + et lancer les commandes suivantes:

 SQL> @?\sqlplus\admin\plustrce.sql
 SQL>  GRANT PLUSTRACE TO PUBLIC;

le "?" (uniquement sous WINDOWS il me semble) permet d'accéder à ORACLE_HOME. 
Je pourrai très bien restreindre le GRANT des utilisateurs nommés. Mais honnetement, je ne vois pas l'interêt.

 Pour le moment je n'ai parlé que de l'option SET AUTOTRACE ON, mais il en existe d'autre.

Option Explication
SET AUTOTRACE OFF Il s'agit de la valeur par défaut.Dans ce cas aucune trace n'est générée.
SET AUTOTRACE ON EXPLAIN Cette option n'affiche que le plan d'execution de la requête executée.
SET AUTOTRACE ON STATISTICS Cette option n'affiche que les statistiques.
SET AUTOTRACE ON Ici, nous avons les deux : Plan d'execution + Statistiques
SET AUTOTRACE TRACEONLY Cette option est identique à la précédente à l'exception de l'affichage du résultat de la requête. Ce peut être très utile dans le cas de SELECT retournant plusieurs milliers de lignes.


Les statistiques données par AUTOTRACE sont très importantes et peuvent vous permettent dans certains cas de faire le bon choix entre deux requêtes.
Il n'est pas forcement nécessaire d'être un export en tuning pour comparer deux tableaux de statistiques et en tirer des conséquences.

Souvenez vous de l'article précédent ou nous opposions une requête à plus de trois millions de "consistent gets" (jointure de type nested loop) contre 23000 (jointure par hashage).
Sans avoir activer AUTOTRACE à ON, il n'était pas forcément évident d'effectuer le bon choix.

Bonne soirée !

LAO. 
 
Partager cet article
Repost0
Published by LAO - dans PERFORMANCE
commenter cet article …
11 octobre 2008 6 11 /10 /octobre /2008 23:04

PERFORMANCE (1)

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
Repost0
Published by LAO - dans PERFORMANCE
commenter cet article …