Ingénierie et Optimisation de Bases de Données

Ingénierie et Optimisation de Bases de Données
FIP - ABD
TPs
CNAM Paris
Nicolas.Travers (at) cnam.fr

Table des matières
1InformationssurlaBasedeDonnées 3
1.1 Informations sur les capacités . . . . . . . . . . . . . . . . . . . . . ............ 3
1.2 Démarrage ............................................ 3
1.3 Commandespratiques ..................................... 3
1.4 Schéma .............................................. 4
1.5 EXPLAIN............................................. 5
2Utilisationdel’outilsTOAD 7
2.1 Connexion ............................................ 7
2.2 SchémaetStructures ...................................... 8
2.3 Interrogation........................................... 8
3Etudedelabasededonnées 11
3.1 Tables............................................... 11
3.2 Indexes .............................................. 11
4 Analyse de plan EXPLAIN 13
4.1 Instructions ........................................... 13
4.2 Étude de plans d’exécution . . . . . . . . . . . . . . . . . . . . . . . . . .......... 13
4.2.1 RequêtesSimples .................................... 14
4.2.2 Requêtes de jointures . . . . . . . . . . . . . . . . . . . . . . . . . . . ....... 14
5Optimisation 17
5.1 HINT ............................................... 17
5.2 Optimisation de requête . . . . . . . . . . . . . . . . . . . . . . . . . . . ......... 17
5.3 EXPLAINversSQL ....................................... 18
5.4 Scénario.............................................. 18

1InformationssurlaBasedeDonnées
1.1 Informations sur les capacités
1. Taille totale de la base (tables + index) : 1,438 Go
2. Taille d’une page disque : 8ko
3. Mémoire centrale : 400 Mo (bridée volontairement)
1.2 Démarrage
1. La base de données utilisée pour le TP se trouve sur le serveur UNIX dept25 :
ssh dept25
2. Initialisation des variables d’environnement pour Oracle :
.oraenv(entrée) (Il y a bien un espace entre “." et “oraenv")
ORACLE_SID : orcl
Affichage d’un message Warning que vous pouvez ignorer.
3. Connexion à oracle :
sqlplus (entrée)
Login : NFE106USER
Mdp :Ademander
Connexion hors CNAM
1. Ouvrir une console SSH (linux/Mac en natif, sinon putty.exe sous Windows qu’il faut trouver
sur le net)
2. Se connecter au serveur vlad.cnam.fr (login/mdp d’auditeur cnam et non pleiad)
3. Executer les commandes vues précédemment à partir de ssh dept25.
1.3 Commandes pratiques
!!!N’oubliezpasquevouspouvezsélectionnerdutexteavecla souris et le copier à l’aide du bouton
du milieu ! ! !
Il est conseillé de travailler avec un éditeur de texte (nedit, emacs, kedit, vim, etc...) et de recopier vos
requêtes dans le navigateur et vis-versa.

1.4. SCHÉMA CHAPITRE 1. INFORMATIONS SUR LA BASE DE DONNÉES
;Rappel la dernière commande SQL executée
COLUMN <nom de l’attribut>FORMAT A10 Permet d’afficher les résultats de sqlplus sur 10 carac-
tères
SET LINESIZE 30 Permet d’afficher 30 tuples résultats avant qu’il ne ré-
pète le schéma
CLEAR SCREEN Efface le contenu de l’écran
SELECT table_name FROM all_tables Affichage de toutes les tables
DESC artiste Schéma de la table artiste
SELECT constraint_name, table_name, column_name
FROM all_cons_columns Where OWNER=’NFE106_ADMIN’
AND INDEX_NAME NOT LIKE ’%$%’
Affichage des contraintes ainsi que leur affectation
SELECT index_name, table_name, column_name FROM
all_ind_columns Where INDEX_OWNER=’NFE106_ADMIN’
AND INDEX_NAME NOT LIKE ’%$%’
Affichage des index et leur affectation
show PARAMETERS db_block_size; Taille d’u n block (page disq ue)
show PARAMETERS db_block_buffers; Taille du cach e (plu s le cach e est grand, moins le sys-
tème fera d’accès disque)
select SEGMENT_NAME, BLOCKS from dba_segments
where segment_name not like ’%$%’ and SEG-
MENT_TYPE = ’TABLE’;
Taille pr ise par toutes les seg ments de ty pe TABLE
(il existe aussi INDEX, INDEX PARTITION, CLUS-
TER...
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH from
ALL_TAB_COLUMNS where table_name =’ARTISTE’;
Taille pr ise par chaque attribut dans la table ’Artiste’
SET AUTOTRACE TRACE Permet d’afficher le plan d’execution EXPLAIN et les
statistiques d’exécution pour chacune des requêtes exé-
cutées. (on peut remplacer TRACE par ON qui affichera
aussi les résultats, ou par EXPLAIN ou encore OFF)
ALTER SYSTEM FLUSH BUFFER_CACHE Cette commande vide le cache mémoire réalisé durant
les dernières requêtes. Afin de mieux étudier les sta-
tistiques, il est préférable d’exécuter cette commande
avant toute requête.)
ALTER SESSION SET OPTIMIZER_MODE=RULE Passe en mode sans statistiques
Information pratique : Ecrivez votre requête dans un éditeur de texte (i.e. nedit). Mettez la com-
mande ALTER SYSTEM FLUSH BUFFER_CACHE ; avant celle-ci. Ensuite, sélectionnez les deux re-
quêtes et copiez les dans SQLPLUS via le bouton du milieu de la souris.
1.4 Schéma
Voici une table récapitulative du schéma de la base. Pour chacun, vous pourrez identifier :
–Nomdelatable;
–Attributsettypeavec:
1. PK : Attribut faisant parti de la clé primaire (Primary Key);
–Lenombredetuplesdanslatable;
–lescontraintesetindexsurlestables’table(attributs)’:
1. FK : Foreign Key, avec table destination
2. BITMAP : ...
3. HASH : ...

