22/04/2023

Administration &
Tuning des Bases
de données
Dr FERRAHI

0
Introduction

1
 22/04/2023

Plan

Présentation générale des BD et SGBD : Rappels

❑Généralités
▪ Définitions, historique des SGBD, des BD
❑Le SGDB
▪ Niveaux de description et Architecture des SGBD
▪ Objectifs et fonctionnalités d’un SGBD
❑Le SGBD ORACLE
▪ Architecture générale, description, fonctions ..
3

Définition d’une bases de données

▰ Collection de données cohérentes et structurées (données persistantes) utilisées par des

systèmes informatiques.

▰ Collection de données fortement structurées et persistantes dont la structure est définie dans un
schéma au moyen d’un langage de définition de données.

▰ Les données et le schéma sont gérés à l’aide d’un logiciel appelé système de gestion de bases de
données (SGBD).

2
 22/04/2023

Définition d’un SGBDS

Un SGBD: système de gestion de base de données

▰ Un ensemble de logiciels informatiques qui sert à la manipulation des bases de données.

▰ Il sert à effectuer des opérations ordinaires telles que consulter, modifier, construire, organiser,
transformer, copier, sauvegarder ou restaurer des bases de données. Il est souvent utilisé par
d'autres logiciels ainsi que les administrateurs ou les développeurs.

▰ L’ensemble, dont le composant central est le moteur de base de données, peut être sous forme
de composants logiciels, de serveurs, de logiciels applicatifs ou d’environnement de
programmations.

▰ Il permet généralement à plusieurs utilisateurs et plusieurs logiciels de manipuler plusieurs

bases de données en même temps quelque soit le contenu et l’organisation des BDs 5

Rôle d’un administrateur

Garantir un fonctionnement optimal de la base ▰ définir les stratégies de sauvegarde (sauvegarde,

(maintenance) restauration, archivage)
▰ installer le SGBD ▻ gérer les accès aux données
▻ serveur, clients, outils d’applications ▰ maintenir de bonnes performances (ou les
▰ créer la base et la faire évoluer améliorer)

▻ définir les organisations logique et physique de

▰ permettre l’échange de données Base <-> Extérieur
stockage ▻ Export/import, SQLloader
▻ définir les ressources de mémorisation des
données
▰ enregistrer et gérer les utilisateurs
▻ créer les rôles, profils utilisateurs
▻ affecter les rôles appropriés aux utilisateurs
▻ assurer la sécurité et la cohérence des
données 6

3
 22/04/2023

Historique des SGBDs

▰ Premiers SGBD créés dans les années 1960

▰ Technologie répandue dès les années 1970

▰ Le consortium CODASYL a été formé dans les années 1960 en vue de produire des normes
et standards en rapport avec les SGBD.

▰ Le consortium CODASYL est à l'origine de diverses normes en rapport avec le langage de

programmation COBOL, un langage créé en 1965, axé sur la manipulation des bases de
données.

▰ Le modèle de données relationnel a été créé en 1970.

7

Historique des SGBDs

▰ Les premiers SGBD qui manipulent des bases de données relationnelles — IBM System R et Oracle V2 — sont
apparus en 1978.
▰ 1981 : Informix
▰ 1983 : Oracle V3
▰ 1987 : SyBase SQL Server
Actuellement Oracle Database 21c
▰ 1989 : PostGres
▰ 1992 : MS Access
▰ 1999 : Oracle 8i
▰ 2000 : SQL Server 2000
▰ 2005 : Oracle 10g R2, SQL Server 2005
▰ 2006 : IBM DB2
Pour plus de détail sur tout l’historique des SGBD, voir le lien suivant :
[Link] 8

4
 22/04/2023

Génération des SGBDs

▰ 1ère génération (années 70): réseau, hiérarchique (CODASYL, IMS,...)

