Oracle SQL Tuning

Chapitre 1: L’architecture de la base de données

1. Rôles de l'Administrateur de Base de Données (DBA)
2. Types de Connexions
3. Processus Utilisateur et Serveur
4. Connexion et Session
5. Composants de l'Architecture Oracle
6. Structures Mémoire
9. Fichiers Importants
Chapitre 2: Traitement d’une requête SQL
1. Étapes du Traitement d'une Requête SQL
2. Étapes du Hard Parse
3. Optimiseur Basé sur le Coût (CBO - Cost-Based Optimizer)
Chapitre 3: Les Requêtes Hiérarchiques
1. Syntaxe de Base des Requêtes Hiérarchiques
2. Parcours de l'Arbre
3. Fonctions Utiles pour les Requêtes Hiérarchiques
4. Exemples Pratiques
Chapitre 4: Plan d’exécution
1. Introduction au Plan d'Exécution
2. Statistiques Utilisées par l'Optimiseur
3. Collecte des Statistiques
4. Plan d'Exécution
5. Affichage du Plan d'Exécution
6. Lecture du Plan d'Exécution
7. Types d'Opérations dans le Plan d'Exécution
8. Vues de Performance
Chapitre 5: Les Hints
1. Qu'est-ce qu'un Hint dans Oracle SQL ?
2. Limitations des Hints
3. Syntaxe et Exemple de Hints
4. Types de Hints Courants
Chapitre 6: les Types de Jointures
1. Types de Jointures
2. Équi-Jointure et Non-Équi-Jointure
3. Auto-Jointure (SELF JOIN)
Chapitre 9: Les Vues
1- Vue Classique vs Vue Matérialisée
2- Rafraîchissement des Vues Matérialisées
Chapitre 10: la Normalisation
1. Les Dépendances Fonctionnelles (DF)
2. Les Axiomes d’Armstrong
3. Les Formes Normales (FN)
Exemple :
Étape 1 : 1ère Forme Normale (1FN)
Étape 2 : 2ème Forme Normale (2FN)
Étape 3 : 3ème Forme Normale (3FN)

Oracle SQL Tuning 1

 Chapitre 1: L’architecture de la base de données

1. Rôles de l'Administrateur de Base de Données (DBA)

Rôle Organisationnel :

Définition du schéma conceptuel des données.

Gestion des accès utilisateurs (qui peut accéder à quoi).

Rôle Technique :

Installation et configuration du SGBD.

Gestion des performances, de la sécurité, et de la cohérence des données.

Plan de sauvegarde et récupération.

2. Types de Connexions
Connexion Locale : Utilisateur directement sur le serveur Oracle.

Connexion Client/Serveur (Deux Tiers) : Utilisateur connecté via une machine cliente.

Connexion Multi-Tiers : Utilisateur connecté via un serveur applicatif (ex: Serveur Web).

3. Processus Utilisateur et Serveur

User Process (Processus Utilisateur) : Représente une application ou un outil qui se
connecte à la base de données.

Oracle SQL Tuning 2

 Server Process (Processus Serveur) : Gère l'interaction entre le processus utilisateur et le
serveur Oracle. Il analyse et exécute les requêtes SQL, lit les données, et renvoie les
résultats.

4. Connexion et Session
Connexion : Lien entre le processus utilisateur et le processus serveur.

Session : Connexion spécifique entre un utilisateur et le serveur Oracle, démarrant à la

validation de la connexion et se terminant à la déconnexion.

5. Composants de l'Architecture Oracle

Instance Oracle : Programme chargé en mémoire (RAM) lors du démarrage de la base de
données.

SGA (System Global Area) : Mémoire partagée contenant les données et informations
de contrôle.

Processus en Arrière-plan : Gèrent les opérations de base de données (écriture sur

disque, cohérence, etc.).

Stockage de Base de Données : Fichiers physiques contenant les données.

6. Structures Mémoire
PGA (Program Global Area) : Mémoire non partagée pour chaque processus serveur.

Session Area : Stocke les informations de session (variables, mots de passe, etc.).

