FILIÈRE: INGÉNIERIE INFORMATIQUE ET RÉSEAUX

(5ème année)
Pr. Omar EL BEGGAR

Oracle SQL Tuning

1 Oracle SQL Tuning

 Plan du module
Rappel Architectures Oracle
1. Optimisation algébrique
2. Processus d’exécution des requêtes et Optimisation
3. Index, méthodes d’accès et de Jointures dans Oracle
4. Optimiseur Oracle et plans d’exécution
5. Oracle Statistiques
6. Oracle Hints
7. Oracle SQL Tuning
8. Outils manuels et automatisés de diagnostic et tuning

2 Oracle SQL Tuning

 Rappel

Architectures Oracle

3 Oracle SQL Tuning

 Rappel

Principes de blocs et ROWID

Blocs
… B3 RAM
Fichier
…
B3
enregistrements

Les données sur disque sont stockées dans des fichiers. Un fichier occupe plusieurs
pages sur disque gérées par l’OS. La page ou bloc est l'unité d'échange entre le disque et
la mémoire. Ainsi le coût des opérations dans les BDD est évalué principalement par le
nombre de E/S de pages (i.e. accès au disque). Un fichier stocke un ensemble
d'enregistrements de taille fixe ou variable dans des pages ayant une adresse (ROWID)
composée de l’adresse page plus l’indice de l‘enregistrement dans la page.

4 Oracle SQL Tuning

 Rappel

Architecture Oracle Non-CDB

Structures mémoire Instance

Mémoire SGA
Structures (System Global Area)
de processus
Processus en arrière-plan

Structures de stockage Fichiers de base de données

5 Oracle SQL Tuning

 Rappel

Structures mémoire et Processus Oracle

.

Mémoire SGA

Zone de mémoire Zone de mémoire Zone de mémoire

partagée Streams LARGE POOL

Cache de
Zone de mémoire Tampon de
tampons de la
Java journalisation
base de données

Processus
Processus Processus Processus Fichiers de
Log Writer journalisation
SMON PMON DBWn
(LGWR) archivés

Point de Fichiers de Processus

reprise contrôle d'archivage
(CKPT) ARCn

6 Oracle SQL Tuning

 Rappel

Architecture Multitenant
• L'architecture multitenant ou multi-locataire est une architecture
plusieurs BDs-à-une instance, elle permet à une base de données Oracle de
fonctionner comme une base de données conteneur multilocataire (CDB).

• Une CDB comprend zéro, une ou plusieurs bases de données enfichables

ou pluggables (PDB) créées par l’utilisateur.

• Un conteneur (Container) est une collection logique de données ou de

métadonnées au sein de l'architecture multilocataire.

• Une PDB est une collection logique portable de schémas, d'objets de

schéma et d'objets non schéma qui apparaît à un client Oracle Net comme une
base de données non-CDB.

• Toutes les bases de données Oracle antérieures à Oracle Database 12c

étaient des bases de données non-CDB.
7 Oracle SQL Tuning
 Rappel

Contenu d’une CDB

Une base de données conteneur est composée obligatoirement:

• Un seul conteneur racine (Root nommé CDB$ROOT): contient les

métadonnées fournies par Oracle et les utilisateurs communs.

• Une seule base de données enfichable d’amorçage (Seed ou PDB$SEED): un

template fourni par le système que le CDB peut utiliser pour créer de nouveaux
PDB.

• Zéro ou plusieurs bases de données enfichables "utilisateurs" (PDB), dans la

pratique au minimum une puisque c’est dans une PDB "utilisateur" que seront
créés les objets des applications (tablespace, tables, index, utilisateurs, etc.).

8 Oracle SQL Tuning

 Rappel

Notes
• Le terme conteneur système désigne le conteneur (CDB) dans son
ensemble (CDB racine + tous ses PDBs).

• Un conteneur d’application: un composant CDB facultatif créé par

l'utilisateur pour stocker la définition de l'application principale : objets
communs : tables, vues, etc. Il se comporte comme une CDB ou une PDB. Un
exemple de cas d'utilisation, une entreprise place des données spécifiques à
chaque trimestre financier dans une PDB distincte. Par exemple, le conteneur
d'application nommé sales_ac inclut q1_2016_pdb, q2_2016_pdb,
q3_2016_pdb et q4_2016_pdb.

•Dans la terminologie Oracle, le terme "conteneur" peut désigner la CDB

Root ou bien une PDB (dont la PDB seed).

9 Oracle SQL Tuning

 Rappel

Architecture d’une CDB

10 Oracle SQL Tuning

 Rappel

Architecture physique d’un CDB

11 Oracle SQL Tuning

 Rappel

Avantages de l’architecture multitenant

Isolation et agilité avec économie d'échelle
PDB autonome pour chaque application
• Les applications s'exécutent sans changement
• Provisionnement rapide (cloning)
• Portabilité (plugging)

Opérations communes au niveau CDB

• Manage many to one (upgrade, haute
disponibilité, sauvegarde)
• Contrôle granulaire le cas échéant

Mémoire partagée et processus d'arrière-plan

• Plus d'applications par serveur

12 Oracle SQL Tuning

 Architecture en grille
 Oracle Database peut être configuré :
en tant que:
o BD à instance unique (non cluster)
o ou BD Real Application Clusters (RAC).
 Une base de données à instance unique
contient le logiciel Oracle, les fichiers
de base de données, la mémoire et les
processus sur un seul serveur.

 Une base de données à instance unique a une relation un-à-un entre l'instance (mémoire
et processus) et la base de données (fichiers de base de données).
 Les environnements Oracle RAC, cependant, ont une relation un-à-plusieurs entre la
base de données et les instances. Une BD Oracle RAC peut avoir jusqu'à 100 instances,
qui accèdent toutes à une seule BD.
 Tous les nœuds accèdent à un seul ensemble de fichiers de BD sur le shared storage à
travers une instance ASM.
13 Oracle SQL Tuning
 Rappel

Échanges SGA – Processus en coulisse

14 Oracle SQL Tuning

 Chapitre 1

Optimisation algébrique

15 Oracle SQL Tuning

 Chapitre 1

Objectifs du chapitre 1

A la fin de ce chapitre, vous pouvez :

 Réécrire les expressions algébriques.

 Appliquer l’algorithme d’optimisation sur un plan d’exécution

logique.

 Calculer le coût d’un plan d’exécution logique.

16 Oracle SQL Tuning

 Chapitre 1

Optimisation algébrique

 L’objectif de l’optimisation algébrique des requêtes et de créer un plan

d’exécution logique optimal en termes de temps d’exécution et de mémoire
utilisée et ceci en se basant sur les propriétés des opérateurs algébriques.

 Un plan d’exécution logique est arbre algébrique qui décrit les étapes
d’exécution logique d’une requête sous forme d’opérations algébriques,
telles que la projection, la restriction, l’union, la jointure, etc.

 Un plan d’exécution optimale est obtenu suite à une réécriture algébrique

basée sur un ensemble de règles appliquées sur les opérateurs algébriques se
trouvant dans le plan initial.

17 Oracle SQL Tuning

 Chapitre 1

Règles de réécriture algébrique (1)

1. Commutativité des Jointures
JOIN(R,S)≡ JOIN(S,R)

≡
R S S R
2. Associativité des jointures
JOIN(JOIN(R,S),T)≡ JOIN(R,JOIN(S,T))

T R
≡
R S S T

18 Oracle SQL Tuning

 Chapitre 1

Règles de réécriture algébrique (2)

3. Groupabilité des restrictions
RESTRICT(RESTRICT(R,COND1), COND2)≡ RESTRICT(R,COND1 and COND2)
COND2 COND1 and COND2
COND1
≡ R
R
4. Semi commutativité des projections et restrictions
PROJECT(RESTRICT(R,Ai=a), A1,…,Ap)≡
PROJECT(RESTRICT(PROJECT(R, Ai, A1,…,Ap),Ai=a),A1,…Ap)
 A1,…,Ap  A1,…,Ap
Ai=a
Ai=a
≡  Ai,A1,…,Ap
R
R
19 Oracle SQL Tuning
 Chapitre 1

Règles de réécriture algébrique (3)

5. Distributivité des restrictions sur les jointures
RESTRICT(JOIN( R,S),Ai=a)≡ JOIN(RESTRICT(R,Ai=a),S) ou
RESTRICT(JOIN( R,S),Ai=a) ≡ JOIN(R,RESTRICT(S,Ai=a)) (selon la condition porte sur R ou S)
Ai=a
≡ Ai=a ou Ai=a
S R
R S R S
6. Semi distributivité des projections sur les jointures
PROJECT(JOIN(R,S,A=B), Ar1,…,Arp, As1 ,…,Asm)≡
PROJECT(JOIN(PROJECT(R,A, Ar1,…,Arp),PROJECT(S,B, As1 ,…,Asm),A=B), Ar1,…,Arp, As1 ,…,Asm)
 Ar1,…,Arp, As1 ,…,Asm  Ar1,…,Arp, As1 ,…,Asm
≡ A=B
A=B  A,Ar1,…,Arp  B, As1 ,…,Asm
R S
R S
20 Oracle SQL Tuning
 Chapitre 1

Règles de réécriture algébrique (4)

7. Distributivité des restrictions sur l’union et la différence
RESTRICT(UNION( R,S),Ai=a)≡
UNION(RESTRICT(R,Ai=a),RESCTRICT(S,Ai=a))
Ai=a ∪/-
≡
∪/- Ai=a Ai=a
R S R S
8. Distributivité des projections sur l’union
PROJECT(UNION( R,S),A1,…Ap)≡
UNION(PROJECT(R,A1,…Ap),PROJECT(S,A1,…,Ap))
 A1,…,Ap ∪
≡
∪ A1,…,Ap  A1,…,Ap
R S R S
21 Oracle SQL Tuning
 Chapitre 1

Algorithme d’optimisation algébrique

Pour optimiser une requête algébriquement, on applique les huit règles citées
précédemment et en suivant l’heuristique suivante:
1. Décomposer les restrictions Unaires (Règle 3).
2. Rapprocher les restrictions des feuilles (Règles 4,5 et 7)
3. Grouper les restrictions aux feuilles (Règle 3).
4. Rapprocher les projections des feuilles (Règles 4,6 et 8)
5. Ordonner les jointures afin de minimiser le temps.
Exemple:
Soit le schéma relationnel illustratif suivant:
Joueur(NJ,Nom,Prenom,Adresse),
Jouer(NJ,NS,Date,Heure),
Stade(NS,Ville,Capacité)
Nous voulons écrire une requête permettant de retourner les noms et prénoms des joueurs
habitant à Maarif ayant joués le 01/01/2024 à 18h à Casablanca.
22 Oracle SQL Tuning
 Chapitre 1

Select Nom, Prenom From Joueur J, Jouer Jo, Stade S Where Ville=‘Casablanca’ and
Adresse=‘Maarif ’ and date=’01/01/2024’ and heure=18 and [Link]=[Link] and [Link]=[Link]

 Nom, Prenom
Ville=‘Casablanca’ ∧ Adresse=‘Maarif ’ ∧ Date=’01/01/2024’ ∧ Heure=18

NS=NS

Stade

NJ=NJ
Joueur Jouer

On constate que cette requête n’est pas optimisée. On applique l’heuristique

précédent en respectant l’ordre des règles comme suit:

23 Oracle SQL Tuning

 Chapitre 1

Le résultat du processus d’optimisation permet d’obtenir le résultat suivant:

Quelques formules:
 Nom, Prenom CE(R)sélection=|R|
CS(R)sélection=|R| * sels
CE(R S)jointure=|R|+|R|*|S|(Nested Loops)
NS=NS
CS(R S)jointure=|R|*selj

 NS,Nom, Prenom |R|: [Link] ou card de R.

sels: sélectivité de la sélection.
selj: sélectivité de la jointure.

NJ=NJ
 NJ,Nom, Prenom  NJ,NS  NS
Adresse=‘Maarif ’ Date=’01/01/2024’∧ Heure=18 Ville=‘Casablanca’
Joueur Jouer Stade

24 Oracle SQL Tuning

 Chapitre 1

Calcul du coût (1)

PLAN INITIAL
JOINTURE SELECTION

