TP BD2
OPTIMISATION (D’APRÈS N. ANCIAUX)
Nous allons maintenant étudier le comportement de l’optimiseur Oracle. Pour cela, nous allons comparer des plans
d’exécution / temps d’exécution de requêtes identiques posées sur en présence ou non d’indexes, ainsi que des
requêtes équivalentes (exprimées différemment, mais qui produisent le même résultat)
Préparation de l’expérience :
Récupérer les fichiers nommés load.sql, load-det.sql, createnoind.sql et create.sql sur le
répertoire « optimisation » du serveur.
NOUS UTILISERONS SQL*PLUS et non pas SQL Developper. Configurez la taille de la fenêtre (set linesize 500)
Vous pouvez créer une base sans index sous SQL*PLUS en exécutant createnoind.sql, et créer une base avec
index en exécutant create.sql.Les données sont dans le script load.sql.
Note :Pour mesurer les temps avec SQL*plus, on peut utiliser les instructions set timing on et set autotrace
on. Toutefois, les temps seront donnés pour chaque instruction ! Observez le script load.sql et essayez de
comprendre son fonctionnement. Alternativement, on aurait pu créer un script de chargement (avec begin insert….
end ;)
Q1 : Comparez les temps de chargement de tuples dans la table DET avec et sans index. Pour cela, créer la base
sans index en lançant createnoind.sql, puis effectuer un chargement des tables CLI, COM, et PRO en
construisant un script à partir du script load.sql. Charger ensuite la table DET (avec le script load-det.sql) et
observez le temps de chargement. Refaites la même chose avec create.sql. Analyser les résultats et conclure.
Faites de même en modifiant les scripts load.sql et load-det.sql pour les encapsuler dans un bloc begin…
end ; et utilisez set timing on. Comparez.
Q2 : Ecrivez les requêtes, dessinez les plans (sous forme d’arbre) et notez les temps d’exécutions de différentes
requêtes (ci-dessous) sur les schémas IND/NOIND. Pensez à noter proprement vos résultats d'évaluations pour vous
y retrouver : requête SQL, Plan (sous forme algébrique), paramètres de l'expérience, résultat (temps). Analyser les
différences et conclure.
Les requêtes SQL à considérer sur chacun des 2 schémas IND/NOIND sont les suivantes:
R1 : Select count(distinct L.NumCli) from CLI L, COM C, DET D, PRO P
Where L.NumCli = C.NumCli and C.NumCom = D.NumCom and D.NumPro = P.NumPro;
R2 : version avec uniquement des IN : L.NumCli IN (SELECT C.NumCLI FROM COM C WHERE C.NumCOM IN ...
R3 : version avec uniquement des EXISTS - tous les prédicats dans la requête la plus interne : WHERE EXISTS
(SELECT C.NumCli from COM C WHERE EXISTS …(L.NUMCLI = C.NumCli AND …)
R4 : version avec uniquement des EXISTS - les prédicats le plus tôt
R5 : R1 + « and NumCom = 10000 »
R6 : R1 + « and NumCom> 10000 »
Pour accéder au plan d’exécution, exécuter l’instruction suivante : explain plan for
sql-statement; puis : SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
Mais il est plus simple d’activer set autotrace on, puis exécuter une requête normalement.
Q3 : Optimisation manuelle
Vous pouvez changer le mode d’optimisation grâce aux commandes :
alter session set optimizer_mode = RULE ;
alter session set optimizer_mode = ALL_ROWS ;
alter session set optimizer_mode = FIRST_ROWS ;
Ajoutez des hints d’optimisation (indications données à l’optimiseur), qui sont classés dans les catégories suivantes:
Les hints forcant une approche d’optimisation:
ALL_ROWS, FIRST_ROWS(n), RULE
Les hints forçant l’Optimiseur à choisir la méthode d’accès spécifiée (si elle existe) :
FULL, CLUSTER, HASH, INDEX, NO_INDEX, INDEX_ASC, ...
Les hints suggérant un ordre de jointure :
LEADING, ORDERED
Les hints suggèrant un algorithme de jointure:
USE_NL, NO_USE_NL, USE_MERGE, NO_USE_MERGE, …
Exemple : Select /*+FULL(CLI)*/ count(*) From CLI Where NomCli = ‘Jean’;
Vous trouverez la syntaxe exacte des “hints” sur le web.
Ajouter des statistiques avec la console Oracle (analyze table CLI estimate statistics ; ) Vous pouvez
consulter le Web pour voir le type d’informations disponibles.
Ces statistiques peuvent être vues en utilisant les scripts index.sql et tabcol.sql (à télécharger).
Tester alors à nouveau la requête R6 dans les 3 modes RULE, ALL_ROWS, FIRST_ROWS.
Page -1 1
