Ingénierie et Optimisation de Bases de Données

FIP - ABD

TPs

CNAM ParisNicolas.Travers (at) cnam.fr

Table des matières

1 Informations sur la Base de Données 31.1 Informations sur les capacités . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.2 Démarrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.3 Commandes pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.4 Schéma . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.5 EXPLAIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

2 Utilisation de l’outils TOAD 72.1 Connexion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72.2 Schéma et Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82.3 Interrogation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

3 Etude de la base de données 113.1 Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113.2 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

4 Analyse de plan EXPLAIN 134.1 Instructions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134.2 Étude de plans d’exécution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

4.2.1 Requêtes Simples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144.2.2 Requêtes de jointures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

5 Optimisation 175.1 HINT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175.2 Optimisation de requête . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175.3 EXPLAIN vers SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185.4 Scénario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

1 Informations sur la Base de Donné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 : orclA!chage d’un message Warning que vous pouvez ignorer.

3. Connexion à oracle :sqlplus (entrée)Login : NFE106USERMdp : Ademander

Connexion hors CNAM

1. Ouvrir une console SSH (linux/Mac en natif, sinon putty.exe sous Windows qu’il faut trouversur 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’oubliez pas que vous pouvez sélectionner du texte avec la souris et le copier à l’aide du boutondu milieu ! ! !Il est conseillé de travailler avec un éditeur de texte (nedit, emacs, kedit, vim, etc...) et de recopier vosrequê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éeCOLUMN <nom de l’attribut> FORMAT A10 Permet d’a!cher les résultats de sqlplus sur 10 carac-

tèresSET LINESIZE 30 Permet d’a!cher 30 tuples résultats avant qu’il ne ré-

pète le schémaCLEAR SCREEN E"ace le contenu de l’écranSELECT table_name FROM all_tables A!chage de toutes les tablesDESC artiste Schéma de la table artisteSELECT constraint_name, table_name, column_nameFROM all_cons_columns Where OWNER=’NFE106_ADMIN’AND INDEX_NAME NOT LIKE ’%$%’

A!chage des contraintes ainsi que leur a"ectation

SELECT index_name, table_name, column_name FROMall_ind_columns Where INDEX_OWNER=’NFE106_ADMIN’AND INDEX_NAME NOT LIKE ’%$%’

A!chage des index et leur a"ectation

show PARAMETERS db_block_size; Taille d’un block (page disque)show PARAMETERS db_block_buffers; Taille du cache (plus le cache est grand, moins le sys-

tème fera d’accès disque)select SEGMENT_NAME, BLOCKS from dba_segmentswhere segment_name not like ’%$%’ and SEG-MENT_TYPE = ’TABLE’;

Taille prise par toutes les segments de type TABLE(il existe aussi INDEX, INDEX PARTITION, CLUS-TER...

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH fromALL_TAB_COLUMNS where table_name =’ARTISTE’;

Taille prise par chaque attribut dans la table ’Artiste’

SET AUTOTRACE TRACE Permet d’a!cher le plan d’execution EXPLAIN et lesstatistiques d’exécution pour chacune des requêtes exé-cutées. (on peut remplacer TRACE par ON qui a!cheraaussi les résultats, ou par EXPLAIN ou encore OFF)

ALTER SYSTEM FLUSH BUFFER_CACHE Cette commande vide le cache mémoire réalisé durantles dernières requêtes. Afin de mieux étudier les sta-tistiques, il est préférable d’exécuter cette commandeavant 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 :– Nom de la table ;– Attributs et type avec :

1. PK : Attribut faisant parti de la clé primaire (Primary Key) ;

– Le nombre de tuples dans la table ;– les contraintes et index sur les tables ’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 StatistiquesARTISTE (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)) 200FILM1 (IDFILM PK NUMBER(38), TITRE VARCHAR2(24), ANNEE NUMBER(38), IDMES

NUMBER(38), GENRE VARCHAR2(20), RESUME VARCHAR2(300), CODEPAYSCHAR(4))

500 000 FK :FILM1(IDMES) :ARTISTE Années de1990 à 2010

FK :FILM1(CODEPAYS) :PAYSCopies 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) :ARTISTEINTERNAUTE (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 calls0 db block gets

8940 consistent gets5542 physical reads332 redo size

2220518 bytes sent via SQL*Net to client41729 bytes received via SQL*Net from client3761 SQL*Net roundtrips to/from client

8 sorts (memory)0 sorts (disk)

56386 rows processed

1. Id : Identifiant de l’opérateur ;

2. Operation : type d’opération utilisée (voir tableau récapitulatif ci-dessous) ;

3. Name : Nom de la relation utilisée ;

4. Cost : C’est le coût estimé. Ici il n’y a pas d’unité de valeur, d’ailleurs il faut prendre avec précautionla 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 et que la requête fait appel àplusieurs tables dont une n’a pas de statistiques calculées, Oracle choisira quand même le modeCBO ;

5. Rows : Le nombre de lignes qu’Oracle pense transférer. Ici il faut vérifier que ce nombre est enadéquation avec le nombre réel de lignes ramenées. Ici il s’agit d’une estimation basée sur lesstatistiques de la table, d’où l’importance de calculer ces statistiques. ,

6. Bytes : Nombre d’octets qu’oracle pense transférer.

1.5. EXPLAIN CHAPITRE 1. INFORMATIONS SUR LA BASE DE DONNÉES

Voici un sous-ensemble des OPERATIONS et OPTIONS du plan d’exécution EXPLAIN (non exhaustif) :

OPERATIONS OPTIONS SIGNIFICATIONAGGREGATE GROUP BY Une recherche d’une seule ligne qui est le résultat de l’application d’une fonction

de group à un groupe de lignes sélectionnées.AND-EQUAL Une opération qui a en entrée des ensembles de rowids et retourne l’intersection de

ces ensembles en éliminant les doublants. Cette opération est utilisée par le chemind’accès par index.

CONNECT BY Recherche de ligne dans un ordre hiérarchiqueCOUNTING Opération qui compte le nombre de lignes sélectionnées.FILTER Accepte un ensemble de ligne, appliqué un filter pour en éliminer quelque unes et

retourne le reste.FIRST ROW Recherché de le première ligne seulement.FOR UPDATE Opération qui recherche et verrouille les lignes pour une mise à jourINDEX UNIQUE SCAN Recherche d’une seule valeur ROWID d’un index.INDEX RANGE SCAN Recherche d’une ou plusieurs valeurs ROWID d’un index. L’index est parcouru

dans un ordre croissant.INDEX RANGE SCAN DESCEN-

DINGRecherche d’un ou plusieurs ROWID d’un index. L’index est parcouru dans unordre décroissant.

INTERSECTION Opération qui accepte deux ensembles et retourne l’intersection en éliminant lesdoublons.

MARGE JOIN+ Accepte deux ensembles de lignes (chacun est trié selon un critère), combine chaqueligne du premier ensemble avec ses correspondants du deuxième et retourne lerésultat.

MARGE JOIN+ OUTER MARGE JOIN pour e!ectuer une jointure externeMINIUS Di!érence de deux ensembles de lignes.NESTED LOOPS Opération qui accepte deux ensembles, l’un externe et l’autre interne. Oracle com-

pare chaque ligne de l’ensemble externe avec chaque ligne de l’ensemble interne etretourne celle qui satisfont une condition.

NESTED LOOPS OUTER Une boucle imbriquée pour e!ectuer une jointure externe.PROJECTION Opération interneREMOTE Recherche de données d’une base distante.SEQUENCE Opération nécessitant l’accès à des valeurs du séquenceurSORT UNIQUE Tri d’un ensemble de lignes pour éliminer les doublons.SORT GROUP BY Opération qui fait le tri à l’intérieur de groupesSORT JOIN Tri avant la jointure (MERGE-JOIN).SORT ORDER BY Tri pour un ORDER BY.TABLE ACCESS FULL Obtention de toutes lignes d’une table.TABLE ACCESS CLUSTER Obtention des lignes selon la valeur de la clé d’un cluster indexé.TABLE ACCESS HASH Obtention des lignes selon la valeur de la clé d’un hash clusterTABLE ACCESS BY ROW ID Obtention des lignes on se basant sur les valeurs ROWID.UNION Union de deux ensembles avec élimination des doublons.VIEW Opération qui utilise une vue et retourne le résultat à une autre opération.

Informations sur les statistiques :

recursive calls Number of recursive calls generated at both the user and system level. Oracle maintainstables used for internal processing. When Oracle needs to make a change to these tables, itinternally generates an internal SQL statement, which in turn generates a recursive call.

db block gets Number of times a CURRENT block was requested. See also "consistent gets", above.consistent gets Number of times a consistent read was requested for a block.physical reads Total number of data blocks read from disk. This number equals the value of "physical reads

direct" plus all reads into bu!er cache.redo size Total amount of redo generated in bytes.sorts (disk) Number of sort operations that required at least one disk write.sorts (memory) Number of sort operations that were performed completely in memory and did not require

any disk writes.You cannot domuch better thanmemory sorts, except maybe no sorts at all. Sorting is usuallycaused by selection criteria specifications within table join SQL operations.

2 Utilisation de l’outils TOAD

L’outils TOAD de Quest© est un produit très utilisé sur le marché par les développeurs et DBA surdes bases de données (principalement Oracle). Il permet principalement :

– Consulter le schéma et le informations sur la bases de données– Lancer des requêtes– Consulter le plan EXPLAIN et les statistiques– Créer des requêtes (query builder)– Vérifie et Lance des procédures PL/SQL– Créer des scripts– . . .

2.1 Connexion

Sous Windows, vous pourrez trouver l’outils TOAD dans le menu :

’Quest Software / TOAD for Oracle / TOAD. . . ’ (figure 2.1)

Figure 2.1 – Ouvrir TOAD

Une fois lancé, une fenêtre de connexion s’a"che. Remplissez les informations ci-dessous :– User/Schema : NFE106USERPassword : Ademander

–– Onglet ’Direct’ :– Host : dept25.cnam.fr– Port : 1521– cocher ’SID’ : orcl

– connect as : Normal– appuyer sur ’connect’

Voir la figure 2.2.

2.2. SCHÉMA ET STRUCTURES CHAPITRE 2. UTILISATION DE L’OUTILS TOAD

Figure 2.2 – Connexion avec TOAD

2.2 Schéma et Structures

Maintenant que vous êtes connecté, vous pouvez consulter le schéma de la base de données, ainsique les di!érentes informations et structures qui la compose. Pour ce faire (Voir la figure 2.3) :

– Menu ’Database / Schema Browser’ (ou deuxième bouton ’Schema Browser’)– Dans le premier menu déroulant choisir ’NFE106_ADMIN’– Double clicker sur une table ou choisir dans la liste de gauche pour en voir le schéma– Sélectionner l’onglet :

– ’Indexes’ pour voir les indexes associés (on pourra également y trouver le clustering Factorpour les BTree - et IOT pour FILM6)

– ’Data’ pour visualiser quelques données– ’Script’ pour voir les requêtes associées– ’Partition’ pour les tables FILM4 et FILM5– ’Stats’ pour la taille, les extensions, les tuples,

2.3 Interrogation

– Menu ’Editor / New Tab / SQL Style’ ou premier bouton ’Editor’ (voir figure 2.4)– Ecrire une requête SQL dans la partie edition (il y a une règle au dessus)– Pour exécuter : Bouton ’flèche verte au dessus d’un disque dur’ (touche F9)– Onglet du bas (changent sur passage de la souris) : 1) Data Grid : le résultat de la requête 2) Trace :