Jointure 1: C(E)sélection=C(S)jointure2=96 E
C(E) jointure1=200+200*100=20200 E C(S)sélection=96*0.0015=1S
C(S) jointure1=200*0.6=120 S C(T)sélection=97 E/S
C(T)jointure1= 20320 E/S
Statistiques:
Jointure 2: Joueurs-> 200 tuples
C(E)jointure2=120+120*20=2520 E Jouer->100 tuples
Stade->20 tuples
C(S)jointure2=120*0.8=96 S Joueurs qui ont joué->60%
C(T)jointure2= 2616 E/S Jouer qui ont des stades->80%
Joueurs habitant à maarif->5%
Les deux Jointures: Stades à casablanca->30%
C(T)=22936 E/S Jouer le 01/01/2024 à 18h ->10%

C(T)plan_initial=22936+97=23033 E/S
25 Oracle SQL Tuning
 Chapitre 1

Calcul du coût (2)

PLAN OPTIMISE
SELECTION JOINTURE
Sélection1:
C(E)sélection1=200 E Jointure1:
C(S)sélection1=200*0.05=10 S C(E)jointure1=10+10*10=110 E
C(T)sélection1= 210 E/S C(S) jointure1=10*0.6=6 S
Sélection2: C(T)jointure1=116 E/S
C(E)sélection2=100 E Jointure2:
C(S)sélection2=100*0.1=10 S C(E)jointure2=6+6*6=42E Statistiques:
Joueurs-> 200 tuples
C(T)sélection2= 110 E/S C(S) jointure2=6*0.8=5 S Jouer->100 tuples
Sélection3: C(T)jointure2=47 E/S Stade->20 tuples
Joueurs qui ont joué->60%
C(E)sélection3=20 E
Jouer qui ont des stades->80%
C(S)sélection3=20*0.3=6 S Joueurs habitant à maarif->5%
C(T)sélection3= 26 E/S Stades à casablanca->30%
Jouer le 01/01/2024 à 18h->10%
Coût total des sélections:
C(T)=346 E/S C(T)plan_optimisé=346+47=393 E/S
26 Oracle SQL Tuning
 Chapitre 1

Passage du SQL vers le plan d’exécution physique

 Généralement, Le passage d’une requête SQL a un plan d’exécution
physique passe par deux étapes:
A) La requête SQL est traduite à l’aide des opérateurs d’algèbre en plusieurs
plans d’exécution logiques (assez abstraits pour l’exécution).
B) Le système choisit des opérateurs d’exécution en fonction du contexte
d’exécution (l’existence ou non d’index, tailles des tables, mémoire disponible,
etc) afin d’obtenir des plans d’exécution physiques équivalents. À la fin, le
système choisit le plan d’exécution optimal ayant le meilleur coût.

27 Oracle SQL Tuning

 Chapitre 2

Processus d’exécution des requêtes

et Optimisation

28 Oracle SQL Tuning

 Chapitre 2

Objectifs du chapitre 2

A la fin de ce chapitre, vous pourrez :

 Identifier les différentes phases du processus d’exécution de
requêtes SQL.
 Distinguer entre soft parsing et le hard parsing.

29 Oracle SQL Tuning

 Chapitre 2

Le processus d’exécution d’une requête SQL

 Le processus d’exécution d’une instruction
SQL dans Oracle passe par 3 étapes principales
(parsing, execution et fetch(Select)).
 Il existe deux manières possibles d'exécuter
une requête.
o Soft parsing - également appelé « Library Cache Hit
».
o Hard parsing - « Library Cache Miss » (moins
performant).
 Les 3 sous premières étapes du parsing sont
prises en charge par l'analyseur et ils sont
commun à toutes les requêtes.
 Les sous étapes 4 et 5 sont à la charge de
l’optimiseur Oracle, elles sont nécessaires que
dans le Hard parsing.

30 Oracle SQL Tuning

 Chapitre 2
Processus d’exécution d’instruction SQL
(Parsing)
 Parsing-sous-étape 1 :Vérification de la syntaxe
Cela vérifie uniquement les erreurs de syntaxe. (Les mots clés sont-ils
corrects? les noms de colonnes dans la clause « Select » sont-ils séparés par
des virgules (,) ? ,etc....).
 Parsing-sous-étape 2 :Vérification sémantique
Ici, les références de tous les objets présents dans la requête sont vérifiées
ainsi que les privilèges de l'utilisateur. La vérification s'effectue par rapport aux
métadonnées conservées dans Dictionary cache du Shared Pool. Exemples : Le
nom de la table est-il valide? Une telle table existe-t-elle? Les noms des
colonnes sont-ils corrects ? L'utilisateur dispose-t-il du privilège? etc.
 Parsing-sous-étape 3 :Vérification du pool partagé
Si l'analyseur détecte que la requête est présente dans le library cache du
Shared Pool, il s’agit alors de « soft parsing ». Il passe à l'étape 6, i.e exécute la
requête et envoie la sortie à celui qui l'a demandée. Sinon, l'analyseur procède à
« hard parsing »., i.e. Oracle doit exécuter l'étape 4 et l'étape 5.
31 Oracle SQL Tuning
 Chapitre 2
Processus d’exécution d’instruction SQL
(Parsing)
Les sous étapes 4 et 5 ne se produit qu'en cas « hard parsing ».
 Parsing-sous-étape 4: Optimisation : L'optimiseur génère plusieurs plans en
utilisant diverses combinaisons de jointures (neested loop, sort merge, hach
join), de méthodes d'accès (Full scan, By index ROWID, index range scan, index
skip scan etc.) et d'ordres de jointure. Enfin, il sélectionne le meilleur plan en
fonction du coût. Plus le coût est bas, meilleures sont les performances.
 Parsing-sous-étape 5; Génération source de lignes : le plan d'exécution optimal
est généré pour la requête. C'est ce que nous voyons dans le Explain plan.
Nous pouvons apporter d'autres modifications à la requête, forcer l'optimiseur
à sélectionner un autre plan encore moins coûteux en modifiant les chemins
d'accès, les jointures, les ordres de jointure ou en utilisant des Hints. Et nous
pouvons vérifier si le plan d'exécution a été amélioré en examinant à nouveau
son plan d'explication. Cela s'appelle « Performance Tuning » ou « SQL tuning ».

32 Oracle SQL Tuning

 Chapitre 2
Processus d’exécution d’instruction SQL
(Exécution)
 Exécution-sous-étape 6 : Exécution ou récupération des données
(Commune qu'il s'agisse de soft parsing ou hard parsing)
Le plan d'exécution détermine les blocs contenant les données recherchées. Le
processus serveur recherche dans le buffer cache les blocs de données
concernées par la requête. Si le processus trouve ces blocs, il doit les déplacer
vers la fin de la liste LRU (Least Recently Used) . Sinon, le processus serveur
doit les extraire des disques et les écrire en buffer cache des données.
Dans le cas de LMD, le processus serveur doit effectuer en plus les tâches
suivantes:
o Mise à jour des blocs adéquats du DB buffer cache (image après). Les blocs
d’annulation (image avant) sont générées aussi dans le DB buffer cache.