Private SQL Area : Stocke les informations d'exécution des curseurs.

SQL Work Area : Zones de travail pour le traitement des requêtes SQL (tri, jointures,
index bitmap).

SGA (System Global Area)

Shared Pool : Cache les requêtes SQL récentes et les informations du dictionnaire de
données.

Data Dictionary Cache : Contient les métadonnées de la base de données.

Library Cache : Stocke le texte des requêtes, l'arborescence d'analyse, et les plans
d'exécution.

Database Buffer Cache : Cache les blocs de données lus depuis les fichiers de
données.

Redo Log Buffer : Tampon de journalisation pour les modifications de données.

Java Pool : Mémoire pour le code Java et les données de session.

Streams Pool : Mémoire pour Oracle Streams (capture et application des

modifications).

9. Fichiers Importants

Oracle SQL Tuning 3

 Fichiers de Contrôle (Control Files) : Contiennent des informations sur la structure
physique de la base.

Fichiers de Données (Data Files) : Stockent les données utilisateur et les métadonnées.

Fichiers de Journalisation (Redo Log Files) : Permettent la récupération en cas de

défaillance.

Fichiers de Sauvegarde (Backup Files) : Utilisés pour la récupération après une

défaillance.

Fichiers de Paramètres (Parameter File) : Définissent la configuration de l'instance.

Fichiers Trace et Alertes : Contiennent les logs des erreurs et des messages.

Chapitre 2: Traitement d’une requête SQL

1. Étapes du Traitement d'une Requête SQL

Analyse Syntaxique (Syntax Check) : Vérifie la correction syntaxique de la requête.

Exemple : SELECT * FORM employees; → Erreur : ORA-00923: FROM keyword not found .

Analyse Sémantique (Semantic Check) : Vérifie l'existence des objets interrogés (tables,
colonnes, etc.).

Exemple : SELECT * FROM nonexistent_table; → Erreur : ORA-00942: table or view does not exist .

Vérification des Privilèges (Privilege Check) : Vérifie si l'utilisateur a les droits nécessaires
pour accéder aux objets.

Vérification de la Zone Mémoire Partagée (Shared Pool Check) :

Si une requête similaire a déjà été exécutée, Oracle réutilise le plan d'exécution stocké
dans la Shared SQL Area (Soft Parse).

Oracle SQL Tuning 4

 Sinon, Oracle procède à un Hard Parse pour générer un nouveau plan d'exécution.

2. Étapes du Hard Parse

Allocation de la Zone SQL Partagée : Oracle alloue de la mémoire dans la Shared SQL
Area pour stocker le nouveau plan d'exécution.

Optimisation : L'optimiseur génère plusieurs plans d'exécution et choisit le plus efficace.

Génération de la Source de Lignes (Row Source Generation) : Le plan d'exécution est

transformé en étapes exécutables.

Exécution : Le serveur exécute le plan d'exécution et renvoie les résultats à l'utilisateur.

3. Optimiseur Basé sur le Coût (CBO - Cost-Based Optimizer)

Objectif : Trouver le plan d'exécution le plus efficace pour une requête SQL.

Étapes de l'Optimiseur :

1. Transformation de la Requête : L'optimiseur transforme la requête pour améliorer le

plan d'exécution.

2. Estimation : L'estimateur calcule la sélectivité, la cardinalité, et le coût de la requête.

Sélectivité : Proportion estimée des lignes retournées par la requête.

Cardinalité : Nombre de lignes retournées par chaque opération du plan

d'exécution.

Coût : Estimation des ressources nécessaires (E/S disque, CPU, mémoire).

3. Génération du Plan : L'optimiseur génère plusieurs plans d'exécution et choisit celui

avec le coût le plus bas.

Chapitre 3: Les Requêtes Hiérarchiques

1. Syntaxe de Base des Requêtes Hiérarchiques
Clause CONNECT BY : Indique la condition qui lie un enregistrement à son parent.

Exemple : CONNECT BY PRIOR employee_id = manager_id (relation parent-enfant).

