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.