1. No category

LOG660 - Bases de données de haute performance
Évaluation et optimisation de requêtes
Département de génie logiciel et des TI
Question
■ 
Laquelle de ces requêtes est la plus performante?
Requête 1:
SELECT SUM(LC.quantite)
FROM LigneCommande LC, Produit P
WHERE LC.idProduit = P.idProduit AND
P.categorie = ‘imprimante’
Requête 2:
SELECT SUM(LC.quantite)
FROM LigneCommande LC
WHERE LC.idProduit IN
(SELECT idProduit FROM Produit P
WHERE P.categorie = ‘imprimante’)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
2
Évaluation des requêtes relationnelles
■ 
SQL
– 
■ 
QUOI
Évaluateur de requêtes du SGBD
– 
COMMENT
– 
en fonction du schéma interne
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
3
Traitement des requêtes
Requête (ex:SQL)
Décomposition
Schéma
conceptuel &
externe
Requête interne
Optimisation
Schéma interne &
statistiques
Plan d'exécution
Exécution
BD
Résultats
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
4
Requête interne
Schéma relationnel:
<<table>>
Livre
{Clé prim aire : ISBN}
ISBN : CHAR(13)
titre : VARCHAR(50)
annéeParution : Dom aineAnnée
nom Editeur : VARCHAR(20)
code : VARCHAR(10)
Requête SQL:
SELECT
FROM
WHERE
<<ta ble>>
Catégorie
{Cl é pri maire : c ode}
code : VARCHAR(10)
des cripteur : VARCHAR(20)
codeParent : VARCHAR( 10)
codeParent
titre, descripteur
Livre, Catégorie
ISBN = 1-111-1111-1 AND Livre.code = Catégorie.code
Π titre, descripteur
Requête interne:
σ ISBN = 1-111-1111-1
Livre
Département de génie logiciel et des TI
Catégorie
© R. Godin, C. Desrosiers - Hiver 2011
5
Coût des opérations physiques
■ 
■ 
La performance d’une requête interne est évaluée en fonction:
Métrique
Description
TempsES :
Temps d’accès (lectures et écritures) à la mémoire secondaire
TempsUCT :
Temps de traitement de l’unité centrale (souvent négligeable
par rapport au temps d’accès)
TailleMC :
Espace requis en mémoire centrale
TailleMS :
Espace requis en mémoire secondaire
Dans les systèmes transactionnels:
– 
– 
– 
Le principal soucis est de pouvoir traiter les requêtes le plus
rapidement possible
Les accès disque sont les opérations les plus coûteuses
Donc, la principale métrique de performance est TempsES
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
6
Statistiques d’évaluation
■ 
■ 
L’estimation du coût des opérations se base sur des statistiques
Statistiques d’une table T:
Statistique
NT
TailleLigneT
FBT
FBMT
BT
CardT (col):
Description
Nombre de lignes de la table T
La taille d'une ligne de la table T
Facteur de blocage moyen de T
(nombre moyen de lignes contenues dans un bloc)
Facteur de blocage maximal de T
Estimation : !(TailleBloc-TailleDescripteurBloc )/TailleLigneT"
Nombre de blocs de la table T
Estimation : #NT / FBT$
Nombre de valeurs distinctes (cardinalité) de la colonne col
pour la table T
Ex : CardT(sexe) = 2
Valeurs minimale et maximale que peut prendre une
MinT (col), MaxT (col)
colonne col
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
7
Statistiques d’évaluation
■ 
Statistiques d’une expression de sélection par valeur (col = val) ou par
intervalle (col ∈ [val1, val2]) :
Statistique
FacteurSélectivitéT (col = val)
FacteurSélectivitéT (col ∈ [val1,val2])
SelT (col = val)
Description
Pourcentage de lignes pour lesquelles la colonne col
contient la valeur val
Estimation : 1/CardT (col)
Pourcentage de lignes pour lesquelles la colonne col
contient une valeur comprise entre val1 et val2
Estimation : (val2- val1)/( MaxT (col)- MinT (col) )
Nombre de lignes de T sélectionnées par l'expression de
sélection.
Estimation : FacteurSélectivitéT (col) * NT
■ 
Le facteur de sélectivité est important car il détermine le nombre de
blocs à transférer (TempsES)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
8
Statistiques d’évaluation
■ 
Statistiques d’un index I (arbre-B+):
Statistique
TailleEntréeI
OrdreI
OrdreMoyenI
HauteurI
■ 
Description
Taille d'une entrée dans un bloc interne de l'index
Approximation : taille de la clé d'index + taille pointeur de bloc
Nombre maximum de fils (pointeurs) pour un bloc interne de l'index I
Estimation : !(TailleBloc-TailleDescripteurBloc)/TailleEntréeI"
Nombre moyen de fils (pointeurs) pour un bloc interne de l'index I
Nombre de niveaux dans l'arbre de l'index I
Estimation : #logOrdreMoyenI (NT)$
Autres statistiques:
Statistique
THT
M
Description
Taille de l'espace d'adressage pour la fonction de hachage
Nombre de blocs disponibles en mémoire centrale pour le
traitement des opérations
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
9
Maintien des statistiques par le SGBD
■ 
■ 
Dilemme entre la précision des statistiques (menant à une
meilleure estimation des coûts) et l’effort (temps, cpu) requis
pour les générer
Types de mise à jour des statistiques:
– 
Incrémentale
■  À
– 
chaque mise à jour de la table ou de l’index
Mise à jour en différé
■  Durant
les périodes de faible activité (ex: la nuit)
■  Déclenchée par un script
– 
Estimation par échantillonnage
■  Pour
les tables très volumineuses (ex: pourcentage des lignes)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
10
Maintien des statistiques par le SGBD
■ 
Clause ANALYZE (Oracle)
– 
Statistiques complètes:
ANALYZE TABLE NomTable COMPUTE STATISTICS
– 
Échantillonnage (10 pourcent des lignes):
ANALYZE TABLE NomTable ESTIMATE STATISTICS
SAMPLE 10 PERCENT
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
11
Opérations à optimiser
1)  Sélection de lignes selon une clé
– 
Ex: balayage, index, hashage, etc.
2)  Tri des lignes d’une table
– 
Ex: algorithme tri-fusion
3)  Jointure de deux tables
– 
Ex: boucles imbriquées, jointure tri-fusion, etc.
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
12
Sélection d’enregistrements
■ 
Sélection par balayage:
– 
– 
■ 
Sélection par indexage:
– 
– 
– 
■ 
On parcourt la table jusqu'à ce qu'on trouve l'enregistrement désiré
Lire toute la table dans le pire cas: O(N)
On traverse l'index selon la clé de recherche
Complexité: O(log(N))
Permet également la sélection par intervalle
Sélection par hachage:
– 
– 
– 
L'adresse du bloc contenant l'enregistrement est obtenu en
appliquant une fonction de hashage sur la clé
Complexité: ~O(1)
Ne permet pas la sélection par intervalle
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
13
Balayage de tables (FULL TABLE SCAN)
■ 
■ 
On lit séquentiellement tous les blocs de la table
Facteurs à considérer:
– 
– 
■ 
Le nombre de blocs dans la table
Les temps de positionnement de la tête de lecture, dus aux bris de
séquence
Temps estimé:
– 
TempsES (BAL) = BT × TempsTrans + NombrePos × TempsPosDébut
Positionnement #1
Positionnement #2
Transfert sans bris de séquence
Positionnement #3
Transfert sans bris de séquence
...
Département de génie logiciel et des TI
Transfert sans bris de séquence
...
© R. Godin, C. Desrosiers - Hiver 2011
...
14
Balayage de tables (FULL TABLE SCAN)
■ 
Situations où il peut être acceptable de balayer une table:
– 
Sur les petites tables:
■  Plus
avantageux de lire tous les blocs de la table en mémoire
centrale et de faire les opérations (sélection, tri, jointure, etc.)
en mémoire centrale
– 
Sur les tables intermédiaires:
■  Ex:
table retournée par un SELECT imbriqué
■  On ne possède pas d’index ou autres structures d’optimisation
pour ces tables
■  Le balayage est souvent la seule option
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
15
Exemple : balayage de la table Livre
NLivre
FBMLivre
1 000 000
20
■ 
FBLivre= FBMLivre= 20
■ 
BLivre = ⎡NLivre/ FBLivre⎤ = ⎡1 000 000/ 20⎤ = 50 000 blocs
■ 
Meilleur cas (aucun bris de séquence):
– 
■ 
Pire cas (bris de séquence après chaque lecture de bloc) :
– 
■ 
TempsES (BALLivre) = 50,01 secs
TempsES (BALLivre) = 9,16 minutes
Observations:
– 
– 
Le balayage de grandes tables est à éviter si possible
Le temps de repositionnement peut avoir un impact significatif sur la
performance de la requête
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
16
Sélection par égalité dans un index
secondaire (INDEX SCAN)
■ 
Ex: SELECT * FROM Client WHERE id=1052
...
...
...
...
...
...
...
Hauteur -1
...
...
...
...
Département de génie logiciel et des TI
Feuilles à transférer
contenant les références
aux blocs de
l'organisation primaire
...
...
Blocs de l'organisation
primaire à transférer
© R. Godin, C. Desrosiers - Hiver 2011
17
Sélection par égalité dans un index
secondaire (INDEX SCAN)
■ 
Trois groupes de blocs à lire:
– 
– 
– 
■ 
Blocs internes de l’index (traverse de l’arbre):
■  (HauteurI -1) blocs à lire
Feuilles de l’index (les références):
■  ⎡SelT (cléIndex = val) /OrdreMoyenI⎤ blocs à lire
Blocs de l ’organisation primaire (les lignes recherchées)
■  SelT (cléIndex = val) blocs à lire
Sélection sur une clé unique (ex: clé primaire)
– 
– 
Un seul bloc feuille et un seul bloc de l’organisation primaire, car
une seule ligne (au max.) à récupérer
Total de (HauteurI +1) blocs à lire
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
18
Exemple: sélection sur clé non unique
(facteur de sélectivité faible)
NLivre
FBMLivre
CardLivre(code)
OrdreMoyenI
1 000 000
20
4 000
66
■ 
HauteurI = ⎡log66(1 000 000)⎤ = 4
■ 
SelLivre (code = val) ≈ NLivre/CardLivre(code) = 250 lignes
■ 
TempsES (index secondaire) = 2,83 sec
■ 
Observations:
– 
La sélection par index secondaire est nettement plus rapide que le
balayage dans ce cas ci (2.8 sec versus 50 sec)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
19
Exemple: sélection sur clé non unique
(facteur de sélectivité élevé)
NLivre
FBMLivre
CardLivre(code)
OrdreMoyenI
1 000 000
20
20
66
■ 
HauteurI = ⎡log66(1 000 000)⎤ = 4
■ 
SelLivre (code = val) ≈ NLivre/CardLivre(code) = 50 000 lignes
■ 
TempsES (index secondaire) = 558,37 secs
■ 
Observations:
– 
– 
– 
La sélection par index secondaire est pire que le balayage (558 sec
versus 50 sec) !
Comme les lignes se trouvent (potentiellement) dans des blocs
différents, il faut repositionner la tête de lecture pour chaque ligne
Il est préférable de simplement balayer la table dans ce cas
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
20
Exemple: sélection sur clé unique
(clé primaire ISBN)
■ 
HauteurI = ⎡log66(1 000 000)⎤ = 4
■ 
TempsES (index secondaire) =
NLivre
FBMLivre
CardLivre (ISBN)
OrdreMoyenI
1 000 000
20
1 000 000
66
(HauteurI +1) × TempsESBloc = 55 ms
■ 
TempsES (index primaire) =
HauteurI × TempsESBloc = 44 ms
■ 
Observations:
– 
– 
Un index secondaire est comparable à un index primaire dans le
cas d'une clé unique (un seul bloc de plus à lire pour la référence)
L'index secondaire est donc préférable pour les clés uniques
(Oracle en crée un par défaut sur chaque clé primaire)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
21
Balayage vs sélection par index secondaire
(N = 1 000 000, FBM =20)
temps (ms)
Index secondaire
■ 
Balayage
Observations:
– 
– 
– 
L’index secondaire est préférable au balayage si la colonne de
sélection possède plus de 200 valeurs différentes (CardT(col) ≥ 200)
Oracle recommande de NE PAS créer d’index si la sélection peut
retourner plus que 15% des lignes (FacteurSélectivitéT(col) ≥ 15%)
Ex: créer un index sur une colonne sexe est une mauvaise idée
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
22
Taille de l'index secondaire
■ 
■ 
Index secondaire sur la colonne code de Livre (clé non unique)
Taille de la table Livre:
– 
■ 
1 000 000
20
4 000
66
BLivre = ⎡NLivre/ FBMLivre⎤ = ⎡1 000 000/ 20⎤ = 50 000 blocs
Taille de l’index (estimée par le nombre de blocs feuilles):
– 
■ 
NLivre
FBMLivre
CardLivre(code)
OrdreMoyenI
BIndexSecondaire = ⎡NLivre / OrdreMoyenI ⎤ = 15 152 blocs
Observations:
– 
– 
La taille de l’index en mémoire secondaire correspond à 30% de
la taille de la table indexée !
Il faut donc créer des index UNIQUEMENT lorsque pertinent
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
23
Balayage de l’index (FULL INDEX SCAN)
■ 
Dans les cas où toutes les colonnes souhaitées se trouvent dans
la clé de l’index
■ 
Ex: (index sur la colonne idClient d’une table Transaction)
SELECT idClient, COUNT(*) as nbTransactions
FROM Transaction
GROUP BY idClient
■ 
On balaye les blocs feuilles de l’index au lieu de balayer les
blocs de la table
■ 
Comme l’information est plus compacte dans les feuilles de
l’index, il y a moins de blocs à lire
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
24
Sélection par égalité avec hachage
(HASH SCAN)
■ 
Stratégie de chaînage en cas de débordement
■ 
Dans le pire cas, il faut parcourir tous les blocs de l’adresse
correspondant à la clé de sélection
■ 
Nombre moyen de blocs à lire (distribution uniforme des lignes):
– 
⎡NT / (THT × FBT)⎤ blocs
Adresse du
paquet
0
1
2
3
Département de génie logiciel et des TI
...
TH-1
© R. Godin, C. Desrosiers - Hiver 2011
25
Exemple: hachage sur ISBN de Livre
(HASH cluster Oracle)
■ 
Cas idéal (sans débordement)
NLivre
FBMLivre
– 
Taux de remplissage des blocs TR = 80%
– 
FBLivre = 80% × FBMLivre = 16
– 
THLivre = ⎡NLivre/ FBLivre⎤ = 62 500
1 000 000
20
■ 
TempsES (hashage) = ⎡1 000 000/(62 500 × 16)⎤ × 11 ms = 11 ms
■ 
TempsES (index secondaire) = (HauteurI + 1) × 11 ms = 55 ms
■ 
Observations:
– 
– 
Dans le cas idéal, la sélection par hashage est optimale car il n’y a qu’un
seul bloc à lire (versus 5 pour l’index secondaire)
Cependant, la performance peut être affectée par les débordements, si
la taille d’adressage (paramètre TH) est mal choisie
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
26
Tri d'une table
■ 
Plusieurs opérations nécessitent de trier une table:
– 
– 
– 
– 
■ 
Si le nombre de blocs en mémoire centrale (M) est grand
– 
■ 
Résultats triés (ORDER BY)
Élimination des doubles (DISTINCT)
Opérations d’agrégation (GROUP BY)
Jointure par tri-fusion
On lit la table et la trie en mémoire centrale
Sinon, si M est petit
– 
On doit faire un tri externe de la table (ex: algorithme de trifusion)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
27
Algorithme de tri fusion
■ 
Étape tri
– 
– 
– 
On trie la table en mémoire centrale par groupes de M blocs
On doit lire et écrire tous les blocs de la table, avec un
repositionnement pour chaque groupe de M blocs
Coût : 2 × ( ⎡BT /M⎤ × TempsPosDébut + BT × TempsTrans)
Positionnement
Positionnement
Lecture
15
4
Positionnement
Lecture
3
9
18
Positionnement
Ecriture
3
Positionnement
4
Lecture
12
16
9
2
Positionnement
Ecriture
15
Positionnement
12
Département de génie logiciel et des TI
Lecture
5
7
2
6
Positionnement
Ecriture
18
14
5
Création
de 12/3 =
4 groupes
Ecriture
16
6
7
14
© R. Godin, C. Desrosiers - Hiver 2011
28
Algorithme de tri fusion
■ 
Étape fusion
– 
– 
– 
On fusionne récursivement les groupes voisins (triés) jusqu’à
obtenir un seul groupe contenant tous les blocs
Environ ⎡logM-1(BT /M)⎤ passes de fusion, chacune demandant un
balayage en lecture et écriture de la table
Coût : BT × (2⎡logM-1(BT /M)⎤ - 1) × TempsESBloc
3
4
15
9
12
18
2
5
16
6
7
14
Passe de
fusion #1
produit 4/2 = 2
groupes
3
4
9
12
15
18
2
5
6
7
14
16
Passe de
fusion #2
produit 2/2 =1
groupe
2
3
4
5
6
7
Département de génie logiciel et des TI
9
12
14
15
16
18
© R. Godin, C. Desrosiers - Hiver 2011
29
Exemple: Tri de la table Livre
■ 
M = 50
■ 
TempsES (tri-fusion) = 29,5 mins
■ 
Observations:
– 
– 
NLivre
FBMLivre
BLivre
1 000 000
20
50 000
Inefficace de trier une grande table lorsqu’on possède peu de
mémoire centrale (ex: seulement M=50 blocs)
Solution alternative: créer un index primaire ou CLUSTER
INDEX sur la clé de tri afin que les lignes soient physiquement
ordonnées selon cette clé, en mémoire secondaire
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
30
Jointure de deux tables (R !θ S)
■ 
Une des opérations ayant le plus d’impact sur la
performance des requêtes
■ 
L’optimisation des jointure est donc cruciale
■ 
Principales approches de jointure:
– 
– 
– 
– 
– 
Jointure par boucles imbriquées par blocs (BIB)
Jointure par boucles imbriquées avec index (BII)
Jointure par tri-fusion (JTF)
Jointure par hashage (JH)
Pré-jointure (PJ)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
31
Boucles imbriquées par blocs
(NESTED LOOP JOIN)
POUR chaque bloc bR de R
POUR chaque bloc bS de S
POUR chaque ligne lR de bR
POUR chaque ligne lS de bS
SI θ sur lR et lS est satisfait
Produire la ligne concaténée à partir de lR et lS
FINSI
FINPOUR
FINPOUR
FINPOUR
FINPOUR
■ 
TempsES (BIB) =
BR × (TempsESBloc + BS×TempsTrans + TempsPosDébut)
■ 
Variante : Boucles imbriquées multiblocs (BIM)
– 
– 
On lit M blocs de R à la fois (au lieu de 1 bloc)
Réduit le nombre de repositionnements de la tête de lecture
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
32
Exemple: BIB sur Livre ! Catégorie
■ 
■ 
■ 
■ 
R=Livre et S=Catégorie:
– 
TempsES (BIB) = 100,83 mins
– 
TempsES (BIB) = 50,1 sec
NCatégorie
FBCatégorie
BCatégorie
NLivre
R = Catégorie et S = Livre:
FBLivre
–  TempsES (BIB) = 83,37 mins
BLivre
Si S est lue une seule fois en mémoire centrale:
4 000
40
100
1 000 000
20
50 000
Observations:
– 
– 
Légèrement avantageux de mettre la plus petite table dans la
boucle externe (83,37 mins vs 100,83 mins)
Gain important de performance si la table de la boucle interne
peut être mise en mémoire (aucune relecture)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
33
Boucles imbriquées avec index
(NESTED LOOPS with index)
■ 
Comme BIB, sauf que les lignes la table interne sont obtenues à
l’aide d’un index (au lieu d’un balayage)
POUR chaque ligne lR de R
POUR chaque ligne lS de S satisfaisant θ (sélection en utilisant un index)
Produire la ligne concaténée à partir de lR et lS
FINPOUR
FINPOUR
■ 
TempsES (BII) =
BR × TempsESBloc + NR × TempsES (index secondaire)
■ 
Exige d’avoir un index sur la colonne de jointure de la table
située dans la boucle interne
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
34
Exemple: BII sur Livre ! Catégorie
■ 
R=Livre et S=Catégorie
– 
– 
■ 
4 000
40
100
NLivre
FBLivre
BLivre
1 000 000
20
50 000
TempsES (index secondaire sur Catégorie.code) = 55 ms
TempsES (BII) =
BLivre × TempsESBloc + NLivre × TempsES (index sec.) = 15,43 hrs
R = Catégorie et S = Livre
– 
– 
■ 
NCatégorie
FBCatégorie
BCatégorie
TempsES (index secondaire sur Livre.code) = 2827 ms
TempsES (BII) =
BCatégorie× TempsESBloc + Ncatégorie× TempsES (index sec.) = 3,14 hrs
Observations:
– 
– 
La sélection par index n’est pas tellement affectée par la taille de la table
Il faut donc TOUJOURS mettre la plus grande table dans la boucle
interne de la jointure BII (ex: table Livre dans ce cas)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
35
Contexte avantageux pour BII
■ 
■ 
■ 
Π titre, descripteur
L’algorithme BII est très avantageux
lorsqu’une des tables est beaucoup
plus petite que l’autre
(Balayage)
Ex: la table produite par la sélection
de la table Livre à l’aide d’une clé
primaire (ne contient qu’une seule
σ ISBN = 1-11-111-1111-1
ligne)
Dans ce cas, l’algorithme BII
n’exige qu’une seule sélection par
index (table Catégorie dans la boucle
interne)
Département de génie logiciel et des TI
(Boucle imbriquée
avec index secondaire
sur code de la table
interne Catégorie)
Catégorie
( S é le c t io n p a r in d e x
s e c o n d a ir e s u r IS B N )
Livre
© R. Godin, C. Desrosiers - Hiver 2011
36
Jointure par tri-fusion (SORT MERGE JOIN)
■ 
■ 
On trie les tables R et S, et on les fusionne en faisant un balayage
synchronisé dans les tables triées
On joint les groupes de lignes ayant la même valeur pour la clé
Trier R et S par tri externe et réécrire dans des fichiers temporaires
Lire groupe de lignes GR(cR) de R pour la première valeur cR de clé de jointure
Lire groupe de lignes GS(cS) de S pour la première valeur cS de clé de jointure
TANT QUE il reste des lignes de R et S à traiter
SI cR = cS
Joindre chaque paire de lignes GR(cR) et GS(cS);
Lire les groupes suivants GR(cR) de R et GS(cS) de S;
SINON SI cR < cS
Lire le groupe suivant GR(cR) de R;
SINON SI cR > cS
Lire le groupe GS(cS) suivant dans S;
FINSI
FIN TANT QUE
■ 
TempsES (JTF) =
TempsES (TRIR) + TempsES (TRIS) + 2 × (BR + BS) × TempsESBloc
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
37
Exemple: JTF sur Livre ! Catégorie
NCatégorie
FBMCatégorie
BCatégorie
TempsES(TRICatégorie)
■ 
NLivre
FBMLivre
BLivre
TempsES(TRILivre)
1 000 000
20
50,000
1 770 000 ms
M = 50
– 
– 
■ 
4 000
40
100
1 340 ms
TempsES (JTF) = TempsES (TRILivre) + TempsES (TRICatégorie) +
2 × (Blivre+BCatégorie) × TempsESBloc = 2 874 secs
TempsES (BIM) = 150 secs (Catégorie dans la boucle externe)
Observations:
– 
– 
La jointure BIM est plus performante, même si sa complexité
moyenne est pire que celle de JTF
Explication: une des tables est petite (table Catégorie), ce qui
correspond au cas avantageux pour BIM (ou BII)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
38
Exemple 2: 100 fois plus de Catégories
NCatégorie
FBMCatégorie
BCatégorie
TempsES(TRICatégorie)
■ 
– 
1 000 000
20
50,000
1 770 000 ms
TempsES (JTF) = 3 444 secs
TempsES (BIM) = 10 464 secs
M = 10
– 
– 
■ 
NLivre
FBMLivre
BLivre
TempsES(TRILivre)
M = 50
– 
■ 
400 000
40
10 000
350 000 ms
TempsES (JTF) = 6 180 secs
TempsES (BIM) = 62 535 secs
Observations:
– 
Lorsque les deux tables sont grandes, JTF est plus performante
que BIM, surtout lorsqu’il y a peu d’espace en mémoire centrale
(M est petit)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
39
Jointure par hachage (HASH JOIN)
■ 
Étape 1: partition des tables
{Partitionner R par hachage}
POUR chaque ligne lR de R
Ajouter lR au paquet Ri , où i = h(clé de lR)
Chaîner un nouveau bloc s'il y a débordement
FINPOUR
{Partitionner S par hachage}
POUR chaque ligne lS de S
Ajouter lS au paquet Si , où i = h(clé de lS)
Chaîner un nouveau bloc s'il y a débordement
FINPOUR
■ 
Étape 2: jointure des lignes dans les paquets de même adresse
POUR chaque paquet Ri
POUR chaque ligne lR de Ri
Joindre lR avec les lignes de Si ayant la même valeur pour la clé de jointure
FINPOUR
FINPOUR
■ 
TempsES (JH) =
TempsES (BALR) + TempsES (BALS) + 2 × (BR + BS) × TempsESBloc
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
40
Exemple: JH sur Livre !Catégorie
NCatégorie
FBCatégorie
BCatégorie
TempsES (BALCatégorie)
■ 
NLivre
FBLivre
BLivre
TempsES (BALLivre)
1 000 000
20
50 000
50 010 ms
M = 50
– 
– 
– 
■ 
400 000
40
10 000
10 010 ms
TempsES (JH) = 2 700 secs
TempsES (JTF) = 3 444 secs
TempsES (BIM) = 10 464 secs (R = Catégorie)
Observations:
– 
Sauf si une des tables est déjà triée, JH est un peu plus rapide que
JTF
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
41
Pré-jointure par une organisation en grappe
hétérogène (CLUSTER JOIN)
■ 
Les deux tables à joindre sont organisées dans une même
grappe (cluster) hétérogène, selon la clé de jointure
– 
■ 
Les tables sont physiquement jointes dans la grappe
– 
■ 
Ex: Client et Compte dans une même grappe selon la clé idClient
Ex: grappe ≈ Client ! Compte (égalité sur idClient)
Il suffit de balayer séquentiellement les blocs de la grappe:
– 
– 
TempsES (PJ) = TempsES (BALR) + TempsES (BALs)
Optimal en théorie, car un seul balayage des tables à faire, mais la
fragmentation interne peut nuire aux performances
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
42
Exemple: PJ sur Livre ! Catégorie
NLivre
FBLivre
BLivre
TempsES (BALLivre)
1 000 000
20
50 000
50 010 ms
NCatégorie
FBCatégorie
BCatégorie
TempsES (BALCatégorie)
4 000
40
100
110 ms
■ 
On suppose que les tables Livre et Catégorie sont organisées dans
une même grappe, selon la clé code
■ 
TempsES (PJ) = 50 secs
■ 
TempsES (BIM) = 150 secs (R = Catégorie, M = 50)
■ 
Observations:
– 
– 
PJ est trois fois plus rapide que BIM, même si l’une des tables est
beaucoup plus petite que l’autre
Pas toujours le cas en pratique
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
43
Comparaison des méthodes de jointure
■ 
BIB / BIM (NESTED LOOPS JOIN)
–  Lorsqu’une des deux tables à joindre (ou les deux) est petite
et peut être lue en mémoire centrale
■ 
BII (NESTED LOOPS INDEX JOIN)
–  Lorsqu’une des tables est beaucoup plus petite que l’autre et
qu’on possède un index secondaire sur la clé de jointure de
la plus grande table
–  La plus grande table est mise dans la boucle interne, et le
coût de sélection de ses lignes est amorti par l’index
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
44
Comparaison des méthodes de jointure
■ 
JTF (SORT MERGE JOIN)
– 
– 
– 
– 
■ 
Lorsqu’on doit joindre deux grandes tables
Permet également les jointures sur des conditions d’inégalité
Avantageux lorsqu’une des tables (ou les deux) est déjà triée (ex: index
primaire sur la table ou tri provenant d’une opération en amont)
Note: l’étape de tri peut être accélérée à l’aide d’un FULL INDEX
SCAN sur un index de la clé de jointure (la clé déjà triée dans l’index)
JH (HASH JOIN)
– 
– 
– 
Lorsqu’on doit joindre deux grandes tables
Permet seulement les jointures sur des conditions d’égalité
Avantageux lorsqu’une des tables (ou les deux) est déjà organisée
dans une grappe homogène (single table hash cluster) selon la clé de
jointure
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
45
Comparaison des méthodes de jointure
■ 
PJ (CLUSTER JOIN)
–  Optimal en théorie, mais peut parfois nuire à l’exécution:
■  Fragmentation
importante de la mémoire secondaire
■  Ralentit le balayage d’une table dans la grappe (car les lignes
de la table sont plus distantes les unes des autres)
■  Donc détériore la performance dans la situation ou le balayage
est privilégié
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
46
Optimisation
■ 
Chercher le meilleur plan d’exécution ?
– 
■ 
Beaucoup trop de possibilités en pratique
Solution approchée à coût raisonnable
– 
Générer un nombre limité d’alternatives prometteuses
■  Heuristiques
– 
(ex: ordre des sélections, projection, jointures, etc.)
Choisir la meilleure
■  Estimation
approximative du coût d’exécution
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
47
Plans d'exécutions équivalents
■ 
Plusieurs arbres algébriques équivalents
Π titre, descripteur
Π titre, descripteur
σ ISBN = 1-111-1111-1
σ ISBN = 1-111-1111-1
Livre
Catégorie
Département de génie logiciel et des TI
Catégorie
Livre
© R. Godin, C. Desrosiers - Hiver 2011
48
Règles d’équivalences d’algèbre relationnelle
■ 
Eclatement d'une sélection conjonctive (SE)
– 
■ 
Commutativité de la sélection (SC)
– 
■ 
π liste1 (π liste2 (T)) = π liste1 (T)
Commutativité de la jointure (JC)
– 
■ 
σ e1 (σ e2 (T)) = σ e2 (σ e1 (T))
Elimination des projections en cascades (PE)
– 
■ 
σ e1 ET e2 (T) = σ e1 (σ e2 (T))
T1 ! T2 = T2 ! T1
Associativité de la jointure (JA)
– 
T1 ! (T2 ! T3) = (T1 ! T2) ! T3
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
49
Règles d’équivalences d’algèbre relationnelle
(suite)
■ 
■ 
Commutativité restreinte de la sélection et de la jointure (CSJ)
– 
σe (T1 ! T2) = σe (T1) ! T2
– 
Si l'expression de sélection e ne contient que des colonnes de T1
Commutativité restreinte de la projection et de la sélection (CPS)
– 
■ 
πlisteCol (σe (T)) = πlisteCol (σe (π(listeCol ∪ col de e) T))
Commutativité restreinte de la projection et de la jointure (CPJ)
– 
πlisteCol (T1 ! T2) =
πlisteCol (π(listeCol
■ 
∩ col de T1) (T1)
! π(listeCol ∩ col de T2) (T2))
etc.
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
50
Plusieurs plans d’exécution pour un
arbre algébrique
■ 
Pour chaque opération logique
– 
Plusieurs choix d’opérations physiques
Π titre, descripteur (Balayage)
Π titre, descripteur (Balayage)
Π titre, descripteur (Balayage)
σ ISBN = 1-111-1111-1 (Balayage) σ ISBN = 1-111-1111-1 (Balayage) σ ISBN = 1-111-1111-1 (Balayage)
(Jointure par
tri-fusion)
Livre
Catégorie
– 
(Jointure par
BIM)
Livre
Catégorie
(Jointure par
hachage)
Livre
Catégorie
etc.
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
51
Mise en oeuvre par matérialisation
Π titre, descripteur(Balayage)
(Boucle imbriquée par
index secondaire sur
code de la table
interne Catégorie)
Catégorie
σ
(Sélection par index
secondaire sur
annéeParution = 2000 annéeParution)
Matérialisation:
•  On exécute les opérations depuis les
feuilles vers la racine de l’arbre
algébrique
•  Chaque opération produit une table
intermédiaire
Désavantages:
•  On doit écrire les tables intermédiaires
sur disque si elles sont volumineuses
(coût additionnel)
•  On ne possède pas d’index sur les
tables intermédiaires, donc les
opérations sur ces tables sont
coûteuses
Livre
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
52
Mise en oeuvre par pipeline
Π titre, descripteur (Balayage)
Pipeline:
•  Chaque ligne traverse toutes les
opérations sans être écrite dans une
table temporaire
(Boucle imbriquée par
index secondaire sur
code de la table
interne Catégorie)
Catégorie
Avantage:
•  Aucune écriture supplémentaire
Désavantage:
•  Pas adapté à toutes les opérations
•  Ex (tri): on doit trier toutes les lignes
en même temps
En pratique:
σ
(Sélection par index
annéeParution = 2000 secondaire sur
annéeParution)
•  On utilise le pipeline si possible, et la
matérialisation sinon
Livre
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
53
Heuristiques d'optimisation
■ 
Élaguer l’espace des solutions
– 
■ 
Solutions non applicables
Exemples d’heuristiques
– 
– 
– 
– 
Sélections le plus tôt possible
Projections le plus tôt possible
Les jointures plus restrictives en premier
Jointures supportées par index, hachage ou grappe en
premier
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
54
Optimisation par coût
■ 
Minimiser le coût (ex: temps d’exécution)
■ 
Stratégies
– 
– 
– 
– 
– 
Programmation dynamique
Amélioration itérative
Recuit simulé
Algorithme génétique
etc.
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
55
Estimation du coût d'un plan d'exécution
(Balayage
Π titre, descripteur TempsES
= 11 ms)
(Ecriture du résultat
TempsES = 11 ms)
(Balayage
σ ISBN = 1-111-1111-1TempsES
= 92 314 ms)
(Ecriture du résultat
TempsES = 846 164 ms)
(Jointure par tri-fusion
TempsES = 2 873 540 ms)
Livre
Coût total =
3 812 040ms
TempsES(Plan avec pipeline) =
TempsES (JTFLivre!Catégorie) =
2 873 540 ms
Catégorie
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
56
Autre exemple
Π titre, descripteur (Balayage
TempsES = 11 ms)
(Ecriture du résultat
TempsES = 11 ms)
(Boucle imbriquée par
index secondaire sur
code de la table
interne Catégorie
TempsES = 44ms)
Coût total =
132ms
Catégorie
TempsES(Plan avec pipeline) =
(Ecriture du résultat
TempsES = 11ms)
σ ISBN = 1-111-1111-1
(Sélection par index
secondaire sur ISBN
TempsES = 55ms)
TempsES(S=IS pour index sur ISBN) +
N σ ISBN=1-11-111-1111 (Livre) * TempsES(S=IS sur code
de Catégorie)
= 55 ms + 33 ms = 88 ms
Livre
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
57
Types d’optimisation Oracle
■ 
COST (statistique): minimise le coût estimé
– 
– 
– 
Besoin de statistiques (ANALYSE)
Plus cher à calculer mais donne de meilleurs plans d’exécution
Mode ALL_ROWS
■  Minimise
le temps total
■  Ex: on préfère JTF à BIM pour joindre deux grandes tables
– 
Mode FIRST_ROWS
■  Minimise
temps de réponse (obtention des premières lignes)
■  Ex: on préfère BIM à FTH pour joindre deux grandes tables
■  Utilisé dans les applications interactives où l’utilisateur n’a pas
besoin de voir simultanément toutes les lignes d’une requête
■ 
RULE (heuristique)
– 
Utilisé seulement lorsque des statistiques ne sont pas disponibles
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
58
Optimisation heuristique
■ 
Oracle
– 
mode RULE
Rang
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Chemin d'accès
Sélection par ROWID
Sélection d'une ligne par jointure dans une organisation par
index groupant ou hachage hétérogène (CLUSTER)
Sélection d'une ligne par hachage sur clé candidate
(PRIMARY ou UNIQUE)
Sélection d'une ligne par clé candidate
Jointure par une organisation par index groupant ou
hachage hétérogène (CLUSTER)
Sélection par égalité sur clé de hachage (HASH CLUSTER)
Sélection par égalité sur clé d'index groupant (CLUSTER)
Sélection par égalité sur clé composée
Sélection par égalité sur clé simple d'index secondaire
Sélection par intervalle borné sur clé indexée
Sélection par intervalle non borné sur clé indexée
Tri-fusion
MAX ou MIN d'une colonne indexée
ORDER BY sur colonne indexée
Balayage
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
59
Changement du mode pour une session
■ 
ALTER SESSION SET OPTIMIZER_GOAL =
– 
RULE | ALL_ROWS | FIRST_ROWS | CHOOSE
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
60
Indices d'optimisation (hints)
■ 
Suggère des techniques d’optimisation à SGBD pour une requête
particulière
■ 
Sert en mode de conception ou lorsque l’optimiseur ne choisit pas
un plan optimal (ex: mauvaises statistiques)
■ 
Exemple 1: Forcer l’utilisation du mode d’optimisation RULE
SELECT /*+ RULE*/ nom
FROM Client
WHERE noClient = 10 ;
■ 
Exemple 2: Forcer l’utilisation de l’index secondaire pour les
sélections sur la colonne index_sexe de Employé (mauvaise idée)
SELECT /*+ INDEX(EMPLOYÉ INDEX_SEXE)*/ nom, adresse
FROM EMPLOYÉ WHERE SEXE = ‘F’
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
61
Optimisation dans SQL Developper
■ 
Mode AUTOTRACE
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
62
Optimisation dans SQL Developper
■ 
Plan d’exécution:
OPERATION
Nom de l’opération du plan d’exécution (ex :
NESTED LOOPS, HASH JOIN)
COST
Coût de l’opération estimé par l’optimiseur de
requête
LAST_CR_BUFFER_GETS
Nombre de blocs lus de la cache pour chaque
opération, lors de la dernière exécution
LAST_ELAPSED_TIME
Temps (en microsecondes) passé dans chaque
opération, lors de la dernière exécution
LAST_STARTS
Nombre de fois qu’une opération a été faite
lors de la dernière exécution
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
63
Optimisation dans SQL Developper
■ 
Statistiques d’exécution (liste partielle):
consistent gets
Nombre de blocs lus en mémoire centrale après accès
éventuel au disque
physical reads
Nombre de lectures effectives sur le disque (en blocs)
redo size
Le nombre d’octets générés pour le journal permettant de
refaire l’opération (redo log)
bytes sent
Le nombre d’octets envoyés de la BD au client par le réseau.
bytes received
Le nombre d’octets envoyés du client à la BD par le réseau
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
64
Oracle SQL Tuning Advisor
Source: Oracle® Database 2 Day + Performance Tuning Guide 11g Release 1 (11.1)
Département de génie logiciel et des TI
© R. Godin, C. Desrosiers - Hiver 2011
65