Tablespaces et fichiers de

données
Oracle stocke les données logiquement dans les tablespaces et
physiquement dans les fichiers de données.
– Un tablespace :
• ne peut appartenir qu’à une seule base de données à la
fois,
• est composé d’un ou de plusieurs fichiers de données,
• est divisé en unités logiques de stockage.
– Un fichier de données : Base de données

• ne peut appartenir qu’à un Tablespace

tablespace et qu’à une seule
• base de données, Fichiers de
données
• est un référentiel pour les
8 -1
• données d’objet de schéma.
 Types de tablespace
– Le tablespace SYSTEM :
• est créé en même temps que la base de
données,
• contient le dictionnaire de données,
• contient le segment d'annulation SYSTEM.
– Le tablespace non SYSTEM
• sépare les segments,
• facilite l'administration de l'espace,
• gère la quantité d'espace allouée aux
utilisateurs.

8 -2
Tablespace temporaire

8 -3
Le tablespace est une unité logique de stockage composée de fichiers de données

8 -4
 Obtenir des informations
sur les tablespaces
Vous pouvez obtenir des informations sur les
tablespaces et les fichiers de données en interrogeant les
éléments suivants :
Tablespaces :
DBA_TABLESPACES
V$TABLESPACE
Informations sur le fichier de données :
DBA_DATA_FILES
V$DATAFILE
Informations sur les fichiers temporaires :
DBA_TEMP_FILES
V$TEMPFILE
8 -5
 Créer des tablespaces
Un tablespace est créé à l'aide de la commande : CREATE TABLESPACE
drop tablespace OTR including contents and datafiles cascade constraints
/
CREATE TABLESPACE OTR
DATAFILE 'D:\ORACLE\DATA\oradata\DB11G\[Link] '
SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

drop tablespace OTR including contents and datafiles cascade constraints

/
CREATE TABLESPACE OTR_INDEXE
DATAFILE 'D:\ORACLE\DATA\oradata\DB11G\OTR_INDEXE.DBF
'
SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

Le fichier de données [Link] est crée automatiquement par ORACLE

8 -6
 Création d’un tablespace

select substr(f.TABLESPACE_NAME,1,30),substr(f.FILE_NAME,1,60)
from dba_data_files f
order by f.TABLESPACE_NAME
/ 8 -7
 Création d’un compte utilisateur
 Création d’un utilisateur

drop user OTR cascade

/
create user OTR identified by OTR default tablespace OTR
temporary tablespace TEMP
/
grant connect,resource to OTR
/

8 -8
 Consultation d’un compte
select distinct(u.TABLESPACE_NAME) from user_tables u;

select distinct(i.TABLESPACE_NAME) from user_indexes i;

8 -9
 Creation d’une base
Voir fichier D:\cours_nejib\cours_tuning\debutant\base_otr.sql

Creation des données

Voir fichier D:\cours_nejib\cours_tuning\debutant\creation_otr.sql

8 -10
 Consultation de la base
select u.TABLE_NAME,u.TABLESPACE_NAME from user_tables u

8 -11
 Consultation des indexes
select i.TABLE_NAME,i.INDEX_NAME,i.TABLESPACE_NAME from user_indexes i
order by i.TABLE_NAME

8 -12
INDEXES SOUS ORACLE

8 -13
8 -14
8 -15
8 -16
 Index
… WHERE key = 22

Pointeur
Clé de ligne

22

22

Index Table

8 -17
8 -18
8 -19
Le rôle d’un indexe dans le parcours d’une table

8 -20
8 -21
8 -22
 Creation des nouveaux tablespaces
(D:\cours_nejib\cours_tuning\debutant\tablespace_volume.sql)

drop tablespace VOLUME including contents and datafiles cascade

constraints
/
drop tablespace VOLUME_INDEXE including contents and datafiles cascade
constraints
/

CREATE TABLESPACE VOLUME

DATAFILE 'D:\ORACLE\DATA\oradata\DB11G\[Link] '
SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 500M
/
CREATE TABLESPACE VOLUME_INDEXE
DATAFILE 'D:\ORACLE\DATA\oradata\DB11G\VOLUME_INDEXE.DBF '
SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M
/

