Comparaison des plans de requêtes paramétrées dans SAP HANA Cloud avec SQL Analyzer

Contexte : Requêtes paramétrées et fluctuation des performances

Une requête paramétrée peut s'exécuter rapidement pour certaines valeurs d'entrée et lentement pour d'autres en raison d'une distribution asymétrique des données. L'optimiseur compile un seul plan par instruction, et ce plan peut être optimal pour une plage de sélectivité étroite mais médiocre pour une plage large. Par exemple, une fonction de table acceptant un paramètre de région peut retourner 50 lignes en 20 ms pour la région DE, mais 2 millions de lignes en 4 s pour la région GLOBAL. Cette différence n'est pas nécessairement un bug de la requête ; il s'agit souvent d'un choix de plan dépendant de la sélectivité. La première étape consiste à capturer le plan d'exécution pour chaque ensemble de paramètres et à les comparer avec SQL Analyzer. L'objectif est de déterminer si la fluctuation est causée par la sélection du plan et si l'activation des Plan Variants ou la réécriture de la requête est la solution appropriée.

SQL Analyzer est un ensemble de vues, de tables et de graphiques permettant d'analyser n'importe quelle requête SQL. Vous pouvez approfondir l'exécution d'un graphique, analyser la chronologie de compilation et d'exécution de la requête, visualiser les tables utilisées, voir combien d'enregistrements chaque étape a traités et vérifier la séquence des opérateurs. Il n'est pas limité aux vues de calcul ; il peut analyser le SQL défini dans les fonctions de table et les instructions de la console SQL. Pour l'utiliser, vous devez ajouter l'extension SAP HANA Performance Tools à l'espace de développement, ce qui ne peut être fait que lorsque l'espace de développement est arrêté. L'utilisateur d'analyse a besoin des privilèges système TRACE ADMIN et INIFILE ADMIN. Le flux de travail commence par le placement du SQL paramétré dans une console SQL, puis la génération d'un fichier de plan avec Analyze Generate SQL Analyzer Plan File, et enfin l'ouverture du fichier de plan dans la vue HANA SQL Analyzer.

Les fichiers de plan peuvent être générés depuis l'explorateur de base de données SAP HANA intégré, où le fichier de plan s'ouvre directement dans la vue SQL Analyzer, ou depuis des outils externes, où le fichier doit être téléchargé puis téléchargé vers la vue Explorer de SAP Business Application Studio. L'emplacement des fichiers de plan générés est fixe et ne peut être modifié. Si vous souhaitez analyser un fichier de plan plus tard, vous pouvez y accéder via la vue Explorer ou via l'explorateur de base de données externe sous Catalog Database Diagnostic Files DB Instance ID other. Le point clé est qu'il faut un fichier de plan distinct pour chaque valeur de paramètre que vous souhaitez comparer. Un seul plan mis en cache ne suffit pas lorsque la sélectivité varie selon les valeurs de paramètres.

La comparaison doit se concentrer sur le temps d'exécution au niveau de l'étape, le nombre d'enregistrements traités et la séquence des opérateurs. Une étape prenant significativement plus de temps que les autres, ou traitant beaucoup plus d'enregistrements que prévu, constitue un goulot d'étranglement. La séquence des opérateurs indique si l'optimiseur a choisi un ordre de jointure différent, un chemin d'accès différent ou un moteur de traitement différent. Si le plan diffère pour une valeur de paramètre lente, vous pouvez soit activer les Plan Variants pour que HANA mette en cache plusieurs plans basés sur des clusters de sélectivité de filtres, soit réécrire la requête et restructurer le modèle pour que l'optimiseur produise un plan stable pour toutes les plages de sélectivité. La décision dépend de la capacité de l'optimiseur à distinguer les clusters de sélectivité et de l'efficacité fondamentale de la structure de la requête.