Clause START WITH : Définit le point de départ de la hiérarchie (la racine de l'arbre).

Exemple : START WITH employee_id = 100 (commence par l'employé dont l'ID est 100).

2. Parcours de l'Arbre
De Haut en Bas : Parcours de la racine vers les feuilles (du parent vers les enfants).

Exemple : CONNECT BY PRIOR employee_id = manager_id .

De Bas en Haut : Parcours des feuilles vers la racine (de l'enfant vers le parent).

Exemple : CONNECT BY PRIOR manager_id = employee_id .

Oracle SQL Tuning 5

 3. Fonctions Utiles pour les Requêtes Hiérarchiques
SYS_CONNECT_BY_PATH : Retourne le chemin complet depuis la racine jusqu'à l'enregistrement
courant.

Exemple :

SELECT LEVEL, first_name, SYS_CONNECT_BY_PATH(first_name, '/') AS

path
FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

CONNECT_BY_ISLEAF : Retourne 1 si l'enregistrement est une feuille (n'a pas d'enfants),

sinon 0 .

Exemple :

SELECT LEVEL, first_name, CONNECT_BY_ISLEAF AS is_leaf

FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

4. Exemples Pratiques
Parcours de Haut en Bas (Parent → Enfant):

SELECT employee_id, first_name, manager_id

FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

Parcours de Bas en Haut (Enfant → Parent):

SELECT employee_id, first_name, manager_id

FROM employees
CONNECT BY PRIOR manager_id = employee_id
START WITH employee_id = 110;

Affichage de la Hiérarchie avec LEVEL :

SELECT LEVEL, LPAD(' ', 3 * (LEVEL - 1)) || first_name AS name

FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

Oracle SQL Tuning 6

 Chapitre 4: Plan d’exécution
1. Introduction au Plan d'Exécution
Rôle de l'Optimiseur : L'optimiseur Oracle génère plusieurs plans d'exécution, estime leur
coût, et choisit le plan le plus efficace pour exécuter une requête SQL.

Facteurs Influençant le Coût :

Chemins d'accès disponibles.

Ressources système disponibles.

Statistiques sur les tables et les index.

2. Statistiques Utilisées par l'Optimiseur

Statistiques sur les Tables :

Rows : Nombre total de lignes dans une table.

Blocks : Nombre de blocs de données occupés par la table.

Avg_row_len : Taille moyenne d'une ligne.

Statistiques sur les Colonnes :

Num_distinct : Nombre de valeurs distinctes dans une colonne.

Low_value et High_value : Valeurs minimale et maximale dans une colonne.

Statistiques sur les Index :

Level : Profondeur de l'index.

Leaf_blocks : Nombre de blocs feuilles.

Clustering_factor : Mesure de l'ordre des blocs de données par rapport à l'index.

3. Collecte des Statistiques

Commande ANALYZE : Utilisée pour collecter des statistiques sur une table ou un index.

Exemple : ANALYZE TABLE employees COMPUTE STATISTICS;

Package DBMS_STATS : Introduit à partir d'Oracle 8, il permet de collecter des statistiques sur
tous les objets de la base de données.

Exemple : DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

4. Plan d'Exécution
Définition : Le plan d'exécution est un ensemble d'opérations que Oracle effectue pour
exécuter une requête SQL. Il est généré par l'optimiseur pendant la phase de Hard Parse.

Objectif : Minimiser les opérations d'entrée/sortie (I/O) et le temps de traitement.

Exemple de Plan d'Exécution :

Oracle SQL Tuning 7

 5. Affichage du Plan d'Exécution
Méthode 1 : Interface SQL Developer :

Utilisez l'option "Explain Plan" pour afficher le plan d'exécution directement dans
l'interface.

Méthode 2 : Utilisation de EXPLAIN PLAN et DBMS_XPLAN.DISPLAY :

Génère le plan d'exécution et le stocke dans la table PLAN_TABLE .

Exemple :

EXPLAIN PLAN FOR SELECT * FROM employees;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Méthode 3 : Utilisation de V$SQL_PLAN

SELECT * FROM V$SQL_PLAN

WHERE SQL_ID = 'your_sql_id';

6. Lecture du Plan d'Exécution

Structure Hiérarchique : Le plan d'exécution est présenté sous forme d'arbre, où les
opérations sont liées par des relations parent/enfant.

Ordre d'Exécution :

1. Commencez par la racine (opération de niveau supérieur).

2. Descendez jusqu'aux feuilles (opérations qui produisent des données).

3. Remontez l'arbre en exécutant les opérations parentes après les enfants.

Exemple de Lecture :

Oracle SQL Tuning 8

 Oracle commence par l'opération 2 (TABLE ACCESS FULL sur COLOURS), puis passe à
l'opération 3 (TABLE ACCESS FULL sur BRICKS), et enfin exécute l'opération 1 (HASH
JOIN).

7. Types d'Opérations dans le Plan d'Exécution

Parcours Complet de Table (FULL TABLE SCAN) : Parcourt toute la table. Coûteux pour les
grandes tables.

Parcours d'Index (INDEX SCAN) : Utilise un index pour accéder aux données.

Index Range Scan : Recherche dans un intervalle de valeurs.

Index Unique Scan : Recherche une seule entrée dans un index unique.

Accès par ROWID : Accède directement aux lignes via leur ROWID.

Algorithmes de Jointure :

Nested Loop Join : Utilisé lorsqu'il y a un index sur une table.

Hash Join : Utilisé lorsqu'il n'y a pas d'index.

Sort Merge Join : Utilisé pour les jointures sur des données triées.

8. Vues de Performance

Oracle SQL Tuning 9

 V$SQL: Contient des informations sur les requêtes SQL exécutées, comme le coût, le
nombre d'exécutions, et les lectures physiques.

V$SQL_PLAN : Affiche les plans d'exécution réels stockés dans le cache de la bibliothèque
(Library Cache).

V$SQLAREA : Contient des statistiques sur l'utilisation de la mémoire pour les requêtes SQL.

V$SQL_WORKAREA : Affiche des informations détaillées sur les zones de travail utilisées pour les

jointures et les tris.

Chapitre 5: Les Hints

1. Qu'est-ce qu'un Hint dans Oracle SQL ?
Un hint est une "suggestion" donnée au moteur SQL pour influencer la manière dont une
requête est exécutée.

Les hints peuvent :

Limiter l'utilisation de mémoire (même si cela ralentit l'exécution).

Forcer ou éviter l'utilisation d'index, indépendamment des choix de l'optimiseur.

2. Limitations des Hints

L'optimiseur peut ignorer un hint si la demande est jugée irréalisable.

Les hints doivent être :

Complémentaires (liés entre eux).

Insérés comme commentaires dans la requête ( /*+ HINT */ ).

Attention : les changements dans le volume de données ou la sélectivité peuvent rendre les
hints inefficaces avec le temps.

3. Syntaxe et Exemple de Hints

Syntaxe générale :

SELECT /*+ HINT_NAME(parameters) */ columns

FROM table;

Exemple : Force l'utilisation d'un Full Table Scan sur la table employees .

SELECT /*+ FULL(e) */ employee_id, last_name

FROM employees e
WHERE last_name LIKE 'A%';

4. Types de Hints Courants

1. FULL(table_alias) : Forcer un Full Table Scan.

Oracle SQL Tuning 10

 2. INDEX(table_name, index_name) : Forcer l'utilisation d'un index précis.

3. USE_NL(table_name) : Forcer une jointure par boucle imbriquée (Nested Loop).

4. USE_HASH(table_name) : Forcer une jointure par hachage.

5. LEADING(table_name) : Définir l'ordre de traitement des tables.

6. ORDERED : Respecter l'ordre des tables dans la clause FROM .

Chapitre 6: les Types de Jointures

1. Types de Jointures
Jointure Interne (INNER JOIN) :

Retourne uniquement les lignes qui ont des correspondances

dans les deux tables.

Syntaxe :

SELECT *
FROM table1
INNER JOIN table2 ON table1.colonne = table2.
colonne;

Jointure Externe Gauche (LEFT JOIN) :

Retourne toutes les lignes de la table de gauche, même si

elles n'ont pas de correspondance dans la table de droite.
Les lignes sans correspondance dans la table de droite
auront des valeurs NULL .

Syntaxe :

SELECT *
FROM table1
LEFT JOIN table2 ON table1.colonne = table2.c
olonne;

Jointure Externe Droite (RIGHT JOIN) :

Retourne toutes les lignes de la table de droite, même si elles

n'ont pas de correspondance dans la table de gauche. Les
lignes sans correspondance dans la table de gauche auront
des valeurs NULL .

Syntaxe :

SELECT *
FROM table1

Oracle SQL Tuning 11

 RIGHT JOIN table2 ON table1.colonne = table2.
colonne;

Jointure Externe Complète (FULL JOIN) :

Retourne toutes les lignes des deux tables, même si elles

n'ont pas de correspondance. Les lignes sans
correspondance auront des valeurs NULL .

Syntaxe :

SELECT *
FROM table1
FULL JOIN table2 ON table1.colonne = table2.c
olonne;

Jointure Cartésienne (CROSS JOIN) :

Retourne le produit cartésien des deux tables, c'est-à-dire toutes les combinaisons
possibles de lignes entre les deux tables.

Syntaxe :

SELECT *
FROM table1
CROSS JOIN table2;

2. Équi-Jointure et Non-Équi-Jointure
Équi-Jointure : Utilise une condition d'égalité pour joindre deux tables.

Exemple :

SELECT *
FROM table1, table2
WHERE table1.colonne = table2.colonne;

Non-Équi-Jointure : Utilise des conditions autres que l'égalité (ex: > , < , BETWEEN , LIKE ,
etc.).