8 -23
8 -24
 Creation d’un nouveau compte
(D:\cours_nejib\cours_tuning\debutant\creation_compte_volume)
/*****sous system **************************/
drop user VOLUME cascade
/
create user VOLUME identified by VOLUME default tablespace VOLUME
temporary tablespace TEMP
/
grant connect,resource to VOLUME
/

8 -25
 Creation d’une base de données pour le compte volume
(D:\cours_nejib\cours_tuning\debutant\base_volume)

select [Link],u.DEFAULT_TABLESPACE,u.TEMPORARY_TABLESPACE
from user_users u;

8 -26
 Consultation de la base
select u.TABLE_NAME,u.TABLESPACE_NAME from user_tables u

select ind.INDEX_NAME,ind.TABLESPACE_NAME from user_indexes ind

8 -27
 Les règles
Insertion SQLpour
des donées d’optimisation
le compte volume
(D:\cours_nejib\cours_tuning\debutant\creation_volume.sql)

Ajout des données dans la base

(D:\cours_nejib\cours_tuning\debutant\creation_volume_employees,sql)

8 -28
 Les indexes implicites crées par Oracle
Un index est créé automatiquement lors de la création de
la clé primaire ou unique dans une table

8 -29
 Opérations
Les règles SQLd'optimisation
d’optimisation

Pour toute instruction SQL traitée par Oracle, l'optimiseur effectue

les opérations répertoriées dans le tableau suivant

8 -30
Les règles SQL d’optimisation

8 -31
Comment
Leslancer
règlesunSQL
Explain Plan d’une requête
d’optimisation

8 -32
Les Description
règles SQL dud’optimisation
Explain Plan

8 -33
 Les Description
règles SQL dud’optimisation
Explain Plan

Id
Identifiant unique interne de sa requête d’analyse (n° séquentiel
généré par Oracle).

OPERATION
Nom de l’opération interne exécutée lors de l’étape relative à l’occurrence affichée.
Pour la 1ère occurrence (ID=0), cela correspond au type de requête analysée :
DELETE STATEMENT
INSERT STATEMENT
SELECT STATEMENT
UPDATE STATEMENT 8 -34
 Les Description
règles SQL dud’optimisation
Explain Plan

NAME
L’objet exploité : Nom de la table ou de l’index utilisé dans l’opération
élémentaire.

ROWS
estimation du nombre de ligne que Oracle va devoir examiner pour exécuter la requête

BYTES
Taille estimée d’octets accédés par l’opération élémentaire

8 -35
 Les Description
règles SQL dud’optimisation
Explain Plan

COST
Coût d’exécution de la requête estimé par l’optimiseur Oracle. C’est une
notion abstraite à utiliser pour comparer 2 plans d’exécutions différents. Ce
coût est calculé en fonction des informations CPU_COST et IO_COST.
TIME
Temps elapsed en seconds estimé pour exécuter l’opération élémentaire.
La valeur doit être la plus petite possible.

Un plan se lit de bas en haut. Le moteur commence par les

actions du bas et remonte progressivement
8 -36
 Les Description
règles SQL dud’optimisation
Explain Plan

COST
Coût d’exécution de la requête estimé par l’optimiseur Oracle. C’est une
notion abstraite à utiliser pour comparer 2 plans d’exécutions différents. Ce
coût est calculé en fonction des informations CPU_COST et IO_COST.

CARDINALITE
Nombre estimé de lignes accédées par l’opération élémentaire

BYTES
Taille estimée d’octets accédés par l’opération élémentaire.
8 -37
 Plan
Les d’execution
règles d’une requête
SQL d’optimisation

Cette opération est aussi connue sous le nom de parcours complet

de table.
Elle lit la table entière, toutes les lignes et toutes les colonnes,
comme elle est stockée sur le disque. Bien que les opérations
multi-blocs accélèrent fortement la rapidité d'un parcours
complet de table, c'est l'une des opérations les plus coûteuses.
TABLE ACCESS FULL : parcourir
8 -38 toutes les lignes de la table
 Le rôle d’unutilisé
méthode indexe dans lepour
par l’optimiseur parcours d’une
récupérer les table
données