Les Plan Variants permettent à SAP HANA de mettre en cache plusieurs plans d'exécution pour une requête paramétrée en fonction de la sélectivité des filtres de table. L'optimiseur évalue les prédicats, compile les plans et associe les valeurs de filtres à des clusters. Cela réduit les fluctuations de performance et assure une performance de requête plus stable. Cependant, les Plan Variants n'aident que lorsque l'optimiseur peut regrouper les valeurs de paramètres en clusters de sélectivité distincts. Si la structure de la requête est intrinsèquement inefficace pour toutes les entrées, ou si l'optimiseur ne peut pas distinguer les clusters, la gestion des plans seule ne résoudra pas le problème. Dans ce cas, la réécriture de la requête ou la restructuration du modèle reste la seule option.

Les vues de surveillance M_SQL_PLAN_VARIANTS et M_SQL_PLAN_VARIANT_STATISTICS peuvent être utilisées pour surveiller les Plan Variants actifs et consulter les statistiques d'exécution correspondantes. Ces vues permettent de vérifier que l'optimiseur a créé les clusters attendus et que les plans sont utilisés. Elles aident également à mesurer les améliorations de performance obtenues, notamment la réduction des fluctuations. La décision de réécrire doit se baser sur les preuves issues des fichiers de plan, et non sur des suppositions concernant la distribution des données.

L'exemple concret est une fonction de table acceptant un paramètre de région. Pour la région DE, la requête retourne 50 lignes en 20 ms ; pour la région GLOBAL, elle retourne 2 millions de lignes en 4 s. Placez les deux appels paramétrés dans la console SQL, générez deux fichiers de plan, ouvrez-les côte à côte dans SQL Analyzer et observez que pour GLOBAL, l'optimiseur a choisi un scan complet du column-store avec une jointure nested-loop au lieu de la recherche d'index et de la jointure hash utilisée pour DE. Cela confirme que le plan diffère selon la valeur du paramètre et suggère soit d'activer les Plan Variants, soit de réécrire l'ordre des jointures et le pushdown des filtres.

SELECT * FROM TABLE(TABLE_FUNCTION(:region)) WHERE region = :region;

Raisonnement : Plan Variants versus Réécriture de requête

Une requête paramétrée est mise en cache avec un seul plan d'exécution, mais ce plan est un compromis. Lorsque la sélectivité d'un filtre change, le plan optimal peut également changer. Si l'optimiseur ne peut pas distinguer les clusters de sélectivité, il peut choisir un plan efficace pour certaines valeurs et mauvais pour d'autres. C'est la cause profonde de la fluctuation des performances SQL. Les Plan Variants remédient à cela en permettant à SAP HANA de mettre en cache plusieurs plans pour les requêtes paramétrées en fonction de la sélectivité des filtres de table. L'optimiseur évalue les prédicats, compile les plans et associe les valeurs de filtres à des clusters, permettant ainsi de conserver un plan pour une plage sélective restreinte et un autre pour une plage non sélective étendue.

La limitation du cache d'un seul plan d'exécution est son incapacité à s'adapter aux changements de distribution des données. Un plan optimal pour un petit ensemble de résultats peut être désastreux pour un grand ensemble s'il utilise une jointure nested-loop ou un scan complet. Les Plan Variants résolvent ce problème en créant des plans distincts pour chaque cluster de sélectivité. L'optimiseur décide à quel cluster appartient une valeur de paramètre et utilise le plan correspondant. Cela stabilise les performances. Les vues M_SQL_PLAN_VARIANTS et M_SQL_PLAN_VARIANT_STATISTICS permettent de vérifier la création correcte des clusters et l'utilisation effective des plans.

Cependant, les Plan Variants ne sont pas une solution universelle. Ils n'aident que si l'optimiseur peut distinguer les clusters de sélectivité. Si la structure de la requête est intrinsèquement inefficace pour toutes les entrées, ou si l'optimiseur ne peut pas séparer les valeurs de paramètres en clusters distincts, la gestion des plans n'améliorera pas les performances. Dans ce cas, la seule option restante est de réécrire la requête ou de restructurer le modèle. La réécriture peut modifier l'ordre des jointures, pousser les filtres vers le bas ou remplacer une fonction de table par une construction plus efficace. La décision doit s'appuyer sur les fichiers de plan de SQL Analyzer.

