CORRECTION 1
Absolument ! Voici une correction détaillée pour chaque partie de votre TP
d'administration de bases de données Oracle.
I- Création de BD
a. Création de la BD sous Oracle
SQL
-- Création des tables
CREATE TABLE Clients (
id_client NUMBER PRIMARY KEY,
nom VARCHAR2(100),
adresse VARCHAR2(255),
email VARCHAR2(100)
);
CREATE TABLE Produits (
id_produit NUMBER PRIMARY KEY,
nom VARCHAR2(100),
description VARCHAR2(255),
prix NUMBER
);
CREATE TABLE Commandes (
id_commande NUMBER PRIMARY KEY,
id_client NUMBER,
date_commande DATE,
FOREIGN KEY (id_client) REFERENCES Clients(id_client)
);
CREATE TABLE Lignes_commande (
id_ligne NUMBER PRIMARY KEY,
id_commande NUMBER,
id_produit NUMBER,
quantite NUMBER,
FOREIGN KEY (id_commande) REFERENCES Commandes(id_commande),
FOREIGN KEY (id_produit) REFERENCES Produits(id_produit)
);
b. Peuplement des tables
SQL
-- Exemple d'insertion de données (à adapter avec vos données
générées)
INSERT INTO Clients VALUES (1, 'Jean Dupont', '12 Rue du Tchad,
N''Djamena', '[Link]@[Link]') ;
-- ... Insérer 9 autres clients
INSERT INTO Produits VALUES (1, 'Ordinateur portable', 'PC portable
performant', 80000) ;
-- ... Insérer 19 autres produits
INSERT INTO Commandes VALUES (1, 1, TO_DATE ('2023-10-26', 'YYYY-MM-
DD')) ;
-- ... Insérer 14 autres commandes
INSERT INTO Lignes_commande VALUES (1, 1, 1, 2) ;
-- ... Insérer 29 autres lignes de commande
c. Tests des requêtes
a Sélection :
SQL
-- Clients ayant commandé en 2023
SELECT nom, adresse FROM Clients c
JOIN Commandes co ON c.id_client = co.id_client
WHERE EXTRACT (YEAR FROM co.date_commande) = 2023;
-- Produits avec prix entre 5000 et 100000 FCFA
SELECT * FROM Produits WHERE prix BETWEEN 5000 AND 100000 ;
-- Clients avec adresse contenant 'Tchad'
SELECT * FROM Clients WHERE adresse LIKE '%Tchad%’ ;
b Jointures :
SQL
-- Noms des clients, numéros et dates de commande
SELECT [Link], co.id_commande, co.date_commande
FROM Clients c JOIN Commandes co ON c.id_client = co.id_client;
-- Noms des produits et quantités commandées
SELECT [Link], [Link] FROM Produits p
JOIN Lignes_commande lc ON p.id_produit = lc.id_produit;
-- Clients ayant commandé 'Ordinateur portable'
SELECT [Link] FROM Clients c
JOIN Commandes co ON c.id_client = co.id_client
JOIN Lignes_commande lc ON co.id_commande = lc.id_commande
JOIN Produits p ON lc.id_produit = p.id_produit
WHERE [Link] = 'Ordinateur portable’ ;
c Agrégation :
SQL
-- Nombre de commandes par client
SELECT [Link], COUNT(co.id_commande) FROM Clients c
JOIN Commandes co ON c.id_client = co.id_client
GROUP BY [Link];
-- Montant total des ventes par produit
SELECT [Link], SUM([Link] * [Link]) FROM Produits p
JOIN Lignes_commande lc ON p.id_produit = lc.id_produit
GROUP BY [Link];
-- Client ayant dépensé le plus
SELECT [Link] FROM Clients c
JOIN Commandes co ON c.id_client = co.id_client
JOIN Lignes_commande lc ON co.id_commande = lc.id_commande
JOIN Produits p ON lc.id_produit = p.id_produit
GROUP BY [Link] ORDER BY SUM ([Link] * [Link]) DESC FETCH FIRST
1 ROWS ONLY;
d Sous-requêtes :
SQL
-- Produits commandés par tous les clients (complexe, nécessite des
concepts avancés)
-- Clients ayant commandé le produit le plus cher
SELECT [Link] FROM Clients c
JOIN Commandes co ON c.id_client = co.id_client
JOIN Lignes_commande lc ON co.id_commande = lc.id_commande
JOIN Produits p ON lc.id_produit = p.id_produit
WHERE [Link] = (SELECT MAX(prix) FROM Produits);
e Modification de données :
SQL
-- Ajouter un client
INSERT INTO Clients VALUES (11, 'Nouveau Client', 'Nouvelle
Adresse', 'nouveau@[Link]') ;
-- Mettre à jour l'adresse
UPDATE Clients SET adresse = 'Nouvelle Adresse 2' WHERE id_client =
1 ;
-- Supprimer une commande
DELETE FROM Commandes WHERE id_commande = 1 ;
f. Structure des tables :
SQL
DESC Clients ;
DESC Produits;
DESC Commandes;
DESC Lignes_commande ;
II- Requêtes critiques
A. Génération et peuplement de la BD
Votre code Python est correct et bien structuré.
L'utilisation de SQL*Loader est une excellente méthode pour insérer les
données.
III- Évaluation des performances
1. Analyse des performances :
o Utiliser EXPLAIN
PLAN FOR suivi de SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY); est la procédure standard.
o Identifier les "Full Table Scans" est crucial.
o SET TIMING ON dans SQL*Plus est utile pour mesurer le temps
d'exécution.
2. Optimisation des index :
o Créer des index sur id_commande, id_produit, id_client.
o Ré-exécuter EXPLAIN PLAN et mesurer les temps.
3. Optimisation des requêtes :
o Utiliser des alias courts (c, p, co, lc).
o Éviter SELECT * si possible.
o Utiliser des variables de liaison (":id_commande").
4. Optimisation des paramètres Oracle :
o Ajuster SGA et PGA selon les besoins.
o Configurer le cache de requêtes.
5. Tests et validation :
o Mesurer les temps avant et après les optimisations.
IV- Performances dynamiques
1. Consommation CPU/Mémoire :
o Utiliser V$SYSSTAT, V$PROCESS, V$SESSION.
2. V$SESSION :
o Contient des infos sur les sessions actives.
o Exemples de requêtes :
SELECT username, status FROM v$session;
SELECT sql_id, program FROM v$session WHERE username
IS NOT NULL;
3. Liste des tablespaces :
o SELECT tablespace_name FROM dba_tablespaces;
4. Tablespaces utilisés :
o SELECT tablespace_name FROM dba_data_files GROUP BY
tablespace_name;
5. Fichiers de données :
o SELECT name FROM v$datafile;
6. Fichiers de contrôle :
o SELECT name FROM v$controlfile;
7. Taille moyenne des extents :
o Requêtes complexes utilisant DBA_EXTENTS et DBA_SEGMENTS.
V- Gestion des performances et des ressources et Sécurité et audit
1. Sessions bloquantes :
o SELECT blocking_session, session_id FROM v$session WHERE
blocking_session IS NOT NULL;
2. Utilisation du disque :
o SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS
"Taille (MB)" FROM dba_data_files GROUP BY
tablespace_name;
3. Statistiques d'exécution :
o SELECT sql_id, executions, elapsed_time FROM v$sqlarea
ORDER BY elapsed_time DESC;
4. Utilisation des indexes :
o `SELECT index_name, table_name, last_used_at FROM
dba_indexes WHERE last_used_at IS NOT NULL ORDER BY
last_used_at
Utilisation de l’instruction EXPLAIN pour l’analyse et l’optimisation des perfor-
mances SQL
L’instruction EXPLAIN PLAN est un outil essentiel pour analyser et optimiser les performances
des requêtes SQL. Elle permet de visualiser le plan d’exécution d’une requête, c’est-à-dire
les étapes que le moteur Oracle exécute pour récupérer les résultats.
1. Analyse des performances
Utilisez l’instruction EXPLAIN PLAN FOR suivie de la requête à analyser pour générer
le plan d’exécution.
Affichez ensuite le plan d’exécution avec la fonction :
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Identifiez les opérations coûteuses, telles que les scans complets de table (Full Table
Scans), les jointures en boucle imbriquée (Nested Loops), etc.
Activez la mesure du temps d’exécution avec :
o Dans SQL*Plus : SET TIMING ON
o Dans SQL Developer : utilisez l'onglet Statistiques ou Plan d'exécution.
2. Optimisation des index
Créez des index sur les colonnes fréquemment utilisées dans les clauses WHERE, JOIN,
ou comme critères de tri :
o Exemple : id_commande (table Commandes), id_produit (table
Lignes_commande).
Évaluez l’impact de ces index sur les performances à l’aide de EXPLAIN PLAN et des
mesures de temps d’exécution.
3. Optimisation des requêtes
Réécrivez les requêtes pour les rendre plus efficaces :
o Utilisez des alias de table pour améliorer la lisibilité.
o Évitez les jointures inutiles.
o Préférez les fonctions optimisées comme COUNT(*) à la place de COUNT(1)
(même si Oracle les optimise de manière similaire).
Utilisez des variables de liaison (bind variables) pour optimiser les requêtes paramé-
trées et favoriser le partage des plans d’exécution.
4. Optimisation des paramètres Oracle
Ajustez les paramètres mémoire selon les besoins de l’application :
o SGA (System Global Area)
o PGA (Program Global Area)
Activez le cache de requêtes pour stocker les résultats des requêtes fréquemment exé-
cutées.
5. Tests et validation
Après chaque optimisation, exécutez à nouveau les requêtes critiques pour vérifier les
gains de performance.
Comparez les temps d’exécution et l’utilisation des ressources avant et après chaque
amélioration.
Si tu veux, je peux t’aider à créer un petit exemple de plan d'exécution avec une requête
Oracle et son interprétation. Tu veux ça ?
4o
