Oracle

Module 1 : Outils de Supervision et Diagnostic

1.1 Référentiel AWR (Automatic Workload Repository)

Concept : L'AWR collecte automatiquement des statistiques de performance toutes les heures par défaut, stockant des informations sur
l'utilisation du CPU, de la mémoire, des E/S, ainsi que les plans d'exécution des requêtes SQL.

Exemple :

SQL
-- Générer un rapport AWR entre deux snapshots
@?/rdbms/admin/[Link]

Pourquoi c'est important : L'AWR permet d'identifier les requêtes problématiques et les goulets d'étranglement système sur des périodes
prolongées.

1.2 ASH (Active Session History)

Concept : ASH capture une image des sessions actives toutes les secondes pendant 60 minutes, stockant ces données en mémoire avant
de les archiver.

Différence clé AWR vs ASH :

AWR : Vue globale sur des périodes longues (minimum 10 minutes), idéal pour les tendances
ASH : Vue détaillée en temps quasi-réel (seconde par seconde), parfait pour les problèmes transitoires

Exemple d'utilisation :

SQL
-- Voir les sessions actives
SELECT * FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > SYSDATE - 1/24;

1.3 Enterprise Manager (OEM)

Interface graphique permettant de visualiser les performances et d'identifier les requêtes à problème.

1.4 Automatic Diagnostic Repository (ADR)

Concept : Référentiel centralisé basé sur des fichiers, stockant les données diagnostiques de la base de données.

Contenu :

Fichiers trace
Journal d'alerte (alert log)
Rapports du moniteur de santé
Données d'incidents

Outils d'accès :

bash
# Utilisation d'ADRCI (command line tool)
adrci> show home
adrci> show alert
adrci> show incident
 adrci> purge

Module 2 : Analyse d'Exécution des Requêtes

2.1 Parsing (Analyse Syntaxique)

Hard Parse vs Soft Parse :

Hard Parse : Processus complet d'analyse, transformation et optimisation d'une requête (coûteux en ressources)
Soft Parse : Réutilisation d'un plan d'exécution existant dans le shared pool (beaucoup plus rapide)

Exemple de Hard Parse :

SQL
ALTER SYSTEM FLUSH SHARED_POOL;
SELECT * FROM dual WHERE dummy='LITERAL1';
SELECT * FROM dual WHERE dummy='LITERAL2'; -- Nouveau hard parse

Exemple de Soft Parse :

SQL
SELECT * FROM dual WHERE dummy='LITERAL2';
-- Même requête, soft parse (le compteur d'exécutions augmente)

2.2 Variables Bind

Concept : Les variables bind permettent de réutiliser les plans d'exécution en paramétrant les valeurs littérales.

Exemple :

SQL
-- Mauvaise pratique (littéraux)
SELECT * FROM emp WHERE deptno = 10;
SELECT * FROM emp WHERE deptno = 20;

-- Bonne pratique (variables bind)

VARIABLE deptno NUMBER;
EXEC :deptno := 10;
SELECT * FROM emp WHERE deptno = :deptno;
EXEC :deptno := 20;
SELECT * FROM emp WHERE deptno = :deptno; -- Soft parse!

2.3 EXPLAIN PLAN

Concept : Permet de voir le plan d'exécution sans exécuter la requête.

Exemple :

SQL
EXPLAIN PLAN FOR
SELECT [Link], [Link]
FROM emp e JOIN dept d ON [Link] = [Link]
WHERE [Link] > 2000;

-- Afficher le plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
2.4 AUTOTRACE
Concept : Utilitaire SQL*Plus qui exécute la requête et affiche des statistiques de performance.

Exemple :

SQL
SET AUTOTRACE ON
SELECT * FROM emp WHERE sal > 2000;

Module 3 : Techniques d'Optimisation des Requêtes Basiques

3.1 Opérateur EXISTS vs IN

Concept : L'opérateur EXISTS est souvent plus performant que IN pour les sous-requêtes corrélées.

Scénario : Afficher tous les employés qui ont un département correspondant dans la table DEPT.

Méthode 1 : Jointure interne (plus lente)

SQL
SELECT ename, sal, [Link]
FROM emp
JOIN dept ON [Link] = [Link];

Méthode 2 : Sous-requête simple avec IN (mieux)

SQL
SELECT ename, sal, deptno
FROM emp
WHERE deptno IN (SELECT deptno FROM dept);

Méthode 3 : Sous-requête corrélée avec EXISTS (la plus rapide)

SQL
SELECT ename, sal, deptno
FROM emp
WHERE EXISTS (SELECT null FROM dept WHERE [Link] = [Link]);

Pourquoi EXISTS est plus rapide :

S'arrête dès qu'une correspondance est trouvée

N'a pas besoin de construire une liste complète de valeurs

Évite les problèmes de valeurs NULL

3.2 Gestion des requêtes Top-N

Concept : Techniques pour récupérer efficacement les N premiers résultats d'une requête triée.

Exemple traditionnel (avant Oracle 12c) :

SQL
SELECT * FROM (
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
 ) WHERE rownum <= 5;

Syntaxe moderne (Oracle 12c+) :

SQL
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

Option WITH TIES :

SQL
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;
-- Inclut tous les employés ayant le même salaire que la 5ème position

Module 4 : Indexation Optimale

4.1 Types d'Index

4.1.1 Index B-Tree

Concept : Structure d'index équilibrée (B = Balanced) où les valeurs sont stockées dans une structure arborescente triée.

Fonctionnement détaillé :

Bloc racine : Pointe vers les blocs branches

Blocs branches : Pointent vers les blocs feuilles
Blocs feuilles : Contiennent les paires (valeur clé → ROWID) et sont chaînés entre eux

Exemple concret :

1) CONTEXTE: Index sur EMP_ID