Cliquer sur “Enabled” pour autotrace pour avoir les statistiques (il faut réexécuter la requête) -les lignes ’DB block gets’ et ’Physical Read’ sont utiles 3) Explain Plan (Ctrl+E) : permet de voir

CHAPITRE 2. UTILISATION DE L’OUTILS TOAD 2.3. INTERROGATION

Figure 2.3 – Consultation du Schéma

le plan d’exécution– Possibilité d’utiliser le ’Query Builder’ (4° bouton) pour générer des requêtes.

Figure 2.4 – SQL avec TOAD

2.3. INTERROGATION CHAPITRE 2. UTILISATION DE L’OUTILS TOAD

3 Etude de la base de données

Avant de commencer, veuillez lire le chapitre 1 sur les Informations Pratiques qui vous permet-tront de mieux appréhender ce TP et de vous permettre de vous connecter à la base de données. Ilvous donne tous les éléments nécessaires au bon déroulement de ce TP.

Le but de ce TP est d’étudier le contenu de la base de données. Vous devrez en extraire la listedes tables, leur taille en nombre de pages (blocks), et surtout, la liste des indexes. Cette étape estindispensable car elle vous permettra de déduire et de comprendre les plans d’exécutions que nouse!ectuerons dans les TPs suivants.

3.1 Tables

