Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Ingénierie et Optimisation de Bases de Données FIP - ABD TPs CNAM Paris Nicolas

Ingénierie et Optimisation de Bases de Données FIP - ABD TPs CNAM Paris Nicolas.Travers (at) cnam.fr Table des matières 1 Informations sur la Base de Données 3 1.1 Informations sur les capacités . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.2 Démarrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.3 Commandes pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.4 Schéma . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.5 EXPLAIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2 Utilisation de l’outils TOAD 7 2.1 Connexion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 2.2 Schéma et Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 2.3 Interrogation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 3 Etude de la base de donné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êtes Simples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 4.2.2 Requêtes de jointures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 5 Optimisation 17 5.1 HINT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 5.2 Optimisation de requête . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 5.3 EXPLAIN vers SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 5.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 : orcl Aﬃchage 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’oubliez pas que vous pouvez sélectionner du texte avec la 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’aﬃcher les résultats de sqlplus sur 10 carac- tères SET LINESIZE 30 Permet d’aﬃcher 30 tuples résultats avant qu’il ne ré- pète le schéma CLEAR SCREEN Eﬀace le contenu de l’écran SELECT table_name FROM all_tables Aﬃchage 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 ’%$%’ Aﬃchage des contraintes ainsi que leur aﬀectation SELECT index_name, table_name, column_name FROM all_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_segments where 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 from ALL_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 les statistiques d’exécution pour chacune des requêtes exé- cutées. (on peut remplacer TRACE par ON qui aﬃchera 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. Aﬁn 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 identiﬁer : – 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 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)) uploads/Geographie/ fip-tp-optim.pdf