- Chaque ligne possède un ROWID (ex: AAA12.0001.01)
- Un index contient des paires: EMP_ID → ROWID

2) Première insertion: création de la racine

- Insertion de 5, 12, 18
- Bloc racine/feuille: [5→ROWID(5), 12→ROWID(12), 18→ROWID(18)]

3) Insertion de 27 → Split
- Si un bloc peut contenir 3 clés, 27 provoque un débordement
- Trie: 5, 12, 18, 27
- Clé médiane: 18
- Nouvel arbre:
[18] ← racine
/ \
[5,12] [18,27] ← feuilles chaînées

Avantages de la structure B-Tree :

Hauteur logarithmique (généralement 2-3 niveaux même pour des tables de millions de lignes)
 Recherche rapide: coût moyen = log(n) vs n/2 pour un scan linéaire
Parcours séquentiel efficace grâce aux feuilles chaînées

Quand l'utiliser :

Colonnes à haute cardinalité (beaucoup de valeurs distinctes)

Clés primaires et clés étrangères

Colonnes utilisées dans les clauses WHERE avec des prédicats d'égalité

Exemple :

SQL
-- Création (B-Tree est le type par défaut)
CREATE INDEX idx_emp_ename ON emp(ename);

-- Utilisation
SELECT * FROM emp WHERE ename = 'SMITH'; -- Utilisera l'index

4.1.2 Index Bitmap

Concept : Structure d'index utilisant des masques de bits pour pointer vers de multiples lignes simultanément.

Fonctionnement :

Pour chaque valeur distincte, un bit est défini pour chaque ligne de la table
Bit = 1 si la ligne a cette valeur, 0 sinon
Les opérations logiques (AND, OR) permettent de combiner plusieurs conditions

Avantages :

Très efficace pour les colonnes à faible cardinalité

Possibilité de combiner plusieurs index bitmap
Gestion efficace des valeurs NULL
Compression élevée

Inconvénients :

Problèmes de verrouillage sur les tables OLTP très actives

Mauvaise performance sur les tables avec de fréquentes opérations DML

Quand l'utiliser :

Colonnes à faible cardinalité (sexe, statut, type, etc.)

Environnements d'entrepôt de données (OLAP)
Requêtes avec de multiples conditions combinées avec AND/OR

Exemple :

SQL
CREATE BITMAP INDEX idx_emp_job ON emp(job);

-- Efficace pour les requêtes comme:

SELECT COUNT(*) FROM emp
WHERE job = 'CLERK' AND deptno = 20;

4.1.3 Index Composites

Concept : Index composé de plusieurs colonnes, permettant d'optimiser les requêtes filtrant sur ces combinaisons.

Règles d'utilisation :
 L'ordre des colonnes dans l'index est crucial
Placez d'abord les colonnes les plus sélectives

Les colonnes utilisées avec des opérateurs d'égalité (=) devraient précéder celles avec des intervalles (>, <, BETWEEN)

Quand l'utiliser :

Requêtes filtrant simultanément sur plusieurs colonnes

Combinaisons fréquentes dans les clauses WHERE
Optimisation des jointures sur plusieurs colonnes

Exemple :

SQL
-- Création d'un index composite
CREATE BITMAP INDEX idx_sales_color_size
ON sales(color, tsize);

-- Utilisé quand:
SELECT COUNT(*) FROM sales
WHERE color = 'BLACK' AND tsize = 'L';

-- Non utilisé si:

SELECT COUNT(*) FROM sales
WHERE tsize = 'L'; -- La première colonne de l'index n'est pas utilisée

4.1.4 Index Basés sur Fonctions

Concept : Index créé sur une expression ou une fonction appliquée à une colonne.

Quand l'utiliser :

Requêtes filtrant sur des expressions (UPPER(col), TRUNC(date), etc.)

Optimisation des recherches insensibles à la casse
Calculs fréquents sur les données

Exemple :

SQL
-- Sans index fonctionnel:
SELECT * FROM emp WHERE INITCAP(ename) = 'Smith'; -- Full scan!