Exemple :

SELECT *
FROM table1, table2
WHERE table1.colonne > table2.colonne;

3. Auto-Jointure (SELF JOIN)

Oracle SQL Tuning 12

 Définition : Une jointure d'une table avec elle-même, souvent utilisée pour comparer des
lignes dans la même table.

Exemple :

SELECT e1.nom AS Employe, e2.nom AS Superieur

FROM Employes e1, Employes e2
WHERE e1.id_superieur = e2.id_employee;

Chapitre 9: Les Vues

1- Vue Classique vs Vue Matérialisée
Vue Classique :

Une table virtuelle basée sur une ou plusieurs tables.

Les données sont obtenues dynamiquement via une clause SELECT .

Exemple :

CREATE VIEW Les_emp_dept_10 AS

SELECT ename, job, sal, deptno
FROM emp
WHERE deptno = 10;

Vue Matérialisée :

Stocke physiquement des données précalculées pour améliorer les performances.

Avantages :

Précalcul des données complexes (agrégations, jointures).

Réduction des coûts pour des requêtes fréquentes ou coûteuses.

Syntaxe de base :

CREATE MATERIALIZED VIEW MV1

Build {IMMEDIATE | DEFERRED} -- Mode de construction
REFRESH {COMPLETE|FAST|FORCE} -- Méthode de rafraîchissement des don
nées
ON {DEMAND | COMMIT}
{Disable|Enable Query rewrite} -- Mode de réécriture des requêtes pa
r l’optimiseur
AS SELECT * FROM emp;