oÉcrire les vecteurs de changements dans le redo buffer (description des blocs
modifiées, description des (blocs d’annulation) et description de la transaction ainsi
que son SCN.

33 Oracle SQL Tuning

 Chapitre 2
Processus d’exécution d’instruction SQL
(Fetch)
 Étape : Fetch
En cas d’exécution d’une requête Select, le processus serveur renvoie les
tuples trouvés au processus client. Ce dernier se charge de l’affichage selon
l’application utilisée.

34 Oracle SQL Tuning

 Chapitre 3

Index, méthodes d’accès

et de jointures

35 Oracle SQL Tuning

 Chapitre 3

Objectifs du chapitre 3

A la fin de ce chapitre, vous pourrez identifier :

 Types d’indexes et leurs méthodes d’accès

 Méthodes d’accès aux tables

 Méthodes de jointures.

36 Oracle SQL Tuning

 Chapitre 3

Index (1)
 Un index une structure de données qui constitue un objet d’un schéma. il
contient une entrée pour chaque valeur qui apparait dans la colonne
indexée et fournit un accès direct et rapide aux enregistrements.
 L’indexation est mécanisme qui fournit un ROWID pour retrouver un
enregistrement particulier.
 ROWID est une adresse d’un enregistrement composé de : object_id,
data_file_id, data_bloc_id et position_in_block.
 L’index dans une BD facilite et accélère les opérations de recherche, de tri,
de jointure ou d'agrégation.
 Oracle créé systématiquement un index pour un clef primaire (PRIMARY
KEY) ou une contrainte d’unicité (UNIQUE) sur une colonne. Index
#ID Nom … Valeur de Adresse
clé (ROWID)
Leila Ahmed 1234
Index est un ensemble d’entrées
Ahmed

entrées
Entrée=(clé , rowid) Leila 1235
Samir Samir 1241

37 Oracle SQL Tuning

 Chapitre 3

Index (2)
 Les index doivent être utilisés pour:
o Les colonnes qui sont fréquemment soumises à des recherches.
o Les colonnes souvent mobilisées dans une jointure (clé Etrangère).
o Les colonnes très discriminées (i.e. peu de colonnes ont les mêmes valeurs).
o Les colonnes rarement modifiées.
 Inconvénients des index
o Les index diminuent les performances en mise à jour (puisqu'il faut mettre à
jour les index en même temps que les données).
o Les index ajoutent du volume à la base de données et leur volume peut devenir
non négligeable.
 Syntaxe:
SQL> CREATE INDEX index_name ON table_name(column1 [asc,column2 desc,...])
Pour afficher les indexes d’une table:
SQL>Select index_name from USER_INDEXES where table_name='EMPLOYEES';
SQL>Select column_name from USER_IND_COLUMNS where table_name='EMPLOYEES';
38 Oracle SQL Tuning
 Chapitre 3

Types d’index Oracle

 Arbre équilibré (B-Tree).
 Index composé (Composite Index).
 Index unique (Unique Index).
 Index inversé (Reverse Index).
 Bitmap Index.
 Index basé sur une fonction (Function Based Index).
 Tables organisées par index (Index Organized Tables).

39 Oracle SQL Tuning

 Chapitre 3

Arbre équilibré (B-Tree)

racine
 Balanced tree ou arbres équilibré est 0..40
…
l’index par défaut d’oracle. 200-250
 Il contient une racine, plusieurs branches
(contiennent seulement les clés triés en
Niveau 200..210
rangées dans l’ordre ascendant) et feuilles 0..10
…
Branches … …
(contiennent les clés et rowids). 30-40 240-250
 Il est parcourue de gauche à droite.
Niveau
 Le niveau le plus bas est appelé « niveau feuilles
des feuilles », qui contient les entrées
d’index composées des pairs valeur de clé 0 rowid 11 rowid 240 rowid
et adresse vers un bloc d’enregistrement. … … … …
…
10 rowid 19 rowid 250 rowid
 Pour éxécuter "select nom,salaire from emp
where emp_id=10", il faut parcourir le B-
tree en commençant par la racine, passant Bloc 1 Bloc 2
par les braches jusqu’au atteindre la feuille 0, ahmed,1995 10,Samir,2008
qui permet d’avoir l’adresse ou le ROWID 1, leila,2001 … 11,Nora,1999
2,hassan,1998
pour accéder au bloc.
40 Oracle SQL Tuning
 Chapitre 3

Index unique et index inversé

Unique index
 Index Unique: C’est un index de type B-tree crée automatiquement
lors de l’ajout de la contrainte unique sur une colonne
(recommendé). Comme il est possible de créer manuellement un
index unique.
 Syntaxe:
SQL>CREATE UNIQUE INDEX index_name ON table_name(column1)
Index inversé (Reverse index)
 Index inversé: est utilisé pour rechercher souvent les valeurs les plus
élevées. Il est de type B-tree, mais l’arbre sera parcourue de droite à
gauche.
 Syntaxe:
SQL>CREATE INDEX index_name ON table_name(column1) REVERSE

41 Oracle SQL Tuning

 Chapitre 3
Index composé
(composite Index)
 Index composé: est un index appliqué sur deux ou plusieurs
colonnes.
 La clé n'est pas représentée par une valeur unique, mais par un
tuple. Étant donné que la clé de l’index n'est pas une valeur unique,
alors l'index sera ordonné par le premier élément clé, puis par le
deuxième élément clé, et ainsi de suite.
 Ainsi, l’ordre des colonnes lors de la création d’index est important.
Il faut commencer par la colonne avec une haute selectivité (sel=no
distinct values/no lignes)
 Syntaxe:
SQL> Create Index Emp_Nom_Ville_IDX on Employees(Nom,ville).

L’ordre des colonnes dans l’index composé doivent correspondre à leur ordre
d’apparence dans la clause where, sinon l’index est ignoré.
42 Oracle SQL Tuning
 Chapitre 3

Bitmap Index (1)

 Utilisé lorsque le nombre de valeurs distinctes dans une colonne est
assez petit que le nombre total des enregistrements dans une table.
Exemple gender dans la table employees.
 Il peut être simple ou composé.
 Oracle convertit l'ensemble des rowids en bitmap et effectue des
opérations AND et OR. Ces bitmaps sont reconvertis en rowids.
 Utilisé pour les tables en lecture seule ou rarement mises à jour
(datawarehouse).
 Consomme moins d'espace que l'index B-Tree.
 Syntaxe:
SQL>CREATE BITMAP INDEX index_name ON table_name(column1[,column2,...]);

43 Oracle SQL Tuning

 Chapitre 3

Bitmap Index (2)

 Après éxécution de la requête:
SQL>Select EMP_ID from EMPLOYEES where gender=‘F’.
Chaque bit correspond à un rowid.
Si le bit est à 1, l’enregistrement avec
Gender F 111101011011…1010111 le rowid correspondant contient la
valeur de la clé ‘F’. Une fonction de
mappage convertit la position du bit
en un rowid.
 Après éxécution de la requête, on obtient :
SQL>Select EMP_ID from EMPLOYEES where gender=‘M’
Chaque bit correspond à un rowid.
Gender M 000010100100…0101000 Si le bit est à 1, l’enregistrement avec
le rowid correspondant contient la
valeur de la clé ‘M’. Une fonction de
mappage convertit la position du bit
en un rowid.

44 Oracle SQL Tuning

 Chapitre 3

Bitmap Index (3)

SQL> Select EMP_ID from EMPLOYEES where gender=‘F’ and HIRE_DATE>SYSDATE-365

Gender F 111101011011…1010111

Hire_date Msysdate-365 10000100001…1000111

= 10000000001…0000111

45 Oracle SQL Tuning

 Chapitre 3
Index basé sur une fonction
(Function Based Index)
 Si on utilise une fonction sur une colonne qui est indexée, alors cet index ne
sera pas utilisé.
SQL> Select last_name from employees where lower(email)=‘smith’
 Solution:

o Créer un index sur la fonction lower:

SQL> Create Index Emp_Fun_IDX On Employees(lower(email));

o On peut créer un index aussi sur une expression:

SQL> CREATE INDEX emp_total_sal_idx ON employees (12 * salary *
commission_pct);

46 Oracle SQL Tuning

 Chapitre 3
Tables organisées par index
(Index Organized Tables)
 Les tables organisées par index (IOT) ont leurs clés primaires et leurs données
de colonne non clé stockées dans la même structure B-Tree.
 Les ROWIDs ne sont pas enregistrés.
 L'accès aux données via la clé primaire est plus rapide car la clé et les données
résident dans la même structure. Il n'est pas nécessaire de lire un index puis de
lire les données de la table dans une structure distincte.

 Dans l’IOT, l’index c’est les données et

les données se sont l’index.
 Syntaxe:
SQL>Create table table_name(id_name int primary
key, column1,.. ) ORGANIZATION INDEX
INCLUDING colonne 1, colonne2,…
IOT est un excellent choix pour une recherche
dans des petites tables basée principalement sur
la clé primaire (sans trop de mises à jour).
47 Oracle SQL Tuning
 Chapitre 3

Reconstruire un Index (Rebuild)

 Au fil du temps, à mesure que les données sont insérées, modifiées et
supprimées, les index peuvent devenir fragmentés. La fragmentation peut
entraîner des analyses d'index inefficaces et des performances de requête
plus lentes.
 La reconstruction d'un index consiste à supprimer la structure existante et
à en créer une nouvelle. Cela supprime la fragmentation et réorganise les
données.
 lorsqu'un index est en cours de reconstruction, des verrous seront placés
sur l'index, empêchant quiconque d'y accéder pendant la reconstruction.
 Une reconstruction d’indexes est nécessaire pour maintenir les bonnes
performances de votre BD (il est recommandé de le faire souvent le soir
ou les weekends).
SQL>ALTER INDEX index_name Rebuild [online];
Online: pour autoriser DML et partition sur la table pendant la reconstruction de
l'index.
48 Oracle SQL Tuning
 Chapitre 3

Méthodes d’accès d’indexes

Méthode d’accès
Description
d’index
Full Index Scan -Aucun prédicat n'est spécifié, mais les 2 conditions suivantes sont remplies :
* Toutes les colonnes sélectionnées et de la requête sont dans l'index.
* Au moins une colonne indexée n'est pas nulle.
(Select last_name,first_name from employees)
-Une requête inclut un ORDER BY sur les colonnes indexées non nullables.
(Select * from employees order by employee_id)
Fast Full Index Scan lit les blocs d'index dans un ordre non trié, tels qu'ils existent sur le disque. Cette analyse
n'utilise pas l'index pour scanner la table, mais lit l'index au lieu de la table, en utilisant
essentiellement l'index lui-même comme table.
Choisi lorsqu'une requête accède uniquement aux attributs de l'index. Un hint peut être
utilisé /*+ index_ffs()*/
Index Range Scan Choisie lorsque la condition sur la colonne indexée est faite sur un ensemble de valeurs;
typiquement :
- les opérateurs >, >=, <, <=, BETWEEN, LIKE.
- l'opérateur = dans le cas d'un index non-unique.
(égalité sur indexes non-unique ou plage sur indexes uniques)
Index Unique Scan Choisie lorsque l'index utilisé est unique (ou index d'une clé primaire).
Index Skip Scan Choisi lorsque la colonne à la tête d'un index composite n'est pas spécifiée dans le
(index composé) prédicat de la requête. Select * from employees where first_name like ‘E%’
49 Oracle SQL Tuning
 Chapitre 3

Méthodes d’accès de tables

Méthode d’accès
Description
d’index
FULL SCAN Un FULL SCAN de la table est souvent effectué, par exemple, lorsque il n'y a
pas d'indexes sur les colonnes sélectionnées et que la clause WHERE ne
contient pas de conditions ou que les conditions sont faites sur des colonnes
non indexées.
Le FULL SCAN de la table peut être aussi choisi si Oracle juge que
l'utilisation des indexes peut être moins performante ; c'est le cas lorsque la
requête devrait retourner une grande partie ou la totalité des données de la
table ou que les données de la table sont physiquement ordonnées dans un
ordre non optimal ou qu'il y a beaucoup d'espace libre entre les données de
la table.
BY INDEX ROWID Cette méthode d'accès est choisie lorsqu'un index est utilisé : pour chaque
ligne de l'index (dont les colonnes matchent avec les conditions de sélection)
un accès à la table est effectué en utilisant le ROWID.

50 Oracle SQL Tuning

 Chapitre 3

Méthodes de Jointure
Méthode de jointure est un algorithme utilisé pour effectuer une jointure:

Méthode de Description
Jointure
Nested Loops Utilisée lorsque les tables à joindre sont petites. La recherche dans la
table interne doit être supporté par index. Moins performant que les
jointures hash et Sort Merge.
Hash Join Utilisée lorsque les tables à joindre sont grandes et et il y a un prédicat
de jointure d'égalité.
Sort Merge Join Utilisée lorsque les tables à joindre sont grandes et et il y a un prédicat
de jointure de non-égalité.
-Cela aide également si l'une ou les deux entrées de jointure sont déjà
triées sur la colonne de jointure.
-la plupart des données dans les tables doivent être incluses ou lorsqu'il
n'y a pas d'index dans la table interne.

51 Oracle SQL Tuning

 Chapitre 3

Nested Loop (Boucles imbriquées)

Déterminer la table dite driving (outer)

//Souvent, la table la plus petite sera outer Regions Countries
Pour(chaque ligne dans outer table) {
//inner loop
Pour(chaque ligne dans inner table)
{
Vérifiez si vous avez une correspondance
}… Pour chaque ligne de la table externe
(Regions) nous recherchons une ligne
}
correspondante dans la table interne
(Countries)

Fonctionne mieux si la table externe est plus petite et que la table interne
a un index hautement sélectif sur la clé de jointure de la table externe.

52 Oracle SQL Tuning

 Chapitre 3

Hash Join(Jointure par hachage)

Une table de hachage est créée Hash Table Grâce à la fct de
hash(idc) Clients hachage récupérer
en mémoire en fonction des
valeurs de la colonne de jointure Id_5 Ajman Bv 12, Rabat les indices à partir
indice1
hachée ainsi que de toutes les de commandes et
autres colonnes de la requête. Indice2 Id_4 Atlas Place 1, Fès chercher leurs
Id_6 Azur Bv11, Taza correspondants ds
Indice3 la table de hachage.
Appliquer une fonction de Id_2 Atos RN21,Casa
hachage à la/les colonne(s) de Mémoire PGA
jointure indice=hash_fct([Link])
Indice?=hash_fct([Link])
Commencez par
scanner le plus petit Clients
Scanner la deuxième
des deux tables et Commandes
table et appliquez
appliquez n'importe
n'importe quel prédicat
quel prédicat de
de clause where
clause where

Select name, address, datecmd From Clients C,

Commande O Where [Link]=[Link] and name like ‘A%’
53 Oracle SQL Tuning
 Chapitre 3

Sort Merge Join (Jointure par fusion-tri)

Trier la table 2 sur la Fusion des deux
colonne de jointure (déjà fait) segments temporaires

Departments
Segment 2
Afficher les résultats
temporaire trié
Segment
temporaire
Segment 1 fusionné
temporaire trié
Mémoire PGA

Trier la table 1 sur la colonne

de jointure (department_id) Résultats

employees
Select first_name, department_name From employees
E, Departments D Where
E.department_id=D.department_id;
54 Oracle SQL Tuning
 Chapitre 3

Ordre des Jointures

 L’ordre des jointures dans une requête peut influencer son
coût.
 La méthode pour vérifier l’ordre des jointure est la méthode
Filtred Row Percentage (FRP). qui consiste à commencer par
la table qui a le FRP le plus petit jusqu’au plus grand, tout en
respectant la jointure entre les tables.
FRP=[Link] rows/[Link]
 Vérifier ensuite si le CBO respecte cet ordre ?
 Si oui, Ok
 Sinon, (pb: statistiques erronées ou inexistantes)
Solution appliquer les hints (leading, ordered), créer un
index, appliquer extended ou dynamiques statistiques ).
55 Oracle SQL Tuning
 Chapitre 4

Optimiseur Oracle et plans

d’exécution

56 Oracle SQL Tuning

 Chapitre 4

Objectifs du chapitre 4

A la fin de ce chapitre, vous pourrez :

 Identifier le rôle et les composants de l’optimiseur Oracle.

 Afficher et interpréter le plan d’exécution d’une requête.

57 Oracle SQL Tuning

 Chapitre 4

Introduction
 L'optimiseur de requêtes (Optimizer) est un logiciel intégré de base de
données qui détermine la méthode la plus efficace permettant à une instruction
SQL être exécutée.
 Il se base sur des statistiques collectées sur les données pour déterminer le
plan optimal pour une instruction SQL, et ceci en examinant plusieurs méthodes
d'accès, telles que full table scan ou index scans, différentes join méthodes
telles que nested loops and hash joins, differents join orders, et les
transformations possibles.
 Pour une requête et un environnement donnés, l'optimiseur attribue un coût
numérique relatif à chaque étape d'un plan possible, puis regroupe ces valeurs
pour générer une estimation globale du coût du plan. Il choisit le plan dont
l'estimation du coût est la plus basse parmi les plans alternatifs. Il est appelé
optimiseur basé sur les coûts ou Cost Based Optimizer (CBO) le plus répandu
par rapport à l'optimiseur basé sur les règles (RBO).

