Performance (3) - Indexation ( re-suite)

Bonsoir,

Presque le week end...

Alors qu'allons nous faire avec cette table de plus de 100 millions de lignes, sachant qu'en général quand on parle de Datawarehouse on parle de plusieurs centaines millions de lignes et rarement d'une seule table !

Vous vous souvenez de la requête:

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; 

---

1er hypothèse:

Je pars du principe que les indexes ne servent à rien (j'en connais qui pensent ca) et je lance ma requête.
Bien evidemment, comme j'ai lu "Performance (2) - Autotrace", je pense à activer cette foncionnalité.

SET AUTOTRACE ON

Le résultat est prévisible, un scan complet de la table.

Les statistiques données (AUTOTRACE) nous indiquent plus de 700000 consistents gets ainsi que 730000 lectures physiques pour une durée de l'ordre de 40 secondes.

Autant dire que ce n'est pas acceptable. Un DW, est fait pour être interrogé et pas avec une seule requete par une seule personne.


2ème hypothèse.


Dans notre cas, on pourrait alors poser un index par question (q1,q2....q10).
Ca nous fait une dizaine d'indexes et ca permet toutes les combinaisons possibles
Remarque : Les indexes sont crées sur la table Reponse_2 qui est une copie de Reponse_1

Create index idx_q1_r2 on reponse_2(q1);

Create index idx_q2_r2 on reponse_2(q2);

Create index idx_q3_r2 on reponse_2(q3);

Create index idx_q4_r2 on reponse_2(q4);

Create index idx_q5_r2 on reponse_2(q5);

Create index idx_q6_r2 on reponse_2(q6);

Create index idx_q7_r2 on reponse_2(q7);

Create index idx_q8_r2 on reponse_2(q8);

Create index idx_q9_r2 on reponse_2(q9);

Create index idx_q10_r2 on reponse_2(q10);

---

Faut compter quelques minutes par indexes ... et auxquelles il faut ajouter quelques GO... supplémentaires.
Et pour être plus précis, il faut 1,7 GO par index multiplié par 10 ce qui nous fait 17 GO d'indexes.

Voyons au moins si le jeu en vaut la chandelle.

Paradoxalement les statistiques nous indiquent une baisse conséquente des consistent gets (174000) ainsi que des lectures physiques (189000) le temps de'execution est passé lui d'une quarentaine de secondes à presque deux minutes !

les statistiques nous indiquent également qu'il y a eu quelques 1000 appels recursifs (certainement des appels systemes[mais pour quoi faire ?].

Le plan d'execution nous montre que nos indexes sont bien utilisés.

Alors que faire ? Gacher 17 GO d'index pour un résultat qui devrait être meilleur mais qui ne l'est pas !
Ce qui coute en temps ce sont les "appels récursives" qui sont certainement des appels systemes.
Mais pourquoi faire ?

Le plan d'execution nous indique bien que les indexes sont utilisés, mais qu'ORACLE effectue une conversion des ROWID en BITMAP !
Du sex dans ORacle ? Non mais un type d'index : Les index bitmap !

3 ème hypothèse.

Si oracle décide de faire une conversion de type bitmap, peut être avons nous interet à créer directement des indexes de type bitmap.

Qu'est ce qu'un index bitmap ? :
ex: Créeons un index bitmap sur q1.

---

CREATE BITMAP INDEX idx_q1_r1 ON Reponse_1(q1);

---

1ere constation : La création est beaucoup plus rapide que pour un index classique.

Comme ca marche ?
Q1 peut prendre 11 valeurs (de 0 à 10). Oracle va alors créer un tableau de valeur de 0 à 10 et pour chaque rowid (numéro de sécu d'une ligne dans la table, et accés direct à l'info) va indiquer par 0 ou 1 pour chaque valeur. On peut représenter cela par un tableau. Admettons que pour les premières lignes de la tables, les notes à q1 soient 2,3,4,0,0,1,5,8,9,1,6...

	2	3	4	0	0	1	5	8	9	1	6
0	0	0	0	1	1	0	0	0	0	0	0
1	0	0	0	0	0	1	0	0	0	1	0
2	1	0	0	0	0	0	0	0	0	0	0
3	0	1	0	0	0	0	0	0	0	0	0
4	0	0	1	0	0	0	0	0	0	0	0
5	0	0	0	0	0	0	1	0	0	0	0
6	0	0	0	0	0	0	0	0	0	0	1
7	0	0	0	0	0	0	0	0	0	0	0
8	0	0	0	0	0	0	0	1	0	0	0
9	0	0	0	0	0	0	0	0	1	0	0

Une suite de 1 er 0 pour définir mon index !!! du binaire ! C'est pas beau.
Beau je ne sais pas, mais efficace certainement.
 
2ème constatation: la taille de mon index est de l'ordre de 150 MO soit 10 fois moins important qu'un index normal.

Crééons donc tous nous index. (on travaille sur la table Reponse_1)

---

Create bitmap index idx_q1_r1 on reponse_1(q1);

Create bitmap index idx_q2_r1 on reponse_1(q2);

Create bitmap index idx_q3_r1 on reponse_1(q3);

Create bitmap index idx_q4_r1 on reponse_1(q4);

Create bitmap index idx_q5_r1 on reponse_1(q5);

Create bitmap index idx_q6_r1 on reponse_1(q6);

Create bitmap index idx_q7_r1 on reponse_1(q7);

Create bitmap index idx_q8_r1 on reponse_1(q8);

Create bitmap index idx_q9_r1 on reponse_1(q9);

Create bitmap index idx_q10_r1 on reponse_1(q10);

---

à peine 1,5 GO pour les dix indexes. Même pas la taille d'un index sur la table Reponse_2.
 

Reste à esperer qu'en plus ca améliore les performances.
Rejouons notre requête.


1er appel : 6,5 secondes ! pour uniquement 12431 consistents et 12316 lecture phyisiques.
Faut dire que je viens de faire ma première requete sur la table et que la base vient de démarrer. Ce qui est rarement le cas en production.

2ème appel: moins d'une seconde !!!!!! et 12324 consistents gets pour 0 accès disque !

Pour info, dans les deux hypothèses précédentes j'avais également fait plusieurs appels ! mais vu la taille des indexes cela ne pouvait rester en mémoire d'ou les accès disques.
Ma base est configuré avec une SGA de 512 MO et une PGA de 100 MO


J'en vois venir qui vont vouloir coller du BITMAP partout, alors lisez d'abors les lignes qui suivent:

Avertissement:

• On ne pose un index bitmap que sur une colonne contenant à nombre réduit de valeurs distinctes !  Typiquement on ne posera pas un index bitmap sur la colonne date_questionnaire sachant que la table va être alimenté tous les mois (voir tous les jours). La documentation ORACLE parle de 300 valeurs pour la limite de valeurs distincte. Moi je dis qu'il faut tester et mesurer (statistiques, taille index,...)
• On pose principalement des indexes bitmap dans des environnement de type datawarehouse ou sur des tables statiques. Imaginons un instant que je décide qu'une question puisse avoit la note de 11 ! à la première insertion avec la valeur 11, ce sont les 115 millions de lignes d'indexes qui vont être mise à jour.
• On préferera créer des index bitmap sur une colonne plutot que des indexes composés.

Conclusion:
C'est beau, c'est pratique et c'est efficace. Mais une question commence à venir, si ma table doit être alimentée tous les jours et qu'un index bitmap est couteux lors des mise à jour, comment allons nous gérer cela ?

Affaire à suivre ....
 

Comments

[ndu]

Bonjour,

Avant de commencer, je voulais indiquer que ma base est plus petite que la tienne, je n'ai pas assez d'espace disque pour le moment.
Et en mettant ma proposition d'un index composé, j'espérais que tu puisses faire les tests et voir le gain.

Au départ, j'ai créé les 6 index classiques et le plan d'exécution indiqué qui n'utilisé que les colonnes q5,6,7. Ces pour celà que j'ai créé qu'un seul index composé q5,6,7
Ensuite, l'index composé est tout seul lors des tests.
Et j'avais obtenu un petit gain de temps.

Concernant la taille des index, chez moi, un index composé classique est 2 fois plus petit que si il y a 3 index classiques. La taille est similaire entre un index composé bitmap et 3 index bitmap.

Je suis d'accord avec toi, si demain la requête change, l'index composé ne sera plus efficace. C'est pour celà qu'il faut bien connaître le fontionnel pour apporter la meilleur solution.

Tout comme toi, l'index bitmap doit être employer uniquement si le nombre de valeurs distinctes est peu élevé.

[LAO]

Pas de soucis.
En revanche attention à la notion "gain de temps". C'est pour cela que je parle plus facilement de lecture physique ou "consistents gets" qui permettent vraiement de comparer.
En effet, si tes données sont en caches, tu peux croire que ton index a apporté un gain.
Pour ce qui est de l'espace, effectivement mon index sur (q4,q5,q6) a une taille de moitié par rapport à 3 index unique, mais il faut toujours plus que l'ensemble des indexes bitmap.

En tout cas merci, pour ta participation au sujet.

LAO. 

[ndu]

Bonjour,

Au lieu de créer 3 index, on crée un seul index composé :

Create index idx_q456_r3 on reponse_3(q4,q5,q6);

et si on désire encore y gagner on le crée en bitmap.

[LAO]

Bonjour,

Tou d'abord chapeau bas ! un commentaire à 6h30 un dimanche matin, ca n'est pas courrant.
Concernant les index bitmap, je maintiens que l'interet d'en créer des composés n'est pas plus performant que de créer des indexes uniques. Les indexes Bitmap, de par leurs structures (cf.
article), offre justement de très grandes perforamances dans des requêtes avec multicri-critères (OR , AND) de part leur structure binaire.