INDEX FAST FULL SCAN

Lit l'index en entier (toutes les lignes) comme stocké sur disque. Cette
opération est typiquement exécutée à la place d'un parcours complet de table
si toutes les colonnes requises sont disponibles dans l'index. Comme le TABLE
ACCESS FULL, l'opération INDEX FAST FULL SCAN peut bénéficier
d'opérations de lecture multi-blocs
8 -39
Le rôle d’unutilisé
méthode indexe dans lepour
par l’optimiseur parcours d’une
récupérer les table
données

8 -40
Le rôle d’unutilisé
méthode indexe dans lepour
par l’optimiseur parcours d’une
récupérer les donnéestable

8 -41
Le rôle d’unutilisé
méthode indexe dans lepour
par l’optimiseur parcours d’une
récupérer les table
données

8 -42
Le rôle d’unutilisé
méthode indexe dans lepour
par l’optimiseur parcours d’une
récupérer les table
données

select /*+ FIRST_ROWS */ * from employees

8 -43
 Les règles SQL d’optimisation
Parcours d’une table via une clé primaire
(indexe crée par Oracle par défaut)

8 -44
INDEX UNIQUE SCAN réalise un parcours seul du B-tree.
La base de données utilise cette opération si une
contrainte unique assure que le critère de recherche
ne correspondront pas à plus d'une entrée

TABLE ACCESS BY INDEX ROWID

Récupère une ligne à partir de la table en utilisant le ROWID
récupéré lors d'une recherche précédente dans l'index.
parcourir les lignes d’une table à partir des valeurs d’index

8 -45
 Les règles SQL d’optimisation
Parcours d’une table via une clé étrangère
(indexe non déclaré au niveau clé étrangère)

TABLE ACCESS FULL : parcourir séquentiel

8 -46
 Creation des indexes explicites

Il est d’usage d’un créé un pour chaque clé étrangère afin

d’ameliorer la vitesse lors des jointures

Syntaxe de création d’un indexe

CREATE INDEX my_index ON

employees(code_job)
Tablespace tablespace_indexe;

8 -47
 Les règles SQL d’optimisation
Creation des indexes pour toutes les clés etrangeres
(D:\cours_nejib\cours_tuning\debutant\creation_indexe_explicite_volume.sql)
drop index emp_dept;
drop index emp_job;
drop index emp_manager;
drop index date_embauche_ind ;
drop index job_hist_dept ;
drop index job_hist_job ;
create index emp_dept on employees (code_dept)
Tablespace VOLUME_INDEXE;
create index emp_job on employees (code_job)
Tablespace VOLUME_INDEXE;
create index emp_manager on employees (code_manager)
Tablespace VOLUME_INDEXE;
create index date_embauche_ind on employees (date_embauche)
Tablespace VOLUME_INDEXE;
create index job_hist_dept on job_hist (code_dept)
Tablespace VOLUME_INDEXE;
create index job_hist_job on job_hist (code_job)
Tablespace VOLUME_INDEXE; 8 -48
 Les règles SQL d’optimisation

select i.TABLE_NAME,i.INDEX_NAME,i.TABLESPACE_NAME from

user_indexes i
order by i.TABLE_NAME

8 -49
 Liste des indexes d’un compte
Purge recyclebin;
select ind.TABLE_NAME,ind.INDEX_NAME,ind.COLUMN_NAME,
ind.COLUMN_POSITION
from user_ind_columns ind
order by ind.TABLE_NAME,ind.INDEX_NAME,ind.COLUMN_POSITION

8 -50
Le rôle d’un indexe dans
Parcours leindexe
avec parcours d’une table

8 -51
Le rôle d’un indexe dans
INDEX le parcours
RANGE SCAN d’une table
L'opération INDEX RANGE SCAN réalise un parcours du B-tree et suit la
chaîne de nœuds feuilles pour trouver toutes les entrées correspondantes.
Le balayage d'intervalle d'index (index range scan):
L'index est parcouru pour trouver les valeurs. Les clauses d'égalité,
supériorité ou infériorité déclenchent généralement ces opérations

TABLE ACCESS BY INDEX ROWID