58 Oracle SQL Tuning

 Chapitre 4

Composants de l’optimiseur

Query Transformer: détermine s'il est

utile de modifier la forme de la
requête afin que l'optimiseur puisse
générer un meilleur plan d'exécution.

Estimator: estime le coût de chaque

plan en fonction des statistiques du
dictionnaire de données.

Plan Generator: génére le plan

d’exécution optimal.

59 Oracle SQL Tuning

Transformations possibles par le CBO-Transformer

 Subquery unesting: Cette transformation consiste à transformer des sous

requêtes en jointures.

 Suppression d’éléments inutiles (certaines jointures, des colonnes

sélectionnées dans les sous-requêtes).

 Predicate push: Cette transformation consiste à dupliquer certains prédicats

dans les sous-vues et les sous-requêtes.

 View merging: Intègre l’exécution des vues à la requête principale.

 Query Rewriting: Utilisation des vues matérialisées .

 Or Expansion: Transforme des conditions OR en plusieurs requêtes

fusionnées par une sorte d’"Union ALL".

60 Oracle SQL Tuning

 Chapitre 4

Estimateur
L’estimateur utilise trois mesures différentes pour déterminer le coût total:
Sélectivité: Le pourcentage de lignes dans l'ensemble de lignes que la requête
sélectionne. La sélectivité est liée à un prédicat de requête ou à une combinaison de
prédicats. Un prédicat devient plus sélectif si la valeur de sélectivité s’approche de 0,
sinon il est moins sélectif si la valeur s’approche de 1. La sélectivité est une mesure
interne qui n’est pas visible dans les plans d’exécution.
Sélectivité = [Link] vérifiant les prédicats/[Link] de lignes
Cardinalité: La cardinalité est le nombre estimé de lignes renvoyées par chaque
opération dans un plan d'exécution. La colonne Rows d'un plan d'exécution affiche la
cardinalité estimée. Pour un simple prédicat (distribution uniforme):
Cardinalité=[Link] lignes / NDV (*)
(*) NDV: number of distinct values of a column.