1. Donner la liste des tables du TABLESPACE_NAME ’NFE106’ (ou utilisateur ’NFE106_ADMIN’).Utiliser la vue ’all_tables’ ;

2. Donner le nombre de segments et de pages (blocks) pour chaques objets de type ’TABLE%’(utiliser la table OWNER=’NFE106_ADMIN’). Concernant la table FILM6, cherchez le SEG-MENT_NAME=’PK_FILKM6’. Calculer pour chacune sa taille en Mo ;

3. Donner le nombre de tuples pour chacunes de ces tables ;

4. Calculer la taille moyenne d’un tuple (en octets). Puis donner la taille maximum de chaque tuple(requête sur ALL_TAB_COLUMNS) ;Expliquer la différence visible entre la moyenne et le max, mais aussi le nombrede segments.

3.2 Indexes

1. Pour chaque table, donner la liste des indexes associés ;

2. Donner pour chacun de ces indexes le nombre de blocs qu’il prend (dba_segments), ainsi que saplace en Mo (taille d’un bloc 8ko). Expliquer pourquoi certains indexes apparaissent plusieursfois ;

3. Donner pour chaque attribut indexé, le nombre de valeurs distinctes et pour chaque valeurle pourcentage de tuples correspondants. Cela vous permettra de connaître les statistiques duSGBD dans les requêtes futures.

