Archives du mot-clé Hints

Tous les Hints disponibles

Publié le par
Répondre

La vue V$SQL_HINT donne tous les hints disponibles avec la version où ils sont apparus et le hint inverse.
Assicié à v$sql_feature_hierarchy et v$sql_feature on a une indication de ce qu’ils font. Attention, beaucoup sont non documentés (et donc peuvent avoir des effets non désirés)

La demo montre cette liste pour la 12c.

le nouveaux hints documentés pour la 12c sont:

  • GATHER_OPTIMIZER_STATISTICS/NO_GATHER_OPTIMIZER_STATISTICS pour la fonctionnalité de calcul de statistiques lors de du chargement
  • PQ_CONCURRENT_UNION/NO_PQ_CONCURRENT_UNION pour la fonctionnalité de parallelisme des UNION.
  • PQ_SKEW/NO_PQ_SKEW pour préciser la distribution des colonnes de jointure en parallel query.
  • PQ_FILTER pour définir la distribution en paralle query pour les sous-requêtes
  • USE_CUBE/NO_USE_CUBE pour le nouveau type de jointure entre cubes.

Et tous les nouveaux hints pour la 12c (attention beaucoup sont non documentés):

Hint Description
WITH_PLSQL Nécessaire lorsque une fonction pl/sql est dans une clause WITH qui n’est pas au début de la requête
(NO_)CLUSTER_BY_ROWID Hint du CBO
BITMAP_AND Bitmap tree access path
(NO_)AUTO_REOPTIMIZE Automatic reoptimization (utilise les statistiques de la première exécution pour reoptimiser le plan)
DISABLE_PARALLEL_DML,ENABLE_PARALLEL_DML Equivallent de ENABLE PARALLEL DML pour une requête
(NO_)USE_CUBE, CUBE_AJ , CUBE_SJ Nouvelle methode de jointure Cube Join
(NO_)PARTIAL_JOIN Partial Join Evaluation (transformation lorsque on n’a pas besoin de toutes les lignes retournées par la jointure car il y a un distinct à appliquer ensuite)
USE_HIDDEN_PARTITIONS Fonctionalité non documentée ‘hidden partition’
(NO_)PARTIAL_ROLLUP_PUSHDOWN Hint Parallel Query
(NO_)PQ_CONCURRENT_UNION Parallel Query Рparall̩lisme pour les UNION
PQ_DISTRIBUTE_WINDOW Hint Parallel Query
PQ_FILTER Hint Parallel Query
(NO_)PQ_SKEW Hint Parallel Query
(NO_)PX_FAULT_TOLERANCE Hint Parallel Query
(NO_)PQ_REPLICATE ‘replicate small tables‘ en Parallel Query
(NO_)GATHER_OPTIMIZER_STATISTICS Collection des statistiques lors d’un chargement direct-path sur une table vide
DATA_SECURITY_REWRITE_LIMIT XS Data Security Rewrite
NO_DATA_SECURITY_REWRITE XS Data Security Rewrite
(NO_)DECORRELATE View Decorrelation (transformation pour les jointures LATERAL ?)
(NO_)ZONEMAP materialized zonemap (Exadata) Рressemble aux Storage Indexes mais stock̩ comme vues mat̩rialis̩es
(NO_)BATCH_TABLE_ACCESS_BY_ROWID Batching de l’accès par index à une table
(NO_)CLUSTERING table clustering clause (Exadata) – contrôle l’ordre physique des lignes.

Fixer les jointures avec les hints USE_HASH, USE_NL, USE_MERGE

Publié le par
Répondre

Il n’est pas conseillé d’utiliser des hints pour forcer l’optimiseur. Il est préférable de laisser le CBO trouver le bon plan d’exécution en lui donnant de bonnes statistiques.
Mais quelquefois, cette tâche est longue et complexe alors qu’on connaît d’emblée le plan d’exécution optimal.
L’autre cas où j’utilise souvent des hints pour forcer un plan, c’est pour vérifier un plan que je pense être meilleur: en le forçant, je vois d’abord s’il est possible, et ensuite s’il est optimal. Il ne reste plus alors qu’à voir comment arriver plus proprement à ce plan…

Si l’on doit forcer la méthode de jointure, il faut par contre faire attention à définir complètement le plan, et cela nécessite plusieurs hints. Sinon, on risque de provoquer un plan ni voulu ni optimal.

La première chose, c’est de définir l’ordre dans lequel vont se faire les jointures, avec LEADING. Il y aura toutes les tables dans LEADING.
Puis, pour chacune de ces tables, sauf la première il y aura l’un des hints suivants:

  • USE_NL(alias) pour faire un Nested Loop join: pour chaque ligne du résultat précédent, on va aller voir la table précisée dans le hint (alias)
    l’accés à alias se fera autant de fois qu’il y a de ligne venant de la table précédente.
  • USE_HASH(alias) pour faire un Hash Join. Il faut alors rajouter un autre hint pour préciser quelle est la source qui va être hachée:
    • USE_HASH(alias) NO_SWAP_JOIN_INPUTS(alias)

      • Le résultat précédent va être haché, puis on va lire alias et pour chaque ligne aller voir la table de hachage

    • USE_HASH(alias) SWAP_JOIN_INPUTS(alias)

      • C’est la nouvelle table qu’on va voir (alias) qui va être hachée, et interrogée pour chaque ligne du résultat précédent

  • USE_MERGE(alias) pour faire un Sort Merge join: le résultat précédent est trié (si ce n’est pas déjà dans le bon ordre), alias est lu et trié (dans tous les cas) puis la jointure est faite en parcourant les deux. Le résultat suit la même tri.
  • USE_MERGE_CARTESIAN(alias)

    • pour faire une jointure où la plupart des lignes de alias vont satisfaire la jointure pour la plupart des lignes du résultat précédent.
    Dans ce cas, pas de tri, pas de hachage, mais simplement un buffer (qui peut aller en tempfile) pour ne pas avoir à accéder plusieurs fois à alias (comme le ferait un Nested Loop par exemple)

Lorsque je parle du résultat précédent, c’est celui qui vient des jointures des tables de gauche dans le hint LEADING.

Dans la demo, en faisant varier les cardinalités de 3 tables, on voit les risque d’avoir d’autres plans si on ne précise pas tous les alias nécessaires.

Pourquoi SWAP_JOIN_INPUTS ? pour faire passer la table hachée au dessus du plan d’exécution, car c’est la première opération fille d’un HASH JOIN dans le plan d’exécution (parce qu’il faut avoir lu cette table avant de parcourir l’autre). Avec NESTED LOOPS, c’est l’inverse: la table qui est parcourue se trouve en premier. Avec LEADING on liste l’ordre des tables du plan d’exécution. Avec SWAP_JOIN_INPUTS pour Hash Join seulement, on change l’ordre.