Le flux de travail consiste à générer des fichiers de plan pour chaque valeur de paramètre, à les comparer dans SQL Analyzer, puis à décider si les Plan Variants ou une réécriture sont nécessaires. Si les fichiers montrent des séquences d'opérateurs, des nombres d'enregistrements ou des temps d'étape différents, l'optimiseur distingue déjà les valeurs. L'activation des Plan Variants peut alors réduire la fluctuation. Si les fichiers montrent le même plan mais que celui-ci est lent pour une valeur spécifique, l'optimiseur ne distingue pas les clusters, et une réécriture est probablement requise.

La distinction clé réside entre la gestion du plan et la structure de la requête. Les Plan Variants gèrent plusieurs plans, mais ne corrigent pas une structure inefficace. Si l'optimiseur ne peut pas distinguer les clusters de sélectivité ou si l'asymétrie des données est extrême, la requête doit être réécrite. Les preuves de SQL Analyzer doivent guider cette décision. Si les plans sont stables mais lents pour une valeur spécifique, la structure est le problème. Si les plans diffèrent mais que le plan lent est choisi pour une large plage de sélectivité, les Plan Variants peuvent suffire.

Les vues de surveillance fournissent les preuves nécessaires. M_SQL_PLAN_VARIANTS montre les variants actifs et M_SQL_PLAN_VARIANT_STATISTICS montre les statistiques d'exécution. Il est crucial de confirmer que le bénéfice l'emporte sur le coût, car les Plan Variants peuvent ajouter un certain overhead. La décision de réécrire doit reposer sur les fichiers de plan et les vues de surveillance, et non sur des suppositions.

L'exemple concret d'une fonction de table avec un paramètre de région illustre ce point : pour DE (50 lignes, 20 ms) versus GLOBAL (2 millions de lignes, 4 s). L'observation dans SQL Analyzer d'un scan complet et d'une jointure nested-loop pour GLOBAL, contre une recherche d'index et une jointure hash pour DE, confirme que le plan varie selon le paramètre, justifiant soit l'usage de Plan Variants, soit une réécriture du modèle.

SELECT * FROM M_SQL_PLAN_VARIANTS WHERE SCHEMA_NAME = '<container_schema_name>' AND OBJECT_NAME = '<calculation_view_or_function>';

Illustration concrète : Comparaison de fichiers de plan dans SQL Analyzer

Le flux de travail commence par le placement du SQL paramétré dans une console SQL. L'exécution de la requête n'est pas nécessaire ; seule la génération du fichier de plan compte. Utilisez l'option de menu Analyze Generate SQL Analyzer Plan File, choisissez un préfixe de nom de fichier et enregistrez. L'emplacement est prédéfini. Si la console SQL a été ouverte depuis l'explorateur de base de données SAP HANA intégré, le fichier s'ouvre immédiatement dans la vue HANA SQL Analyzer. S'il a été généré par un autre outil, vous devez le télécharger puis le charger dans la vue Explorer de SAP Business Application Studio. L'extension SAP HANA Performance Tools doit être ajoutée à l'espace de développement, ce qui nécessite que celui-ci soit arrêté.

Une fois le fichier chargé, sélectionnez-le pour afficher les résultats. La vue SQL Analyzer présente le plan d'exécution sous forme de graphique, avec le temps d'exécution par étape, le nombre d'enregistrements traités et la séquence des opérateurs. Vous pouvez analyser la chronologie de compilation et d'exécution et visualiser le nombre de tables utilisées. L'objectif est d'identifier les étapes anormalement longues, celles traitant trop d'enregistrements et les séquences d'opérateurs divergeant selon les paramètres, signes d'un choix de plan dépendant de la sélectivité.

Pour l'exemple concret, générez deux fichiers : un pour la région DE et un pour la région GLOBAL. Comparez-les côte à côte. Pour DE, le plan devrait montrer une recherche d'index et une jointure hash avec peu d'enregistrements. Pour GLOBAL, il devrait montrer un scan complet du column-store et une jointure nested-loop avec un volume massif de données. Le temps d'exécution et le nombre d'enregistrements confirmeront la différence. Cette preuve démontre que l'optimiseur a choisi un plan différent pour la valeur lente, suggérant le besoin de Plan Variants ou de réécriture.