Pour revenir à ton exemple index sur (q4,q
5,q6), est ce ce que c'est en plus des binaires sur q1, q2,q3,... ou uniquement cet index.
 
cas 1: Uniquement l'index idx_q456_r3
Dans ce cas, je ne vois pas ou est le gain: 
En effet, je viens de faire le test, et la requête exemple prendre 1 minute 40 dans ce cas ci avec plus de 100000 lectures physiques contre 12000 dans le cas d'utilisation de bitmap unique sur
l'ensemble des colonnes.

Cas 2 : idx_q456_r3 vient s'ajouter aux indexes existants (bitmap unique sur q1,q2,q3)

Alors dans ce cas, je ne comprens pas pourquoi ne pas avoir poussé la logique jus'au bout et mettre un index composé sur (q1,q2,q3). Au passage, je rappelle qu'il s'agissait d'une requete, et qu'en
générale dans un datawarehouse, les requetes sont multiples. Les avantages des index uniques bitmap sont multiples:

Espace disque réduit (important ne serait ce que pour les sauvegardes)

Toutes les permutations possible entre les colonnes dans la clause where

Administration facilité (temps de création, de rebuild réduit).

Un full scan d'index bitmap est beaucoup plus rapide.


Pour revenir à cette hypothèse, si j'ajoute trois indexex bitmap uniques sur q1,q2,q3, j'obtiens effectivement un meilleurs résultat avec 7000 "consistents gets" contre 12000 dans le cas ou je
n'utilise que des indexes bitmap.

En revanche l'ensemble des indexes bitmaps (q1,q2,...q10) n'occupent que 1,5 Go la ou ton index idx_q456 en fait 2,5. Si demain la clause where devient ... AND (q4=5 and q5=5 and q8=5), les index
bitmpas seront toujours aussi efficaces alors que tu devras reconstruire un index de 2,5 GO pour rester performance.

En conclusion, je maintiens ma position pour l'utilisation d'index bitmap lorsqu'ils correspondent au critères suivantes:

Nombre de valeurs distinctes peu élevé (la doc ORACLE parle de 300, mais il faut ramener ce chiffre au nombre totale de lignes)

Environnemen décisonnel (pas de mise à jour)



Bonne continuation,
LAO. 
 

[Stéphane]

Bonjour trés bonne solution...

Puisque un index bitmap est trés couteux pour la maj on aurait plutôt intérêt de les supprimés avant chaque maj et de les recréer aprés.
Si c'est faux soyez fair play je suis débutant ;)
bon week

[LAO]

Bonjour,

Effectivement ca peut être une solution. Mais il y a un moment ou cela risque quand meme d'etre couteux....
Il va donc falloir trouver d'autres solutions. Je pense en parler dans mon prochain post.

Bon week end également,