▻ LMD navigationnel
▰ 2ième génération (années 80): relationnel (Oracle, Ingres, DB2, SQL server,…)
▻ LMD non procédural
▰ 3ième génération (années 90)
▻ Objet (Object Store, Versant,…)
▻ Relationnel objet (Oracle8, DB2 UDS, Informix US)
▰ 4ième génération ?
▻ Déductif (« Datalog »), entrepôt de données, data mining, support du WEB (XML, XMLQL

Niveaux de description des données

Stockage physique
Description de l’organisation
physique des données
Domaine de
Schéma interne
l’administrateur de
la base de données

Description de l’univers à modéliser

Domaine des
avec éventuellement les contraintes
concepteurs de Schéma conceptuel
liées à ces objets (contraintes
BD
d’intégrité)

Domaine des Application accédant aux

créateurs Schéma Schéma Schéma
Externe 1 Externe 2 Externe 3
données : notion de vue
d’applications (accès limités)
10

5
 22/04/2023

Architecture Fonctionnelle générale d’un SGBD

INTERFACE UTILISATEUR

ANALYSE DE REQUÊTES Mémoire centrale

Analyse Optimisation
Syntaxique Dictionnaire de données

Transactions Buffer SQL

Verrous exécution

Gestion de la mémoire Buffer données

(Mémoire cache)

BD
Mémoire secondaire
11

Fonctionnalités d’un SGBD

▰ Interface : Rôle : Interpréteur de requêtes
▰ Analyseur de requêtes : Rôle :

▻ Génération de l’arbre algébrique

▻ Optimisation de la requête : génération de l’arbre optimisé

▻ Génération du plan d’exécution

▰ Gestionnaire de transactions : une transaction est l’unité d’exécution d’une requête: elle a la forme

▻ Début transaction

▻ Demande de verrous

▻ Lecture/ ecriture

▻ Fin transaction
Rôle : piloter l’exécution d’une requête
▰ Gestion de la mémoire : Le SGBD gère un espace de stockage suivant deux aspects :

▻ Mémoire centrale : Buffer de données reçoit les données transférées de la MS et le Buffer SQL qui contient le plan
d’exécution de la requête
▻ Mémoire secondaire qui contient les tables de données stockées suivant des organisations de stockage
12

6
 22/04/2023

Objectifs des SGBD

▰ Indépendance des données par rapport aux supports physiques

▻ Notion de relation qui définit la structure de données
▻ La structure d’implémentation d’une relation est la table sur laquelle seront définis des
chemins d’accès.

▰ Indépendance logique
▻ Le SGBD doit autoriser plusieurs visions d’utilisateurs sur la base
▻ Possible grâce à la notion de vues

13

Objectifs des SGBD

▰ Evolution de la BD
▻ Evolution des données
▻ Insertion
▻ Suppression
▻ Modification
▻ Evolution du schéma : Modification de la structure d’une relation
▻ Ajout, suppression d’attributs
▻ Ajout d’une nouvelle relation.
▰ Cohérence de données
▻ Intégrité des données
▻ Les données d’une base de données doivent vérifier des invariants appelés contraintes
d’intégrité. 14

7
 22/04/2023

Objectifs des SGBD

Partageabilité des données et sécurité de fonctionnement

▰ Possibilité d’autoriser plusieurs utilisateurs à accéder en même temps à la base de données
▰ Gestion des conflits d’accès et d’écriture.
▰ Sécurité de la BD
▻ Gestion des utilisateurs
▻ Gestion des droits d’accès
▻ Gestion des transactions
▰ Intégrité de la BD
▻ Protection contre les pannes
▻ Reprise après panne
▻ Gestion des journaux log
15

Principales architectures des BDs

▰ Architecture centralisée
▻ Programme d'application et SGBD sur même machine (même site)
▻ Premiers systèmes

▰ Architecture du type client-serveur

▻ Programme d'application = client
▻ Interface (IHM) + traitement du domaine d’application

▰ SGBD = serveur de données

▻ machines (sites) différentes
16

8
 22/04/2023

Architecture Client-Serveur

17

Architecture à trois tiers

18

9
 22/04/2023

Bases de données réparties

▰ SGBD réparti
▻ Répartition des données de manière transparente
▻ Objet

19

Bases de données parallèles

▰ Parallélisme à l'intérieur d'un site

20

10
 22/04/2023

Entrepôt de données

21

Architecture interne d’un SGBD

Administrateur Utilisateur Applications

Requêtes DDL Requêtes Programme

interactives
Compilateur du
Pré-compilateur
langage de prog

Compilateur de Commandes DML

Compilateur DDL
requêtes

Compilateur DML Transaction

compilé
Méta-base

Gestionnaire
des données Processeur BD

Module de contrôle des accès concurrents

BD Module de restauration er de recouvrement 22

11
 22/04/2023

Architecture d’un SGBD

▰ Gestionnaire de données
▻ La base de données ainsi que la méta-base sont stockées en mémoire secondaires. L’accès
à leurs données est assuré par le gestionnaire de données.
▻ L’accès est d’abord contrôlé par le système de gestion de fichier, le gestionnaire de
données est un module de plus haut niveau assurant l’accès aux données via un ensemble
de buffers.

▰ Méta-base
▻ Contient toutes les informations sur les données comme la structure des tables, les types
de données, les index crées, etc.

23

Architecture d’un SGBD

▰ Compilateur DDL
▻ Compile et exécute le schéma de définition de données et sauvegarde les descriptions dans
la méta-base.

▰ Compilateur DML
▻ Compile et exécute les requêtes de manipulation de données.

▰ Compilateur de requêtes
▻ Traite les requêtes utilisateurs saisies de manière interactive. Il parse, analyse puis compile
ces requêtes.

24

12
 22/04/2023

Architecture d’un SGBD

▰ Processeur de base de données

▻ Contrôle l’accès à la base de données. Il reçoit des ordres de recherche ou de mise à jour et
les exécute sur la base de données. L’accès aux données est assuré par le gestionnaire de
données.

▰ Pré-compilateur
▻ Permet d’extraire les commandes DML pour une compilation en code objet. Le reste du
code est envoyé au compilateur du langage de programmation, ensuite les deux codes
objets sont fusionnés pour générer des transactions qui seront à leur tour envoyée au
processeur de BD

25

SGBD ORACLE

▰ Un système de gestion de base de données relationnel (SGBDR) qui depuis l'introduction du

support du modèle objet dans sa version 8 peut être aussi qualifié de système de gestion
de base de données relationnel-objet (SGBDRO).

▰ Fourni par Oracle Corporation, il a été développé par Larry Ellison, accompagné d'autres
personnes telles que Bob Miner et Ed Oates.

▰ ORACLE a été édité par la société Oracle Corporation, implantée aux USA à RedWood
Shores en Californie.

▰ Depuis, les produits ORACLE n’ont cessé d’évoluer avec l’évolution des technologies de
l’information et de la communication. Aujourd’hui ORACLE est un SGBD réparti, qui s’est
tourné vers le Web. 26

13
 22/04/2023

Architecture

Designer
Client

Developer Discoverer

SQL*Plus

PL/SQL SQL
Serveur

ORACLE SERVER

27

Architecture
▰ L’environnement ORACLE est un ensemble de produits autour de sa base de données
▰ ORACLE SERVER (gestionnaire de bases de données)
▻ Contrôle toutes les actions de la BD comme l’accès et la sécurité, stockage et intégrité des données
▰ SQL et l’extension PL/SQL (langage comprenant des commandes procédurales supportant la gestion des erreurs et
déclaration de variables). SQL*Plus est un outil permettant la mise en forme de résultats de requêtes SQL.

▰ ORACLE Designer
▻ Ensemble de produits intégrés dans un référentiel unique d’entreprise pour la conception des applications

▰ ORACLE Developer
▻ Outils de développement d’applications client/serveur ou Internet (Forms, Reports, Jdeveloper).

▰ ORACLE Discoverer
▻ Outil d’interrogation pour des utilisateurs qui ont besoin d’accéder par eux-mêmes aux données, Datawarehouse,
Datamart (Discoverer Administrator, Discoverer Catalog, etc.). 28

14
 22/04/2023

System Global Area

▰ Les composants principaux de l’architecture d’ORACLE sont

Background Process
▻ Les processus
spmo
▻ Les structures mémoires n
▻ Les fichiers Structures mémoires : SGA

Shared Pool DB Buffer Redo Log

Cache Buffer

Processus
Processus
utilisateurs
Serveur

Fichiers
de
Fichiers de Fichiers Redo Log
contrôle
données
29

System Global Area

▰ Représente l’ensemble des buffers nécessaires à la gestion des transactions.

▻ La SGA est également appelée Shared Global Area, elle est partagée entre plusieurs utilisateurs.
▻ La SGA est une zone mémoire allouée au démarrage d’une instance, elle est restituée à la fermeture de cette
instance.
▻ Les données de la SGA sont partagées par l’ensemble des utilisateurs connectés à un moment donné ; elles
sont divisées en plusieurs types de buffers :

▰ Database Buffer Cache

▻ Contient les blocs de données, les blocs d’index, des blocs contenant les RollBack Segments (ils enregistrent
les actions d’une transaction pour la reprise en cas de panne) et des blocs pour la gestion du système, les
plus récemment utilisés; il peut contenir des données modifiées qui n’ont pas encore été enregistrées sur
disque.

30

15
 22/04/2023

System Global Area

▰ Redo Log Buffer

▻ Contient les redo entries, ensemble des modifications réalisées sur la base ; ces redo entries sont
mémorisées sur un fichier redo log, qui pourra être utilisé en cas de panne.

▰ Shared Pool ou zone de partage des ordres SQL

▻ Utilisé pour mémoriser, analyser et traiter les ordres SQL soumis par les utilisateurs. Elle peut
réutiliser les ordres SQL déjà exécutés.

31

Les process

▰ Les Oracle Process sont divisés en trois catégories

▻ Users Process : processus utilisateurs
▻ Server Process : processus serveurs
▻ Backgrounds Process. Processus d’arrière plan ou détachés
▻ Les Background Process (Process détachés) prennent en charge les mécanismes internes
d’Oracle. Une instance Oracle contient au minimum quatre background process : DBWR, LGWR,
SMON et PMON.

32

16
 22/04/2023

Background process

▰ DBWR (DataBase Writer) : Son rôle est de mettre à jour les fichiers de données. Les blocs de données
modifiés en SGA sont stockés dans la base de données: écriture des données physiques

▰ LGWR (LoG Writer) : Ce process écrit séquentiellement dans le fichier Redo Log courant des enregistrements
Redo Log de la SGA.

▰ SMON (System MONitor) : Il vérifie si la base a été arrêtée proprement. Si ce n’est pas le cas, il récupère dans
les fichiers redo log les enregistrements validés, qui n’ont pas encore été écrits dans la base par Oracle, pour
les insérer = (reprise, nettoyage, compactage)

▰ PMON (Process MONitor) : Il administre les différents process d’Oracle comme la Reprise en cas d’échec,
libération des ressources occupées, par exemple dans le cas où un client éteint sa machine sans se
déconnecter,
33

Background Process

▰ CKPT : Checkpoint: informe de l’arrivée d’un point de contrôle

▰ ARCH : Archiver : archivage du journal
▰ RECO : Recover : résout les transactions en suspens du fait de problèmes système ou réseau
▰ LCKn : LOCK : verrouillage inter-instances de la BD
▰ SNPn : Snapshot Refresh: rafraichissement des vues matérialisées après les modifications de données
▰ Snnn : Shared server: pour gérer plusieurs utilisateurs
▰ Dnnn : Dispatcher: répartiteur de sessions utilisateurs (utilisation de file d’attente)
▰ Pnnn : Parallel Query : permet l’exécution parallèle de certaines requêtes

34

17
 22/04/2023

Process utilisateur

▰ Un process utilisateur est démarré lorsqu’un utilisateur exécute un programme applicatif.

▻ Exécute l’outil ou l’application
▻ Exemple : SQL*plus, Forms,
▻ Considéré comme étant le client.
▻ Il transmet le SQL au process serveur et en reçoit les résultats.

35

Process serveur

▰ Prennent en charge les demandes des utilisateurs. Ils sont responsables de la

communication entre la SGA et le Process User.
▰ Les tâches du process serveur
▻ Analyser et exécuter les commandes SQL
▻ Lire les blocs de données à partir du disque dans les zones partagées de la
SGA
▻ Renvoyer les résultats des commandes SQL au Process utilisateur

36

18
 22/04/2023

Instance ORACLE

▰ C’est la combinaison de la SGA et des bakground process de la base de données. Quand une instance est
démarrée, les zones mémoire de la SGA sont allouées et les bakground process sont lancés.
▻ Ne pas confondre une BD ORACLE et une instance d’ORACLE : l’instance est d’abord démarrée puis elle
monte la BD (ouverture des fichiers).
▰ Les process serveurs et utilisateurs ne font pas partie de l’instance d’ORACLE.
▰ MÉCANISME
▻ Lorsqu’un utilisateur demande une donnée, son processus va la chercher en SGA, si elle n’y est pas, le
processus va la chercher dans les fichiers de données.
▻ Toutes les transactions sont enregistrées dans les fichiers Redo Log, En cas de problème la reprise
après panne est assurée par le process SMON au démarrage d’une nouvelle instance.

37

BD Oracle

▰ La base de données ORACLE est composée de fichiers de données et de fichiers redo log tous étant
référencés dans un ou plusieurs fichiers de contrôle.

Structure physique Définition

Contiennent toutes les données de la base : les tables, index, procédures, fonctions,… ; ainsi que le dictionnaire de
Fichiers de données
données (créé lors de la création de la base de données).

Gardent des enregistrements de toutes les modifications effectuées sur la base de données dans un but de
Fichiers Redo Log
restauration

Conservent la définition des structures physiques et les états de la Base de données: nom de la base, nom et chemin
Fichiers de contrôle
d’accès des fichiers de données et Redo Log, date et heure de création de la base.

38

19
 22/04/2023

Structure interne de la BD

▰ LES TABLESPACES
▻ Les données d’une base Oracle sont mémorisées dans une ou plusieurs unités logiques appelées
tablespaces et physiquement dans des fichiers associés à ces tablespaces.
▻ Chaque base contient obligatoirement un tablespace SYSTEM, celui-ci contient les tables du
dictionnaire de données, les procédures, les fonctions, les packages, les triggers et le rollback segment
SYSTEM.
▻ Les autres tablespaces contiennent les segments de la base de données (tables, index,…).
39

Structure interne

▰ Les ROLLBACK SEGMENTS

▻ Une base de données contient un ou plusieurs ROLLBACK SEGMENTS ;
▻ Un rollback segment enregistre les actions d’une transaction qui peuvent être annulées en cas
d’incident.
▻ Le rollback segment SYSTEM est créé lors de la création de la base dans le tablespace SYSTEM; il n’est
utilisé que pour les transactions portant sur les données du dictionnaire.
▻ Un ou plusieurs autres rollback segments doivent exister pour les transactions portant sur des données
utilisateur.

40

20
 22/04/2023

Fonction d’un SGBD

▰ Création / Modification ▰ Chemins d’accès

▻ Création : CREATE TABLE ▻ Index : CREATE INDEX
▻ Modification : ALTER TABLE ▰ Catalogues
▻ Suppression : DROP TABLE, DELETE ▻ Informations sur toutes les objets de la Base
de Données
▻ Insertion : INSERT INTO
▰ Schémas externes
▻ Mise à Jour : UPDATE
▻ Les vues : CREATE VIEW
▻ Interrogation : SELECT

41

21