CHAPITRE 1. INFORMATIONS SUR LA BASE DE DONNÉES 1.5. EXPLAIN
Table Schema Nb tuples Index Statistiques
ARTISTE (IDARTISTE PK NUMBER(38), NOM VARCHAR2(30), PRENOM VARCHAR2(24),
DATENAISS DATE) 1 000 000 BTREE :ARTISTE(NOM)
PAYS (CODE PK CHAR(4), NOM VARCHAR2(31), LANGUE VARCHAR2(31)) 200
FILM1 (IDFILM PK NUMBER(38), TITRE VARCHAR2(24), ANNEE NUMBER(38), IDMES
NUMBER(38), GENRE VARCHAR2(20), RESUME VARCHAR2(300), CODEPAYS
CHAR(4))
500 000 FK :FILM1(IDMES) :ARTISTE Années de
1990 à 2010
FK :FILM1(CODEPAYS) :PAYS
Copies de FILM1
FILM2 FK :FILM2(IDMES) :ARTISTE, FK :FILM2(CODEPAYS) :PAYS BTREE :FILM2(ANNEE), BTREE :FILM2(CODEPAYS)
FILM3 FK :FILM3(IDMES) :ARTISTE, FK :FILM3(CODEPAYS) :PAYS BITMAP :FILM3(ANNEE), BITMAP :FILM3(CODEPAYS)
FILM4 FK :FILM4(IDMES) :ARTISTE, FK :FILM4(CODEPAYS) :PAYS HASH :FILM4(ANNEE)
FILM5 FK :FILM5(IDMES) :ARTISTE, FK :FILM5(CODEPAYS) :PAYS HASH :FILM5(CODEPAYS)
FILM6 FK :FILM6(IDMES) :ARTISTE, FK :FILM6(CODEPAYS) :PAYS ORGANIZATION INDEX
ROLE (IDFILM PK NUMBER(38), IDACTEUR PK NUMBER(38), NOMROLE VAR-
CHAR2(24)) 3 000 000 FK :ROLE(IDFILM) :FILM1
FK :ROLE(IDACTEUR) :ARTISTE
INTERNAUTE (EMAIL PK CHAR(40), NOM VARCHAR2(24), PRENOM VARCHAR2(24),RE-
GION VARCHAR2(31))
500 000
NOTATION (IDFILM PK NUMBER(38), EMAIL PK CHAR(40), NOTE NUMBER(38)) 4 000 000 FK :NOTATION(IDFILM) :FILM1 Notes de 0 à
20
FK :NOTATION(EMAIL) :INTERNAUTE
1.5 EXPLAIN
Plan d’exécution
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 2 |
| 1 | TABLE ACCESS FULL | ARTISTE | 14 | 518 | 2 |
--------------------------------------------------------------------
Statistiques
--------------------------------------------------------------------
541 recursive calls
0 db block gets
8940 consistent gets
5542 physical reads
332 redo size
2220518 bytes sent via SQL*Net to client
41729 bytes received via SQL*Net from client
3761 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
56386 rows processed
1. Id :Identifiantdel’opérateur;
2. Operation :typed’opérationutilisée(voirtableaurécapitulatifci-dessous) ;
3. Name :Nomdelarelationutilisée;
4. Cost :C’estlecoûtestimé.Iciiln’yapasd’unitédevaleur,d’ailleurs il faut prendre avec précaution
la valeur, et y préférer plutôt la lecture du plan. Ici il n’y aura de valeur que pour le CBO (Cost-
Based Optimizer). Attention, si nous sommes en mode CHOOSE etquelarequêtefaitappelà
plusieurs tables dont une n’a pas de statistiques calculées,Oraclechoisiraquandmêmelemode
CBO ;
5. Rows :Lenombredelignesqu’Oraclepensetransférer
.Iciilfautvérifierquecenombreesten
adéquation avec le nombre réel de lignes ramenées. Ici il s’agit d’une estimation basée sur les
statistiques de la table, d’où l’importance de calculer ces statistiques. ,
6. Bytes :Nombred’octetsqu’oraclepensetransférer
.