-- Avec index fonctionnel:

CREATE INDEX idx_emp_ename_initcap
ON emp(INITCAP(ename));

-- Maintenant la requête utilisera l'index

SELECT * FROM emp WHERE INITCAP(ename) = 'Smith';

4.2 Règles d'Or pour l'Indexation

Les index B-Tree sont inefficaces pour récupérer plus de 5-10% des lignes d'une table
Les index bitmap sont excellents pour les combinaisons de conditions (AND/OR)

Les index occupent de l'espace et ralentissent les opérations DML

Les statistiques doivent être régulièrement mises à jour pour que l'optimiseur choisisse les bons index
Évitez les index redondants ou peu utilisés
Module 5 : Stratégies de Jointure

5.1 Types de Jointures Physiques

5.1.1 Nested Loops Join

Concept : Algorithme similaire à des boucles imbriquées.

Fonctionnement :

for (chaque ligne de la table externe) {

for (chaque ligne de la table interne) {
vérifier si correspondance
}
}

Quand Oracle le choisit :

Tables de petite taille

Présence d'un index efficace sur la table interne
Jointures avec des prédicats d'égalité

Exemple :

SQL
SELECT e.last_name, [Link], d.department_name
FROM employees e, departments d
WHERE d.department_name IN ('Marketing','Sales')
AND e.depart_id = d.depart_id;
-- Employees (107 lignes), Departments (27 lignes)
-- Nested Loops sera probablement choisi

5.1.2 Hash Join

Concept : Algorithme utilisant une table de hachage en mémoire pour les jointures.

Fonctionnement détaillé :

1. Phase BUILD : Construction de la table de hachage

Oracle prend la plus petite table
Calcule un hash sur la clé de jointure
Range les lignes dans des "buckets" en mémoire (PGA)

2. Phase PROBE : Sondage de la table de hachage

Oracle lit la grande table
Pour chaque ligne, calcule le même hash
Va directement au bon bucket pour comparaison

Exemple concret :

1️⃣ Phase BUILD:

cust_id → hash(cust_id) → bucket
101 → 1 → B1
205 → 5 → B5
387 → 7 → B7

2️⃣ Table de hash en mémoire:

Bucket 1: [cust_id=101]
 Bucket 5: [cust_id=205]
Bucket 7: [cust_id=387]

3️⃣ Phase PROBE:

order.cust_id=205 → hash(205)=5 → Bucket 5 → MATCH

Quand Oracle le choisit :

Tables de grande taille

Prédicats d'égalité
Suffisamment de mémoire PGA disponible

Cas problématique : Si la table de hash ne tient pas en mémoire, Oracle partitionne le hash et écrit sur TEMP, ce qui dégrade les
performances.

5.1.3 Sort Merge Join

Concept : Jointure basée sur le tri des tables avant la fusion.

Fonctionnement :

1. Trie les deux tables sur la clé de jointure

2. Fusionne les résultats triés en un seul passage

Quand Oracle le choisit :

Tables déjà triées sur la clé de jointure

Prédicats non-égaux (BETWEEN, >, <)
Grandes tables où le hash join serait trop coûteux en mémoire

5.2 Types de Jointures Logiques

5.2.1 Inner Join

Concept : Retourne uniquement les lignes ayant des correspondances dans les deux tables.

Syntaxe :

SQL
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

5.2.2 Outer Join

Left Outer Join :

SQL
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
-- OU
SELECT column_name(s)
FROM table1, table2
WHERE table1.column_name = table2.column_name(+);

Right Outer Join :

 SQL
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
-- OU
SELECT column_name(s)
FROM table1, table2
WHERE table1.column_name(+) = table2.column_name;

Full Outer Join :

SQL
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

5.2.3 Cartesian Join

Concept : Jointure croisée produisant le produit cartésien des deux tables.

Quand cela arrive :

Tables très petites

L'optimiseur suppose qu'une seule ligne sera retournée
Statistiques obsolètes ou manquantes
Oubli de la condition de jointure

À éviter : Généralement involontaire et très coûteuse en ressources.

5.3 Résolution des Problèmes de Jointure

5.3.1 Mauvaise Estimation de Cardinalité

Causes fréquentes :

Statistiques manquantes ou obsolètes

Skew des données (une valeur est beaucoup plus fréquente)
Fonctions appliquées aux colonnes dans les clauses WHERE
Prédicats multiples sur une même table

Solutions :

SQL
-- 1. Collecter des statistiques représentatives
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME');

-- 2. Créer des histogrammes pour les données skewées

EXEC DBMS_STATS.GATHER_TABLE_STATS(
'SCHEMA', 'TABLE_NAME',
METHOD_OPT => 'FOR COLUMNS SIZE 254 column_name'
);

-- 3. Créer des statistiques étendues pour les colonnes corrélées