3.2. INDEXES CHAPITRE 3. ETUDE DE LA BASE DE DONNÉES

4 Analyse de plan EXPLAIN

Le but de ce TP est de comprendre le fonctionnement de l’optimiseur à travers l’outils EXPLAIN.Vous aurez un ensemble de requêtes à e!ectuer sur la base de données que vous avez étudié précé-demment.

4.1 Instructions

– Nous ne souhaitons voir que le plan EXPLAIN et les statistiques, pas les résultats. Pour ce faire :

SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS;

– Le mode sans statistiques permet de voir l’e!et des règles de transformations :

ALTER SESSION SET OPTIMIZER_MODE=RULE;

– Le mode avec statistiques permet de voir un meilleur plan d’exécution 1 :

ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;

– Afin de vider le cache avant chaque exécution 2 :

ALTER SYSTEM FLUSH BUFFER_CACHE

– Pour vous faciliter la tâche de recopie de requêtes, ouvrez un éditeur de texte 3 pour y écrire vosrequêtes puis les copier dans SQLPLUS.

– Pour enregistrer automatiquement le contenu de votre console dans un fichier, utiliser cettecommande 4 :

SPOOL mon_fichier_resultat.txt

4.2 Étude de plans d’exécution

Pour chaque requête à e!ectuer :– Exécuter la requête sur les di!érentes versions demandées de la table FILMi (de 1 à 6) ;– Expliquer les di!érences de plan d’exécution et statistiques obtenues 5 ;– E!ectuer cette requête à la fois en mode RULE et en mode STATISTICS. Expliquer les di!érences

obtenues ;

1. Toujours en fonction des statistiques qui lui sont fournies2. Sélectionnez une requête, puis utilisez la copie avec le bouton centrale de la sourie3. nedit par exemple4. A chaque exécution de cette commande, un nouveau fichier est créé. Faites le pour chaque groupe de requêtes.5. En fonction de l’organisation des tables FILMs que vous avez étudié précédemment

4.2. ÉTUDE DE PLANS D’EXÉCUTION CHAPITRE 4. ANALYSE DE PLAN EXPLAIN

Exemple de suite d’exécution pour une requête :

ALTER SESSION SET OPTIMIZER_MODE=RULE;alter system flush bu!er_cache;select titre, genre from FILM1 f where IDFILM=50273 ;alter system flush bu!er_cache;select titre, genre from FILM6 f where IDFILM=50273 ;

ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;alter system flush bu!er_cache;select titre, genre from FILM1 f where IDFILM=50273 ;alter system flush bu!er_cache;select titre, genre from FILM6 f where IDFILM=50273 ;

4.2.1 Requêtes Simples

1. Toutes les tables :

SELECT TITRE FROM FILMi WHERE IDFILM=50273 ;

2. FILM1 et FILM6 :

SELECT COUNT(!) FROM FILMi WHERE IDFILM BETWEEN 50273 AND 60000 ;

3. Toutes :

SELECT TITRE FROM FILMi WHERE ANNEE=1999 ;

4. FILM2 et FILM3

SELECT COUNT(!) FROM FILMi WHERE ANNEE=1999 ;

5. FILM2, FILM3 et FILM5 :

SELECT TITRE FROM FILMi WHERE CODEPAYS=’aaej’ ;

6. FILM1 et FILM6 :

SELECT TITRE FROM FILMi WHERE IDFILM>50273 ;

4.2.2 Requêtes de jointures

1. FILM2, FILM3 et FILM4 :

SELECT TITRE, NOM FROM FILMi F, ARTISTE AWHERE F.IDMES=A.IDARTISTE AND ANNEE=1999;

2. FILM2, FILM3 et FILM5 :

SELECT TITRE, NOM FROM FILMi F, ARTISTE AWHERE CODEPAYS=’aaej’ AND F.IDMES=A.IDARTISTE ;

CHAPITRE 4. ANALYSE DE PLAN EXPLAIN 4.2. ÉTUDE DE PLANS D’EXÉCUTION

3. FILM3 et FILM5 :

