OPTIMISATION

1
OPTIMISATION
Institut National des Sciences Appliquées
Rouen Département Architecture des Systèmes
dInformation
2
SGBD RELATIONNEL

• LE CHOIX DU CHEMIN D'ACCES AUX DONNEES EST FAIT
  PAR LE SYSTEME.
• QUESTIONS SIMPLES
• CHOIX PEU SIGNIFICATIF
• FAIBLE COUT
• QUESTIONS COMPLEXES
• TEMPS DE REPONSE DEPENDANT DU BON CHOIX
• COUT ELEVE POUR REALISER LE BON CHOIX
• FAIRE LE BON CHOIX
• TEMPS D'EXECUTION QUESTION OPTIMISEE
• TEMPS D'OPTIMISATION
• gt INFERIEUR A QUESTION NON OPTIMISEE

3
TRAITEMENT D'UNE REQUETE

• ANALYSE DE LA QUESTION 
• - SYNTAXIQUE
• - SEMANTIQUE
•  
• MODIFICATION DE QUESTION 
• - LOGIQUE
• - FACITITES UTILISATEURS (VUES, INTEGRITE,
  DROITS)
• OPTIMISATION (PLAN D'EXECUTION)
• - ALGEBRIQUE
• - MODIFICATION DE QUESTION
• - EVALUATION DE PLAN 

4
TRAITEMENT D'UNE REQUETE (2)
5
OBJECTIFS

• TRADUCTION DES REQUETES UTILISATEURS (EXPRIMEES
  DANS UN LANGAGE ASSERTIONNEL) DANS UNE EXPRESSION
  OPTIMISEE DU PLAN D'EXECUTION
• TROIS TECHNIQUES DE BASE
• RESTRUCTURATION ALGEBRIQUE
• --gt CLASSIQUE
• MODIFICATION DE QUESTIONS
• --gt INGRES
• EVALUATION DU COUT
• --gt SYSTEM-R

6
ACTEURS

• DEUX INTERVENANTS
• L'EVALUATEUR TRADUIRE LA REQUETE
  UTILISATEUR EN UNE SUITE D'OPERATIONS
  ELEMENTAIRES DE L'ALGEBRE RELATIONNELLE.
• L'OPTIMISEUR ASSOCIER A CETTE SUITE UN PLAN
  D'EXECUTION AFIN QUE LE COUT D'EXECUTION DE
  LA REQUETE SOIT MINIMUM. 
• MOMENT DE L'OPTIMISATION
• COMPILATION
• - Optimisation a priori
• - Stockage dans une bibliothèque
• INTERPRETE
• - Optimisation a chaque requête

7
1. ANALYSE DE LA QUESTION

• ANALYSE SYNTAXIQUE
• A/ VERIFIER LA COHERENCE DE LA QUESTION PAR
  RAPPORT AU SCHEMA DE LA BD
• gt EXISTENCE DES RELATIONS, ATTRIBUTS, ... 
•  
• B/ MISE SOUS FORME STANDARD DE LA REQUETE SOUS
  FORME NORMALE CONJONCTIVE (OU DISJONCTIVE)
• FNC (A1 OR A2 OR ... AN) AND
• (B1 OR B2 OR ... BN)
• FND (A1 AND A2 AND ... AN) OR
• (B1 AND B2 AND ... BN)

8
1. ANALYSE DE LA QUESTION (2)

• ANALYSE SEMANTIQUE
• VERIFIER LA CORRECTION DE LA QUESTION
• GRAPHE DE CONNECTION DES RELATIONS (PAS DE
  RELATIONS ISOLEES)
• Un sommet pour chaque relation
• Une jointure est représentée par un arc
• Une sélection par une boucle sur la relation
• GRAPHE DE CONNECTION DES ATTRIBUTS NORMALISES
  (PAS DE CRITERES CONTRADICTOIRES)
• Un sommet est associe a chaque référence
  d'attribut ou de constante,
• Une jointure est représentée par un arc entre
  les attributs participants,
• Une sélection par un arc entre un attribut et une
  constante

9
VERIFICATION DE LA CORRECTION DE LA QUESTION

• UNE REQUETE EST INCORRECTE SI
• 1. SON GRAPHE DE CONNECTION DES RELATIONS N'EST
  PAS CONNEXE
• gt IL EXISTE AU MOINS UNE RELATION ISOLEE NON
  RATTACHEE AUX AUTRES
• OU BIEN
• 2. SON GRAPHE DE CONNECTION DES ATTRIBUTS
  PRESENTE UN CYCLE DONT LA SOMME DES VALUATIONS
  EST NEGATIVE
• gt LA QUESTION EST CONTRADICTOIRE

10
VERIFICATION DE LA CORRECTION D'UNE QUESTION

• BUVEURS (NB, NOM, PRENOM, VILLE, REGION)
• VINS (NV, CRU, REGION, MILL, DEGRE)
• ABUS (NV, NB, DATE, QUANTITE)
• SELECT
• FROM VINS V, ABUS A
• WHERE V.MILL 1978 AND
• A.DATE lt 1976
• LES RELATIONS ABUS ET VINS NE SONT PAS RELIEES
  DANS LE GRAPHE DES RELATIONS
• gt CETTE SELECTION N'EST PAS SATISFAISABLE

11
VERIFICATION (2)
12
2. MODIFICATION DE QUESTIONS

•  DEFINITION
• UNE REQUETE PEUT ETRE TRANSFORMEE EN PLUSIEURS
  ARBRES ALGEBRIQUES (IL Y PLUSIEURS FACONS DE
  DEFINIR UNE REQUETE EN SQL ET EN ALGEBRE)
• BUT
• 1. METTRE LA REQUETE SOUS UNE FORME STANDARD
• 2. SIMPLIFIER EN ELIMINANT LES REDONDANCES
• 3. CONSTRUIRE UNE EXPRESSION EQUIVALENTE PLUS
  PERFORMANTE A EVALUER

13
2. MODIFICATION DE QUESTIONS (2)

• PRINCIPE QUESTIONS EQUIVALENTES
• DEUX QUESTIONS SONT EQUIVALENTES SI ELLES DONNENT
  LE MEME RESULTAT POUR TOUTE EXTENSION DE LA BD
• 2 MOYENS  
• 1. CRITERES PREDICATS DE LA LOGIQUE DU
  PREMIER ORDRE
• gt LOGIQUE
• 2. UTILISATION DES FACILITES UTILISATEURS

14
FACILITES UTILISATEURS (Exemple CONTRAINTES
D'INTEGRITE)

• DEPART
• UNE QUESTIONAVEC QUALIFICATION
• UN ENSEMBLE DE CONTRAINTES D'INTEGRITE I1, I2,
  ... In
• PRINCIPE
• NON CONTRADICTION QUESTION /CONTRAINTE
  D'INTÉGRITÉ (DETECTION IMMEDIATE)
• EXEMPLE
• SELECT
• FROM VINS
• WHERE CRU 'JURANCON' AND DEGRE lt 10

15
FACILITES UTILISATEURS (2)

• CONTRAINTE D'INTEGRITE
• SUR VINS
• CRU 'JURANCON' gt DEGRE gt 12
• REQUETE MODIFIEE
• SELECT
• FROM VINS
• WHERE CRU 'JURANCON'
• AND DEGRE lt 10 AND DEGRE gt 12
• gt REPONSE VIDE

16
3. OPTIMISATION DE QUESTION

• LORSQUE LA REQUETE EST CORRECTE, LE BUT EST
  D'ASSOCIER UN PLAN D'EXECUTION DONT LE COUT EST
  MINIMUM ET DONT LA DETERMINATION NE NECESSITE PAS
  TROP DE TEMPS.
• 3 METHODES
• 1. Restructuration algébrique
• 2. Décomposition de questions
• 3. Evaluation de plans
• REMARQUE CES METHODES NE SONT PAS EXCLUSIVES

17
ARBRE ALGEBRIQUE

• ARBRE ayant pour NOEUDS les OPERATIONS de
  L'ALGEBRE RELATIONNELLE, pour FEUILLE des
  RELATIONS et dont les ARCS représentent les FLUX
  DES DONNEES
• Remarque
• Plusieurs arbres différents permettent de
  modéliser la même requête

18
Restructuration algébrique

• "DONNER LES NOMS ET LES PRENOMS DES BUVEURS
  HABITANT Paris QUI ONT COMMANDE DU Mâcon 1977
  AVANT LE 15/05/88"

19
Restructuration algébrique (2)
20
Restructuration algébrique (3)

• PRINCIPE
• Suivant L'ORDRE DES OPERATEURS ALGEBRIQUES dans
  un arbre, LE COUT D'EXECUTION est DIFFERENT
• POURQUOI ? 
• 1. LE COUT DES OPERATEURS VARIE EN FONCTION DU
  VOLUME DES DONNEES QU'ILS TRAITENT (PLUS LE
  NOMBRE DE TUPLE DES RELATIONS TRAITEES EST PETIT,
  PLUS LES COUTS CPU ET D'E/S SONT MINIMISES)
• 2. CERTAINS OPERATEURS DIMINUENT LE VOLUME DES
  DONNEES
• IL FAUT DONC
• POUR CHAQUE OPERATEUR, EVALUER LA TAILLE DE LA
  RELATION RESULTAT, APRES APPLICATION DES
  OPERATEURS ALGEBRIQUES

21
FACTEURS

• LA TAILLE D'UNE RELATION R EST EGALE AU NOMBRE DE
  TUPLES DE R QUE MULTIPLE LA LONGUEUR DE SES
  TUPLES
• TAILLE (R) Nb_tuples (R) Lg (R)
•  
• 1. SELECTION a (R)
• TAILLE (a(R)) p(a) TAILLE (R)
• p (a) probabilité que a soit vérifiée 
• gt PUISSANT REDUCTEUR
• 2. PROJECTION P x (R)
• TAILLE (P x (R)) L(x) TAILLE (R)
• Avec L(x) Lg (x) / Lg (R)
• Sans élimination de doubles
• gt PUISSANT REDUCTEUR

22
FACTEURS (2)

• 3. JOINTURE R1 R2
• J TAILLE (R1) TAILLE (R2)
• Avec J dépends de chaque jointure
• J 0 Si aucun tuple ne joint
• J 1 Si produit cartésien 
• Nb_tuple (R1) Nb_tuple (R2)
• (Lg (R1) Lg (R2))
• gt PEUT AUGMENTER LA TAILLE DE LA RELATION
  RESULTAT

23
HEURISTIQUE

• APPLIQUER D'ABORD TOUTES LES
• SELECTIONS ET PROJECTIONS
• MOYEN
• UTILISATION DES REGLES DE
• RESTRUCTURATION D'OPERATEURS
• ALGEBRIQUES
• 1. PROPRIETES DES JOINTURES
• (1) COMMUTATIVITE
• (2) ASSOCIATIVITE 
•  
• 2. PROPRIETES DU PRODUIT CARTESIEN
• (3) COMMUTATIVITE
• (4) ASSOCIATIVITE

24
HEURISTIQUE (2)

• 3. PROPRIETES DES s  
• (5) CONCATENATION DES s
• (6) CONCATENATION DES P
• 4. COMMUTATION DES s AVEC
• (7) LA JOINTURE
• (8) LE PRODUIT CARTESIEN
• (9) LA PROJECTION
• (10) L'UNION
• (11) LA DIFFERENCE
•  
• 5. COMMUTATION DES P AVEC
• (12) LA JOINTURE
• (13) LE PRODUIT CARTESIEN
• (14) L'UNION

25
Graphiques
26
Graphiques (2)
27
Graphiques (3)
28
Graphiques (4)
29
Graphiques (5)
30
DECOMPOSITION DE QUESTION

• OUTIL GRAPHE DES RELATIONS
• PRINCIPE
• LE DETACHEMENT DE QUESTIONS TRANSFORMATION
  CONSISTANT A DIVISER LE GRAPHE DES VARIABLES EN
  DEUX COMPOSANTES CONNEXES PAR DEDOUBLEMENT D'UN
  NOEUD.
•  
• DETACHEMENT POSSIBLES
• SELECTIONS, SEMI-JOINTURES

31
DECOMPOSITION DE QUESTION (2)

• SELECTIONS
• TOUTE SELECTION PEUT ETRE DETACHEE
•  
• EXEMPLE
• SELECT Ai
• FROM R1, R2, ... Rm
• WHERE Rj. Ai Rk. Ai
• and R1. An VALEUR
• gt
• INSERT INTO T
• SELECT
• FROM R1
• WHERE R1. An VALEUR
•  
• SELECT Ai FROM T, R2, ... Rm
• WHERE Rj. Ai Rk. Ai

32
DECOMPOSITION DE QUESTION (3)

• REQUETE  
• "Buveurs habitant Macon ayant bu du Macon après
  le 1/1/83"
•  
• Q1 INSERT INTO V' SELECT NV
• FROM VINS
• WHERE CRU 'MACON'
• Q2 INSERT INTO A' SELECT NB
• FROM ABUS
• WHERE DATE gt "1/1/83"
• Q3 INSERT INTO T SELECT NB FROM A', V'
• WHERE A'.NV V'.NV
• Q4 INSERT INTO B' SELECT NOM, PRENOM, NB
• FROM BUVEURS
• WHERE VILLE 'MACON
• Q5 SELECT NOM, PRENOM
• FROM B', T
• WHERE BUVEUR.NB T'.NB

33
DECOMPOSITION DE QUESTION (4)

• SEMI-JOINTURES
• LA SEMI-JOINTURE DE R PAR S, NOTEE R S, EST
  LA JOINTURE DE R PAR S, PROJETEE SUR LES
  ATTRIBUTS DE R
• R S P r (R S)
• TOUTE SEMI-JOINTURE PEUT ETRE DETACHEE

34
DECOMPOSITION DE QUESTION (5)

• EXEMPLE
• SELECT R.B
• FROM R,S
• WHERE R.A S.A
• and PRED (R)
• gt
• INSERT INTO T
• SELECT R.
•   FROM R, S
• WHERE R.A S.A
• SELECT T.B
• FROM T
• WHERE PRED (T)

35
EVALUATION DE PLANS

• METHODES D'EVALUATION DE COMPOSANTS DE REQUETE DE
  COMPLEXITE VARIABLE AVEC UN PRINCIPE DE BASE
  L'EVALUATION DE LA TAILLE DES RESULTATS
• PROBLEME
• POUR CHAQUE RELATION, CHOISIR LE MEILLEUR CHEMIN
  D'ACCES ET LE MEILLEUR ALGORITHME DE JOINTURE

36
EVALUATION DE PLANS (2)

• ELEMENTS DISPONIBLES
• 1. METHODES D'ACCES
• - SEQUENTIEL
• - INDEXE
• - PLACANT
• - NON PLACANT
•  
• 2. ALGORITHMES DE JOINTURE
• - BOUCLES IMBRIQUEES
• - TRI-FUSION
• - HACHAGE
•  
• 3. STATISTIQUES SUR LES RELATIONS ET LES INDEX

37
EVALUATION DE PLANS (3)

•  METHODE D'ACCES
•  
• CAS SANS INDEX
• - FILTRAGE EN SEQUENTIEL DU FICHIER
• - TRIER LA RELATION (GARDEE TRIEE)
  ASCENDANTE, DESCENDANTE, DICHOTOMIQUE
• Ex "RANGE QUERIES"
• CAS AVEC INDEX
• - UNI-DIMENSIONNEL (ISAM, Arbre-B)
• - MULTI-DIMENSIONNEL (Grid File, Arbres de
  Prédicats)

38
STRUCTURE DE STOCKAGE

• DONNEES STOCKEES DANS DES SEGMENTS
• RELATIONS STOCKEES ENTIEREMENT DANS UN SEGMENT.
• PLUSIEURS RELATIONS PAR SEGMENT.
• SEGMENTS TYPES TEMPORAIRES, PERMANENTS.
• ALLOCATIONS REGROUPEES DE PAGES.

39
CHEMINS D'ACCES

• INDEX TRIES MONO OU MULTI-ATTRIBUT.
• INDEX BASES SUR LES ARBRES-B.
• EXISTANCE DES TIDs Nro PAGE, ADR.
  Indirecte/Page
• POSSIBILITE D'AVOIR DES INDEX PLACANTS 2 TYPES
  DE SEGMENTS
• - SEQUENTIELS
• AUCUN ORDRE LOGIQUE DES TUPLES
• - CLUSTERS
• LES TUPLES SONT INSERES DANS L'ORDRE D'UN
  INDEX (Triés ou pré- jointure)

40
(No Transcript)
41
METHODE

• GENERATION DE PLUSIEURS PLANS
• COUT COMBINATOIRE gt
• TROP COUTEUX.
• SOLUTION
• ELIMINER DES PLANS A PRIORI-HEURISTIQUES-
• OPTIMUM LOCAL
• COMPILATION DU PLAN D'EXECUTION OBTENU
• INSERER DANS UN CATALOGUE DE PLANS.
• PROBLEME
• PLAN STATIQUE. NE TIENT PAS COMPTE DU
  CHANGEMENT DE TAILLES DES DONNEES.
• SI LA METHODE D'ACCES CHANGE
• EN - RECOMPILER LA QUESTION
• EN ON NE PEUT PAS EN PROFITER

42
CHOIX DES ALGORITHMES(JOINTURE)

• PAR BOUCLE IMBRIQUEE
• Pour CHAQUE PAGE DE R1
• Pour CHAQUE PAGE DE R2 
• JOINDRE (P.R1, P.R2)
• Fpour
• Fpour
• COUT Í R1 Í Í R2 Í
• PAR TRI-FUSION 
• TRIER (R1) TRIER (R2)
• FUSIONNER (R1, R2) 
• COUT 2 Í R1 Í LOG Í R1 Í Í R2 Í LOG Í R2
  Í Í R1 R2

43
CHOIX DES ALGORITHMES (2)

• JOINTURE (AVEC INDEX) SUR R1 
• POUR CHAQUE PAGE DE R2
• LIRE (R2) 
• POUR CHAQUE TUPLE LU 
• ACCEDER A R1 EN INDEX
• JOINDRE SI SUCCES
• Fpour
• Fpour
• SUR R1 ET R2 
• -gt FUSIONNER LES INDEX

44
CHOIX DES ALGORITHMES (3)

• PAR HACHAGE
• HACHER (R1) ( PETITE RELATION)
• POUR CHAQUE PAGE DE R2
• LIRE (R2) 
• POUR CHAQUE TUPLE LU 
• ACCEDER AU FICHIER ALEATOIRE
• JOINDRE SI SUCCES 
• Fpour
• Fpour  
• COUT Í R1 Í A Í R1 Í
• Í R2 Í A Í R2 Í
• PAR SEMI-JOINTURE 
• EFFECTUER LES SEMI-JOINTURES
• R1 R2 ET R2 R1 AVANT JOINTURE 

45
CHOIX DES ALGORITHMES (4)

• EXECUTION PARALLELE
• (EVITER L'ACCES REPETITIF
• AUX MEMES DONNEES)
•   
• - EVALUATION DE TOUTES LES SELECTIONS SUR UNE
  RELATION EN UNE SEULE PASSE
• - JOINTURE EN PARALLELE
• A.NV V.NV ET A.NB B.NB
• LA RELATION A N'EST LUE QU'UNE SEULE FOIS
• - PIPELINE D'OPERATIONS ENCHAINER SELECTION ET
  PROJECTION SUR UNE MEME RELATION

46
CHOIX DU PLAN D'EXECUTION

• POUR CHAQUE SOLUTION UN COUT EST CALCULE
• COUT NOMBRE D'ACCES PAGES
• W Nombre d'appels internes
• (W IMPORTANCE DU TEMPS CPU PAR RAPPORT AUX E/S
  OU LE TEMPS DES E/S EST FONCTION DU NOMBRE DE
  PAGE ACCEDEES)

47
CHOIX DU PLAN D'EXECUTION (2)

• STATISTIQUES
• NBTUP Nombre de tuples
• NBPAG Nombre de pages
• NBCLE Nombre de clés
• NCARD (R) Cardinalité
• TCARD (R) Nombre de pages
• MIN (attribut) Valeur minimum d'un
  attribut numérique
• MAX (attribut) Valeur maximum d'un
  attribut numérique
• ICARD (I) Nombre clés distinctes dans index
• NBINDX (I) Nombre pages de l'index
• CLUSTER (I) Index plaçant ou non
• RISCARD F(PRED) NBTUP

48
CALCUL DU FACTEUR DE SELECTIVITE

• ATTRIBUT VALEUR
• ATTRIBUT gt VAL
•  
• VAL 1 lt ATTRIBUT lt VAL 2
• P1 OR P2 F F (P1) F (P2)
• F (P1) F (P2)
• P1 AND P2 F F (P1) F (P2)
• NOT P F 1 - F (P)

49
CALCUL DU COUT TOTAL D'UNE SELECTION

• ACCES SUR CLE PRIMAIRE
• 1 1 W
• ACCES SUR CLE SECONDAIRE VIA UN PREDICAT P
• - INDEX CLUSTERISE
• F (P) (NBINDX TCARD)
• W RSICARD
• - INDEX NON CLUSTERISE
• F (PRED) (NBINDX NCARD)
• W RSICARD
• ACCES SEQUENTIEL (SCAN)
• TCARD W RSICARD
• TRI EVENTUEL

50
EXEMPLE
51
CALCUL DE RISCARD

• RSICARD
• F(P) (DEGRE gt 12)
• F (CRU "MORGON")
• F (MILLESIME 80)
• F (DEGRE gt 12) (2000 - 1400) /
• (2000 - 1000) 0,6
• F (CRU "MORGON") 1 / 4 0,25
• F (MILLESIME 80) 1 / 4 0,25
• F (P) 0,6 x 0,25 x 0,25 0,0375
• RSICARD 0,0375 1000 _at_ 37
• 
  

52
EVALUATION

• INDEX DEGRE -gt NBCLE 50
• NBINDX 2
• COUT (IND.DEGRE)
• F (IND.DEGRE) (2 1000) W 37
• 0,6 1 002 W 37 601 37 W
• FACTEUR W W 0,2
• 1 E / S _at_ 20 ms
• 1 Appel _at_ 4 ms
• COUT (IND.DEGRE) 608
• INDEX CRU -gt NBCLE 4
• NBINDX 1
• COUT (IND.CRU)
• F (IND.CRU) (1 1000) W 37
• 0,25 1 001 37 W
• COUT (IND.CRU) 257

53
CONCLUSION

• LES PROBLEMES A RESOUDRE
• 1. TROUVER DES HEURISTIQUES RAISONNABLES
  PERMETTANT DE DIMINUER LA COMBINATOIRE
• 2. EVALUER LES COUTS ET CHOISIR LE PLUS FAIBLE
  PROBLEME ESTIMATION DE LA TAILLE
• 3. PROPAGER LES CLES 

54
CONCLUSION (2)

• L'APPROCHE COMPILATION
• Effet considérable sur les performances.
• Evaluation et chemins d'accès pris en compte
  lors de la compilation.
• Si le contexte change le module compile est
  invalide et recompilé lors de son appel
• (exemple destruction d'un index)

55
CONCLUSION (3)

• Transactions prédéfinies
• Modules déjà compiles.
• Lévaluation n'est faite qu'une fois.
• Transactions interactives
• On n'exécute qu'une seule fois.
• Résultat dépend de la complexité de la
  question. si la question est complexe on gagne
  a réaliser la compilation.