LLI Wiki — My Digital Knowledge Base

Requête Oracle parfois lente : plans d'exécution et cardinality feedback

Diagnostiquer une requête Oracle tantôt rapide tantôt lente : trace 10046, TKPROF, et paramètres cachés _optimizer_use_feedback.

Une requête tantôt rapide, tantôt lente est un symptôme classique sous Oracle. Cet article liste les causes habituelles, montre comment capturer le plan d'exécution d'une exécution rapide et d'une exécution lente pour les comparer, et décrit le cardinality feedback (la famille de paramètres cachés _optimizer_use_feedback), qui peut faire changer de plan une requête après sa première exécution.

Causes courantes de lenteur intermittente

Cause Que vérifier
Contention de ressources (mémoire, CPU, E/S disque, verrous) AWR, ASH et les vues dynamiques de performance V$
Effets de cache Des données servies par le buffer cache ou le result cache sont rapides ; des données lues sur disque sont lentes (V$BUFFER_CACHE, V$SQL_WORKAREA)
Charge du système La requête ralentit-elle seulement quand la charge globale est élevée ?
Plan d'exécution changeant Les plans peuvent varier selon les statistiques, les paramètres et les valeurs des variables de liaison ; comparez-les avec DBMS_XPLAN ou SQL Tuning Advisor
Variables de liaison et Adaptive Cursor Sharing Un plan adapté à une valeur peut être mauvais pour une autre
Statistiques obsolètes Les actualiser avec DBMS_STATS

Capturer et comparer les plans des exécutions rapide et lente

Activez une trace SQL étendue (événement 10046, niveau 8 : variables de liaison et événements d'attente) dans la session qui exécute la requête rapide, avec un identifiant de fichier de trace :

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'fast_query';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Exécutez la requête, puis désactivez la trace :

ALTER SESSION SET EVENTS '10046 trace name context off';

Faites de même pour l'exécution lente avec un autre identifiant :

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'slow_query';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
ALTER SESSION SET EVENTS '10046 trace name context off';

Trouvez le répertoire des traces :

SHOW PARAMETER dump_dest;

Repérez les fichiers contenant vos identifiants (fast_query, slow_query), puis formatez-les avec TKPROF :

tkprof input_trace_file output_text_file explain=USER/PASSWORD@CONNECT_IDENTIFIER

Dans chaque fichier de sortie, le plan se trouve dans la section Rows Row Source Operation. Comparez les deux plans pour repérer la différence.

Cardinality feedback

Le cardinality feedback (11g) permet à l'optimiseur d'apprendre de ses erreurs d'estimation : après la première exécution, une requête peut être marquée comme candidate à une seconde optimisation, et un plan différent peut être choisi à l'exécution suivante. Cela peut expliquer une requête rapide à la première exécution puis plus lente ensuite (voir Ask TOM et le blog de l'optimiseur Oracle).

Caractéristiques du cardinality feedback :

Pour tester l'hypothèse sur une requête, désactivez la fonctionnalité pour cette requête avec un hint :

select /*+ opt_param('_optimizer_use_feedback' 'false') */ ...

Une autre approche mentionnée dans la discussion d'origine consiste à créer un stored outline à partir de l'exécution rapide.

Paramètres cachés associés

Ce sont des paramètres cachés (préfixés par un tiret bas). Ne les modifiez que pour un diagnostic, de préférence au niveau de la session et idéalement avec l'aide du support Oracle.

Paramètre Rôle Défaut
_optimizer_use_feedback L'optimiseur utilise-t-il le cardinality feedback TRUE
_optimizer_gather_feedback L'optimiseur demande-t-il au moteur d'exécution de détecter les erreurs d'estimation et de créer du feedback (indépendant de OPTIMIZER_ADAPTIVE_STATISTICS) TRUE
_optimizer_use_feedback_for_join L'optimiseur utilise-t-il le feedback de cardinalité des jointures, non couvert par _optimizer_use_feedback FALSE

Consulter la valeur courante d'un paramètre caché (nécessite l'accès aux tables X$, donc SYS) :

col Name for a31
col Value for a10
select ksppinm "Name", ksppstvl "Value"
from x$ksppi a, x$ksppsv b
where a.indx = b.indx and ksppinm = '_optimizer_use_feedback';
Name                            Value
------------------------------- ----------
_optimizer_use_feedback         TRUE

Activer ou désactiver au niveau de l'instance :

alter system set "_optimizer_use_feedback" = true scope=both;
alter system set "_optimizer_use_feedback" = false scope=both;

Ou tester au niveau de la session :

alter session set "_optimizer_use_feedback" = true;
alter session set "_optimizer_use_feedback" = false;

La même syntaxe s'applique aux deux autres paramètres :

alter system set "_optimizer_gather_feedback" = TRUE scope=both;
alter system set "_optimizer_gather_feedback" = FALSE scope=both;
alter system set "_optimizer_use_feedback_for_join" = TRUE scope=both;
alter system set "_optimizer_use_feedback_for_join" = FALSE scope=both;

Remarques

Mastodon LLIMastodon SocialFosstodon