Récupère une ligne à partir de la table en utilisant le ROWID récupéré lors
d'une recherche précédente dans l'index.
parcourir les lignes d’une table à partir des valeurs d’index

8 -52
 Le rôle d’un indexe
Parcours dans
avec le parcours d’une table
indexe

Si l'optimiseur pense que la requête nécessite la plupart des blocs

de la table, il utilise une analyse complète de la table, même si des
index sont disponibles.

8 -53
 Les règles SQL d’optimisation
Un index n’est pas utilisé si une colonne indexée figure dans une fonction

select * from employees em

where em.date_embauche = to_date('31/12/1990','dd/mm/yyyy')

8 -54
 Les règles SQL d’optimisation
Un index n’est pas utilisé si une colonne indexée figure dans une fonction

TABLE ACCESS FULL : parcourir toutes les lignes de la table

8 -55
 Les règles SQL d’optmisation
Un index n’est pas utilisé lorsqu’une colonne indexée est comparée à NULL

8 -56
 Les règles SQL d’optmisation
Un index n’est pas utilisé lorsqu’une colonne indexée est comparée à NULL

TABLE ACCESS FULL : parcourir toutes les lignes de la table

8 -57
 Les règles SQL d’optimisation
Un index n’est pas utilisé lorsqu’une colonne indexée est comparée avec diffèrent

TABLE ACCESS FULL : parcourir toutes les lignes de la table

8 -58
 Les règles SQL d’optimisation
Un index n’est pas utilisé quand une colonne indexée est comparée à une
chaine commençant par un caractère générique

create index nom_employe_ind on employees (nom_employe);

8 -59
Drop index nom_employe_ind ;
8 -60
8 -61
INDEX SKIP SCAN
Est utilisé pour un index avec plusieurs champs si dans votre
requête vous ne prenez pas en compte tous les champs de l’index,
généralement le premier.

8 -62
Dans une expression filtrée, placez toujours une colonne seule d'un
côté de l'opérateur de comparaison.

8 -63
8 -64
 LES INDEXES BITMAP

Drop index emp_civil;

Drop index emp_sexe ;

create bitmap index emp_civil on employees (etat_civil);

create bitmap index emp_sexe on employees (sexe);

8 -65
 LES INDEXES BITMAP

Bitmap Index Full Scan :Un parcours de bitmap récupère tous les
pointeurs de ligne dans l'index en un coup
BITMAP CONVERSION COUNT : Compte le nombre de bit à 1

SORT GROUP BY NOSORT

Agrège un ensemble pré-trié suivant la clause group by.
8 -66
8 -67
8 -68
8 -69
drop index emp_sexe;
Drop index emp_civil;
create bitmap index emp_sexe on employees (sexe);
Create bitmap index emp_civil on employees(etat_civil);

Et parfois nous donne ce chemin

8 -70
Pour éviter ce comportement je l’oblige à exploiter mon indexe

select /*+ INDEX(e,emp_sexe)*/ * from employees e

where [Link] = 'M'

8 -71
 Bitmap index single value
L'analyse d'index bitmap à valeur unique est effectuée par Oracle lorsque la
requête spécifie une condition d'égalité pour une colonne indexée bitmap

Bitmap conversion to rowids

Convertir les adresses des numéros d’enregistrements

TABLE ACCESS BY INDEX ROWID

Récupère une ligne à partir de la table en utilisant le ROWID récupéré lors
d'une recherche précédente dans l'index

8 -72
8 -73
RECOMMANDATIONS

8 -74
RECOMMANDATIONS

8 -75
 Evitez d’utiliser DISTINCT lorsque cela n’est pas nécessaire

HASH UNIQUE : tri un ensemble de lignes

8 -76 pour éliminer les doublons
N’employez pas de colonne dans la clause SELECT de la sous requête EXISTS

SORT UNIQUE
Trie les lignes et supprime les doublon

NESTED LOOPS Joint deux tables en récupérant le résultat d'une table et en

recherchant la condition de jointure dans
8 -77l'autre table ligne par ligne.
N’employez pas de colonne dans la clause SELECT de la sous requête EXISTS

8 -78
N’employez pas de colonne dans la clause SELECT de la sous requête EXISTS