Oracle SQL Tuning 13

 2- Rafraîchissement des Vues Matérialisées
Modes de Rafraîchissement :

Complete : Recalcule entièrement les données. (définie avec BUILD IMMEDIATE)

Fast : Met à jour uniquement les données modifiées. Requiert un journal de vue
matérialisée.

Exemple pour créer un journal :

CREATE MATERIALIZED VIEW LOG ON NomDeLaTable;

Force : Tente un Fast , sinon effectue un Complete .

On Demand : Actualisation manuelle sur demande.

Exemple :

EXECUTE DBMS_MVIEW.REFRESH('NomVM');

On Commit : Mise à jour après validation ( COMMIT ).

Chapitre 10: la Normalisation

1. Les Dépendances Fonctionnelles (DF)
Définition :

Une DF existe si une valeur d’attribut (ou un ensemble d’attributs) détermine de manière
unique une autre valeur.

Notation : X → Y (X détermine Y).

Exemple :
Dans une table EMPLOYE(numero, nom, adresse, salaire) :

Oracle SQL Tuning 14

 Le numero détermine sans ambiguïté le nom , l’ adresse , etc.

2. Les Axiomes d’Armstrong

Règles utilisées pour travailler sur les dépendances fonctionnelles :

1. Réflexivité : Si Y ⊆ X, alors X → Y .

