TP1 : Administration de bases de données Scénario : Vous êtes consultant pour une entreprise de

commerce électronique qui gère une base de données Oracle. L'entreprise rencontre des problèmes
de performance avec son application de gestion des commandes. Les requêtes sont lentes, ce qui
affecte l'expérience utilisateur et l'efficacité des opérations. Votre mission est d'identifier les
problèmes et de mettre en œuvre des optimisations pour améliorer les performances de la base de
données. Base de données : La base de données contient les tables suivantes : • Clients (id_client,
nom, adresse, email) • Produits (id_produit, nom, description, prix) • Commandes (id_commande,
id_client, date_commande) • Lignes_commande (id_ligne, id_commande, id_produit, quantité) I-
Création de BD a. Créez cette BD sous Oracle. b. Peuple chaque table d’une dizaine d’enregistrement.
c. Tester les requêtes de projection, de sélection et de jointure suivantes : a Sélection : • "Affichez les
noms et adresses des clients qui ont passé des commandes en 2023." • "Affichez les produits dont le
prix est compris entre 5000et 100000 FCFA." • "Affichez les clients dont l'adresse contient le mot
'Tchad'." b Jointures : • "Affichez les noms des clients, les numéros de commande et les dates de
commande." • "Affichez les noms des produits et les quantités commandées pour chaque
commande." • "Affichez les noms des clients qui ont commandé le produit 'Ordinateur portable'." c
Agrégation : • "Affichez le nombre total de commandes par client." • "Affichez le montant total des
ventes par produit." • "Affichez le client qui a dépensé le plus d'argent." d Sous-requêtes : • "Affichez
les produits qui ont été commandés par tous les clients." • "Affichez les clients qui ont commandé le
produit le plus cher." e Modification de données : • "Ajoutez un nouveau client." • "Mettez à jour
l'adresse d'un client existant." • "Supprimez une commande." f. II- Afficher la structure de chaque
table. Requêtes critiques : Les requêtes suivantes sont considérées comme critiques en raison de leur
fréquence et de leur impact sur les performances : 1. Récupérer les détails d'une commande : SQL
SELECT [Link], [Link], [Link], [Link], [Link]é FROM Commandes co JOIN Clients c ON
co.id_client = c.id_client JOIN Lignes_commande lc ON co.id_commande = lc.id_commande JOIN
Produits p ON lc.id_produit = p.id_produit WHERE co.id_commande = :id_commande; 2. Afficher les
produits les plus vendus : SQL SELECT [Link], COUNT(*) AS nombre_ventes FROM Lignes_commande
lc JOIN Produits p ON lc.id_produit = p.id_produit GROUP BY [Link] ORDER BY nombre_ventes DESC;
A. Génération et peuplement de la Base de données Vous pouvez générer des données pour remplir
les tables que vous avez définies, en utilisant un langage de programmation comme Python et la
bibliothèque faker pour générer des données fictives réalistes. 1. Installation de Python et des
bibliothèques nécessaires Si vous n'avez pas encore phython installé, vous pouvez installer python
IDE et installer faker avec pip : pip install faker 2. Génération des données avec Python Méthodes
utiles : • [Link]('fr_FR'): Initialise faker avec la localisation française pour générer des noms,
adresses, etc., réalistes. • generer_clients(nombre_clients): Génère un nombre spécifié de clients
avec des informations fictives. • generer_produits(nombre_produits): Génère des produits avec des
noms, descriptions et prix aléatoires. • generer_commandes(nombre_commandes, clients): Génère
des commandes associées à des clients existants, avec des dates de commande aléatoires. •
generer_lignes_commande(nombre_lignes, commandes, produits): Génère des lignes de commande
associées à des commandes et produits existants, avec des quantités aléatoires. Voici un script
Python qui génère des données pour chaque table : Python from faker import Faker import random
from datetime import datetime, timedelta fake = Faker('fr_FR') # Utiliser la localisation française def
generer_clients(nombre_clients): clients = [] for i in range(1, nombre_clients + 1):
[Link]({ 'id_client': i, 'nom': [Link](), 'adresse': [Link](), 'email': [Link]() })
return clients def generer_produits(nombre_produits): produits = [] for i in range(1,
nombre_produits + 1): [Link]({ 'id_produit': i, 'nom': [Link]().capitalize() + " " +
[Link]().capitalize(), 'description': [Link](), 'prix': round([Link](10, 200), 2) })
return produits def generer_commandes(nombre_commandes, clients): commandes = [] for i in
range(1, nombre_commandes + 1): client = [Link](clients) date_debut = datetime(2023, 1,
1) date_fin = [Link]() date_commande = fake.date_time_between(start_date=date_debut,
end_date=date_fin) [Link]({ 'id_commande': i, 'id_client': client['id_client'],
'date_commande': date_commande.strftime('%Y-%m-%d %H:%M:%S') }) return commandes def
generer_lignes_commande(nombre_lignes, commandes, produits): lignes_commande = [] for i in
range(1, nombre_lignes + 1): commande = [Link](commandes) produit =
[Link](produits) quantite = [Link](1, 10) lignes_commande.append({ 'id_ligne': i,
'id_commande': commande['id_commande'], 'id_produit': produit['id_produit'], 'quantite':
quantite }) return lignes_commande # Exemple d'utilisation clients = generer_clients(10) produits =
generer_produits(20) commandes = generer_commandes(15, clients) lignes_commande =
generer_lignes_commande(30, commandes, produits) # Affichage des données (vous pouvez les
enregistrer dans des fichiers CSV ou les insérer dans une base de données) print("Clients:", clients)
print("Produits:", produits) print("Commandes:", commandes) print("Lignes de commande:",
lignes_commande) 3. Utilisation des données générées Vous pouvez maintenant utiliser les données
générées de plusieurs manières : • Les enregistrer dans des fichiers CSV pour les importer dans une
base de données ou les utiliser dans une feuille de calcul. • Les insérer directement dans une base de
données en utilisant SQL Loader. Pour chaque table, créer un fichier de comme pour la table Client
LOAD DATA INFILE '[Link]' REPLACE INTO TABLE Clients FIELDS TERMINATED BY "," OPTIONALLY
ENCLOSED BY '"' ( id_client INTEGER EXTERNAL, nom CHAR, adresse CHAR, email CHAR ) • Pour
exécuter SQL*Loader, utilisez la commande suivante dans votre terminal ou invite de commandes :
sqlldr votre_login/votre_mot_de_passe@votre_base_de_données control=[Link] log=[Link]
III- Evaluation des performations L'instruction EXPLAIN est un outil essentiel pour analyser et
optimiser les performances des requêtes SQL. Elle vous permet de visualiser le plan d'exécution
d'une requête, c'est-à-dire les étapes que la base de données effectue pour récupérer les résultats. 1.
Analyse des performances : o Utilisez l'outil EXPLAIN PLAN pour examiner le plan d'exécution des
requêtes critiques. Une fois exécuté EXPLAIN PLAN For pour une requête, vous pouvez afficher le
plan d'exécution en utilisant la fonction DBMS_XPLAN.DISPLAY (SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY);). o Identifiez les opérations coûteuses (par exemple, les full table
scans). o Mesurez le temps d'exécution des requêtes à l'aide de SQL Developer ou sqlplus
(commande SET TIMING ON). 2. Optimisation des index : o Créez des index sur les colonnes utilisées
dans les clauses WHERE et JOIN des requêtes critiques (par exemple, id_commande dans la table
Commandes, id_produit dans la table Lignes_commande). o Évaluez l'impact des index sur les
performances des requêtes à l'aide de EXPLAIN PLAN et de mesures de temps d'exécution. 3.
Optimisation des requêtes : o Réécrivez les requêtes pour les rendre plus efficaces. Par exemple,
utilisez des alias de table plus courts, évitez les jointures inutiles, utilisez des fonctions intégrées (par
exemple, COUNT(*) au lieu de COUNT(1)). o Utilisez des variables de liaison pour optimiser les
requêtes paramétrées. 4. Optimisation des paramètres Oracle : o Ajustez les paramètres de mémoire
(SGA, PGA) en fonction des besoins de l'application. o Configurez le cache de requêtes pour stocker
les résultats des requêtes fréquemment exécutées. 5. Tests et validation : o Après chaque
optimisation, testez les requêtes critiques pour vérifier l'amélioration des performances. o Comparez
les temps d'exécution et l'utilisation des ressources avant et après les optimisations. IV-
Performances dynamiques Oracle fournit un ensemble de vues de performance dynamique (V$) et
de vues DBA_ qui contiennent des informations détaillées sur l'utilisation des ressources. Voici
quelques exemples : • V$SYSSTAT : o Contient des statistiques sur l'ensemble du système, telles que
le temps CPU, le nombre de lectures/écritures de blocs et le nombre de connexions. • V$SGASTAT : o
Affiche des informations sur l'utilisation de la SGA (System Global Area), la zone de mémoire
partagée d'Oracle. • V$PROCESS : o Fournit des informations sur les processus Oracle en cours
d'exécution, telles que le temps CPU utilisé et la mémoire consommée. • V$SESSION : o Affiche des
informations sur les sessions utilisateur en cours, telles que les requêtes en cours d'exécution et les
ressources utilisées. • DBA_DATA_FILES : o Fournit des informations sur les fichiers de données, y
compris leur taille et leur utilisation. • DBA_TABLESPACES : o Fournit des informations sur les
tablespaces, notamment l'espace libre et l'espace utilisé. 1. Reprendre les deux requêtes critiques et
examiner la consommation des ressources CPU et Mémoire 2. Décrire V$SESSION et exécuter
quelques requêtes de consultation 3. Afficher la liste de tablespaces de votre système 4. Quel
tablespaces sont utilisés par votre DB ? 5. Quels sont les fichiers de données utilisés par votre DB ? 6.
Quels sont les fichiers de contrôle utilisés par votre DB ? 7. Quelles est la taille moyennes des extents
utilisés par votre DB (DBA_EXTENTS, et DBA SEGMENTS) ? V. Gestion des performances et des
ressources et Sécurité et audit : Exécuter et examiner les résultats des requêtes suivantes : 1.
Identifier les sessions bloquantes : la requête suivante permet d'identifier les sessions qui bloquent
d'autres sessions, ce qui peut causer des problèmes de performance. o SELECT blocking_session,
session_id FROM v$session WHERE blocking_session IS NOT NULL; 2. Surveiller l'utilisation du
disque : la requête suivante permet de suivre l'utilisation de l'espace disque par tablespace. o SELECT
tablespace_name, SUM(bytes) / 1024 / 1024 AS "Taille (MB)" FROM dba_data_files GROUP BY
tablespace_name; 3. Analyser les statistiques d'exécution des requêtes : la requête suivante affiche
les requêtes SQL les plus longues à s'exécuter, ce qui peut aider à identifier les requêtes à optimiser.
o SELECT sql_id, executions, elapsed_time FROM v$sqlarea ORDER BY elapsed_time DESC; 4.
Consulter l'utilisation des indexes : la requête suivante affiche permet de voir les indexes qui sont
utilisés récemment, et donc d'identifier les index qui ne servent pas et peuvent être supprimés. o
SELECT index_name, table_name, last_used_at FROM dba_indexes WHERE last_used_at IS NOT NULL
ORDER BY last_used_at; 5. Afficher les privilèges des utilisateurs : la requête suivante permet de voir
les privilèges système accordés à un utilisateur spécifique. o SELECT * FROM dba_sys_privs WHERE
grantee = 'NOM_UTILISATEUR'; 6. Consulter les rôles attribués aux utilisateurs : la requête suivante
affiche les rôles attribués à un utilisateur o SELECT * FROM dba_role_privs WHERE grantee =
'NOM_UTILISATEUR'; 7. Afficher les connexions récentes : la requête suivante permet de suivre les
connexions à la base de données o SELECT username, osuser, machine, logon_time FROM v$session
ORDER BY logon_time DESC; 8. Vérifier l'intégrité des blocs de données : la requête suivante permet
de détecter les blocs de données corrompus : ANALYZE TABLE NOM_TABLE VALIDATE STRUCTURE;