Architecture physique dune base de donnes ORACLE SGA

Architecture physique d’une base de données (ORACLE) SGA Shared Pool Dictionary Cache Buffer Cache Redo Cache Library Cache CKPT SMON Control files PMON data files DBWR LGWR Redo log files Driss HAZZAF Archive init file 1

Structure physique Base (ORACLE) Objets Logiques Objets Physiques TABLESPACE DATAFILE SEGMENT DB BLOCK EXTENT OS BLOCK Driss HAZZAF 2

Paramètres création table (ORACLE) INITIAL : La taille du premier extent de la table NEXT : La taille du deuxième extent de la table MINEXTENTS : Le nombre d'extents de la table à sa création MAXEXTENTS : Le nombre maximal d'extents de la table. PCTINCREASE : Le taux d'incrémentation en pourcentage de l'extent N par rapport à l'extent N- 1 ( Pour N > 2). Si pctincrease est égal à zéro, alors tous les extents à partir du troisième sont égaux au deuxième extent. Driss HAZZAF 3

Paramètres création table (suite) Exemple: CREATE TABLE FOURNISSEUR ( NO_FOURNISSEUR NUMBER(10) NOT NULL, NOM VARCHAR 2(100), PRENOM VARCHAR 2(100), MAIL VARCHAR 2(100), TELEPHONE VARCHAR 2(30), CONSTRAINT FOURNISSEUR_PK PRIMARY KEY(NO_FOURNISSEUR) ) PCTFREE 30 PCTUSED 70 STORAGE (INITIAL 2 K NEXT 1 K MINEXTENTS 4 MAXEXTENTS 6 PCTINCREASE 50); Cette table est créée avec un espace de 6. 75 k, et sa taille max sera de 15. 1875 Driss HAZZAF 4

Paramètres DB Block (ORACLE) • Pct. Free : c'est le pourcentage que Oracle laisse de libre dans un bloc de données pour les mises à jour. Dès qu'il reste que ce pourcentage de libre dans le bloc de données, Oracle bloque les insertion dans ce bloc et il ne permet que les "UPDATE" et "DELETE". • Pct. Used : C'est le pourcentage en dessous du quel Oracle autorise à nouveau les insertions dans un bloc pour le quel les insertions étaient bloquées. Driss HAZZAF 5

Structure Index B*Tree Un arbre B d’ordre K est un arbre équilibré : – Chaque nœud est une page contenant au mois K et au plus 2 K articles. – Dans un nœud, l’ordre se fait par rapport à la clé. Les opérations possible sur arbre B sont : – Recherche – Insertion – Suppression Driss HAZZAF 6

Index B*Tree INSERTION L'insertion d’une clé dans un arbre d’ordre k nécessite les opérations suivantes : – Chercher la feuille où la nouvelle clé devrait être insérée. – Insérer la clé dans la feuille. – Si le noeud possède plus de 2 k clés, on le transforme en deux nœuds, chacun possédant k clés, et on remonte la clé du milieu dans le nœud père. – Le nœud père peut alors posséder trop de fils, le procédé continue jusqu'à ce que l'on atteigne la racine. – Si la racine doit être divisée, on remonte la clé du milieu dans une nouvelle racine, qui aura alors pour fils les deux nœuds crées à partir de l'ancienne racine. Driss HAZZAF 7

Exemple Arbre B+ On considère un index de type B+ d’ordre 2 sur la colonne durée de la table Projet. Construire l’arbre correspondant à l’insertion des durées suivantes dans l’ordre indiqué ( ne mettre dans l’arbre que les clés ) : (1, 9, 5, 11, 43, 61, 13, 23, 89, 41, 119, 25, 27, 29, 31) Driss HAZZAF 8

Solution Driss HAZZAF 9