HASH JOIN
La jointure de hachage charge les enregistrements candidats d'un côté de la
jointure dans une table de hachage qui est ensuite sondée pour chaque ligne
8 -79
de l'autre côté de la jointure.
N’employez pas de colonne dans la clause SELECT de la sous requête EXISTS

8 -80
Utilisation de IN

8 -81
8 -82
8 -83
8 -84
 Analyse d’une requête par l’optimiseur
En cas de sous-interrogation avec IN

Prédicat IN :on va d’abord exécuter le sous select ,puis à partir des

données ramenées par celui-ci, on va executer le select principal

select * from employees e

where e.code_dept in (select d.code_dept
from departement d
where d.code_manager <>'100')

8 -85
1) TABLE ACCESS FULL sur « departement »

2) SORT JOIN: SORT JOIN trie un ensemble d'enregistrements sur la colonne

de jointure .

3) INDEX FULL SCAN : Lit l'index entier (toutes les lignes) dans l'ordre de
l'index. Suivant différentes statistiques système, la base de données peut
choisir de réaliser cette opération car elle a besoin de toutes les lignes dans
l'ordre de l'index

4) TABLE ACCESS BY INDEX ROWID : Récupère une ligne à partir de la table en

utilisant le ROWID récupéré lors d'une recherche précédente dans l'index

5) MERGE JOIN : La jointure d'assemblage combine deux listes triées comme

une fermeture Éclair. Les deux côtés de la jointure doivent être pré-triés.

8 -86
 Analyse d’une requête par l’optimiseur
Ou bien

select * from employees e

where e.code_dept not in (select d.code_dept
from departement d
where d.code_manager ='100')

8 -87
Prédicat EXISTS :pour chaque ligne ramenée par le select principal on
va exécuter le sous select
La requête externe s’exécutera uniquement si la requete interne
retourne au moins un résultat

8 -88
 Analyse d’une requête par l’optimiseur
En cas de sous-interrogation avec EXISTS

Prédicat EXISTS :pour chaque ligne ramenée par le select principal on

va exécuter le sous select
La requête externe s’exécutera uniquement si la requete interne
retourne au moins un résultat
select * from employees e
where exists (select null
from departement d
where d.code_dept = e.code_dept
and d.code_manager ='100')

8 -89
1) TABLE ACCESS BY INDEX ROWID : Récupère une ligne à partir de la table en
utilisant le ROWID récupéré lors d'une recherche précédente dans l'index
Voir le lien suivant : [Link]

2) INDEX RANGE SCAN

L'opération INDEX RANGE SCAN réalise un parcours du B-tree et suit la chaîne
de nœuds feuilles pour trouver toutes les entrées correspondantes
Voir le lien suivant :
[Link]

3) TABLE ACCESS FULL sur « departement »

4) SORT UNIQUE
Trie les lignes et supprime les doublon

5) NESTED LOOPS Joint deux tables en récupérant le résultat d'une table et en

recherchant la condition de jointure dans l'autre table ligne par ligne.

8 -90
 Analyse d’une requête par l’optimiseur
Avec EXISTS

select * from employees e

where not exists (select null
from departement d
where d.code_dept = e.code_dept
and d.code_manager <> '100')

8 -91
 Analyse d’une requête par l’optimiseur
Avec EXISTS

select * from employees e

where exists (select null
from departement d
where d.code_dept = e.code_dept
and d.code_manager <>'100')

8 -92
 Choix entre IN ou EXISTS

Quel est le select qui va ramener le moins de lignes

Si le select principal
alors EXISTS
Sinon IN
Fin si

pour une requête avec des sous-select IN ou EXISTS,

pensez à les réécrire sous forme de jointure
pour voir si elle est améliorée et plus performante
8 -93
Evitez de compter une colonne quand il suffit de compter les lignes

8 -94
8 -95
8 -96
Evitez d’utiliser le LIKE si une fourchette de recherche le permet

8 -97
Evitez d’utiliser le LIKE si une fourchette de recherche le permet

8 -98
Evitez les jointures dans le WHERE
Préférez l’operateur normalisé JOIN

8 -99
Evitez les jointures dans le WHERE
Préférez l’operateur normalisé JOIN

