9 – Optimisation des
performances
1
Optimisation des performances
1) Le partitionnement
2) Les vues matérialisées
3) Les index
2
Le partitionnement
n Objectif : Accélérer les requêtes en « distribuant » les données.
n Consiste à découper une table en fragments disjoints et complets :
partitions. Chaque partition occupe un espace physique séparé.
n Intérêts :
q Diminution du nombre d'E/S par élagage de partitions.
q Chaque partition peut être traitée en parallèle.
q Sauvegardes et restaurations par partitions.
n Le plan de partitionnement doit être bien réalisé.
3
Exemple
Produits
Pid
Pnom
Categorie
Ventes
#Cid
#Pid
#Jour Clients
Montant Cid
Nom
Type
Temps Ville
Jour
Mois
Annee
4
Partitionnement horizontal
n Les partitions sont obtenues par sélection sur des attributs de la
table. Leurs schémas sont identiques.
Clients C1 Cid Nom Type Ville
Cid Nom Type Ville 10 BOREL P Riom
10 BOREL P Riom 20 BAGEL P Paris
20 BAGEL P Paris 40 ROLLE P Paris
30 COSTE A Lyon C2 Cid Nom Type Ville
40 ROLLE P Paris 30 COSTE A Lyon
50 TOMAS A Nice 50 TOMAS A Nice
60 SILVA A Lille 60 SILVA A Lille
5
Partitionnement horizontal dérivé
n Les partitions sont dérivées des partitions d'une autre table.
Ventes V1 Cid Pid Jour Montant
Cid Pid Jour Montant 10 100 11-06-15 10
10 100 11-06-15 10 10 200 06-08-15 20
10 200 06-08-15 20 20 300 10-12-15 30
20 300 10-12-15 30 40 500 11-12-15 14
30 300 07-08-15 20 V2 Cid Pid Jour Montant
40 500 11-12-15 14 30 300 07-08-15 20
50 400 12-11-15 10 50 400 12-11-15 10
60 500 15-12-15 15 60 500 15-12-15 15
60 500 17-12-15 20 60 500 17-12-15 20
6
Partitionnement sous Oracle
n Implémentation du partitionnement horizontal, comme pour la
plupart des SGBD.
n Clause PARTITION qui définit une partition horizontale selon
différents modes :
q RANGE
q LIST
q HASH
q COMPOSITE
7
Mode RANGE
n Partition effectuée en fonction d'intervalles sur les
valeurs d'un attribut.
q CREATE TABLE Ventes(Cid NUMBER, Pid NUMBER, Jour DATE, Montant NUMBER)
PARTITION BY RANGE(Jour)
(PARTITION V14q1 VALUES LESS THAN (TO_DATE('01-04-2014',' DD-MM-YYYY')),
PARTITION V14q2 VALUES LESS THAN (TO_DATE('01-07-2014', 'DD-MM-YYYY')),
PARTITION V14q3 VALUES LESS THAN (TO_DATE('01-10-2014', 'DD-MM-YYYY')),
PARTITION V14q4 VALUES LESS THAN (MAXVALUE));
n Mode adapté pour un partitionnement par date ou pour faciliter des
requêtes avec des conditions sur des intervalles.
8
Mode LIST
n Partition effectuée en fonction des valeurs d'un attribut.
q CREATE TABLE Ventes(Cid NUMBER, Pid NUMBER, Jour DATE, Montant NUMBER)
PARTITION BY LIST(Cid)
(PARTITION V1 VALUES ('10','20','40'),
PARTITION V2 VALUES ('30','50','60'),
PARTITION V3 VALUES (DEFAULT));
n Mode adapté pour faciliter des requêtes avec des conditions sur les
valeurs.
n Possible aussi de partitionner sur plusieurs attributs.
9
Mode HASH
n Partition effectuée en fonction des résultats d'un
hachage sur les valeurs d'un attribut.
q CREATE TABLE Ventes(Cid NUMBER, Pid NUMBER, Jour DATE, Montant NUMBER)
PARTITION BY HASH(Pid) PARTITIONS 4;
n Les partitions sont nommées automatiquement.
n Les partitions auront le même nombre de valeurs de l'attribut (Pid).
n Mode adapté pour faciliter les jointures sur prédicats d'égalité.
10
Mode COMPOSITE
n Partition effectuée en combinant les deux modes précédents :
RANGE - HASH ou RANGE – LIST.
q CREATE TABLE Ventes(Cid NUMBER, Pid NUMBER, Jour DATE, Montant NUMBER)
PARTITION BY RANGE (Jour)
SUBPARTITION BY HASH (Pid) SUBPARTITIONS 4
(PARTITION V14q1 VALUES LESS THAN (TO_DATE('01-04-2014',' DD-MM-YYYY')),
PARTITION V14q2 VALUES LESS THAN (TO_DATE('01-07-2014', 'DD-MM-YYYY')),
PARTITION V14q3 VALUES LESS THAN (TO_DATE('01-10-2014', 'DD-MM-YYYY')),
PARTITION V14q4 VALUES LESS THAN (MAXVALUE));
11
Gestion des partitions
n Renommage
q ALTER TABLE table RENAME PARTITION oldnom TO newnom;
n Ajout d'une partition
q ALTER TABLE table ADD PARTITION partition VALUES ('20','97');
n Ajout de valeurs à une partition
q ALTER TABLE table MODIFY PARTITION partition ADD VALUES ('09','31');
n Suppression d'une partition (donc des données)
q ALTER TABLE table DROP PARTITION partition;
q Impossible de supprimer une partition de type hash.
q Si la table ne contient qu'une partition, impossible de la supprimer. Il faut
supprimer la table elle-même.
12
Gestion des partitions
n Suppression de valeurs à une partition
q ALTER TABLE table MODIFY PARTITION partition DROP VALUES ('87');
n Fusion de 2 partitions
q ALTER TABLE table MERGE PARTITIONS partition1, partition2 INTO
PARTITION partition3;
n Eclatement d'une partition en 2
q ALTER TABLE table SPLIT PARTITION partition AT (100) INTO (PARTITION
partition1, PARTITION partition2);
q ALTER TABLE table SPLIT PARTITION partition VALUES ('44','45','92') INTO
(PARTITION partition1, PARTITION partition2) ;
q Impossible pour les partitions de type hash.
13
Optimisation des performances
1) Le partitionnement
2) Les vues matérialisées
3) Les index
14
Rappel sur les vues classiques
n Les vues sont des tables virtuelles dérivées de tables de base au
moyen d'une requête SQL.
n Une vue n'est pas stockée physiquement, elle est évaluée à chaque
fois qu'on en a besoin.
n Les vues s'utilisent dans les requêtes de la même façon que les
tables. Les mises à jour sont effectuées dans les tables de base.
n Utilité : Simplifier les requêtes, différentes visions des données…
CREATE VIEW VINSCRIPTIONS
AS SELECT [Link], NOMCOURS, COUNT(IDETUDIANT) NBINSCRITS
FROM COURS C
LEFT JOIN INSCRIPTIONS I ON [Link]=[Link]
GROUP BY [Link], NOMCOURS;
SELECT IDCOURS, NOMCOURS, NBINSCRITS
FROM VINSCRIPTIONS; (simplification d’écriture de requêtes)
15
Les vues matérialisées
n Les vues matérialisées sont dérivées de tables de base au moyen
d'une requête SQL.
n Les données d'une vue matérialisée sont stockées physiquement.
q Différent des vues classiques qui sont virtuelles (inutiles pour
l’optimisation)
n Consiste à pré-calculer des opérations coûteuses (calcul d'agrégats,
jointure) et à stocker leurs résultats d'une manière permanente =>
duplication des données (redondance calculée).
n Le stockage et le rafraîchissement des vues sont deux tâches très
consommatrices de ressources.
n La sélection des vues à matérialiser est un problème très ouvert.
16
Exemples CREATE MATERIALIZED VIEW [Link]
REFRESH FAST ON COMMIT
AS
SELECT Produit, Jour, Quantité, Montant
FROM [Link]
n L’utilisateur connaît WHERE Magasin='M1’;
la VM (il pense que
c’est une table) : SELECT *
FROM [Link];
CREATE MATERIALIZED VIEW VJOIN
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS SELECT *
FROM Ventes V
JOIN Produits P ON [Link]=[Link];
Q : SELECT Jour, SUM(Montant)
n L’utilisateur ne connaît FROM Ventes V
JOIN Produits P ON [Link]=[Link]
pas la VM : WHERE Pnom='iuyi'
GROUP BY Jour;
=> Optimisation
Q réécrite en :
SELECT Jour, SUM(Montant)
FROM VJOIN
WHERE Pnom='iuyi'
GROUP BY Jour;
17
Les vues matérialisées
n Réécriture de requêtes :
q L'optimiseur doit pouvoir reconnaître les vues matérialisées existantes :
il réécrit la requête d'une manière transparente à l'usager pour réutiliser
le résultat pré-calculé.
n Rafraîchissement :
q Le système peut dans certaines conditions assurer le rafraîchissement
d'une manière automatique et transparente.
q La fraicheur des données de la vue dépend des options choisies. Le
décalage entre les données de la table maître et la vue peut être nul
(rafraîchissement synchrone) ou d'une durée planifiée : heure, jour, etc.
q Demande de bien peser les volumétries, doser les fréquences de
rafraîchissement en fonction des besoins réels…
18
Création des vues matérialisées
n CREATE MATERIALIZED VIEW vue
BUILD [DEFERRED | IMMEDIATE]
REFRESH [<clause_refresh>]
[ENABLE | DISABLE] QUERY REWRITE
AS SELECT…
q BUILD DEFERRED : La vue est créée mais l'alimentation en données
est différée.
q BUILD IMMEDIATE : L'alimentation en données intervient aussitôt.
q ENABLE QUERY REWRITE : Précise si la vue est disponible pour la
réécriture (query_rewrite_enabled=TRUE). Nombreuses restrictions.
q DISABLE QUERY REWRITE : Interdit une réécriture sur la vue.
19
Rafraichissement
n FAST : La vue est mise à jour d'une manière incrémentale, elle utilise des
journaux spécifiques traçant les modifications de la table maître : les logs.
Méthode la plus efficace. Très nombreuses restrictions.
n COMPLETE : La vue est totalement recalculée. Coût plus important mais
économie de place des tables log.
n FORCE : FAST si possible, COMPLETE sinon.
n ON COMMIT : Rafraîchissement synchrone, déclenché automatiquement
au commit des transactions de mise à jour.
n ON DEMAND : Rafraîchissement asynchrone, déclenché par un usager.
n START WITH et NEXT : Rafraîchissement asynchrone, cyclique, précise
une date de début et une période de temps intermédiaire.
20
Exemples
n CREATE MATERIALIZED VIEW LOG ON Ventes;
n CREATE MATERIALIZED VIEW LOG ON Produits;
n CREATE MATERIALIZED VIEW VJoin
ENABLE QUERY REWRITE
REFRESH FAST
AS SELECT Cid, [Link], Jour, Montant, Categorie
FROM Ventes V
JOIN Produits P ON [Link]=[Link];
n Rafraîchissement automatique tous les jours à heure fixe : 8H00
n CREATE MATERIALIZED VIEW Vventes
ENABLE QUERY REWRITE
REFRESH START WITH trunc(sysdate+1)+ 8/24 NEXT trunc(sysdate+1) + 1
AS SELECT Cid, Jour, SUM(Montant)
FROM Ventes
GROUP BY Cid, Jour;
21
Partitionnement des vues matérialisées
n Une vue matérialisée peut être partitionnée.
n CREATE MATERIALIZED VIEW VVentes
PARTITION by RANGE (Jour)
(PARTITION M1 VALUES LESS THAN (TO_DATE('31-12-2015', 'DD-MM-YYYY')),
PARTITION M2 VALUES LESS THAN (TO_DATE('31-01-2016', 'DD-MM-YYYY')),
PARTITION M3 VALUES LESS THAN (MAXVALUE))
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT Jour, Pid, SUM(Montant)
FROM Ventes
GROUP BY Jour, Pid;
n Une vue matérialisée peut être indexée.
22
Optimisation des performances
1) Le partitionnement
2) Les vues matérialisées
3) Les index
23
Les index
n Objectif : Accéder plus rapidement aux données.
n Joue le rôle de « sommaire » pour les tables.
q Les infos concernant le client n°54 se trouvent à tel endroit.
q Evite de parcourir toute la table.
n Contient les données indexées + référence à la ligne : ROWID
q Colonne virtuelle dans chaque table.
q Donne l’adresse physique d’une ligne : Fichier, bloc, position dans le bloc.
q Moyen le plus rapide pour accéder aux données.
n Nécessite d’être mis à jour en même temps que la table.
n Optimisation des temps de réponse aux requêtes mais occupation
d’espace et temps de mise à jour plus longs.
24
Les index
n Index unique : CREATE UNIQUE INDEX idx ON tab(att1);
q Pas de doublon : Pour les clés primaires ou uniques
q Optimisé, plus rapide
n Index simple : CREATE INDEX idx ON tab(att2);
n Index basé sur une fonction : CREATE INDEX idx ON tab(f(att2));
q CREATE INDEX idx ON employees (UPPER(last_name));
q SELECT * FROM employees WHERE UPPER(last_name) LIKE 'J%S_N';
n Index multi-colonnes : CREATE INDEX idx ON tab(att1,att2, att3);
q Efficace pour les requêtes de type :
n WHERE att1.. AND att2… AND att3…;
n WHERE att1.. AND att2…;
n WHERE att1…;
25
Différents types d’index
n Index B-tree
q Pour les attributs servant fréquemment d'attributs de jointure (clés
étrangères) et de critère de recherche avec bcp de valeurs différentes
(nom) et pour les clés primaires et uniques.
q Requêtes renvoyant moins de 15% des lignes de la table.
q CREATE INDEX idx ON table(attributs);
n Index bitmap
q Pour les attributs présentant peu de valeurs distinctes (sexe, statut
marital).
q Requêtes renvoyant un grand nombre de lignes de la table.
q CREATE BITMAP INDEX idx ON table(attributs);
26
L'index B-tree
n Feuilles : valeur du champ indexé + ROWID.
27
L'index Bitmap
28
L'index Bitmap de jointure
n Index matérialisant la jointure de deux ou plusieurs tables.
q CREATE BITMAP INDEX idxVC ON Ventes(Type)
FROM Ventes V
JOIN Clients C ON [Link]=[Link];
SELECT Cid, Pid, SUM(Montant)
FROM Ventes V
JOIN Clients C ON [Link]=[Link]
WHERE Type='A’
GROUP BY Cid, Pid;
q CREATE BITMAP INDEX idxVPC ON Ventes(Type, Categorie)
FROM Ventes V
JOIN Produits P ON [Link]=[Link]
JOIN Clients C ON [Link]=[Link];
29
Les clusters
n Objectif : Accélérer les jointures.
n Stockage direct du résultat de la jointure. Les 2 tables sont stockées
dans le même segment.
n Création d'un cluster
q CREATE CLUSTER ComCli (refClient NUMBER);
q CREATE TABLE client … CLUSTER ComCli (refClient);
q CREATE TABLE commande … CLUSTER ComCli (refClient);
q CREATE INDEX idx_comcli ON CLUSTER ComCli;
30
Exemple
31
Exemple
32
Les clusters
n Le choix d'un cluster doit être bien réfléchi.
n Un sens de parcours privilégié (clé de cluster)
q Efficace :
n SELECT … WHERE refClient = 10;
q Peu efficace :
n SELECT … WHERE numCom = 4;
n Ne pas utiliser de cluster en cas de modification fréquente de la clé
de cluster.
33
Conclusion
n On ne peut pas mettre des index partout, créer pleins de vues, et
tout partitionner ! (trop couteux).
q Quelles tables indexer ? Quel type d'index ? Sur quelles colonnes ?
q Quelles tables partitionner ? Quel type de partitionnement ? Sur quels
critères ? Faut-il partitionner les index ?
q Quelles vues matérialisées créer ? Faut-il indexer les vues ? Faut-il
partitionner les vues ?
q Faut-il regrouper des tables dans des clusters ?
q Pas simple…
n Vérifier que la stratégie est efficace (partitions équilibrées, accès
bien répartis, index utilisés, requêtes réécrites pour utiliser les
vues…).
34
Conclusion
n Plusieurs méthodes d’optimisation :
q Partitionnement : tables
q Index : colonnes
q Vues : jointures, calculs complexes
q Clusters : jointures
n Choix de la stratégie difficile.
n Les index et les vues sont faciles à ajouter lorsque les tables sont
très volumineuses.
n Ces méthodes d’optimisation permettent de gagner beaucoup de
temps : facteurs > 1000.
35