1 Évaluation des requêtes relationnelles n sql – quoi n Évaluateur de requêtes du sgbd –...
Post on 03-Apr-2015
111 Views
Preview:
TRANSCRIPT
1
Évaluation des requêtes relationnelles
SQL
– QUOI
Évaluateur de requêtes du SGBD
– COMMENT
– en fonction du schéma interne
2
Exemple courant
Dans la suite, on va considérer les
tables
– Livre(ISBN, titre, année, éditeur, #code)
– Catégorie(code, description, #code_parent)
3
Requête interne
Livre Catégorie
ISBN = 1-111-1111-1
titre , descrip teur
SELECT titre, descripteur
FROM livre, categorie
WHERE ISBN=1-1111 AND livre.code=categorie.code
4
Plan d ’exécution 1
L ivre C atégorie
(Jo inture par tri-fus ion )
IS B N = 1-111-1111-1
titre , descrip teur
C oût tota l =2 873 540 m s
(Balayage)
(Balayage)
5
Plan d ’exécution 2
L ivre C atégorie
(Jo inture par boucleim briquée m ultib locs - tab le
in terne C atégorie )
IS B N = 1-111-1111-1
titre , descrip teur
C oût to ta l =175 040 m s
(Balayage)
(Balayage)
6
Plan d ’exécution 3
L ivre
C atégorie
(Jo inture par boucleim briquée en passant par
l'index secondaire sur codede la tab le in terne
C atégorie )
IS B N = 1-111-1111-1
titre , descrip teur
C oût tota l =88 m s
(Balayage)
(Sélection parl'index secondaire
sur ISBN )
7
Estimation du coût des opérations physiques
TempsES : temps d’accès à la mémoire secondaire (MS)
TempsUCT – souvent négligeable
TailleMC : espace mémoire centrale TailleMS : espace mémoire
secondaire
8
StatistiquesTempsPosDébut
Temps qu’il faut pour se positionner au début du premier octet à transférer (ex: 10ms)
TempsTrans Temps qu’il faut pour transférer un bloc (ex: 1ms)
TempsESBloc (ex: 11ms)
TailleBloc (ex: 2KO)
NT Nombre de lignes de la table T
TailleLigneT Taille d’une ligne de la table T
FBT Facteur de blocage moyen de T (#lignes/bloc)
FBMT Facteur de blocage Max de T
BT Nombre de blocs de T
9
Coût des opérations de base
Hypothèses
– Néglige effet de l ’antémémoire (cache)
– Néglige écriture du résultat
– TailleLigne < TailleBloc
– Sans interruption
10
Balayage (BAL)
TempsES (BAL) = BT * TempsTrans + NombrePos * TempsPosDébut
Positionnement #1
...
Transfert sans bris de séquence
Positionnement #2
...
Transfert sans bris de séquence
Positionnement #3
...
Transfert sans bris de séquence
11
Exemple : TempsES (BALEditeur)
Allocation sérielle sans fragmentation interne
BEditeur = NEditeur / FBEditeur = 50/ 60 = 1 bloc
TempsES (BALEditeur) = 11 ms
NEditeur 50FBMEditeur 60
12
Exemple : TempsES (BALCatégorie)
Allocation sérielle sans fragmentation interne
BCatégorie = 4 000/ 40 = 100 blocs
Meilleur cas : 100 * 1 ms + 1 * 10 ms = 110 ms
Pire cas : 100 *1 ms+100* 10 ms = 1 100 ms
NCatégorie 4 000FBMCatégorie 40
13
Exemple : TempsES (BALLivre)
Allocation sérielle sans fragmentation interne
BLivre = 1 000 000/ 20 = 50 000 blocs
Meilleur cas :
– TempsES (BALLivre) = 50,01 secs
Pire cas :
– TempsES (BALLivre) = 9,16 minutes
NLivre 1 000 000FBMLivre 20
14
Exemple : TempsES (BALLivre)
Arbre-B+ primaire sur la clé primaire ISBN
FBLivre = 2/3 FBMLivre = 13
BLivre = NLivre/ FBLivre = 1 000 000/ 13 = 76 924 blocs
Pire cas (consécutivité des feuilles non assurée) !
= 76 924* 1ms + 76 924 * 10ms = 848 164 ms = 14,1
mn
NLivre 1 000 000FBMLivre 20
15
Sélection par égalité dans un index arbre-B+ primaire (S=IP)
TempsES (S=IP) = – Parcours des niveaux d ’index
(HauteurI -1) * TempsESBloc +
– Parcours des feuilles SelT (CléIndex = Valeur)/ FBT* TempsESBloc
...
...
...
...
.........
Hauteur -1
Feuilles à transférer
16
Suite
Cas d ’une clé candidate
TempsES (S=IP )= HauteurI * TempsESBloc
Estimation de HauteurI
– 1 + log OrdreMoyenI (CardT (CléIndex) / FBT)
OrdreMoyenI = 2/3 OrdreI
FBT = 2/3 FBMT
17
Index primaire code de la table Catégorie (clé)
OrdreMoyenI = 2/3 OrdreI = 66
FBCatégorie = 2/3 FBMCatégorie = 26
HauteurI = 1 + log OrdreMoyenI (CardCatégorie (code) / FBCatégorie)
= 1+ log 66 (4 000 / 26) = 3
TempsES (S=IP) = HauteurI *TempsESBloc = 33 ms
NCatégorie 4 000FBMCatégorie 40CardCatégorie (code) 4 000OrdreI 100
18
Index primaire sur #code de la table Livre (clé étrangère)
OrdreMoyenI = 2/3 OrdreI = 66
FBLivre = 2/3 FBMLivre = 13
HauteurI = 1 + log OrdreMoyenI (CardLivre (code) / FBLivre) = 3
FacteurSélectivitéLivre (code) = 1/ CardLivre (code) = 1/4 000
SelLivre (code = Valeur ) = 1 000 000/4000 = 250 lignes TempsES (S=IP) =
– (HauteurI -1) * TempsESBloc + SelLivre (code = Valeur)/ FBLivre)* TempsESBloc
– = 2* 11 ms + ( 250/ 13) * 11 ms = 22 ms +20 * 11 ms = 242 ms
NLivre 1 000 000FBMLivre 20CardLivre(code) 4 000OrdreI 100
19
Index primaire sur ISBN de Livre
OrdreMoyenI = 2/3 OrdreI = 66
FBLivre = 2/3 FBMLivre = 13
HauteurI = 1 + log OrdreMoyenI (CardLivre (ISBN) / FBLivre) = 4
TempsES (S=IP) = HauteurI *TempsESBloc = 44 ms
NLivre 1 000 000FBMLivre 20CardLivre (ISBN) 1 000 000OrdreI 100
20
Sélection par égalité dans un index arbre-B+ secondaire (S=IS)
...
...
...
...
.........
Hauteur -1
Feuilles à transférercontenant les références
aux blocs del'organisation primaire
... ...... ... ...Blocs de l'organisation
primaire à transférer...
21
Estimation de TempsES (S=IS)
Niveaux d ’index– (HauteurI -1) * TempsESBloc
Feuilles de l ’index SelT (CléIndex = Valeur)/ OrdreMoyenI
*TempsESBloc Blocs de l ’organisation primaire
– SelT (CléIndex = Valeur)*TempsESBloc
TempsES (S=IS sur clé candidate)– (HauteurI +1)*TempsESBloc
22
Estimation de HauteurI pour index secondaire
Hypothèses
– clés répétées
– FB = OrdreMoyen
HauteurI = logOrdreMoyenI (NT)
23
Index secondaire code de Livre (clé étrangère)
HauteurI = log66(1 000 000) = 4
SelLivre (code = Valeur )= 250 lignes
TempsES (S=IS) = 2 827ms
TempsES (S=ISa) = 2 827ms
TempsES (S=IP) = 242ms
NLivre 1 000 000FBMLivre 20CardLivre(code) 4 000OrdreI 100
24
Index secondaire sur code de Livre (clé étrangère)
HauteurI = log66(1 000 000) = 4
SelLivre (code = Valeur) = 50 000 lignes
TempsES (S=IS) = 558 371ms
TempsES (S=ISa) = 361 207ms
Pire que TempsES (BALLivre) = 50,01 secs !
NLivre 1 000 000FBMLivre 20CardLivre(code) 20OrdreI 100
25
Index secondaire sur ISBN de Livre (clé primaire)
HauteurI = log66(1 000 000) = 4
TempsES(S=IS sur clé candidate)=
(HauteurI +1)*TempsESBloc = 55 ms
~TempsES (S=IP) = HauteurI *TempsESBloc = 44 ms
NLivre 1 000 000FBMLivre 20CardLivre (ISBN) 1 000 000OrdreI 100
26
Sélection par intervalle dans un index arbre-B+ primaire (S>IP)
~ clé non unique
CléIndex [Valeur1..Valeur2]
TempsES (S>IP) =
– (HauteurI -1) * TempsESBloc +
SelT (CléIndex [Valeur1..Valeur2])/ FBT* TempsESBloc
27
Index primaire sur code de la table Livre (clé étrangère)
OrdreMoyenI = 2/3 OrdreI = 66
FBLivre = 2/3 FBMLivre = 13
HauteurI = 3
TempsES (S>IP) = 4 257 ms
NLivre 1 000 000FBMLivre 20CardLivre (code) 4 000SelLivre (code [Valeur1..Valeur2] ) 5 000OrdreI 100
28
TRI D'UNE TABLE (TRI)
Utilité– jointure par tri-fusion– élimination des doubles (DISTINCT)– opérations d ’agrégation (GROUP
BY)– résultats triés (ORDER BY)
Tri externe si M est petit– tri-fusion
29
Tri fusion externe
Étape tri– nombre de groupes = BT /M = 12 /3 = 4– Coût = 2 * ( BT /M * TempsPosDébut + BT * TempsTrans) = 104 ms
15 4 3
P os itionnem ent
Lecture
Créationde 12/3 =4 groupes
9 18 12
Lecture
P os itionnem ent
16 2 5
Lecture
P os itionnem ent
7 14 6
P os itionnem ent
Lecture
3 4 15
P os itionnem ent
E criture
9 12 18
E criture
P os itionnem ent
2 5 16
E criture
P os itionnem ent
6 7 14
P os itionnem ent
E criture
30
Étape fusion
Coût des passes de fusion
– = BT*(2*log M-1 (BT /M)-1) * TempsESBloc
– = 12*(2*log 2 (12 /3)-1) *11ms = 396 ms
3 4 15 9 12 18 2 5 16 6 7 14
3 4 9 12 15 18 2 5 6 7 14 16
2 3 4 5 6 7 9 12 14 15 16 18
Passe defusion #1
produit 4/2 = 2groupes
Passe defusion #2
produit 2/2 =1groupe
31
Coût total du tri-fusion externe
TempsES (TRI) =
– 2*(BT /M* TempsPosDébut + BT*TempsTrans) + BT*(2*log M-1 (BT /M)-1) * TempsESBloc
– = 104 ms + 396 ms = 500 ms
32
Tri de Livre
Allocation sérielle sans fragmentation interne
M = 50
TempsES (TRI) = 29,5 mins
NLivre 1 000 000FBMLivre 20BLivre 50 000
33
OPÉRATIONS DE JOINTURE
Cas de deux tables R|X|S Extension directe à d ’autres cas
– union, intersection, jointure externe, ...
34
Jointure par boucles imbriquées
Boucles imbriquées par lignes (BI)
TempsES (BI) = – BR * TempsESBloc+NR*(BS * TempsTrans+
TempsPosDébut) Meilleur cas (Tampon assez grand) :
– TempsES (BI) = TempsES (BALR) + TempsES (BALS) =
– (BR + BS) * TempsTrans + 2*TempsPosDébut
POUR chaque ligne lR de RPOUR chaque ligne lS de S
SI sur lR et lS est satisfait Produire la ligne concaténée à partir de lR et lS
FINSIFINPOUR
FINPOUR
35
BI : Livre |X| Catégorie
R=Livre et S=Catégorie– TempsES (BI) = 30,57 hrs
R = Catégorie et S = Livre
– TempsES (BI) = 56,57 hrs
Meilleur cas (sans relecture)
– TempsES (BI) = 50 120 ms
NLivre 1 000 000FBLivre 20BLivre 50 000
NCatégorie 4 000FBCatégorie 40BCatégorie 100
36
Boucles imbriquées par blocs (BIB)
TempsES (BIB) = – BR * TempsESBloc +
– BR * (BS * TempsTrans + TempsPosDébut)
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 FINPOURFINPOUR
FINPOUR
37
BIB: Livre |X| Catégorie
R=Livre et S=Catégorie– TempsES (BIB) = 100,83 mins
R = Catégorie et S = Livre
– TempsES (BIB) = 83,37 mins
Meilleur cas (sans relecture)
– TempsES (BIB) = 50 120 ms
NLivre 1 000 000FBLivre 20BLivre 50 000
NCatégorie 4 000FBCatégorie 40BCatégorie 100
38
Boucles imbriquées multi-blocs (BIM)
TempsES (BIM) = – BR * TempsTrans + BR/(M-2) * TempsPosDébut +
BR/(M-2) * (BS * TempsTrans + TempsPosDébut)
POUR chaque tranche de M-2 blocs de RPOUR chaque bloc bS de S POUR chaque ligne lR de la tranche
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 FINPOURFINPOUR
FINPOUR
39
BIM: Livre |X| Catégorie
M = 50 R=Livre et S=Catégorie
– TempsES (BIM) = 175,04secs
R = Catégorie et S = Livre
– TempsES (BIM) = 150,16secs
NLivre 1 000 000FBLivre 20BLivre 50 000
NCatégorie 4 000FBCatégorie 40BCatégorie 100
40
Jointure par boucles imbriquées avec index sur la table interne (BII)
TempsES (BII) = – BR * TempsESBloc +
– NR * TempsES (Sélection par index)
POUR chaque ligne lR de RPOUR chaque ligne lS de S satisfaisant (sélection en utilisant un index) Produire la ligne concaténée à partir de lR et lSFINPOUR
FINPOUR
41
BII: Livre |X| Catégorie
Cas des index primaires R=Livre et S=Catégorie
TempsES (BII) = 9,32 hrs
R = Catégorie et S = Livre
TempsES (BII) = 16,15 mins
NLivre 1 000 000FBLivre 20BLivre 50 000
NCatégorie 4 000FBCatégorie 40BCatégorie 100
TempsES(S=IPCatégorie pour l'index primaire sur code) 33ms
TempsES(S=IPLivre pour l'index primaire sur code) 242ms
42
Jointure par tri-fusion (JTF)
Trier R et S par tri externe et réécrire dans des fichiers temporairesLire groupe de lignes GR(cR) de R pour la première valeur cR de clé de jointureLire groupe de lignes GS(cS) de S pour la première valeur cS de clé de jointureTANT QUE il reste des lignes de R et S à traiter
SI cR = cS
Produire les lignes concaténées pour chacune des combinaisons delignes de 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 SFINSI
FINSIFINSI
FIN TANT QUE
TempsES (JTF) = TempsES (TRIR) + TempsES (TRIS) + 2*(BR + Bs) * TempsESBloc
43
JTF: Livre |X| Catégorie
M = 50TempsES (JTF) = TempsES (TRILivre) + TempsES
(TRICatégorie) + 2*(BLivre + BCatégorie) * TempsESBloc = 2 873 540 ms
– >>TempsES (BIM) = 150 160 ms (R = Catégorie)
NLivre 1 000 000FBMLivre 20BLivre 50,000TempsES(TRILivre) 1 770 000 ms
NCatégorie 4 000FBMCatégorie 40BCatégorie 100TempsES(TRICatégorie) 1 340 ms
44
Si 100 fois plus de Catégories
M = 50– TempsES (JTF) = 3 444 000 ms
– < TempsES (BIM) = 10 464 180 ms
M = 10– TempsES (JTF) = 6 180 000 ms
– << TempsES (BIM) = 62 535 000 ms
NLivre 1 000 000FBMLivre 20BLivre 50,000TempsES(TRILivre) 1 770 000 ms
NCatégorie 400 000FBMCatégorie 40BCatégorie 10 000TempsES(TRICatégorie) 350 000 ms
45
Optimisation
Chercher le meilleur plan d ’exécution?– coût excessif
Solution approchée à un coût raisonnable– Générer les alternatives
heuristiques
– Choisir la meilleure estimation approximative du coût
46
Plans d'exécutions équivalents
Plusieurs arbres algébriques équivalents
etc.
Livre Catégorie
ISBN = 1-111-1111-1
titre, descripteur
Livre
Catégorie ISBN = 1-111-1111-1
titre , descrip teur
47
Règles d ’équivalences de l ’algèbre relationnelle
Eclatement d'une sélection conjonctive (SE)– e1 ET e2 (T) = e1 ( e2 (T))
Commutativité de la sélection (SC) e1 ( e2 (T)) = e2 ( e1 (T))
Elimination des projections en cascades (PE) liste1 ( liste2 (T)) = liste1 (T)
Commutativité de la jointure (JC)– T1 |X| T2 = T2 |X| T1
Associativité de la jointure (JA)– T1 |X| (T2 |X| T3) = (T1 |X| T2) |X| T3
Etc …
48
Plusieurs plans d ’exécution pour un arbre algébrique
Pour chaque opération logique– plusieurs choix d ’opérations physiques
– etc.
Livre Catégorie
ISBN = 1-111-1111-1
titre , descrip teur
(Jo inture partri-fusion)
(Balayage)
(Balayage)
L ivre Catégorie
ISBN = 1-111-1111-1
titre , descrip teur
(Jo inture parBIM )
(Balayage)
(Balayage)
L ivre Catégorie
ISBN = 1-111-1111-1
titre , descrip teur
(Jo inture parhachage)
(Balayage)
(Balayage)
49
Mise en œuvre du plan d'exécution
Matérialisation des tables intermédiaires
Pipeline
50
MISE EN ŒUVRE PAR MATÉRIALISATION
Livre
Catégorie
annéeParution = 2000
titre , descrip teur (Balayage)
(Boucle imbriquée parindex secondaire sur
code de la tableinterne Catégorie)
(Sélection par indexsecondaire surannéeParution )
51
MISE EN ŒUVRE PAR PIPELINE
Livre
Catégorie
annéeParution = 2000
titre , descrip teur (Balayage)
(Boucle imbriquée parindex secondaire sur
code de la tableinterne Catégorie)
(Sélection par indexsecondaire surannéeParution)
52
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– arbres biaisés à gauche seulement– les jointures les plus restrictives en premier– jointures supportées par index en premier
53
Heuristique : arbres biaisés à gauche seulement
Jointure de n tables– (2*(n-1))!/(n-1)! ordres différents pour n
tables– n! biaisés à gauche
T 1 T 2
T 3
T 4
T 1 T 2 T 3 T 4 T 3 T 4
T 2
T 1
Arbre biaisé à gauche Arbre biaisé à droiteArbre équilibré
54
Optimisation par coût
Minimiser le coût Stratégies
– programmation dynamique– amélioration itérative– recuit simulé– algorithme génétique
55
ESTIMATION DU COÛT D'UN PLAN D'EXÉCUTION
Livre Catégorie
(Jointure par tri-fusionTempsES = 2 873 540 ms)
(BalayageTempsES = 92 314 ms)
ISBN = 1-111-1111-1
titre , descrip teur
(Ecriture du résultatTempsES = 846 164 ms)
(Ecriture du résultatTempsES = 11 ms)
(BalayageTempsES = 11 ms)
Coût total =3 812 040ms
TempsES(Plan avec pipeline) = TempsES (JTFLivre|X|Catégorie) = 2 873 540 ms
56
Autre exemple
L ivre
C atégorie
titre , descrip teur
(Boucle im briquée parindex secondaire sur
code de la tab leinterne C atégorie
Tem psES = 44m s)
IS B N = 1-111-1111-1 (Sélection par indexsecondaire sur ISBNTem psES = 55m s)
(Ecriture du résu lta tTem psES = 11m s)
(Ecriture du résu lta tTem psES = 11 m s)
(BalayageTem psES = 11 m s)
C oût to ta l =132m s
TempsES(Plan avec pipeline) =
TempsES(S=IS pour index sur ISBN) +
N ISBN=1(Livre) * TempsES(S=IS sur code de Catégorie)
= 55 ms + 33 ms = 88 ms
57
ESTIMATION DE LA TAILLE DU RÉSULTAT D'UNE OPÉRATION
Taille d'une sélection SelT (Expression de sélection)/ FBMT blocs
Taille d'une projection– N C1,C2,…,Cn(T) = (1- colonnes Ci de la projection (1-
FacteurSélectivitéT (Ci)))*NT
Taille d'une jointure naturelle– NR |X| S = NR * NS /
maximum(CardR(cléJointure), CardS(cléJointure))
58
Contrôle du processus d'optimisation
Cas Oracle– outils
EXPLAIN PLANSQL TraceSQL Analyse (Enterprise Manager Tuning Pack)
Oracle EXPERT
59
Indices (hints)
SELECT /*+ RULE*/ nomFROM ClientWHERE noClient = 10 ;
SELECT /*+ INDEX(EMPLOYÉ INDEX_SEXE)*/ nom, adresseFROM EMPLOYÉ WHERE SEXE = ‘F’
60
Paramètre OPTIMIZER_MODE
COST (statistique): minimise le coût estimé– besoin de statistiques (ANALYSE)– mieux mais plus cher– ALL_ROWS
minimise le temps total (plutôt MERGE JOIN)
– FIRST_ROWS minimise temps réponse (plutôt NESTED LOOPS)
RULE (heuristique)– appelé à disparaître ? (Il semble que oui)
61
Changement du mode pour une session
ALTER SESSION SET OPTIMIZER_GOAL =– RULE | ALL_ROWS | FIRST_ROWS | CHOOSE
62
Exemple d ’utilisation de EXPLAIN PLAN
SQL> start utlxplan.sql Pour créer la table plan_tableTable created....SQL> run 1 explain plan 2 set statement_id = 'com' 3 for select * from commandes, lignes_de_commande 4* where no_commande = commande_no_commande
Explained.
63
Suite
SQL> run 1 SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options 2 ||' '||object_name 3 ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan" 4 FROM plan_table 5 START WITH id = 0 AND statement_id = 'com' 6* CONNECT BY PRIOR id = parent_id AND statement_id ='com'
Query Plan---------------------------------------------SELECT STATEMENT Cost = NESTED LOOPS TABLE ACCESS FULL LIGNES_DE_COMMANDE TABLE ACCESS BY ROWID COMMANDES INDEX UNIQUE SCAN COMMANDE_PK
64
top related