2. Augmentation : Si X → Y , alors XW → YW (W est un ensemble d’attributs).

3. Transitivité : Si X → Y et Y → Z , alors X → Z .

4. Pseudo-transitivité : Si X → Y et YW → Z , alors XW → Z .

5. Union : Si X → Y et X → Z , alors X → YZ .

6. Décomposition : Si X → YZ , alors X → Y et X → Z .

3. Les Formes Normales (FN)

1FN (Première Forme Normale) :

Chaque attribut contient des valeurs atomiques (non divisibles).

2FN (Deuxième Forme Normale) :

Respecte la 1FN et tous les attributs non-clés dépendent entièrement de la clé primaire.

3FN (Troisième Forme Normale) :

Respecte la 2FN et élimine les dépendances transitives entre les attributs non-clés.

2FN 3FN

Supprime les dépendances partielles : les Supprime les dépendances transitives : les
colonnes non-clés doivent dépendre entièrement colonnes non-clés ne doivent pas dépendre
de la clé primaire. d’autres colonnes non-clés.

Une table qui respecte la 1FN et élimine les Une table qui respecte la 2FN et élimine les
dépendances partielles est en 2FN. dépendances transitives est en 3FN.

Exemple :
Nous avons une table appelée COMMANDES qui contient les informations suivantes :

CommandeID ClientNom ClientAdresse Produit Quantité PrixUnitaire

1 Dupont 12 rue A Ordinateur 1 1000

1 Dupont 12 rue A Souris 2 20

2 Martin 45 rue B Clavier 1 50

Étape 1 : 1ère Forme Normale (1FN)

Règle de la 1FN : Toutes les colonnes doivent contenir des valeurs atomiques (indivisibles).
la table actuelle respecte déjà la 1FN.

Étape 2 : 2ème Forme Normale (2FN)

Oracle SQL Tuning 15

 Règle de la 2FN : La table doit respecter la 1FN, et tous les attributs non-clés doivent dépendre
entièrement de la clé primaire.

Clé primaire actuelle : (CommandeID, Produit) (car chaque produit d'une commande est
unique).

Problème : ClientNom et ClientAdresse dépendent uniquement de CommandeID , pas de la

combinaison (CommandeID, Produit) . Ces colonnes provoquent une dépendance partielle.

Solution : Séparer les informations liées au client dans une nouvelle table.

Table CommandesProduits :

CommandeID Produit Quantité PrixUnitaire

1 Ordinateur 1 1000

1 Souris 2 20

2 Clavier 1 50

Table Clients :

CommandeID ClientNom ClientAdresse

1 Dupont 12 rue A

2 Martin 45 rue B

Étape 3 : 3ème Forme Normale (3FN)

Règle de la 3FN : La table doit respecter la 2FN, et les attributs non-clés ne doivent pas avoir
de dépendance transitive.

Dans la table Clients , ClientAdresse dépend de ClientNom (transitivement via CommandeID ).

Solution : Créer une table séparée pour stocker les informations des clients.

Table Commandes :

CommandeID ClientID

1 101

2 102

Table Produits :

CommandeID Produit Quantité PrixUnitaire

1 Ordinateur 1 1000

1 Souris 2 20

2 Clavier 1 50

Table Clients :

ClientID ClientNom ClientAdresse

101 Dupont 12 rue A

102 Martin 45 rue B

Oracle SQL Tuning 16