Lorsqu’une requête est soumise au moteur de base de données, celui-ci doit générer un plan d’exécution avant d’exécuter la requête.
L’organe en charge de la création du plan d’exécution est l’Optimizer. Son fonctionnement est globalement similaire pour Oracle et SQL Server, mais quelques différences significatives existent. Voici les similitudes et les principales différences dans les grandes lignes (je me suis efforcé de pointer vers des compléments d’informations lorsque cela était possible).
Cost Based Optimizer
L’optimizer suit le même mode de fonctionnement pour les deux moteurs: le plan d’exécution est choisi selon une notion de coût (on parle de Cost Based Optimizer ou CBO).
Etapes de compilation
Partie commune
Sans trop de surprise, les étapes de l’exécution d’une requête sont dans l’ensemble les mêmes.
- Validation syntaxique
- Validation sémantique
- Utilisation éventuelle d’un plan précédemment produit
- Réécriture/Simplification de la requête
- Exploration des chemins d’accès et estimation des coûts.
- Désignation du chemin le moins coûteux, génération du plan d’exécution et mise en cache de ce dernier.
Spécificités SQL Server
Le coût associé à chaque chemin d’accès ne prend pas en compte la consommation CPU (celle-ci est tout de même indirectement prise en compte dans le coût des différents opérations physiques, notamment les tris et les jointures physiques).
Pour gérer la compilation simultanée d’un grand nombre de requêtes, le mécanisme de Compilation Gateways est implémenté:
- Une “petite” requête (requête dont le plan d’exécution occupe peu d’espace en mémoire) est compilée dans la Small Gateway. Le nombre de compilations simultanées dans cette Gateway est de 4x<nombre de CPUs logiques vus par l’instance SQL Server.
- Une requête “moyenne” (requête dont le plan d’exécution occupe plus d’espace mémoire) peut continuer sa compilation dans la Medium Gateway. Celle-ci peut héberger jusqu’à <nombre de CPUs logiques vus par l’instance SQL Server> compilations simultanées
- Une requête “grande” peut terminer sa compilation dans la Large Gateway. dans celle-ci, une unique requête peut être compilée à la fois.
Les seuils d’utilisation de la mémoire pour le passage entre Small vers Medium et Medium vers Large ne sont pas fixes, et dépendent grosso modo du rapport <Mémoire allouée à l’instance> / <Nombre de CPUs logiques vus par l’instance>.
Un article assez complet sur le sujet peut être lu ici: Compilation Gateways
Afin d’éviter de stocker en mémoire des plans d’exécution qui ne seront pas réutilisés (cas de requêtes générées à la volée), un paramètre de configuration peut être positionné: “Optimize for AdHoc Workload” . Seuls les plans correspondant aux requêtes appelées au moins deux fois seront remontés en mémoire.
Spécificités Oracle
La mémoire utilisée pour la compilation d’une requête (on parle de curseur ou de Private SQL Area) est dans la zone privée de chaque session (PGA).
Le calcul du coût prend en compte la consommation CPU et sait exploiter les Statistiques Système (cadence CPU, durée moyenne d’une lecture aléatoire/séquentielle, …) qui peuvent être collectées sur l’instance.
Les principaux types d’opérations physiques
Partie commune
Ici encore, un grand nombre d’opérations physiques sont communes, même si le nom de l’opération diffère:
| Opération | SQL Server | Oracle |
| Balayage d’une table | Table Scan | Table Access Full |
| Balayage des feuilles d’un Indexe | Index Scan | Full Index Scan et Fast Full Index Scan |
| Recherche dans un indexe | Index Seek | Index Range Scan |
| Accès à une table depuis un indexe | Bookmark Lookup | Table Access By Index Rowid |
| Jointure de type Fusion | Merge Join | Sort Merge Join |
| Jointure d’exclusion | Anti-Semi Join | Anti Join |
Les deux autres jointures physiques (Nested Loop Join et Hash Join) sont les mêmes, ainsi que les trois jointures partielles ( Semi Join).
Spécificités SQL Server
Le concept de l’indexe Clusterisé (index pour lequel les feuilles sont en fait les données de la table, correspondant au concept d’Index Organized Table d’Oracle) et sa généralisation (par défaut, une clé primaire sous SQL Server est gérée par un indexe Clusterisé) amènent à la mise à disposition de deux opérateurs:
- Clustererd Index Seek
- Clustered Index Scan (qui correspond par définition au Table Scan pour toute table avec un indexe Clusterisé)
Spécificités Oracle
Un plus grand nombre d’opérations physiques sont disponibles avec l’optimizer d’Oracle. Les plus souvent rencontrées sont:
- INDEX SKIP SCAN. Correspond à une recherche dichotomique dans un indexe en ignorant la première colonne de l’indexe
- BITMAP MINUS, BITMAP OR, BITMAP INDEX VALUE. Correspond à l’utilisation d’indexes de type BITMAP
- UNIQUE INDEX SCAN. Oracle distingue la recherche dans un indexe non unique de celle dans un indexe unique
- CARTESIAN JOIN. Oracle utilise cet opérateur pour résoudre une jointure croisée (SQL Server se contente d’un Nested Loop)
Gestion du parallélisme
Partie commune
Les types d’opérations parallèles (disponibles par défaut) sont les mêmes sur les deux moteurs:
- Parallélisation d’un balayage de table (partitionnée ou non) ou d’indexe
- Parallélisation d’opérations (typiquement des scans précédant une jointure)
- Parallélisation de jointures
Mais l’utilisation du parallélisme par l’Otmizer est fondalement différente.
SQL Server propose par défaut le parallélisme. Une requête pourra être parallélisée en autant de processus esclaves que de CPUs logiques vus par l’instance. Ceci est conditionné par le paramètre de configuration Max Degree Of Parallelism qui vaut 0 par défaut. Pour désactiver le parallélisme, il faut passer ce paramètre à 1.
Une requête sera candidate au parallélisme dès que sa durée d’exécution excédera 5 secondes. Ceci correspond à la valeur du paramètre de configuration Cost Threshold for Parallelism.
Oracle ne permet pas par défaut l’utilisation de parallélisme dans une requête. Pour qu’une requête puisse bénéficier du parallélisme, il faut que la directive PARALLEL soit explicitement utilisée dans la requête, OU qu’une des tables (ou indexes) de la requête soit déclarée comme acceptant le parallélisme (clause PARALLEL de CREATE TABLE et ALTER TABLE). Dans ce cas, le degré de parallélisme est la valeur la plus faible parmi La directive PARALLEL, la clause PARALLEL de l’objet, et le produit PARALLEL_THREADS_PER_CPU x CPU_COUNT (typiquement 2 fois le nombre de CPUs logiques de la machine hébergeant l’instance Oracle).
Spécificité Oracle
Oracle permet la parallélisation d’ordres de modification (INSERT … SELECT, UPDATE, DELETE et MERGE), principalement sur des tables partitionnées. Ce mécanisme peut être activé via ALTER SESSION ENABLE PARALLEL DML
Enrichissement des statistiques
Lorsque l’optimizer estime qu’il ne dispose pas de statistique pour une requête précise, il est capable d’en collecter à la volée. Ce mécanisme est effectif par défaut, et est géré comme suit:
Oracle Optimizer Dynamic Sampling. Le paramètre OPTIMIZER_DYNAMIC_SAMPLING définit le niveau d’utilisation du mécanisme de collecte. Ce mécanisme peut collecter des statistiques sur une table, une colonne, ainsi qu’une statistique étendue (statistique sur une requête, ou sur un ensemble de colonnes).
SQL Server Auto Create Statistics. Cette option de base, positionnée à Vraie par défaut, permet au moteur de collecter automatiquement une statistique sur une ou plusieurs colonnes lorsqu’une telle statistique est manquante. Il ne permet pas de collecter de statistique filtrée.
Réutilisation d’un plan
Partie commune
L’utilisation d’un plan précédemment généré est globalement la même.
Oracle CURSOR_SHARING. Ce paramètre définit la similitude que doivent avoir deux requêtes pour pouvoir bénéficier du même plan d’exécution. Il prend trois valeurs:
- EXACT (valeur par défaut). Une requête n’utilisera un plan généré que si celui-ci correspond exactement au texte de la requête. “SELECT * FROM EMPLOYEES WHERE EMP_ID = 5” et “SELECT * FROM EMPLOYEES WHERE EMP_ID = 6” ne partageront donc pas leur plan.
- SIMILAR. Deux requêtes partageront le même plan d’exécution si elles sont similaires et que les cardinalités des valeurs utilisées sont proches. “SELECT * FROM EMPLOYEES WHERE EMP_ID = 5” et “SELECT * FROM EMPLOYEES WHERE EMP_ID = 6” partageront le même plan si le nombre de lignes correspondant à EMP_ID=5 est du même ordre de grandeur que celui des lignes correspondant à EMP_ID=6.
- FORCE. Deux requêtes partageront le même plan d’exécution si elles sont similaires, en ignorant toutes les valeurs directes. “SELECT * FROM EMPLOYEES WHERE EMP_ID = 5” et “SELECT * FROM EMPLOYEES WHERE EMP_ID = 6” partageront donc leur plan, qui correspondra à “SELECT * FROM EMPLOYEES WHERE EMP_ID = :1”
SQL Server PARAMETRIZATION. Cette option d’une base SQL Server va régir la réutilisation des plans. Il peut prendre deux valeurs
- SIMPLE (valeur par défaut). Seules les requêtes simples pourront partager le même plan d’exécution. Une requête simple est une requête ne contenant pas de jointure, pas d’agrégat, et pour laquelle les éventuels prédicats ne concernent que des colonnes uniques. “SELECT * FROM EMPLOYEES WHERE EMP_ID = 5” et “SELECT * FROM EMPLOYEES WHERE EMP_ID = 6” partageront leur plan si EMP_ID est une colonne unique.
- FORCE. Lors de l’exécution d’une requête, tout prédicat utilisant une valeur directe est réécrit en utilisant une variable. Les requêtes “SELECT * FROM EMPLOYEES E INNER JOIN COMPANIES C ON C.COMP_ID = E.CMP_ID WHERE EMP_SALARY >10 AND C.COMP_NAME=’CAPDATA’ “ et “SELECT * FROM EMPLOYEES E INNER JOIN COMPANIES C ON C.COMP_ID = E.CMP_ID WHERE EMP_SALARY >50 AND C.COMP_NAME=’OSMOZIUM’ “ utiliseront le même plan, qui correspondra à “SELECT * FROM EMPLOYEES E INNER JOIN COMPANIES C ON C.COMP_ID = E.CMP_ID WHERE EMP_SALARY >@1 AND C.COMP_NAME=@2”
Spécificité Oracle
Pour résoudre le (grand) problème du “Bind Variable Peeking”, Oracle propose (depuis la version 11gR1) le mécanisme Adaptive Cursor Sharing.
Ce mécanisme permettra l’existence de plusieurs plans d’exécution pour une requête contenant des variables (qu’elles aient été explicitement utilisées dans le texte de la requête ou qu’elles aient été créées par le Cursor_Sharing). Concrètement, lors du calcul d’un plan, Oracle sait si ce plan est candidat à l’utilisation du Adaptive Cursor sharing (on parle alors de Bind-Aware Cursor, et on connaît cette information via la colonne IS_SHAREABLE de la vue V$SQL). Si c’est le cas, il compare régulièrement les statistiques estimées lors du calcul du plan avec celles correspondant aux différentes exécutions, et peut décider de générer un nouveau plan quand ces statistiques estimées et réelles sont trop différentes.
Spécificité SQL Server
SQL Server propose un autre mécanisme pour ce même problème de Bind Variable Peeking: la directive Optimize For.
L’idée ici est de spécifier dans la requête la valeur des variables pour laquelle le plan doit être optimisé.
Affichage d’un plan
Il est possible d’afficher le plan d’exécution estimé par le moteur, et celui existant en mémoire pour une requête donnée:
Oracle: Explain Plan et Display Cursor. L’ordre EXPLAIN PLAN FOR <ordre SQL> permet d’afficher le plan d’exécution. Un prérequis à l’utilisation de cet ordre est la création de la table PLAN_TABLE via le script @?/rdbms/admin/utlxplan.sql. La procédure DBMS_XPLAN.DISPLAY_CURSOR permet de connaître le plan d’exécution en mémoire pour une requête donnée (identifiée par son identifiant SQL_ID).
SQL Server: SET SHOWPLAN_XML ON et sys.dm_exec_cached_plan. L’utilisation de l’option SET SHOWPLAN_XML ON permet, au lieu d’exécuter une requête, d’afficher son plan d’exécution au format XML (le plan au format XML est interprété nativement par SQL Server Management Studio et offre une vue ergonomique du plan). La fonction sys.dm_exec_query_plan permet de connaître le plan d’exécution en mémoire pour une requête donnée (au format XML).
A noter que l’affichage du plan au format texte est disponible via l’option SET SHOWPLAN_TEXT et la fonction sys.dm_exec_text_query_plan.
Influencer un plan d’exécution
Partie commune
Les deux moteurs proposent divers mécanismes pour prendre la main, partiellement ou totalement, sur le plan d’exécution d’une requête donnée:
Les directives (Hints) d’une requête fonctionnent de façon similaire sur les deux moteurs ( /*+ <directives> */ sur Oracle et OPTION( <directives>) sur SQL Server). Une directive peut porter sur l’utilisation:
- D’un indexe
- D’une jointure physique particulière
- Du parallélisme
- De l’ordre spécifique des opérations
Ces mêmes directives peuvent être spécifiées a posteriori pour une requête (allant jusqu’à la spécification complète du plan). Pour Oracle, c’est le mécanisme des Stored Outlines qui offre cette fonctionnalité. Pour SQL Server, le mécanisme est celui du Plan Guide.
Le concept de matérialisation de requêtes est disponible dans l’édition Enterprise des deux moteurs. Oracle sait reconnaître une Vue Matérialisée dans une requête et l’utiliser pour améliorer les performances, tant que celle ci est valide (c-à-d tant que les données des tables sous-jacentes n’ont pas été modifiées depuis le dernier rafraîchissement de la vue matérialisée). SQL Server sait reconnaître une Vue Indexée dans une requête et l’utiliser. Une vue indexée est automatiquement mise à jour lors de la mise à jour d’une table sous-jacente.
Spécificité Oracle
Un certain nombre de paramètres d’initialisation permettent d’influencer globalement le fonctionnement de l’Optimizer.
La liste et la description de ces paramètres est disponible ici (pour la version 11gR2).
Continuez votre lecture sur le blog :
- Oracle et SQL Server: Les Statistiques (Benjamin VESAN) [OracleSQL Server]
- Oracle et SQL Server: La Fragmentation (Benjamin VESAN) [OracleSQL Server]
- Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
- Oracle : SQL Plan Management en version Standard Edition SE2 (Emmanuel RAMI) [Oracle]
- Oracle et SQL Server (Benjamin VESAN) [OracleSQL Server]