Optimisation requêtes SQL • Le travail d'optimisation d'une requête SQL commence par la génération du plan d'exécution "EXPLAIN PLAN", ceci permet de connaître les différentes étapes de l’exécution de la requête (si le système utilise des indexes ou passe par un FULL SCAN de la table. . . ) Pour générer le Plan d'exécution d'une table sous ORACLE il faut : – Créer la table PLAN_TABLE dans le schéma utilisateur si elle n'existe pas : Pour créer cette table il faut exécuter le script UTLXPLAN. SQL, il existe dans "$ORACLE_HOME/RDBMS/ADMIN" sous UNIX ou "%ORACLE_HOME%/RDBMS/ADMIN" sous Windows. – Générer le plan d'exécution de la requête SQL avec la commande EXPLAIN PLAN : EXPLAIN PLAN FOR REQUETE_SQL; – Afficher le plan d'exécution de la requête SQL à partir de la table PLAN_TABLE. • Dans un deuxième temps, il faut interpréter le plan d’exécution et revoir le code SQL de la requête et les indexes des tables s’il le faut. Driss HAZZAF 10

Optimisation requêtes SQL (Suite) EXPLAIN PLAN FOR SELECT P. LIBELLE AS Projet, A. NUMERO_EMPLOYE AS Employé FROM PROJET P INNER JOIN AFFECTATION_PROJET A ON P. PROJET_CODE = A. PROJET_CODE WHERE A. PRIORITE = 1; Le plan d’exécution de la requête est le suivant : 0 SELECT STATEMENT 1 NESTED LOOPS 2 TABLE ACCESS AFFECTATION_PROJET FULL 3 TABLE ACCESS PROJET BY INDEX ROWID 4 INDEX PROJET_PK UNIQUE SCAN Le système fait une boucle imbriquée pour la jointure entre les tables AFFECTATION_PROJET et PROJET. Il commence par une lecture séquentiel de la table AFFECTATION_PROJET pour cher les lignes ayant comme priorité 1. Pour chaque ligne, il extrait la valeur de PROJET_CODE et interroge l’index PROJET_PK de la table PROJET pour récupérer les adresses des lignes correspondantes dans la table PROJET, puis il récupère La ligne correspondante à chaque adresse. Enfin le système fait la jointure entre les lignes deux tables et envoi les libelles des projets et les numéros d’employés. Driss HAZZAF 11

Exercices 1. On considère un index de type B+ d’ordre 2 sur la colonne durée de la table Projet. Construire l’arbre correspondant à l’insertion des durées suivantes dans l’ordre indiqué, ne mettre dans l’arbre que les clés : (1, 9, 5, 11, 43, 61, 13, 23, 89, 41, 119, 25, 27, 29, 31) 2. Générer et expliquer le plan d’exécution de la requête suivante : SELECT P. LIBELLE AS Projet, T. DESCRIPTION AS Tache FROM PROJET P INNER JOIN TACHE T ON P. PROJET_CODE = T. PROJET_CODE WHERE T. PCT_REALISATION = 100; 3. Qu’est-ce que vous pouvez proposer pour optimiser la requête précédente ? Driss HAZZAF 12

Solution 1. L’arbre B+ est le suivant : 29 9 23 1 5 9 11 13 23 41 61 25 27 29 31 41 43 61 Driss HAZZAF 89 119 13

Solution (suite ) 2. Le plan d’exécution de la requête est le suivant : SELECT STATEMENT NESTED LOOPS TABLE ACCESS TACHE FULL TABLE ACCESS PROJET BY INDEX ROWID INDEX PROJET_PK UNIQUE SCAN Le système fait une boucle imbriquée pour la jointure entre les tables TACHE et PROJET. Il commence par une lecture séquentiel de la table TACHE pour cher les lignes ayant un pourcentage de réalisation égal à 100. Pour chaque ligne, il extrait la valeur de PROJET_CODE et interroge l’index PROJET_PK de la table PROJET pour récupérer les adresses des lignes correspondantes dans la table PROJET, puis il récupère La ligne correspondante à chaque adresse. Enfin le système fait la jointure entre les lignes deux tables et envoi les libelles des projets et les descriptions de tâches. 3. Pour améliorer la requête, on peut ajouter un index sur la colonne PCT_REALISATION de la table Tache. Driss HAZZAF 14