Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution....
Transcript of Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution....
![Page 1: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/1.jpg)
L’optimiseur ORACLE suit une approche classique:
Génération de plusieurs plans d’exécution.Estimation du coût de chaque plan généré.Choix du meilleur et exécution.
Tout ceci est automatique, mais il est possible d’influer, voire de forcer le plan d’exécution.
L’optimiseur ORACLE
![Page 2: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/2.jpg)
Estimation du coût d’un plan d’exécution
Beaucoup de paramètres entrent dans l’estimation du coût :
– Les chemins d’accès disponibles.
– Les opérations physiques de traitement des
– résultats intermédiaires.
– Des statistiques sur les tables concernées (taille, sélectivité). Les statistiques sont calculées par appel explicite à l’outil ANALYSE.
– Les ressources disponibles.
![Page 3: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/3.jpg)
Règles
Priorité• 1 Accès à un enregistrement par ROWID• 2 Accès à un enregistrement dans un cluster• 3 Accès à un enregistrement dans une table
de hachage (hash cluster )• 4 Accès à un enregistrement par clé unique
(avec un index)• ... ...• 15 Balayage complet de la table
![Page 4: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/4.jpg)
Principaux paramètres
• OPTIMIZER_MODE (RULE, CHOOSE, FIRST_ROW, ALL_ROWS).
• SORT_AREA_SIZE (taille de la zone de tri).
• HASH_AREA_SIZE (taille de la zone de hachage).
• HASH_JOIN_ENABLED considère les jointures par hachage.
![Page 5: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/5.jpg)
Création des statistiques1. Calcul de la taille et du nombre de lignes :
ANALYSE TABLE Autos COMPUTE STATISTICS FOR TABLE
2. Analyse des index :ANALYSE TABLE Autos COMPUTE STATISTICSFOR ALL INDEX
3. Analyse de la distribution des valeurs :ANALYSE TABLE Autos COMPUTE STATISTICS FOR COLUMNS serial, color, model
![Page 6: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/6.jpg)
Les chemins d’accès
• Parcours séquentiel (FULL TABLE SCAN).
• Par adresse (ACCESS BY ROWID).
• Parcours de regroupement (CLUSTER SCAN).
On récupère alors dans une même lecture les n-uplets
des 2 tables du cluster.
• Recherche par hachage (HASH SCAN).
• Parcours d’index (INDEX SCAN).
![Page 7: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/7.jpg)
Opérations physiques
• INTERSECTION : intersection de deux ensembles de n-uplet.
• CONCATENATION : union de deux ensembles.
• FILTER : élimination de n-uplets (sélection).
• PROJECTION : opération de l’algèbre relationnelle.
D’autres opérations sont liées aux algorithmes de jointures.
![Page 8: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/8.jpg)
Algorithmes de jointure
ORACLE utilise trois algorithmes de jointures :
• Tri/fusion quand il n’y a pas d’index. Opération SORT et MERGE.
• Jointure par hachage quand il n’y a pas d’index. Opération HASH JOIN
• Boucles imbriquées quand il y a au moins un index. Opération NESTED LOOP.
![Page 9: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/9.jpg)
L’outil TRACE
• L’outil TRACE donne le plan d’exécution et les coûts d’exécution d’une requête.
• set timing on
• set autotrace trace
• La description comprend :
– Le chemin d’accès utilisé.
– Les opérations physiques (tri, fusion, intersection, ...).
– L’ordre des opérations.
• Il est représentable par un arbre.
![Page 10: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/10.jpg)
Optimisation dans ORACLE
Autos ( serialNo, model, color)
Sales (serial, id_dealer, datesales, price)
Dealers (id_dealer, city, departement, phone)
![Page 11: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/11.jpg)
Sélection sans indexset timing on
set autotrace trace
select * from autos where model = 'Gobi' ;
Ecoulé : 00 :00 :00.05Plan d'exécution---------------------------------------------------------- 0 SELECT STATEMENT 1 TABLE ACCESS FULL AUTOS----------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("MODEL"='Gobi')
![Page 12: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/12.jpg)
Sélection sans index
Ecoulé : 00 :00 :00.02Plan d'exécution--------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|----------------------------------------------------------------| 0 | SELECT STATEMENT | | 32 | 544 | 3 (0)||* 1 | TABLE ACCESS FULL | AUTOS | 32 | 544 | 3 (0)|----------------------------------------------------------------
Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("MODEL"='Gobi')
set timing onset autotrace traceselect *from autoswhere model = 'Gobi' ;
![Page 13: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/13.jpg)
Plan d’exécution
PROJECTION
PARCOURS SEQUENTIEL
AUTOS
![Page 14: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/14.jpg)
Sélection avec indexset timing onset autotrace traceselect *from dealerswhere id_dealer = 'dealer1';
Ecoulé : 00 :00 :00.00
Plan d'exécution-----------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)|| 1 | TABLE ACCESS BY INDEX ROWID | DEALERS | 1 | 38 | 1 (0)||* 2 | INDEX UNIQUE SCAN | PK_DEALERS | 1 | | 0 (0)|-------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID_DEALER"='dealer1')
![Page 15: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/15.jpg)
Plan d’exécution
ACCES DIRECT
TRAVERSÉE INDEX
Index Dealers
PROJECTION
Dealers
![Page 16: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/16.jpg)
Jointure avec indexsset timing onsset autotrace traceSSELECT serialNo, model, color FROM Autos A, Sales s WHERE serial = serialNo;
Ecoulé : 00 :00 :00.01 Plan d'exécution ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost %CPU)| ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 236 | 5428 | 2 (0)| | 1 | NESTED LOOPS | | 236 | 5428 | 2 (0)| | 2 | INDEX FULL SCAN | PK_SALES | 236 | 1416 | 1 (0)| | 3 | TABLE ACCESS BY INDEX ROWID| AUTOS | 1 | 17 | 1 (0)| |* 4 | INDEX UNIQUE SCAN | PK_AUTOS | 1 | | 0 (0)| ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("SERIAL"="SERIALNO")
![Page 17: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/17.jpg)
Plan d’exécution
ACCES DIRECT
Recherche Par Cléserial=serialNo
Index Autos
PROJECTION
Autos
PARCOURS INDEX
Index Sales
BOUCLE IMBRIQUEE
![Page 18: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/18.jpg)
Jointure sans index On force ici l’optimiseur à ne pas utiliser l’index de sales
set timing onset autotrace traceSELECT /*+ NO_INDEX(s) */ serialNo, model, colorFROM Autos A, Sales sWHERE serial = serialNo;
Ecoulé : 00 :00 :00.04
Plan d'exécution-----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows| Bytes | Cost |-------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 236 | 5428 | 4 || 1 | NESTED LOOPS | | 236 | 5428 | 4 || 2 | TABLE ACCESS FULL | SALES | 236 | 1416 | 3 || 3 | TABLE ACCESS BY INDEX ROWID | AUTOS | 1 | 17 | 1 || 4 | INDEX UNIQUE SCAN | PK_AUTOS | 1 | | 0 |-------------------------------------------------------------------------
![Page 19: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/19.jpg)
Plan d’exécution
ACCES DIRECT
Recherche Par Cléserial=serialNo
IndexAutos
PROJECTION
Autos
PARCOURS SEQUENTIEL
Sales
BOUCLE IMBRIQUEE
![Page 20: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/20.jpg)
Jointure sans index On force ici l’optimiseur à ne pas utiliser les index
set timing onset autotrace traceSELECT /*+ NO_INDEX(s) NO_INDEX(A)*/ serialNo,
model, colorFROM Autos A, Sales sWHERE serial = serialNo;
Ecoulé : 00 :00 :00.02
Plan d'exécution----------------------------------------------------------
------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost |------------------------------------------------------------| 0 | SELECT STATEMENT | | 236 | 5428 | 7 || 1 | HASH JOIN | | 236 | 5428 | 7 || 2 | TABLE ACCESS FULL| AUTOS | 236 | 4012 | 3 || 3 | TABLE ACCESS FULL| SALES | 236 | 1416 | 3 |------------------------------------------------------------
![Page 21: Loptimiseur ORACLE suit une approche classique: Génération de plusieurs plans dexécution. Estimation du coût de chaque plan généré. Choix du meilleur et.](https://reader036.fdocument.pub/reader036/viewer/2022062511/551d9d7d497959293b8b4e0d/html5/thumbnails/21.jpg)
Plan d’exécution
PARCOURS SEQUENTIEL
PROJECTION
Autos
PARCOURS SEQUENTIEL
Sales
Hash Join