8 -100
Evitez les jointures dans le WHERE
Préférez l’operateur normalisé JOIN

8 -101
Evitez les jointures dans le WHERE
Préférez l’operateur normalisé JOIN

8 -102
Evitez les fourchettes <and> pour des valeurs discrètes
Préférez le between

8 -103
Transformez les sous requetes <> all en NOT IN

8 -104
Transformez les sous requetes <> all en NOT IN

8 -105
Transformez les sous requetes = ANY en IN

8 -106
RECOMMANDATIONS

8 -107
RECOMMANDATIONS

8 -108
8 -109
8 -110
 Conseils pour définir vos indexes

 Ne pas créer d'index pour des tables de petite taille

 Ne pas créer d'index de type B-tree sur des champs avec peu de
valeurs distinctes
 Ne pas créer d'index si la plupart de vos requêtes ramènent plus
de 25% des enregistrements.
 Ne pas créer beaucoup d'index sur une table fréquemment mise
à jour.
 En cas de jointures fréquentes effectuées entre deux champs de
deux tables, créer un index sur ce champ.

8 -111
 Conseils pour définir vos indexes

il est recommandé de ne pas abuser des index car :

 Les index utilisent de l'espace disque supplémentaire
 La modification des champs indexés dans la table entraîne une
éventuelle mise à jour de l'index

Il convient également de veiller à indexer autant que possible, en

particulier pour les prédicats des clauses WHERE, ORDER BY
et GROUP BY.

8 -112
Les principes de base pour la déclaration des indexes explicites
(un bon indexe sert à optimiser la recherche)

8 -113
Les principes de base pour la déclaration des indexes explicites
(un bon indexe sert à optimiser la recherche)

8 -114
Dans quel cas ne pas utiliser les indexes

8 -115
8 -116
8 -117
8 -118
8 -119
8 -120
8 -121
Une vue est en fait le fruit d'une requête. Chez Oracle, quand on crée
une vue et qu'on l'interroge Oracle ne fait ni plus ni moins qu'une
réécriture de requête

Les vues matérialisées sont des vues dont on stocke physiquement le

résultat: le résultat de la requête est stocké comme pour une table.
Mais qui dit résultat stocké dit résultat de la requête à un moment t, et
quand la table change la vue matérialisée sera tenue à jour ou non
selon comment on l'a paramétrée

Le problème c'est qu'entretenir ces vues en temps réel est coûteux et

pas souvent mis en œuvre. Les vues matérialisées trouvent tout leur
intérêt dans des cas où l'on n'a pas besoin des données en temps reel.
Par exemple l'analyse des chiffres économiques de la veille. Dans ce
cas, les données sont en réalité désormais figées et il peut être
intéressant de stocker les résultats pour faire des sous interrogations
plus rapidement.

8 -122
8 -123
grant alter any materialized view to OTR
/
grant create any materialized view to OTR
/
grant unlimited tablespace to OTR
/

8 -124
drop materialized view formation
/
create materialized view formation
as select
e.code_employe,e.nom_employe,e.pre_employe,d.nom_dept,j.nom_job
from employees e,departement d,job j
where e.code_dept = d.code_dept
and e.code_job = j.code_job
/

8 -125
8 -126
drop materialized view formation
/
create materialized view formation
refresh next sysdate + 1/24/60
as select
e.code_employe,e.nom_employe,e.pre_employe,d.nom_dept,j.nom_job
from employees e,departement d,job j
where e.code_dept = d.code_dept
and e.code_job = j.code_job
/

8 -127
8 -128
 les indexes non exploités

 Exécuter le script monitor_index.sql pour demarrer l’analyse des indexes exploités

dans l’applicatif

select 'ALTER INDEX '||i.index_name||' MONITORING USAGE;'

from user_indexes i
order by i.index_name;

8 -129
  Après une période d’exploitation (15 jours) exécuter le script suivant Pour
arrêter l’analyse

select 'ALTER INDEX '||i.index_name||' NOMONITORING USAGE;'

from user_indexes i
order by i.index_name;

Exécuter le script suivant :

SELECT index_name,table_name
FROM v$object_usage
WHERE used='NO'
8 -130
/