EXEC DBMS_STATS.CREATE_EXTENDED_STATS(
'SCHEMA', 'TABLE_NAME', '(col1, col2)'
);
5.3.2 Plans d'Exécution Adaptatifs (Oracle 12c+)
Concept : Capacité de l'optimiseur à changer de stratégie pendant l'exécution.

Fonctionnement :

Plusieurs sous-plans sont pré-calculés

Un "Statistics Collector" surveille le nombre de lignes traitées

Si le seuil est dépassé, le plan bascule vers l'alternative

Décision finale prise après le premier passage

Exemple :

Statistics Collector
│
├── Nested Loops (valide pour 1-100 lignes)
└── Hash Join (utilisé si >100 lignes)

5.3.3 Ordre des Jointures

Principe : L'ordre dans lequel les tables sont jointes affecte considérablement les performances.

Règles de l'optimiseur :

1. Commencer par les jointures garantissant au maximum une ligne

2. Pour les outer joins, la table avec l'opérateur (+) doit venir après

3. Si la fusion de vues n'est pas possible, joindre d'abord les tables dans la vue

Comment déterminer l'ordre :

SQL
-- Utiliser le hint LEADING pour voir l'ordre
SELECT /*+ LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1") */
d.dept_name, [Link]
FROM departments d, employees e
WHERE e.dept_id = d.dept_id;

Module 6 : Techniques Avancées d'Optimisation

6.1 Hints (Indices)

Concept : Directives pour guider l'optimiseur vers un plan d'exécution spécifique.

Syntaxe : /*+ hint_name(table_name [index_name]) */

Exemples courants :

SQL
-- Forcer l'utilisation d'un index
SELECT /*+ INDEX(emp idx_emp_ename) */ *
FROM emp WHERE ename = 'SMITH';

-- Forcer l'ordre des jointures

SELECT /*+ LEADING(dept emp) */ [Link], [Link]
FROM dept d JOIN emp e ON [Link] = [Link];

-- Éviter un index
 SELECT /*+ NO_INDEX(emp idx_emp_sal) */ *
FROM emp WHERE sal > 1000;

Règles importantes :

Les hints sont des directives, pas des commandes (l'optimiseur peut les ignorer)
Pas d'espace entre /* et +
Les hints contradictoires sont ignorés (FULL vs INDEX sur la même table)
Aucune notification quand un hint est ignoré

Cas d'erreur courants :

❌ Contradiction: FULL vs INDEX

SQL
--
SELECT /*+ FULL(emp) INDEX(emp emp_idx_emp_id) */ *
FROM employees emp WHERE emp_id = 100;

-- ❌ Contradiction: USE_NL vs USE_HASH

SELECT /*+ USE_NL(emp) USE_HASH(emp) */ [Link], dept.dept_name
FROM employees emp JOIN departments dept ON emp.dept_id = dept.dept_id;

6.2 Vues Inline (Inline Views)

Concept : Sous-requêtes dans la clause FROM, créant des tables virtuelles temporaires.

Avantages :

Éliminent les calculs redondants

Simplifient les requêtes complexes
Améliorent la lisibilité
Aident l'optimiseur à choisir de meilleurs chemins d'accès

Exemple avec calculs redondants :

SQL
-- Sans vue inline (calculs redondants)
SELECT orderid, color, qty,
(cost + 0.20 * cost) AS gross,
(cost + 0.20 * cost) * 0.10 AS tax
FROM sales;

-- Avec vue inline (calcul une seule fois)

SELECT orderid, color, qty, gross, gross * 0.10 AS tax
FROM (
SELECT orderid, color, qty, cost,
(cost + 0.20 * cost) AS gross
FROM sales
) s;

Pour les requêtes Top-N :

SQL
-- Méthode traditionnelle
SELECT * FROM (
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
) WHERE rownum <= 5;
 -- Méthode moderne (12c+)
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

6.3 Tables Temporaires

Concept : Tables dont les données sont visibles uniquement dans une session.

Création :

SQL
-- Par défaut: ON COMMIT DELETE ROWS (données supprimées au COMMIT)
CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER,
name VARCHAR2(50)
) ON COMMIT DELETE ROWS;

-- Pour conserver jusqu'à la fin de session

CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER,
name VARCHAR2(50)
) ON COMMIT PRESERVE ROWS;

Avantages :

Réduisent la contention sur les tables principales

Améliorent les performances en travaillant sur des sous-ensembles de données
Minimisent l'utilisation du cache buffer

Exemple d'utilisation :

SQL
-- Créer une table temporaire avec un sous-ensemble de données
CREATE GLOBAL TEMPORARY TABLE black_tshirts (
orderid NUMBER,
color VARCHAR2(10),
tsize VARCHAR2(4),
qty NUMBER,
cost NUMBER
) ON COMMIT PRESERVE ROWS;

-- Charger les données pertinentes

INSERT INTO black_tshirts
SELECT * FROM sales WHERE color = 'BLACK';

-- Travailler sur le sous-ensemble efficacement

SELECT * FROM black_tshirts WHERE tsize = 'L';

