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 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 instancen, 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).
 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
(Exécution)
Les sous étapes 4 et 5 ne se produit qu'en cas « hard parsing ».
 Exécution-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.
 Exécution-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
Après exécution, 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)
Unique 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 cardinalité (card=no
distinct values/no lignes)
 Syntaxe:
SQL> Create Index Emp_Nom_Ville_IDX on Employees(Nom,ville)

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
Description
d’accès d’index
Full Index Scan Cette méthode d'accès est choisie dans les cas où les colonnes peuvent être
sélectionnées directement de l'index (exemple: index composé, IOT, …).
Fast Full Index Cette méthode d'accès est choisie dans les cas où les colonnes sélectionnées
Scan font parties d' un index et qu'au moins une de ses colonnes a la contrainte NOT
NULL. Elle permet l’accès aux données de l'index lui-même, sans accéder à la
table.
Index Range Scan Cette méthode d'accès est 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.
Index Unique Cette méthode d'accès est choisie lorsque l'index utilisé est unique (ou index
Scan d'une clé primaire).

Index Skip Scan Cette méthode d'accès est choisie dans les cas où une condition est faite sur
une colonne qui fait partie des colonnes d'un index mais qui n'est pas la
première colonne de cet index (cette 1ère colonne sera ignoré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
 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’));

63 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» pour accéder aux indexes.

«filter predicate» pour accéder aux lignes de la table.

64 Oracle SQL Tuning

 Chapitre 4

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

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

 Fils sont en retrait à droite de leurs père.

 Le id du père est inférieur à celui du fils. SORT ACCESS
Join BY
ROWID

FULL
Access INDEX
FULL
SCAN

65 Oracle SQL Tuning

 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).

66 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.
67 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.

68 Oracle SQL Tuning

 Chapitre 5

Oracle Statistiques

69 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.

70 Oracle SQL Tuning

 Chapitre 5

Statistiques
 Oracle collecte les statistiques automatiquement pour tous les objets de
base de données dont les statistiques sont manquantes ou dont les
statistiques sont obsolètes grâce à un job qui s’exécute (de 22h à 02h tous
les jours de la semaine et de 06h à 02 les weekends).

 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.

71 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’indexe
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

72 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.
73 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

74 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.
75 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.

76 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é.

77 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.

78 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.
79 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;
80 Oracle SQL Tuning
 Chapitre 5

Statistiques dynamiques
 Dynamic Statistics ou connu précédemment par Dynamic Sampling
(échantillonnage dynamique).
 Lors de la compilation 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.

81 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.
82 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).
83 Oracle SQL Tuning
 Chapitre 5

Synthèse

Absence
OUI Non
statistiques pour la
table

OUI Si Dynamic NON

Statistics est actif NON Si Histogramme OUI
est présent

Dynamic Statitics Optimizer estime la Optimizer estime la Optimizer estime

estime la cardinalité cardinalité card=no. rows/NDV la cardinalité
(stats basiques) (stats erronés) (stats erronées) (stats correctes)

84 Oracle SQL Tuning

 Chapitre 6

Oracle Hints

85 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.

86 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)
87 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*/ 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;
88 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
FULL Force un Full Scan de table
INDEX 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 indique à l'optimiseur de ne pas utiliser un ou plusieurs index pour la table
spécifiée.
PARALLEL L'indication PARALLEL indique à l'optimiseur d'utiliser le nombre spécifié
de serveurs simultanés pour une opération parallèle.

GATHER_PLAN Collecte des statistiques d’exécution de la requête dans le curseur.

_STATISTICS
89 Oracle SQL Tuning
 Chapitre 7

Oracle SQL Tuning

90 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.

91 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.

92 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.

93 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.

94 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 au lieu de UNION ALL;
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.
95 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.

96 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 prf.
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)
SQL>alter session set sql_trace=true (session en cours)
SQL>exec dbms_system.set_sql_trace_in_session(<sid>,<serial#>,TRUE) (session spécifique dont le
sid et serial# récupérés à partir de V$Session). fichier
SQL Trace
trace(.trc)
oTracer une instance (crée un fichier de trace par processus serveur)
à travers le paramètre [Link] : sql_trace=true. rapport
DB TKPROF
(.tkp)
 Avant d'activer la fonction SQL Trace Facility , vous devez vérifier les paramètres d’initialisation
Timed_statistics, Max_dump_file_size et User_dump_dest qui signifient les statistiques chronométrées,
la taille et l’emplacement des fichiers de traces respectivement.
show parameter timed_statistics; show parameter
max_dump_file_size;show parameter user_dump_dest;

97 Oracle SQL Tuning

 Chapitre 7

TKPROF (1)
 TKPROF c’est un programme (exe) 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.
98 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] ora [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.
99 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

100 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é.

101 Oracle SQL Tuning

 Chapitre 8

Outils automatisés de
Oracle SQL Tuning

102 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.

103 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 Automatic Tuning Optimizer pour
effectuer des réglages sur les requêtes.
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 avantages 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.

104 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;

105 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 selectivité.

 Pour afficher les profils SQL du dictionnaire:

 SQL> SELECT * FROM DBA_SQL_PROFILES;

106 Oracle SQL Tuning

 SQL Access Advisor
 SQL Access Advisor est un logiciel de diagnostic 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, ainsi que de statistiques et
d'attributs décrivant chaque instruction.
Cette charge de travail peut provenir des
sources suivantes :
oZone SQL partagée
oEnsemble de réglages SQL (STS)

107 Oracle SQL Tuning

SQL Tuning Set (STS)
Le SQL Tuning Set regroupe un ensemble d’ordre SQL ainsi que les
informations associées. C’est un outil qui permet de regrouper des requêtes
stockées dans le SHARED POOL, AWR, ou un autre set et d’en appliquer les
outils de tuning: SQL Tuning Advisor et SQL Access Advisor.

 Créer un SQL Tuning Set

SQL> begin
dbms_sqltune.create_sqlset(sqlset_name=>’ Elbeggar_STS’,
description=>’elbeggar SQL Tuning Set’);
end;

108 Oracle SQL Tuning

Remplissage du SQL Tuning Set
 Ajouter des requêtes à SQL Tuning Set
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 :
SQL>DECLARE
c_sqlarea_cursor DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
OPEN c_sqlarea_cursor FOR
SELECT VALUE(p)
FROM TABLE(
DBMS_SQLSET.SELECT_CURSOR_CACHE(
' module = ''SQLT_WKLD'' AND parsing_schema_name = '‘HR'' ')
) p;
-- load the tuning set
DBMS_SQLSET.LOAD_SQLSET (
sqlset_name => ‘Elbeggar_STS‘,
populate_cursor => c_sqlarea_cursor
);
END;
109 Oracle SQL Tuning
SQL Tuning Advisor avec les STS
 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 => ‘Elbeggar_STS’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => ‘elbeggar_TUNING_TASK’,
description => ‘Tuning task for an SQL tuning set.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ ||
l_sql_tune_task_id);
END;
/

110 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.

111 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

112 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 .

113 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.
114
 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é.
115 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
116 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

117 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.

118 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.

119 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)
120 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
121 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).
122 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

123 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.
124 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
125 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

126 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; …
127 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.

128 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

129 Oracle SQL Tuning