La comparaison doit se focaliser sur la séquence des opérateurs, car elle révèle les changements d'ordre de jointure, de chemin d'accès ou de moteur de traitement. Un scan complet au lieu d'une recherche d'index est un signe classique d'inadéquation de sélectivité. Une jointure nested-loop au lieu d'une jointure hash en est un autre. Le nombre d'enregistrements par étape indique la charge de données. Si une étape traite des millions de lignes alors qu'elle ne devrait en traiter que quelques dizaines, c'est un goulot d'étranglement.

Après comparaison, décidez entre Plan Variants et réécriture. Si les plans diffèrent et que l'optimiseur distingue les clusters, les Plan Variants peuvent réduire la fluctuation. Si les plans sont identiques mais lents pour une valeur spécifique, l'optimiseur ne distingue pas les clusters, et la réécriture est nécessaire. Les vues M_SQL_PLAN_VARIANTS et M_SQL_PLAN_VARIANT_STATISTICS permettent de vérifier l'activité des variants et la correspondance avec les clusters de sélectivité attendus.

L'exemple de la fonction de table (DE vs GLOBAL) confirme ce processus : en observant le passage d'une jointure hash/index seek à une jointure nested-loop/full scan, on valide que le plan varie selon le paramètre, orientant ainsi la solution vers la mise en cache multiple (Plan Variants) ou l'optimisation structurelle (réécriture).

EXPLAIN PLAN FOR SELECT * FROM TABLE(TABLE_FUNCTION(:region)) WHERE region = :region;

Limites d'applicabilité

SQL Analyzer nécessite l'extension SAP HANA Performance Tools dans l'espace de développement, laquelle ne peut être ajoutée que lorsque l'espace est arrêté. Ce prérequis doit être vérifié avant tout. L'utilisateur doit posséder les privilèges système TRACE ADMIN et INIFILE ADMIN. Comme ces privilèges sont de niveau système, ils peuvent ne pas être accordés aux utilisateurs d'application en production, limitant ainsi l'accès à ce flux de travail. Les fichiers de plan générés hors de l'explorateur intégré doivent être téléchargés et chargés manuellement, ajoutant une étape au processus. L'emplacement fixe des fichiers générés peut également compliquer la gestion.

Les Plan Variants n'aident que si l'optimiseur peut regrouper les valeurs de paramètres en clusters de sélectivité distincts. Ils ne corrigent pas une requête dont la structure est intrinsèquement inefficace pour toutes les entrées. Si l'optimiseur ne peut pas distinguer les clusters ou si l'asymétrie des données est extrême, la gestion des plans seule ne suffira pas. La réécriture de la requête ou la restructuration du modèle reste alors la seule option. Les vues de surveillance aident à vérifier la création des clusters, mais ne modifient pas la structure sous-jacente de la requête.

La décision d'activer les Plan Variants ou de réécrire la requête doit reposer sur les preuves des fichiers de plan. Si les plans diffèrent et que les clusters sont distincts, les Plan Variants sont appropriés. Si les plans sont identiques mais lents pour certains paramètres, la réécriture est requise. Les vues de surveillance fournissent des preuves complémentaires mais ne remplacent pas la compréhension de la requête et de la distribution des données.

L'exemple de la fonction de table (DE vs GLOBAL) illustre parfaitement ces limites : si l'analyse montre que l'optimiseur choisit systématiquement un plan inefficace malgré la différence de volume, ou s'il ne parvient pas à créer de clusters distincts pour GLOBAL, alors même l'activation des Plan Variants ne résoudra pas le problème de performance, rendant la réécriture du join order et du filter pushdown indispensable.

SELECT * FROM M_SQL_PLAN_VARIANTS WHERE SCHEMA_NAME = '<container_schema_name>' AND OBJECT_NAME = '<calculation_view_or_function>';