Coût: Mesure numérique qui représente l'utilisation estimée des ressources pour un
plan (E/S de disque, l'utilisation du processeur et taille mémoire).

61 Oracle SQL Tuning

 Chapitre 4

Affichage du plan d’exécution

L’affichage du plan d’exécution peut être
 Graphique en utilisant SQL Developer (Cliquez F10)
 Textuel en utilisant le package PL/SQL DBMS_XPLAN:
oDbms_xplan.display: affiche le plan d’exécution théorique (ou plan
d’explication) sans exécuter la requête.
oDbms_xplan.display_cursor: affiche le plan de la dernière requête
exécutée. L’utilisateur autre que SYS doit disposer des privilèges de
sélection suivants:
• GRANT SELECT ON V_$SESSION TO hr;
• GRANT SELECT ON V_$SQL_PLAN TO hr;
• GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO hr;
• GRANT SELECT ON V_$SQL TO hr;

o Dbms_xplan.display_awr(sql_id): affiche le plan d’exécution d’un Top

SQL stockée dans les vues historiques d’AWR (sql_id peut être lu du
rapport AWR).
62 Oracle SQL Tuning
 Plan théorique vs Plan réel
Le plan théorique ou explicatif est celui qu’Oracle prévoit d’utiliser pour exécuter une requête, sans
réellement l’exécuter.
👉 Il se base uniquement sur les statistiques des tables, index, et colonnes et crée le plan et l’enregistre dans
PLAN_TABLE. (si elle n’existe pas la créer avec ce script @$ORACLE_HOME/rdbms/admin/[Link])
🎯 Objectif :
 Comprendre comment Oracle va probablement exécuter une requête (choix des index, des jointures, tri,
etc.).
 Diagnostiquer les problèmes potentiels avant d’exécuter une requête coûteuse.
🎯 🎯 Limites :
 Il ne montre que les estimations, pas la réalité.
 Si les statistiques ne sont pas à jour, le plan théorique peut être faux ou sous-optimal.
Le plan réel reflète ce qu’Oracle a effectivement fait pour exécuter la requête.
👉 Il contient les statistiques d’exécution réelles : nombre de lignes traitées, temps, I/O, etc.
🎯 Objectif :
 Vérifier que le plan réellement choisi correspond bien à celui estimé.
 Identifier les écarts d’estimations et les causes de lenteur (mauvaise cardinalité, tri inutile, etc.).
🎯 🎯 Limites :
 Nécessite l’exécution effective de la requête (attention si elle est lourde !).
63 Oracle SQL Tuning
 Techniques pour afficher plans
d’exécution
Plan théorique Plan réel
1- EXPLAIN PLAN FOR sql statement 1- Exécuter absolument la requête sql statement
2- SELECT * FROM TABLE(DBMS_xplan.display) 2- SELECT * FROM
TABLE(DBMS_xplan.display_cursor)

Table "PLAN_TABLE" Vue "V$SQL_PLAN"

SET AUTOTRACE ON EXPLAIN SET AUTOTRACE ON

64 Oracle SQL Tuning

 Chapitre 4

Générer un plan explicatif (textuel)

Génération:
Syntaxe de explain plan (ce qui est entre [ ] étant optionnel):
Explain Plan [set statement_id='id de requête']
[into Nom2Table] For instruction SQL ;
Instruction SQL: select, insert, update, delete, create table,
create index, alter index ... Rebuild.
Affichage:
On utilise la fonction dbms_xplan.display().
SQL> Select * from
table(dbms_xplan.display(statement_id=>’id de
la requête’));

65 Oracle SQL Tuning

 Chapitre 4

Exemple d’affichage
Option du
format
d’affichage
Plan
Mesures estimées d’exécution
à partir des statistiques explicatif

Coût total (6)= coût

"Sort" (4) + coût
"Table access" (2)

arbre
«access predicate» prédicat appliqué avant accès à l’index
(pour limiter les feuilles concernés).
«filter predicate» prédicat appliqué après récupération de
lignes depuis la table ou l’index.

66 Oracle SQL Tuning

 Chapitre 4

Construire l’arbre d’exécution à 0partir du plan

 L’arbre peut être construit à partir de ces 1 SELECT
règles:
2
 Nœud père a plusieurs fils. 4
 Nœud fils a un seul père. 3
Merge
 La seule opération sans père est la racine 5 Join

de l’arbre.
 Fils sont en retrait à droite de leurs père. SORT ACCESS
Join BY
 Le id du père est inférieur à celui du fils. ROWID

FULL INDEX
Access FULL
SCAN
EMPLO DEP
YEES

Oracle SQL Tuning DEPT_I

D_PK

67
 Chapitre 4

Format d’affichage du plan

Select * from dbms_xplan.display(format=>’format_option’)
Plusieurs options du format d’affichage du plan d’exécution: chaque option déterminer un
niveau de détails
o BASIC : affiche les informations minimales du plan (appelée table du plan) : l'ID
d'opération, le nom de l'opération et son objet.
o TYPICAL : il s'agit de la valeur par défaut. Affiche les informations les plus
pertinentes du plan (ID d'opération, nom et objet, nombre de lignes, nombre d'octets
et coût de l'optimiseur). Les informations de parallélisme et de prédicat ne sont
affichées que si elles sont applicables.
o SERIAL : comme TYPICAL, sauf que les informations parallèles ne sont pas affichées,
même si le plan s'exécute en parallèle.
o ALL : niveau utilisateur maximal. Inclut les informations affichées avec le niveau
TYPICAL avec des informations supplémentaires (PROJECTION, ALIAS et
informations sur REMOTE SQL si l'opération est distribuée).

68 Oracle SQL Tuning

 Chapitre 4

Dbms_xplan.display_cursor
 Pour collecter les statistiques de la dernière requête exécutée dans le
curseur:
1. On exécute la requête premièrement (à la place d’écrie Explain plan for …).
2. Puis, on exécute la requête: Select * from table (Dbms_xplan.display_cursor()).

 Pour comparer les statistiques réelles et celles estimées de la dernière

requête exécutée dans le curseur:
1. On ajoute le hint /*+ GATHER_PLAN_STATISTICS*/ à la requête et on
l’exécute (ce hint permet de collecter les statistiques réelles, ex. A-Rows).
2. On ajoute le format d’affichage: Select * from table
(Dbms_xplan.display_cursor(format=>‘ALLSTATS LAST')).

La comparaison permet de savoir est ce que l’optimiseur Oracle a effectué

une bonne estimation des cardinalités des opérations du plan ou non. C’est une
technique rapide de commencer le tuning de la requête.
69 Oracle SQL Tuning
 Chapitre 4

Techniques d’influence sur l’optimisateur

Vous pouvez influencer l'optimiseur à l'aide de plusieurs techniques:

o Collecte des statistiques,

o Profils SQL,

o Plans de gestion SQL,

o Hints (conseils),

o Paramètres d'initialisation.

70 Oracle SQL Tuning

 Chapitre 5

Oracle Statistiques

71 Oracle SQL Tuning

 Chapitre 5

Objectifs du chapitre 5

A la fin de ce cours, vous pourrez :

 Collecter les statistiques concernant les schémas, tables et
indexes.

 Création des histogrammes pour les données asymétriques.

72 Oracle SQL Tuning

 Chapitre 5

Statistiques
 Oracle collecte les statistiques automatiquement grâce à un job (fenêtre de
maintenance) qui s’exécute (de 22h à 02h tous les jours de la semaine et de
06h à 02 les weekends) et grâce aussi à une tâche légère de haute fréquence
(chaque 15 min) pour les objets obsolètes (durée max 60min) à partir
Oracle 19c.

 La collecte des statistiques permettent de mettre à jour les vues statiques du

dictionnaire USER ou DBA tels que: USER_TAB_STATISTICS,
USER_TAB_COL_STATISTICS, USER_IND_STATISTICS, etc.

 Les statistiques peuvent être aussi collectées manuellement.

73 Oracle SQL Tuning

 Chapitre 5

Collecte des statistiques manuellement

 En cas de statistiques obsolètes ou inexistantes, le DBA peut décider de
collecter les statistiques manuellement pour permettre à l’optimiseur de mieux
exécuter les requêtes.
 Le package PL/SQL DBMS_STATS vous permet de collecter, modifier, afficher,
exporter, importer et supprimer des statistiques.
 Le package DBMS_STATS peut collecter des statistiques sur les index, les
tables, les colonnes, voir même le schéma, la base de données ou le système.
 Procédures de collecte des statistiques dans le package DBMS_STATS.

Procédure Collecte
GATHER_INDEX_STATS Statistiques sur l’index
GATHER_TABLE_STATS Statistiques sur la table, ses colunnes et indexes
GATHER_SCHEMA_STATS Statistiques sur tous les objets dans le schema
GATHER_DATABASE_STATS Statistiques sur tous les objets dans la BD
GATHER_SYSTEM_STATS Statistiques du processeur et des E/S du système

74 Oracle SQL Tuning

 Chapitre 5

Collecte des statistiques

estimate_percent spécifie le
EXEC DBMS_STATS.GATHER_TABLE_STATS( pourcentage de lignes à
ownname=>'hr', utiliser pour calculer des
statistiques
tabname=>'employees',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, À partir d’une table
cascade=>TRUE);
Pour afficher les statistiques:
Select * FROM USER_TAB_STATISTICS WHERE table_name='EMPLOYEES'; AUTO_SAMPLE_SIZE
------------------------------------------------------------------------------------------------ lit toutes les lignes
EXEC DBMS_STATS.GATHER_INDEX_STATS(
ownname=>'hr',
indname=>'EMP_NAME_IX', À partir d’un index
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
Pour afficher les statistiques:
Select * FROM USER_IND_STATISTICS WHERE index_name='EMP_NAME_IX';
--------------------------------------------------------------------------------------------------
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( À partir d’un Schéma
ownname=>'hr',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
Collecter également des
cascade=>true);
statistiques sur les index
Pour afficher les statistiques:
Utiliser les vues statiques précédentes.
75 Oracle SQL Tuning
 Chapitre 5

Suppression des statistiques

 Pour supprimer les statistiques d’une table, un index ou un schéma, les
procédures PL/SQL peuvent respectivement être utilisées:

o DBMS_STATS.DELETE_TABLE_STATS
o DBMS_STATS.DELETE_INDEX_STATS
o DBMS_STATS.DELETE_SCHEMA_STATS

76 Oracle SQL Tuning

 Chapitre 5
Interpréter les statistiques générées sur les
tables
Les problèmes pouvant être détectés sont l’espace inutilisé alloué à une table
et le faible taux d’occupation des blocs.
 Espace utilisé par une table
o Le nombre de blocs inutilisés alloués à la table (EMPTY_BLOCKS) est
important et la table ne va plus grossir (ou peu).
o Cause: La clause STORAGE mal adaptée.
 Faible taux d’occupation des blocs
o Le rapport (DB_BLOCK_SIZE-AVG_SPACE)/DB_BLOCK_SIZE est faible
et les lignes actuelles ne vont pas grossir et peu de nouvelles lignes vont être
insérées.
o Cause: Liée à des valeurs PCTFREE et PCTUSED mal adaptées ou à une
suppression importante de données.

Ces problèmes peuvent impacter les performances de parcours complet

de la table.
77 Oracle SQL Tuning
 Chapitre 5
Interpréter les statistiques générées sur les
indexes
Les problèmes pouvant être détectés sont le grand nombre de blocs de feuilles
et la profondeur importante de l’index.
 Grand Nombre de blocs de feuilles (LEAF_BLOCKS)
o Si le nombre de blocs de feuilles a augmenté de manière significative, le
nombre E/S le sera.
o Cause: des insertions et des suppressions dans une table provoquant la
fragmentation de l’index.
 Profondeur importante de l’index (BLEVEL or height)
oHeight ou BLEVEL(sans compter le root level) : profondeur de B-tree est
élevé (height > 4) . height =blevel+1.
oCause: Lié à un PCTFREE mal adapté lors de la création ou à un index très
volatile (beaucoup de mises à jour)

Dans ces cas, L’index a besoin d’une reconstruction.

78 Oracle SQL Tuning

 Chapitre 5

Statistiques et Histogrammes
 Par défaut, l'optimiseur suppose une distribution uniforme des lignes
sur les différentes valeurs d'une colonne.

 Pour les colonnes contenant des données asymétriques (une

distribution non uniforme des données au sein de la colonne), un
histogramme permet à l'optimiseur de générer des estimations de
cardinalité précises pour les prédicats de filtre et de jointure qui
impliquent ces colonnes.

 Donc, un histogramme est un type de statistiques qui permet

à l’optimiseur d’analyser la distribution des données et estimer
correctement la cardinalité.

79 Oracle SQL Tuning

 Chapitre 5

Distribution asymétrique
 Supposons que dans une université on a des étudiants de nationalités
différentes avec :

95% 4% 1%
nationalité='MAR' nationalité='SEN' nationalité='MAL'
142.5K étudiants 6K étudiants 1.5K étudiants

 La table étudiant compte 150K étudiants (lignes).

 Si on veut récupérer le "nombre d’étudiants maliens":

o L’optimiseur suppose une distribution uniforme de 150K/3=50K(NDV=3
nombre de valeur distinct de la colonne gender).
o Avec cette estimation, l’optimiseur choisit un plan explicatif.

80 Oracle SQL Tuning

 Chapitre 5

Impact d’histogramme sur la cardinalité

Avant Après

Cardinalité =50K, presque 33 fois Après création du histogramme sur la

plus grand que la réalité (1500 colonne nationalité. Le nouveau plan
lignes). De même pour les autres explicatif est maintenant avec une
nationalités !!!!!!!!!!!! cardinalité correcte=1.5K.
81 Oracle SQL Tuning
 Chapitre 5

Gestion des histogrammes

 La création d’un histogramme peut se faire grâce à l’instruction:
exec dbms_stats.gather_table_stats(ownname=>'owner_name',
tabname=>'table_name',
method_opt=>'for columns skewed_column_name');

 Par exemple:
exec dbms_stats.gather_table_stats(ownname=>'hr',
tabname=>'etudiant',
method_opt=>'for all columns nationalite');
 Pour la suppression d’un histogramme:
Begin
dbms_stats.delete_column_stats( ownname=>'hr',
tabname=>'etudiant', colname=>'nationalite',
col_stat_type=>'HISTOGRAM');
end;
82 Oracle SQL Tuning
 Chapitre 5

Statistiques dynamiques
 Dynamic Statistics ou connu précédemment par Dynamic Sampling
(échantillonnage dynamique).
 Lors du parsing d'une instruction SQL, l'optimiseur décide d'utiliser ou
non des statistiques dynamiques pour compenser les statistiques
manquantes ou insuffisantes qui conduiraient autrement à un très
mauvais plan.
 Dans le cas où une ou plusieurs tables de la requête ne disposent pas
de statistiques, l'optimiseur utilise des statistiques dynamiques pour
collecter des statistiques de base sur ces tables avant d'optimiser
l'instruction. Les statistiques collectées dans ce cas ne sont pas
d'aussi bonne qualité ni aussi complètes que celles collectées à
l'aide du package DBMS_STATS. Ce compromis est fait pour limiter
l'impact sur le temps de compilation de l'instruction.

83 Oracle SQL Tuning

 Chapitre 5

Statistiques dynamiques dans le plan explicatif

Dans cet exemple, la table étudiant est nouvellement crée et ne dispose pas de
statistiques. Ainsi, l’optimiseur a choisit de procéder par les Dynamic Statitics
(statistiques de base pour éviter une compilation lourde) comme le montre le plan
explicatif.
84 Oracle SQL Tuning
 Chapitre 5

Niveaux des statistiques dynamiques

 Dynamic Statistics ou Dynamic Sampling est contrôlé par le paramètre
OPTIMIZER_DYNAMIC_SAMPLING, qui peut être défini sur
différents niveaux (0 à 11). Ces niveaux contrôlent deux choses
différentes : le moment où les statistiques dynamiques entrent en jeu
et la taille de l'échantillon qui sera utilisé pour collecter les
statistiques.

 Pour affecter un niveau à Dynamic Statistics :

Alter session set Optimizer_Dynamic_Sampling=6

 Le niveau 0 désactive l’option Dynamic Statistics pour l’optimiseur.

 Le niveau 2 (signifie une ou plusieurs tables de l'instruction ne

contiennent aucune statistique, avec une taille d’échantillon pouvant
atteindre 64 blocks).
85 Oracle SQL Tuning
Statistiques adaptatives
 Collectées au parsing ou juste au début de l’exécution de la
requête (si oracle détecte un écart entre cardinalité réelle et
celle estimée). Il s’agit d’une amélioration ou correction des
statistiques.
 Si les statistiques adaptatives n’ont pas aidé le CBO à choisir
un plan normal :
 Adapter un plan alternative (dit plan adaptative), surtout pour
remplacer une méthode de Jointure (NL, HJ, SMJ) par une autre
alternative.
 Et générer des directives pour être prises en considération lors des
exécutions ultérieures.
 Pour activer:
Alter session set Optimizer_Adaptive_Statistics=true
86 Oracle SQL Tuning
Statistiques feedback (retour de statistiques)

Après l’exécution
 Compare les lignes estimées et lignes réelles et corrige
pour la prochaine exécution.

87 Oracle SQL Tuning

 Chapitre 5

Dynamic sampling & Symétrie

Encours du
Parsing

OUI Absence Non

statistiques

OUI NON NON

Dynamic Statistics Histogramme OUI
est actif est présent

Optimizer estime la
Dynamic Statitics Optimizer estime CBO estime la
card=no. rows/NDV
estime la cardinalité la cardinalité cardinalité
(stats erronées
(stats basiques) (stats erronés) (stats correctes)
en cas de non symétrie)

88 Oracle SQL Tuning

Types de statistiques Oracle
Fin d’Exécution
Statistiques persistantes
(tables, indexes, histogrammes) Statistiques feedback
- Compare estimé vs réél
- Corrige cardinalité
PARSE/ OPTIMISATION (CBO) - Pour la prochaine exécution

Statistiques dynamiques:
Directives (SQL Plan Directives 12c)
- Échantillonne les données
- Recommendation dynamic sampling
- Si stats absentes / douteuses
-Suggèrent extended statistics

Statistiques adaptatives
- Interprètent les infos
- Corrélations / skew / prédicats
complexes Exécution
- Ajustent les estimations
Plan adaptatif (si présent)
- Choix du chemin réél
OUI Plan classique - NLHash Join
Estimation fiable? - IndexFull SCan
Plan adaptatif
NON (plusieurs chemins possibles)

89 Oracle SQL Tuning

 Chapitre 6

Oracle Hints

90 Oracle SQL Tuning

 Chapitre 6

Objectifs du chapitre 6

A la fin de ce cours, vous pourrez :

 Forcer l’optimiseur Oracle à choisir un plan différent en
utilisant les hints.

91 Oracle SQL Tuning

 Chapitre 6

Hint
 Hint est une instruction ou (un commentaire ) adressée à l'optimiseur,
qui peut l'inciter à choisir un plan différent pour l’exécution d’une
instruction SQL.
 Hints doivent être utilisés en dernier recours si les statistiques sont
correctement collectées et que la requête suit toujours un plan
d'exécution optimal.
 Oracle supporte plus de 60 hints, chacun pouvant avoir zéro ou
plusieurs paramètres. Une requête SQL peut contenir un seul
commentaire contenant des hints, et ce commentaire doit suivre le
mot-clé SELECT, UPDATE, INSERT, MERGE ou DELETE.
 Syntaxe : SELECT /*+ hint */ FROM …
Ou bien SELECT --+ hint FROM …
avec requête (select, update, delete, insert ou merge)
92 Oracle SQL Tuning
 Chapitre 6

Exemples de Hints
SELECT /*+ */ FROM
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT /*+ FIRST_ROWS (15) */ * From emp where deptno=10;
SELECT /*+ ALL_ROWS*/ * From emp where deptno=10;
SELECT /*+ PARALLEL(4)*/ From emp;
SELECT /*+ NO_INDEX(emp emp_dept_idx) */ from emp, dept where
[Link]=[Link];
SELECT /*+ INDEX(e emp_dept_idx) */ from emp e;
SELECT /*+ LEADING(dept) */ from emp, dept where [Link]=[Link];
(/*+ ordered*/ forcer le CBO à choisir l’ordre après le from)
SELECT /*+ USE_MERGE(emp dept) */ from emp, dept where
[Link]=[Link];
SELECT /*+ USE_HASH(emp dept) */ from emp, dept where
[Link]=[Link];
SELECT /*+ USE_NL(emp dept) */ from emp, dept where [Link]=[Link];
INSERT /*+ APPEND */ INTO mytab SELECT /*+ CACHE (e) */ from emp e;
Commit;
93 Oracle SQL Tuning
 Chapitre 6

Exemples de hints
Hint Description
ALL_ROWS choisir le plan qui renvoie toutes lignes le plus efficacement possible.
FIRST_ROWS(n) Choisir le plan qui renvoie les premiers lignes dont le nombre n est passé en
paramètre le plus efficacement possible.
RULE Force l’utilisation de l’approche RBO à la place de CBO.
Depuis Oracle 12c, le RBO est supprimé, pas juste déprécié.
FULL (t) / ROWID Force un Full Scan de table, Force BY INDEX ROWID Scan sur la table
(t)
INDEX(e i) Forcer un index scan de la table en utilisant le(s) index(es) spécfié(s). Si la liste
des indexes est spécifiée, l’optimiseur choisit celui avec le coût minimal. Sinon,
l’optimiseur choisit un index disponible avec un coût minimal.
NO_INDEX(t i) indique à l'optimiseur de ne pas utiliser un ou plusieurs index pour la table
spécifiée.
PARALLEL( n) L'indication PARALLEL indique à l'optimiseur d'utiliser le nombre spécifié de
serveurs simultanés pour une opération parallèle.
GATHER_PLAN_S Collecte des statistiques d’exécution de la requête dans le curseur.
TATISTICS

94 Oracle SQL Tuning

 Chapitre 6

Exemples de hints
Hint Description
INDEX_UNIQUE(t i) Pour forcer l’utilisation d’une méthode d’accès à un index
INDEX_RS, Exemple: INDEX_RS(EMP SALARY_IDX)
INDEX_FFS,
INDEX_FS, INDEX_SS
BITMAP (t i) Pour forcer l’utilisation d’un index bitmap
/NO_BITMAP(t) Exemple: SELECT /*+ BITMAP(emp emp_genre_idx) */
Empêche l’utilisation d’index bitmap:
SELECT /*+ NO_BITMAP(emp) */ * FROM emp WHERE deptno = 10
USE_NL (t1 t2) / Pour forcer l’utilisation d’une méthode d’accès à un index
USE_HASH /
USE_MERGE
INDEX_ASC(t i) / Force l’optimiseur à utiliser un index en parcours ascendant ou
INDEX_DESC(t i) descendant. Exemple:
SELECT /*+ INDEX_ASC(t idx_date) */ * FROM commandes t
ORDER BY date_cmd ASC;

95 Oracle SQL Tuning

 Chapitre 5

Hints ignorés

Un hint est ignoré s’il est :

 Mal écrit
 Incompatible
 Contradictoire (conflits de hints, exemple: SELECT /*+ INDEX(e emp_idx1) FULL(e) */)
 Rendu inutile par une transformation (requête réécrite par CBO (Query
Transformer))
 Impossible à appliquer (SELECT /*+ INDEX_RS(e name_idx) */ WHERE UPPER(name) =
'SMITH‘);

Pour Vérifier qu’un hint est ignoré (on peut vérifier le rapport de hints avec: )

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST
+HINT_REPORT'));

96 Oracle SQL Tuning

 Chapitre 7

Oracle SQL Tuning

97 Oracle SQL Tuning

 Chapitre 7

Objectifs du chapitre 7

A la fin de ce cours, vous pourrez :

 Distinguer entre les types de tuning (proactif /reactif) et les
types d’outils de tuning (automatisé/manuel).

 Identifier les tâches de SQL Tuning et les causes des problèmes

de performance.

98 Oracle SQL Tuning

 Chapitre 7

Introduction à SQL Tuning

SQL Tuning ou (Réglage SQL) est un processus itératif d'amélioration
des performances des instructions SQL pour atteindre des objectifs
spécifiques, mesurables et réalisables.
Le tuning peut s’effectuer de manière proactive ou réactive.

oDans le tuning proactif, on utilise régulièrement SQL Tuning Advisor

pour déterminer si on peut améliorer les performances des
instructions SQL.

oDansle Tuning réactif, on corrige un problème lié à une requête

SQL expérimenté par un utilisateur.

99 Oracle SQL Tuning

 Chapitre 7

Outils de tuning
Les outils de tuning SQL sont automatisés ou manuels.

 Un outil est automatisé si la base de données elle-même peut

fournir un diagnostic, des conseils ou des actions correctives.
Exemple d’outils: ADDM, SQL Tuning Advisor,

 Un outil manuel nécessite que vous effectuiez toutes ces

opérations.

Tous les outils de tuning dépendent des vues dynamiques de

performances, des statistiques et des métriques que l'instance de base de
données collecte.

100 Oracle SQL Tuning

 Chapitre 7

Tâches de SQL Tuning

Utilisant un outil automatisé ou manuel, SQL Tuning comporte les tâches suivantes:

1. Identifier les instructions SQL à charge élevée.

2. Collecter des données relatives aux performances.

3. Déterminer les causes des problèmes de performances SQL.

4. Définir la portée du problème.

5. Implémenter des actions correctives pour les instructions SQL moins

performante (éviter hard parsing, utiliser equi-jointures, éviter les fonctions
dans where, etc).

6. Prévenir les régressions des performances SQL.

101 Oracle SQL Tuning

 Chapitre 7

Causes de Problèmes de Performance dans SQL

Les problèmes de performances SQL peuvent parvenir de :
 Mauvaise conception des instructions SQL : Si une instruction SQL est écrite de manière à
effectuer un travail inutile, l'optimiseur ne peut pas faire grand-chose pour améliorer ses
performances. Par exemple :
o Jointure cartésienne;
o Utiliser des hints pour spécifier une grande table comme leading table dans une jointure;
o Spécifier UNION ALL au lieu de UNION;
o Faire exécuter une sous-requête pour chaque ligne d'une requête externe;
 Choix des plans d'exécution sous-optimaux: L'optimiseur choisit un plan avec un chemin
d'accès sous-optimal. Par exemple, le plan d'un prédicat de requête avec une faible sélectivité peut
utiliser une analyse complète de table sur une grande table au lieu d'un index.
 Structures d'accès SQL manquantes: L'absence de structures d'accès SQL, telles que les index
et les vues matérialisées, est une raison typique de performances SQL sous-optimales.
 Statistiques d'optimisation obsolètes : Les statistiques collectées par DBMS_STATS peuvent
devenir obsolètes lorsque les opérations de maintenance des statistiques, automatiques ou manuelles,
ne peuvent pas suivre les modifications des données de la table causées par DML.
 Problèmes matériels: Les performances sous-optimales peuvent être liées à des problèmes de
mémoire, d'E/S et de processeur.
102 Oracle SQL Tuning
 Chapitre 7

Tuning manuel (1)

 Plans d'exécution : les plans d'exécution constituent le principal outil de
diagnostic dans le réglage SQL manuel. Différentes manières pour l’afficher:
oDBMS_XPLAN: le package qui contient les fonctions pour afficher le plan d'exécution
généré par la commande Explain for (sans exécution de la requête).
oV$SQL_PLAN: La vue qui contient des informations sur les instructions SQL exécutées
et leurs plans d'exécution.
oAUTOTRACE: La commande AUTOTRACE de SQL*Plus génère le plan d'exécution et
les statistiques sur les performances d'une requête (SQL>set Autotrace ON/OFF).

 Real-Time SQL Monitoring: La fonctionnalité de surveillance SQL en temps

réel surveille les performances des requêtes SQL pendant leur exécution. Par
défaut, la surveillance SQL démarre automatiquement lorsqu'une requête s'exécute
en parallèle ou lorsqu'elle a consommé au moins 5 secondes de temps CPU ou
d'E/S. Le hint /*+ MONITOR*/ permet de lancer une session de surveillance real-
time même pour une requête qui s’exécute rapidement.

103 Oracle SQL Tuning

Autotrace
Avec AUTOTRACE, la requête est toujours exécutée. Quant à l'affichage d’Autotrace, il dépend
des options utilisées:

Option Description

SET AUTOTRACE ON Affiche le résultat de la requête, le plan d'exécution réel et les statistiques.
SET AUTOTRACE ON EXPLAIN Affiche les résultats et le plan d'exécution théorique.
SET AUTOTRACE ON STATISTICS Affiche les résultats et les statistiques d'exécution.

SET AUTOTRACE TRACEONLY Masque le résultat de la requête, mais affiche le plan réel et les statistiques.
SET AUTOTRACE TRACEONLY
Affiche uniquement le plan d'exécution théorique.
EXPLAIN
SET AUTOTRACE TRACEONLY
Affiche uniquement les statistiques d'exécution.
STATISTICS
SET AUTOTRACE OFF Désactive l'affichage des plans et statistiques.

104 Oracle SQL Tuning

 Chapitre 7

Tuning manuel (2)

 SQL Trace Facility et TKPROF: Permettent une évaluation détaillée et concise de l'efficacité des
instructions SQL exécutées par votre application.
SQL Trace Facility crée les fichiers de trace (contenant [Link], [Link]écutions, [Link],
CPU/temps écoulé, lectures physiques/logiques, rows, miss library cache, nom utilisateur,
commit/rollback, …) et TKPROF formate le fichier de trace et génère un rapport tkp.
Nom de fichier de trace est composé ainsi : <$ORACLE_SID>_ora_<Process_id>.trc
oTrace sur session (crée un fichier de trace pour toutes les requêtes exécutées dans une session
utilisateur) fichier
SQL>alter session set sql_trace=true (session en cours) ou bien: SQL Trace
trace(.trc)
SQL>EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE;
rapport
DB TKPROF
oTracer une instance (crée un fichier de trace par processus (.tkp)
serveur) à travers le paramètre [Link] : sql_trace=true.

 Avant d'activer la fonction SQL Trace Facility , vous devez vérifier les paramètres d’initialisation
Timed_statistics, Max_dump_file_size qui signifient les statistiques chronométrées=true, la taille des
fichiers de traces=unlimited (show parameter timed_statistics; show parameter
max_dump_file_size;)
Le dossier où se trouve les fichiers de trace: C:\APP\VMUSER\diag\rdbms\orcl\orcl\trace\
Et on n'oublie pas de désactiver la trace pour éviter de générer un fichier trop volumineux!
105 Oracle SQL Tuning
 Chapitre 7

TKPROF (1)
 TKPROF c’est un programme (lancé à partir de msdos) dont son exécution formate le
contenu du fichier de trace et place le résultat dans un fichier lisible. En option, Il peut aussi:
o Générer les plans d’exécution des requêtes.
o Générer un script SQL pour enregistrer les statistiques dans la BD.
 TKPROF peut être exécuté avec les arguments suivants:
Argument Description
filename1 Spécifie le fichier d'entrée, un fichier de trace contenant des statistiques produites
par la fonction de trace SQL.
filename2 Spécifie le fichier dans lequel TKPROF écrit sa sortie formatée.
AGGREGATE Si vous spécifiez AGGREGATE = NO, alors TKPROF n'agrège pas plusieurs
utilisateurs du même texte SQL.
EXPLAIN Détermine le plan d'exécution de chaque instruction SQL dans le fichier de trace et
écrit ces plans d'exécution dans le fichier de sortie.
TABLE Spécifie le schéma et le nom de la table dans laquelle TKPROF place temporairement
les plans d'exécution avant de les écrire dans le fichier de sortie.
INSERT Crée un script SQL qui stocke les statistiques du fichier de trace dans la base de
données.
106 Oracle SQL Tuning
 Chapitre 7

TKPROF (2)
Argument Description
SORT Trie les instructions SQL tracées dans l'ordre décroissant de l'option de tri:
PRSCNT nombre de fois analysées
PRSCPU Temps CPU consacré à l'analyse
Pour PRSELA Temps écoulé consacré à l'analyse
l’exécution et
fetch PRSDSK Nombre de lectures physiques à partir du disque pendant l'analyse
remplacer PRSQRY Nombre de buffers lus à partir de la mémoire pendant l’analyse (consistent mode
(PRS par EXE souvent avec Select).
ou FCH)
PRSCU Nombre de buffers lus à partir de la mémoire pendant l’analyse (current mode souvent
avec DML).
En plus pour PRSMIS Nombre d'échecs (miss) de library cache pendant l'analyse
l’exécution et
EXEROW/ nombre de lignes traitées pendant l'exécution/nombre de lignes renvoyés
le fetch
FCHROW

PRINT répertorie uniquement les premières instructions SQL triées par nombre entier dans le
fichier de sortie.
Exemple: TKPROF [Link] [Link] Dans cet exemple, l'instruction suivante imprimera les
SORT = (PRSDSK, EXEDSK, FCHDSK) dix instructions du fichier de trace qui ont généré le
PRINT = 10 plus d'E/S physiques.
107 Oracle SQL Tuning
 Chapitre 7

Rapport TKPROF
 Rapport avec l’option Explain:
tkprof hrdb_ora_6712.trc hrdb_ora_6712.tkp explain=hr/hr@pdb
(i.e se connecter en tant qu'user hr et utiliser la commande EXPLAIN PLAN pour générer le plan
d'exécution de chaque instruction SQL tracée).
Entête du rapport Corps du rapport

Après avoir exécuté tkprof, le rapport généré contient

trois sections :En-tête, Corps, Résumé

Résumé du rapport

108 Oracle SQL Tuning

 Chapitre 7

TKPROF et appels récursifs

 Parfois, pour exécuter une instruction SQL, Oracle doit émettre des instructions
supplémentaires. Ces instructions sont appelées appels récursifs ou instructions SQL
récursives. Par exemple, si vous insérez une ligne dans une table qui ne dispose pas de
suffisamment d'espace pour contenir cette ligne, Oracle effectue des appels récursifs
pour allouer l'espace de manière dynamique.
 Des appels récursifs sont également générés lorsque les informations du dictionnaire
de données ne sont pas disponibles dans le cache du dictionnaire de données et
doivent être récupérées à partir du disque.
 Si des appels récursifs se produisent alors que la SQL trace facility est activée,
TKPROF produit des statistiques pour les instructions SQL récursives et les marque
clairement comme telles dans le rapport généré.

109 Oracle SQL Tuning

 Chapitre 8

Outils automatisés de
Oracle SQL Tuning

110 Oracle SQL Tuning

 Chapitre 8

Objectifs du chapitre 8

A la fin de ce cours, vous pourrez :

 Utiliser les outils de diagnostic et tuning automatiques: SQL
Tuning Advisor, SQL Access Advisor, et SQL Plan Management.

 Génération et lecture des rapports AWR, ADDM, et ASH.

111 Oracle SQL Tuning

 Chapitre 8

Tuning proactif avec SQL Tuning Advisor (1)

SQL Tuning Advisor analyse les requêtes SQL et offre des recommandations. Il peut prendre en
entrée une ou plusieurs requêtes SQL et invoque l’optimiseur dit Automatic Tuning Optimizer
pour effectuer des réglages sur les requêtes.
Lorsque SQL Tuning Advisor appelle l'optimizer celui-ci est appelé Automatic Tuning Optimizer.
Dans ce cas, l’optimizer effectuer des analyses supplémentaires par rapport au mode normal.
Ilpeut être lancé suite à la demande du DBA ou automatiquement en tant que tâche de
maintenance automatisée, SQL Tuning Advisor devient Automatic SQL Tuning Advisor.
SQL Tuning Advisor fournit des conseils sous la forme d'actions SQL précises pour optimiser
les instructions SQL ainsi que les bénéfices attendus en termes de performances. Toutes ces
conseils sont générés aussi dans un rapport détaillé.
La recommandation ou le conseil peut être :
o Collecte de statistiques sur les objets,
o Création de nouveaux index,
o Restructuration des instructions SQL,
o Création d'un profil SQL.
Vous pouvez choisir d'accepter une recommandation pour
effectuer le réglage des instructions SQL.
112 Oracle SQL Tuning
 Chapitre 8

Tuning proactif avec SQL Tuning Advisor (2)

 SQL Tuning Advisor (Ctl+F12)

 Pour permettre à utilisateur non sysdba d’utiliser SQL

Tuning Advisor, vous devez lui attribuer les privilèges
suivants: Advisor et administrer sql tuning set

 Par exemple:
 grant advisor to hr;
 grant administrer sql tuning set to hr;
 grant administer sql management object to hr;

113 Oracle SQL Tuning

 Chapitre 8

Tuning automatique/à la demande

 Configurez SQL Tuning Advisor pour qu'il s'exécute automatiquement à l'aide
de DBMS_AUTO_SQLTUNE ou à la demande à l'aide de DBMS_SQLTUNE.
 Les méthodes d'appel diffèrent comme suit :
• Automatiquement,: Vous pouvez configurer SQL Tuning Advisor pour qu'il
s'exécute en nocturne par exemple . Lorsqu'il est exécuté par AUTOTASK,
Advisor est appelé Automatic SQL Tuning Advisor et effectue un réglage SQL
automatique.

• À la demande: Dans le réglage SQL à la demande, vous appelez manuellement

SQL Tuning Advisor pour diagnostiquer et résoudre les problèmes de
performances liés à SQL après leur découverte. Oracle Enterprise Manager
Cloud Control (Cloud Control) est l'interface préférée pour le réglage SQL à
la demande, mais vous pouvez également utiliser le package PL/SQL
DBMS_SQLTUNE.

114 Oracle SQL Tuning

 Chapitre 8

Activer Automatic SQL Tuning

Pour activer le réglage SQL automatique à l'aide de:
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE (
client_name => 'sql tuning advisor' ,
operation => NULL ,
window_name => NULL );
END; /
Affichage du rapport de Automatic SQL Tuning Advisor
VARIABLE my_rept CLOB;
BEGIN :my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK (
begin_exec => NULL , end_exec => NULL , type => 'TEXT' , level => 'TYPICAL'
, section => 'ALL' , object_id => NULL , result_limit => NULL ); END; /
PRINT :my_rept

115 Oracle SQL Tuning

 Chapitre 8

SQL Profile
 Un SQL profile est un objet de la base de données stocké dans le dictionnaire
et contenant des corrections pour les estimations d'optimiseur sous-
optimales découvertes lors du SQL Tuning automatique.
 Automatic Tuning Optimizer crée un SQL profile qui utilise des données
collectées à partir de l'exécution réelle des requêtes, ce qui peut améliorer la
précision des estimations de cardinalité et de sélectivité.
Le profilage SQL (SQL Profiling) est la
vérification par l'Automatic Tuning
Optimizer de ses propres estimations.

Pour afficher les profils SQL du

dictionnaire:
SQL> SELECT * FROM
DBA_SQL_PROFILES;

116 Oracle SQL Tuning

 Chapitre 8

SQL Access Advisor

 SQL Access Advisor est un logiciel de tuning qui identifie et aide à résoudre les
problèmes de performances SQL en recommandant des index, des vues matérialisées,
des journaux de vues matérialisées ou des partitions à créer, supprimer ou conserver.
 Les vues matérialisés, partitions et index sont essentiels lors du réglage d'une base de
données pour obtenir des performances optimales pour les requêtes complexes et
gourmandes en données.
SQL Access Advisor nécessite une charge de travail
composée d'une ou plusieurs instructions SQL, leurs
statistiques et attributs décrivant chaque instruction.
Cette charge de travail peut provenir de ces sources:
oZone SQL partagée (pour analyser les instructions
récentes qui sont actuellement dans V$SQL.
oEnsemble de réglages (STS) (objet qui stocke les
instructions SQL avec leur contexte d'exécution).
oCharge de travail hypothétique pour analyse des
dimensions et des contraintes dans un schéma DW.

117 Oracle SQL Tuning

 Chapitre 8

Utilisation de SQL Access Advisor

DBMS_SQLTUNE

DBMS_ADVISOR
118 Oracle SQL Tuning
 Chapitre 8

SQL Tuning Set (STS)

Le SQL Tuning Set regroupe un ensemble d’ordre SQL ainsi que les métadonnées
associées. C’est un outil qui permet de regrouper des requêtes stockées dans le SQL
Cahche, AWR, ou un autre set et d’en appliquer les outils de tuning: SQL Tuning Advisor
et SQL Access Advisor. Pour créer un STS, vous devez charger des instructions SQL dans
un STS à partir d'une source.
Un STS contient:
• Un ensemble d'instructions SQL
• Contexte d'exécution associé, tel que le schéma utilisateur, le nom et l'action du module
d'application,
• la liste des valeurs bind et l'environnement de compilation SQL du curseur.
• Statistiques d'exécution de base associées,(temps écoulé, le temps CPU, les get buffers, les
lectures de disque, [Link] traitées, no. d'exécutions, coût, etc
• Plans d’exécution associées
 Créer un SQL Tuning Set
SQL> begin
dbms_sqltune.create_sqlset(sqlset_name=>‘SQL_SET1',
description=>'this is a SQL Tuning Set');
end;
119 Oracle SQL Tuning
 Chapitre 8

Remplissage du SQL Tuning Set

 Charger des requêtes à SQL Tuning Set à partir du SQL cache area
Par exemple, le programme PL/SQL suivant remplit le STS précédent avec toutes les instructions
de cache de curseur qui appartiennent au schéma hr :
DECLARE
cur dbms_sqltune.SQLSET_CURSOR;
BEGIN
OPEN cur FOR SELECT VALUE(P) FROM
dbms_sqltune.select_cursor_cache(basic_filter=>
'parsing_schema_name = ''HR'' and sql_text like ''SELECT%''') P;
dbms_sqltune.load_sqlset(sqlset_name => ‘SQL_SET1',populate_cursor =>cur);
CLOSE cur;
END;
/

120 Oracle SQL Tuning

 Chapitre 8

Créer une tâche SQL Tuning Advisor

 Lancer le SQL Tuning Advisor avec le STS
en créant une tâche de maintenance:
SQL> DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => ‘SQL_SET1’,
scope => ‘comprehensive’,
time_limit => 60,
task_name => ‘task1’,
description => ‘Tuning task for an SQL tuning set.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ ||
l_sql_tune_task_id);
END;
/

121 Oracle SQL Tuning

 Créer une tâche SQL Access Advisor
exec
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLAC
CESS_ADVISOR, task_name=> 'task2');

Paramètre ANALYSIS_SCOPE=ALL de la tâche

// La recommendation va porter sur ALL=INDEX, MVIEW, TABLE, PARTITION
Sinon on peut spécifier un scope restreint sur INDEX et MVIEW ou PARTITION
uniquement
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('task2',
'ANALYSIS_SCOPE', 'ALL');

122 Oracle SQL Tuning

 Chapitre 1
Exécution du SQL Tuning Task Pour STA &
SAA
Pour SQL Tuning Advisor (STA)
SQL>exec
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name
=> 'task1' );

Pour SQL Access Advisor (SAA)

Associer SQL_SET1 à task2 et l’exécuter après
SQL> exec
dbms_advisor.ADD_STS_REF(‘task2', 'HR',‘SQL_SET1‘);
SQL> exec dbms_advisor.execute_task('task2');

123 Oracle SQL Tuning

 Chapitre 1
Affichage du rapport du SQL Tuning Task pour
STA et SAA
Pour SQL Tuning Advisor (STA)
SQL> SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘task1') FROM DUAL;

Pour SQL Access Advisor (SAA)

Afficher les recommandations
SQL> SET LONG 100000;
SET PAGESIZE 50000;
SELECT DBMS_ADVISOR.get_task_script(‘task2') AS script FROM DUAL;
Générer un script pour l’exécuter
create or replace directory MY_DIR as 'C:\doss';
Grant all on directory MY_DIR to SH;
EXEC DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('tasksh'),
'MY_DIR', '[Link]');
124 Oracle SQL Tuning
 Chapitre 8
Dégradation des performances suite aux
changements des plans d’exécution
Oracle Optimizer est capable de sélectionner les meilleurs plans d'exécution, mais
des changements dans les plans d'exécution peuvent survenir en raison de divers
facteurs tels que :

o Mise à niveau de la base de données (upgrade de Oracle Optimizer),

o Mise à jour des statistiques d'optimisation,
o Changements dans les définitions de schéma,
o Modifications dans les paramètres d'optimisation,
o etc.

Ces changements de plan inattendus peuvent parfois provoquer des changements

dans les plans d’exécution et dégrader ainsi les performances précédentes, ce qui
a conduit à rechercher des solutions offrant la stabilité des plans d’exécution.

125 Oracle SQL Tuning

 Chapitre 8

SQL Plan Management

 SPM mécanisme permettant plus de contrôle sur comment l’optimiseur

choisit les plans d’exécution et évite la régression des performances
(stabilité des plans).

 Les plans d’exécution sont capturés et stockés dans SQL Management Base
(SMB) entrepôt logique se trouvant physiquement dans le tablespace
SYSAUX.

 Les plans sont « acceptés » pour pouvoir être utilisés.

SMB
Plan Plan

Requête
SQL

126 Oracle SQL Tuning

 Chapitre 8

Définitions et Concepts
 SQL Plan Management (SPM)
 Mécanisme pour améliorer la stabilité et le contrôle des plans.
 Plan History
 Ensemble de plans générés par le CBO (acceptés ou baselines et non-
acceptés).
 SQL Plan Baselines
 Ensemble de plans acceptés que l'optimiseur est autorisé à les utiliser
pour une instruction SQL. Un plan n’est accepte que lorsque SPM vérifie
que celui-ci fonctionne bien.
 SQL Management Base (SMB)
 Entrepôt de SYSAUX dans le dictionnaire qui sauvegarde les plans: Plan
History, SQL Plan Baseline and SQL Profiles .

127 Oracle SQL Tuning

 Chapitre 8

Composants de SPM
 Plan Capture
Automatique : activée en définissant OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES sur
TRUE. Cela capture les plans des instructions SQL répétables, garantissant que seuls les plans
d'exécution les plus fiables sont utilisés.
Manuelle : permet aux DBA de charger manuellement les plans d'exécution à partir de
diverses sources telles que les ensembles de réglages SQL, le cache du curseur ou le
référentiel AWR. DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE()
 Plan Selection
Garantit que seuls les plans d'exécution acceptés sont utilisés pour les requêtes SQL avec
un plan baseline. Si un nouveau plan est trouvé, il est marqué comme non accepté jusqu'à ce
qu'il soit vérifié qu'il fonctionne mieux que le plan actuel. Utilise un système de
correspondance basé sur les signatures pour comparer les nouveaux plans avec les baselines
existantes, garantissant une sélection de plan efficace sans surcharge significative.
 Plan Evolution
Les nouveaux plans sont ajoutés comme non acceptés et doivent subir une vérification pour
être acceptés. Cela implique de comparer les performances du nouveau plan avec le plan
accepté actuel.
128
 Chapitre 8
Étapes de sélection d’un plan d’exécution par
SPM
1. L’optimiseur CBO génère un plan d’exécution pour une instruction SQL , l'optimiseur
vérifie si un plan baseline existe pour cette instruction. Sinon, il exécute le plan généré.
2. Les instructions SQL sont mises en correspondance avec les lignes de base de plan SQL
à l'aide de la signature de l'instruction SQL. Une signature est un identifiant SQL unique
généré à partir du texte SQL normalisé (sans casse et sans espaces).
3. Si une correspondance est trouvée et qu’ un plan baseline est dans un état accepté,
l'optimiseur continue avec ce plan.
4. Sinon, si aucune correspondance n'est trouvée, le plan nouvellement généré est ajouté
aux plans baselines en tant que plan non accepté. Il devra être vérifié avant de pouvoir
être accepté.
5. Au lieu d'exécuter le plan nouvellement généré, l'optimiseur calculera le coût de chacun
des plans acceptés pour l'instruction SQL et choisira celui qui a le coût le plus bas
(notez qu’on peut avoir plusieurs plans acceptés pour une instruction donnée).
6. Cependant, si un changement dans le système (comme un index supprimé) entraîne la
non-reproductibilité de tous les plans acceptés, l'optimiseur utilisera le plan basé sur les
coûts nouvellement généré et stockera ce plan dans l'historique du plan SQL en tant
que plan non accepté.
129 Oracle SQL Tuning
 Chapitre 8
Diagramme de flux de sélection d’un plan
d’exécution par SPM
Est-ce que Est-ce que le
Requête Est-ce que plan
le plan est
SQL baseline reproductible? Non
dans le
existe?
baseline?
MJ
Oui Oui Exécuter le matched
baseline plan
Non Est-ce qu’il Est-ce que le plan fixé
A B ya un plan est reproductible?
Plan généré baseline Oui Oui Exécuter le plan
fixé? baseline fixé
Non
Non
Exécuter le
plan généré Ajouter le plan généré
Comparer les coûts
aux baselines comme
des plans acceptés
non accepté (en
dans le baseline
attente d’être accepté)
Ajouter le plan
généré au History Exécuter le plan Oui Est-ce que les
plan comme non reproductible le plans sont
accepté moins coûteux reproductibles
Non
130 Oracle SQL Tuning
 Chapitre 8

États des plans d’exécution et leurs emplacements

États d’un plan: SQL Management Base

History Plan
o Accepted: vérifié et autorisé SQL Plan Baseline En attente
pour être utilisé pour l’exécution. MJ NL d’être accepté

En attente de
o Enabled: active. Un plan Plan evolution A B A B
accepté ne peut être modifié à
non-accepté. Le seul moyen de HJ

l’utiliser ou non c’est l’activer ou

le désactiver. A B
Plan non
NL NL reproductible
o Fixed: désigner un plan des
plans baselines qu’on veut
utiliser. A B A B

131 Oracle SQL Tuning

 Chapitre 8

Principes ADDM et ASH

 MMON capture les statistiques à partir de la
SGA toutes les 60 minutes et les stocke dans
l’Automatic Workload Repository (AWR)
sous forme de snapshots.
 Automatic
Database Diagnostic Monitoring
(ADDM) est programmé par le processus
MMON pour tourner automatiquement.
 Chaque fois qu’un snapshot est créé, ADDM
est activé pour faire une analyse préventive
de la période correspondant aux 2 derniers
snapshots.
 MMNL collecte les statistiques sur les
sessions actives chaque seconde et les place à
un ASH buffer. Lorsque cette mémoire est
Les rapports AWR, ADDM
pleine(66%), il flushe son contenu dans la vue
et ASH peuvent être
DBA_HIST_ACTIVE_SESS_HISTORY dans
générés à la demande.
AWR.

132 Oracle SQL Tuning

 Chapitre 8

Automatic Workload Repository (AWR)

 AWR est un composant essentiel d'Oracle pour surveiller et analyser les
performances de la base de données.
 Il Contient des snapshots: ou clichés de performance qui concerne:
o Load Profile (DB load)
o Status de l’instance
o Top wait events
o Top wait events classes
o Top SQL(requêtes SQL qui consomment le plus de ressources)
o…
 Par défaut: la fréquence de génération des snaphots est chaque heure(tt la
semaine). Les snapshots sont maintenues pour 8 jours.
 Pour changer la fréquence, on utilise le package:
 DBMS_workload_repository.modify_snapshot_settings(retention=>1440
0, interval>=35) // valeurs en min.

133 Oracle SQL Tuning

 Chapitre 8

Génération d’un rapport AWR

 Supposons que la BD est lourde le 19 sep de10h à14h (Période du TP
insertion dans la table étudiant). Pour générer un rapport AWR:
 Se connecter
C:/>sqlplus /nolog
SQL> connect sys/oracle@orcl as sysdba

 Commande de génération du rapport

 SQL>@$ORACLE_HOME/rdbms/admin/[Link]
 Enter value for report type: text ou html
 Specify the number of days of snapshots to chose from:
 Specify the begin and the end snapshots Ids:
 Les Ids à entrer doivent correspondre à 10h et 14h (éviter d’avoir des
rapports généraux qui contiennent des moyennes de performances. Il
préférable de générer des rapports pour chaque heure. un rapport de
10à 11, un autre 11 à12 et13à14)
134 Oracle SQL Tuning
 Chapitre 8

Lecture du rapport AWR (1)

 Specify Report name:
report19sep_69_70
 À la fin de la génération vous
trouvez le nom du rapport :
report19sep_69_70.html.
 Ouvrir à
C:/>Windows/System32
Possible Pb:
volume énorme
de redo

no. I/O par

transaction est elevé
(write>read)

no. de requêtes
utilisateurs par
135 trans elevé Oracle SQL Tuning
 Chapitre 8

Lecture du rapport AWR (2)

 Tous les pourcentages doivent être
proches de 100% à l’exception du
dernier:
 Parse CPU to Parse
Elapsed%
 Si c’est le cas, il n’ya pas de problème
pour votre instance.
 Parse CPU to Parse Elapsd%:
signifie combien de temps CPU passe
pour analyser la requête.
 Execute to parse%: le taux du
nombre de fois qu'une requête SQL
est exécutée et le nombre de fois
qu'elle est analysée (si proche de
100% c’est bien car plus d’exécution
que de compilation, et vice versa).
136 Oracle SQL Tuning
 Chapitre 8

Lecture du rapport AWR (3)

L’utilisation de la mémoire
partagé (pourcentage sain):
- Memory Usage% doit être
comprise entre 60% et 85%.

Sinon, elle doit être

redimensionnée:
- SQL>ALTER SYSTEM SET
SHARED_POOL_SIZE=<value>M

137 Oracle SQL Tuning

 Chapitre 8

Lecture du rapport AWR (4)

Performance BD Problème (appelé en oracle « wait »)

o Buffers
o Verrou (Verrou bloquant)
o Disque I/O (accès concurentiel à un
Wait events secteur par plusieurs transactions)
o CPU (95% utilisé par d’autres logiciels)
o…

o File type wait event

o Buffer type wait event
Wait classes o…

Un DBA n ’a besoin de connaitre tous les wait events ou les wait

classes. Le plus important c’est qu’il arrive à distinguer entre les deux.
138 Oracle SQL Tuning
 Chapitre 8

Lecture du rapport AWR (4) Les wait events sont triés

par Total Wait Time.
Pour identifier le pb
n’utilisez pas le no. waits
mais le tot. wait time

PB1: Switch
dans le redolog

PB2: lecture
séquentielle
PB4: Pas assez
PB3: de Full Scan de DBWn
Solutions Probables:
PB1: Augmenter la taille du redolog
PB2: Optimiser requête SQL (créer
indexes ou rebuild).
PB3: Créer des indexes selon les
recommendations de SQL Tuning
Advisor.
PB4: SQL>alter system set Aperçu des wait events à partir d’un environnement de
db_writer_processes=10 scope=spfile sid='* production
139 Oracle SQL Tuning
 Chapitre 8

Lecture du rapport AWR (5)

Pour Identifier les Top SQL requêtes qui posent problème Allez dans
Main Report SQL Statistics:

SQL qui pose pb

en cliquant sur
son ID, on accède
au sql texte

140 Oracle SQL Tuning

 Chapitre 8

Opération Bulk
Problème Solution: Bulk Operation (Urine)
Va-Vient récurrent entre les DECLARE
moteurs PL/SQL et SQL à chaque
itération(context switching) TYPE MyCollType IS TABLE OF NUMBER;
begin mycol MyCollType:=MyCollType();
for i in 1..142500 loop begin
insert into étudiant(id) [Link](150000);
values([Link]); for i in 1..150000 loop
mycol(i):=i; Forall regrouper les
end loop; requêtes dans une
for i in 1..6000 loop end loop; urine avant de les

insert into étudiant(id, FORALL i in 1..142500 envoyer au moteur

SQL
nationalité) insert into etudiant2(id)
values([Link], 'SEN'); values(mycol(i));
end loop; -Épuisement espace Udo segment FORALL i in 1..6000
… -Une transaction longue (temps
insert into etudiant2(id,
d’exécution) nationalite)
Commit; values(142500+mycol(i), 'SEN');
-Verrouillage ou contention de table
end; …
141 Oracle SQL Tuning
 Chapitre 8
Automatic Database Diagnostic Monitoring
(ADDM)
 Automatic Database Diagnostic Monitoring (ADDM) est un outil de
diagnostic et de tuning permettant de génère des rapports analysant
les données AWR. Il détecte la cause du problème affectant la
performance de la BD, et fournit les différentes suggestions ou
recommandations pour le résoudre.
 Le rapport peut être généré de la manière que celui du AWR.
 Génération du rapport ASH
SQL>@$ORACLE_HOME/rdbms/admin/[Link]
Si il n’ya pas d’activités importantes dans la BD, Oracle ne génère pas
le rapport ADDM.

142 Oracle SQL Tuning

 Recommandations de ADDM
 Modifications matérielles
 Ajout de processeurs ou modification de la configuration du sous-système
d'E/S
 Configuration de la base de données
 Modification des paramètres d'initialisation
 Modifications de schéma
 Partitionnement par hachage d'une table ou d'un index, ou utilisation de la
gestion automatique de l'espace de segment (ASSM)
 Modifications d'application
 Utilisation de cache pour les séquences ou utilisation de variables de liaison
 Utilisation d'autres conseillers (advisors)
 Exécution de SQL Tuning Advisor sur des instructions SQL à charge élevée
ou exécution de Segment Advisor sur des objets sensibles
143 Oracle SQL Tuning
 Chapitre 8

Active History Sessions (ASH)

 AWR et ADDM ont tendance à se
concentrer sur ce qui s'est passé dans la BD ,
plutôt que sur ce qui se passe actuellement.

 Oracle dispose de l’outil ASH pour analyser

l'historique des sessions actives de la BD
pour le diagnostic et le tuning des problèmes
récents. Il échantillonne les informations des
vues dynamiques V$, collectant les données
de performance à partir de la mémoire.

 Génération du rapport ASH

 SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.
sql

144 Oracle SQL Tuning