6.4 Expressions de Table Communes (CTE)

Concept : Similaires aux vues inline mais définies avant la requête principale avec la clause WITH.

Syntaxe :

SQL
WITH cte_name AS (
SELECT...
FROM...
WHERE...
 )
SELECT * FROM cte_name;

Avantages par rapport aux vues inline :

Possibilité de référencer le même ensemble de résultats plusieurs fois

Meilleure lisibilité (logique définie avant l'utilisation)
Possibilité de requêtes récursives

Exemple d'optimisation :

SQL
-- Sans CTE (agrégation répétée)
SELECT color, SUM(qty)
FROM sales
GROUP BY color
HAVING SUM(qty) > (
SELECT SUM(qty)
FROM sales
WHERE color = 'Blue'
);

-- Avec CTE (agrégation effectuée une seule fois)

WITH summary AS (
SELECT color, SUM(qty) AS total_qty
FROM sales
GROUP BY color
)
SELECT color, total_qty
FROM summary
WHERE total_qty > (
SELECT total_qty
FROM summary
WHERE color = 'Blue'
);

6.5 Vues Matérialisées (MViews)

Concept : Tables physiques stockant le résultat d'une requête complexe, pouvant être rafraîchies périodiquement.

Création :

SQL
CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT color, tsize, SUM(qty) AS total_qty, AVG(cost) AS avg_cost
FROM sales
GROUP BY color, tsize;

Options de rafraîchissement :

BUILD IMMEDIATE : Remplissage immédiat après création

BUILD DEFERRED : Remplissage différé
REFRESH FAST : Utilise les logs de vue matérialisée (seulement les changements)
REFRESH COMPLETE : Recalcule la vue entière
REFRESH FORCE : Oracle choisit entre FAST et COMPLETE
 ON COMMIT : Rafraîchissement automatique à chaque COMMIT
ON DEMAND : Rafraîchissement manuel nécessaire

Rafraîchissement manuel :

SQL
-- Rafraîchir une vue matérialisée
BEGIN
DBMS_MVIEW.REFRESH('mv_sales_summary', 'F'); -- 'F' = FAST, 'C' = COMPLETE
END;
/

Cas d'utilisation idéaux :

Requêtes analytiques complexes sur de grands volumes de données

Environnements de reporting où les données peuvent être légèrement obsolètes
Scénarios où la performance est plus critique que la fraîcheur des données

Exemple concret :

SQL
-- Création d'une vue matérialisée pour le reporting
CREATE MATERIALIZED VIEW state_summary AS
SELECT [Link],
SUM([Link]) AS "Total Cost",
SUM([Link]) AS "Total Quantity"
FROM sales s
JOIN customers c ON [Link] = [Link]
GROUP BY [Link];

-- Utilisation (très rapide!)

SELECT * FROM state_summary WHERE statename = 'New York';

Bonnes pratiques :

N'utilisez des MViews que pour des requêtes complexes et coûteuses

Évitez les MViews contenant plus de 70% des données des tables source
Planifiez le rafraîchissement pendant les périodes creuses
Surveillez l'espace disque utilisé par les MViews

Cheat Sheet: Oracle SQL Tuning

🔍 Diagnostic & Surveillance

ASH : SELECT * FROM V$ACTIVE_SESSION_HISTORY (problèmes transitoires)
AWR : Rapports pour analyse historique des performances
Top SQL : Identifiez les requêtes consommant le plus de ressources
ADR : adrci> show alert (journal d'alerte), adrci> show incident (incidents)

📊 Plan d'Exécution
EXPLAIN PLAN : Voir le plan sans exécuter

AUTOTRACE : Exécuter + voir statistiques

DBMS_XPLAN : SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR())
 Cardinalité : Nombre estimé de lignes - vérifiez son exactitude!

⚡ Parsing
Hard Parse : Évitez-le! Coûteux en CPU
Soft Parse : Réutilisation des plans existants
Variables Bind : Clé pour réduire les hard parses
SQL
VARIABLE deptno NUMBER;
EXEC :deptno := 10;
SELECT * FROM emp WHERE deptno = :deptno;

📑 Indexation
Type
Quand Utiliser Exemple Notes
d'Index

Structure équilibrée, 2-3 niveaux

B-Tree Haute cardinalité, clés primaires CREATE INDEX idx ON table(col)
max

Faible cardinalité, entrepôts de CREATE BITMAP INDEX idx ON

Bitmap Éviter sur tables OLTP très actives
données table(col)

CREATE INDEX idx ON

Composite Requêtes avec plusieurs conditions Ordre des colonnes crucial
table(col1,col2)

CREATE INDEX idx ON Utiliser exactement la même

Fonction Filtres sur expressions
table(UPPER(col)) expression

🔗 Stratégies de Jointure
Type de Jointure Quand Utilisée Caractéristiques

Nested Loops Petites tables, index sur table interne Comme des boucles imbriquées

Hash Join Grandes tables, prédicats d'égalité Requiert suffisamment de PGA

Sort Merge Tables déjà triées, prédicats non-égaux Deux phases: tri puis fusion

Type Logique Syntaxe Oracle Utilisation

Inner Join JOIN ou WHERE [Link] = [Link] Correspondances dans les deux tables

Left Outer LEFT JOIN ou WHERE [Link] = [Link](+) Toutes les lignes de gauche + correspondances

Right Outer RIGHT JOIN ou WHERE [Link](+) = [Link] Toutes les lignes de droite + correspondances

Full Outer FULL OUTER JOIN Toutes les lignes des deux tables

🚀 Techniques Avancées
Hints : /*+ INDEX(table index_name) */ (utilisez avec prudence!)
CTE : WITH cte_name AS (SELECT...) SELECT... (réutilisation des résultats)

Tables Temp : CREATE GLOBAL TEMPORARY TABLE... (données session-spécifiques)

MViews : CREATE MATERIALIZED VIEW mv_name... (données pré-calculées)

⚡ Optimisation EXISTS vs IN
SQL
-- Méthode la plus lente
SELECT ename, sal FROM emp JOIN dept ON [Link] = [Link];

-- Méthode moyenne
 SELECT ename, sal FROM emp WHERE deptno IN (SELECT deptno FROM dept);

-- Méthode la plus rapide

SELECT ename, sal FROM emp WHERE EXISTS (
SELECT null FROM dept WHERE [Link] = [Link]
);

🔧 Bonnes Pratiques
1. Évitez les SELECT * - Sélectionnez uniquement les colonnes nécessaires
2. Indexez les colonnes de jointure et de WHERE
3. Utilisez des variables bind pour les applications
4. Analysez les statistiques régulièrement : DBMS_STATS.GATHER_TABLE_STATS

5. Testez les plans d'exécution avant de déployer en production

6. Évitez les fonctions sur les colonnes indexées dans les clauses WHERE
7. Privilégiez EXISTS aux sous-requêtes IN pour les grandes tables
8. Utilisez les CTE pour éviter les calculs redondants
9. Envisagez les MViews pour les requêtes analytiques complexes
10. Surveillez les waits events pour identifier les goulets d'étranglement

⚠️ Pitfalls Courants
Trop d'index ralentit les INSERT/UPDATE/DELETE
Les index bitmap sur tables OLTP = problèmes de verrouillage
Les hints peuvent devenir obsolètes après des changements de schéma
Les MViews occupent de l'espace disque et nécessitent un rafraîchissement
Les statistiques obsolètes entraînent de mauvaises décisions d'optimisation
Les jointures manquantes créent des produits cartésiens coûteux
Les variables bind non utilisées causent des hard parses excessifs
Les CTE et vues inline mal conçues peuvent dégrader les performances

Module Complémentaire : Concepts Fondamentaux et Limites Techniques

C.1 Influence de l'utilisateur connecté et des paramètres de session sur l'optimiseur

Concept clé : L'optimiseur Oracle prend en compte le contexte d'exécution, y compris l'utilisateur connecté et les paramètres de session.

Paramètres influençant l'optimiseur :

OPTIMIZER_MODE : ALL_ROWS (par défaut), FIRST_ROWS, FIRST_ROWS_n

CURSOR_SHARING : EXACT (par défaut), FORCE, SIMILAR
OPTIMIZER_INDEX_COST_ADJ : Ajuste le coût des index par rapport aux scans de table
OPTIMIZER_INDEX_CACHING : Pourcentage de blocs d'index supposés dans le cache

Impact de l'utilisateur :

Les privilèges affectent les chemins d'accès disponibles

Les statistiques peuvent être différentes par schéma
Les vues et synonymes peuvent être résolus différemment

Exemple pratique :
 SQL
-- Pour voir les paramètres de session actuels
SELECT name, value FROM v$parameter WHERE name LIKE 'optimizer%';

-- Pour modifier temporairement un paramètre de session

ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;

Pour les examens : Un changement de schéma peut modifier complètement le plan d'exécution d'une requête identique.

C.2 Tables temporaires : structure globale vs données session-spécifiques

Concept clé : Dans Oracle, la structure des tables temporaires est globale (définie dans le dictionnaire de données), mais les données sont
entièrement session-spécifiques.

Différences fondamentales :

Structure : Partagée par tous les utilisateurs ayant le privilège SELECT

Données : Visibles uniquement par la session qui les a insérées
Espace de stockage : Utilise le tablespace TEMP par défaut
Nettoyage : Automatique à la fin de la transaction ou de la session

Comportement lors des commits :

SQL
-- ON COMMIT DELETE ROWS (comportement par défaut)
CREATE GLOBAL TEMPORARY TABLE temp_orders (
order_id NUMBER,
amount NUMBER
) ON COMMIT DELETE ROWS;

-- ON COMMIT PRESERVE ROWS (conserve les données jusqu'à la fin de session)

CREATE GLOBAL TEMPORARY TABLE temp_orders (
order_id NUMBER,
amount NUMBER
) ON COMMIT PRESERVE ROWS;

Pour les examens : Deux sessions peuvent insérer des données dans la même table temporaire simultanément sans jamais voir les
données de l'autre session.

C.3 Ordre logique d'exécution d'une requête SQL

Concept clé : L'ordre d'évaluation logique d'une requête SQL est différent de l'ordre d'écriture dans la syntaxe.

Ordre d'exécution :

1. FROM : Détermine les tables sources et effectue les jointures

2. WHERE : Filtre les lignes avant agrégation
3. GROUP BY : Regroupe les lignes selon les colonnes spécifiées
4. HAVING : Filtre les groupes après agrégation
5. SELECT : Sélectionne et calcule les colonnes finales
6. ORDER BY : Trie le résultat final

Exemple illustratif :

SQL
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department_id
 HAVING AVG(salary) > 5000
ORDER BY avg_salary DESC;

Points clés pour les examens :

Les alias définis dans SELECT ne sont pas disponibles dans WHERE ou GROUP BY
HAVING s'applique après l'agrégation, WHERE avant
ORDER BY est la dernière étape et peut utiliser des alias de colonnes

C.4 Types de données CHAR vs VARCHAR2 (longueur fixe vs variable)

Concept clé : La différence fondamentale réside dans la gestion de l'espace de stockage et du padding.

Caractéristique CHAR(n) VARCHAR2(n)

Stockage Toujours n octets 1 à n octets selon le contenu

Padding Rempli avec des espaces Aucun padding

Comparaison Avec padding (trim implicite) Sans padding

Performance Plus rapide pour des données fixes Meilleure utilisation de l'espace

Limite max 2000 octets 4000 octets (ou 32767 avec extended)

Exemples pratiques :

SQL
-- CHAR(10) occupe toujours 10 octets
CREATE TABLE fixed_length (
code CHAR(10) -- 'ABC' stocké comme 'ABC '
);

-- VARCHAR2(10) occupe seulement l'espace nécessaire

CREATE TABLE variable_length (
code VARCHAR2(10) -- 'ABC' stocké comme 'ABC'
);

-- Comparaison avec CHAR

SELECT * FROM fixed_length WHERE code = 'ABC'; -- Fonctionne car trim implicite
SELECT * FROM fixed_length WHERE code = 'ABC '; -- Fonctionne aussi

Pour les examens : Les index sur les colonnes CHAR peuvent être moins efficaces à cause du padding, surtout pour les recherches
partielles.

C.5 Suppression des doublons : DISTINCT, GROUP BY, UNION vs UNION ALL
Concept clé : Différentes méthodes pour éliminer les doublons avec des performances variables.

Méthodes disponibles :

1. DISTINCT : Élimine les doublons sur toutes les colonnes sélectionnées

2. GROUP BY : Élimine les doublons sur les colonnes de regroupement
3. UNION : Combine deux jeux de résultats et élimine les doublons
4. UNION ALL : Combine deux jeux de résultats sans éliminer les doublons (plus rapide)

Comparaison des performances :

SQL
-- Méthode 1: DISTINCT (la plus simple mais pas toujours la plus rapide)
SELECT DISTINCT department_id FROM employees;

-- Méthode 2: GROUP BY (souvent plus efficace avec des index)

 SELECT department_id FROM employees GROUP BY department_id;

-- Méthode 3: UNION (élimine les doublons entre deux requêtes)

SELECT department_id FROM employees WHERE salary > 5000
UNION
SELECT department_id FROM employees WHERE hire_date > '2020-01-01';

-- Méthode 4: UNION ALL (beaucoup plus rapide si les doublons ne sont pas un problème)
SELECT department_id FROM employees WHERE salary > 5000
UNION ALL
SELECT department_id FROM employees WHERE hire_date > '2020-01-01';

Pour les examens : UNION ALL est significativement plus rapide que UNION car il n'a pas besoin de trier et de comparer les résultats pour
éliminer les doublons.

C.6 Utilisation des alias de colonnes (limites dans WHERE/GROUP BY)

Concept clé : Les alias de colonnes définis dans la clause SELECT ne peuvent pas être utilisés dans les clauses WHERE ou GROUP BY.

Règles d'utilisation des alias :

✅ SELECT : Peut utiliser des alias de tables, mais pas ses propres alias
✅ ORDER BY : Peut utiliser des alias de colonnes définis dans SELECT
❌ WHERE : Ne peut pas utiliser d'alias définis dans SELECT (exécuté avant SELECT)
❌ GROUP BY : Ne peut pas utiliser d'alias définis dans SELECT (exécuté avant SELECT)
❌ HAVING : Ne peut pas utiliser directement les alias de colonnes (mais peut utiliser les alias d'agrégations)
Exemples corrects et incorrects :

❌ INCORRECT: Alias dans WHERE

SQL
--
SELECT employee_id AS emp_id, salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000; -- ERREUR: "annual_salary" invalid identifier

-- ✅ CORRECT: Répéter l'expression dans WHERE

SELECT employee_id AS emp_id, salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;

-- ❌ INCORRECT: Alias dans GROUP BY

SELECT department_id AS dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id; -- ERREUR: "dept_id" invalid identifier

-- ✅ CORRECT: Utiliser le nom de colonne original dans GROUP BY

SELECT department_id AS dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

-- ✅ CORRECT: Alias dans ORDER BY (exécuté en dernier)

SELECT employee_id AS emp_id, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

Pour les examens : L'ordre logique d'exécution explique pourquoi les alias ne sont pas disponibles dans WHERE et GROUP BY.

C.7 Détails AUTOTRACE : différence entre ACCESS et FILTER

Concept clé : Dans les plans d'exécution, les prédicats sont classés en deux types avec des impacts différents sur les performances.
Types de prédicats :

ACCESS PREDICATES : Déterminent comment localiser les données

Utilisés pour accéder à l'index ou aux données
Réduisent le nombre total de lignes à lire
Apparaissent avec INDEX RANGE SCAN, TABLE ACCESS BY INDEX ROWID, etc.

FILTER PREDICATES : Éliminent les lignes après leur récupération

Appliqués après l'accès aux données
Ne réduisent pas le nombre de blocs lus, seulement le nombre de lignes retournées

Exemple avec AUTOTRACE :

SQL
SET AUTOTRACE ON EXPLAIN
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 90 AND salary > 10000;

Sortie typique :

Execution Plan
----------------------------------------------------------
Plan hash value: 1444908879

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 183 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 183 | 2 (0)|
|* 2 | INDEX RANGE SCAN | EMP_DEPT_IX | 6 | | 1 (0)|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------
1 - filter("SALARY">10000) -- FILTER PREDICATE
2 - access("DEPARTMENT_ID"=90) -- ACCESS PREDICATE

Pour les examens : Un bon index devrait transformer les FILTER PREDICATES en ACCESS PREDICATES pour améliorer les
performances.

C.8 Limites techniques Oracle (spécifications importantes)

Concept clé : Oracle a des limites techniques importantes à connaître pour l'architecture et l'optimisation.

Limites principales :

Nombre maximum de colonnes dans une table : 1000 colonnes

Nombre maximum de colonnes dans un index composite : 32 colonnes
Longueur maximum d'un index :
B-Tree : 3800 octets (avant 12c), 3072 octets (12c+ avec COMPATIBLE ≥ 12.1)
Bitmap : 30% moins que B-Tree
Nombre maximum de tables dans une jointure : 64 tables
Longueur maximum d'une requête SQL : 64 Ko (avant 12c), illimitée (12c+)
Nombre maximum de lignes retournées par FETCH FIRST : Illimité, mais performance dégrade avec de très grands nombres

Exemple de création d'index composite :

 SQL
-- Maximum 32 colonnes autorisées
CREATE INDEX idx_max_columns ON employees(
employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id,
department_id, -- ... jusqu'à 32 colonnes maximum
);

Pour les examens : Dépasser ces limites génère des erreurs ORA-00910 (nombre de colonnes trop élevé) ou ORA-01450 (clé d'index trop
grande).

C.9 Clarification sur les MViews : pourcentage de données recommandé

Concept clé : Les vues matérialisées sont efficaces uniquement quand elles représentent une fraction raisonnable des données sources.

Recommandations sur le pourcentage de données :

✅ Recommandé (0-30%) : Excellente performance, faible impact sur l'espace

Résumés agrégés (SUM, AVG, COUNT)
Filtres très sélectifs (WHERE conditions spécifiques)
Partitions de tables très grandes

⚠️ Acceptable (30-70%) : Performance acceptable, mais vérifier l'espace disque

Agrégations sur des dimensions modérément sélectives
Combinaisons de filtres modérément sélectifs

❌ Déconseillé (>70%) : Mauvaise performance, perte d'espace inutile

Presque toutes les données de la table source
Filtres peu sélectifs
Pas d'agrégation significative

Bonnes pratiques supplémentaires :

SQL
-- Vérifier le ratio de compression avant création
SELECT
(SELECT COUNT(*) FROM sales WHERE color = 'BLUE') AS filtered_count,
(SELECT COUNT(*) FROM sales) AS total_count,
ROUND((SELECT COUNT(*) FROM sales WHERE color = 'BLUE') * 100 /
(SELECT COUNT(*) FROM sales), 2) AS percentage
FROM dual;

-- Créer une MView seulement si le pourcentage est < 30%

CREATE MATERIALIZED VIEW mv_blue_sales
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT orderid, customerid, qty, cost
FROM sales
WHERE color = 'BLUE'; -- Seulement 15% des données

Yahia Charif