SELECT NOM, COUNT(!) FROM FILMi F, ARTISTE AWHERE ANNEE=1999 AND F.IDMES=A.IDARTISTEGROUP BY NOM ;

4. FILM3 et FILM5 :

SELECT NOM, COUNT(!) FROM ARTISTEWHERE IDARTISTE IN

(SELECT DISTINCT IDMES FROM FILMiWHERE ANNEE=1999)

GROUP BY NOM ;

5. FILM2, FILM3, FILM5 et FILM6 :

SELECT COUNT(!) FROM FILMi F, ROLE R, ARTISTE AWHERE F.IDFILM=R.IDFILM AND

R.IDACTEUR=A.IDARTISTE AND F.IDMES=A.IDARTISTE ANDCODEPAYS=’aaej’ AND ANNEE=1999;

6. FILM2, FILM6 ;

SELECT TITRE FROM FILMi F, NOTATION NWHERE F.IDFILM = N.IDFILMGROUP BY TITREHAVING AVG(NOTE) > 15;

7. FILM2, FILM6 ;

SELECT TITRE FROM FILMi F,(SELECT IDFILM, AVG(NOTE) as AVG_NOTE FROM NOTATION NGROUP BY IDFILM) N

WHERE F.IDFILM = N.IDFILM AND AVG_NOTE > 15;

4.2. ÉTUDE DE PLANS D’EXÉCUTION CHAPITRE 4. ANALYSE DE PLAN EXPLAIN

Pour aller plus loin, vous pouvez tester les requêtes mais sans vider le cache avant l’exécution(faites deux exécutions successives) ;

5 Optimisation

5.1 HINT

Dans cette partie, il vous est demandé d’utiliser le HINT (Full, Index, et USE_NL) dans les requêtessuivantes. Pour rappel l’utilisateur peut forcer l’optimiseur à faire certains choix, avec la commande/*+ HINT un_conseil */ insérée juste après le SELECT.

1.SELECT TITRE FROM FILMi WHERE CODEPAYS= ’aaej’

2.SELECT NOM, COUNT(!) FROM ARTISTEWHERE IDARTISTE IN

(SELECT DISTINCT IDMESFROM FILMiWHERE ANNEE= 1999)

GROUP BY NOM ;

3.SELECT TITRE FROM FILMi F, NOTATION NWHERE F.IDFILM = N. I DFILMGROUP BY TITREHAVING AVG(NOTE) > 15 ;

5.2 Optimisation de requête

La requête suivante n’est pas optimisée. Étudier le plan EXPLAIN généré (sans cache), et proposerune nouvelle requête SQL produisant le même résultat mais avec moins d’entrées/sorties (consistentget/physical read).

SELECT titre FROM film1WHERE exists (

SELECT idfilmFROM ROLE RWHERE film1.idfilm = r.idfilm and

nomrole like ’t%’GROUP BY idfilmHAVING count(!) > 1);

5.3. EXPLAIN VERS SQL CHAPITRE 5. OPTIMISATION

5.3 EXPLAIN vers SQL

Le plan EXPLAIN ci-dessous fait des parties des fichiers de logs du DBA. Trouver la requête SQLayant produit ce plan d’exécution.

0 SELECT STATEMENT1! HASH JOIN RIGHT SEMI2! TABLE ACCESS FULL ROLE3 TABLE ACCESS FULL FILM1

1 ! access("IDFILM"="IDFILM")2 ! filter("NOMROLE" LIKE ’t%’)

5.4 Scénario

En consultant les statistiques d’accès à la base de données, on constate que celui reçoit régulière-ment les trois requêtes ci-dessous avec leur fréquence :

R1 1 200 fois par jour

SELECT TITRE FROM FILMi WHERE CODEPAYS= ’aaej’

R2 300 fois par jour

SELECT NOM, COUNT(!)FROM FILMi F , ARTISTE AWHERE ANNEE= 1999 AND F.I DMES = A.I DARTISTEGROUP BY NOM ;

R3 100 fois par jour

SELECT TITRE FROM FILMi F, NOTATION NWHERE F.IDFILM = N. I DFI LMGROUP BY TITREHAVING AVG(NOTE) > 15 ;

1. Donner un tableau de “Physical Reads” pour chaque requête sur chacunes des tables FILM1 àFILM6.

2. Calculer le coût généré pour chaque Filmi par jour, en fonction de la fréquence de chaque requête(le cache n’est pas considéré). En déduire la table la plus optimisée pour notre cas.

3. Peut-on encore améliorer les performances du SGBD? (index supplémentaire, organisation dif-férente, extensions. . . )