Plans d'exécution stockés

Il arrive parfois que certaines requêtes (bien que récurrentes) soient lentes. Or lors de tests, on s'aperçoit que :
- La requête est beaucoup plus rapide lors d'une seconde exécution (et après un vidage du cache des données uniquement mais pas après un vidage de cache de la shared pool)
- Le plan d'exécution met un certain temps à sortir
- Le plan d'exécution n'est pas stable (il diffère entre deux demandes de plans d'exécution)

Ces symptômes indiquent généralement que le temps consommé provient en grande partie à l'établissement du plan et à la recherche du plus optimisé (en mode CBO).

Si un certain nombre de requêtes est concerné et qu'elles ne posent problème que de temps à autre (lorsque leur plan n'est plus en cache), il est possible de monter la valeur du paramètre session_cached_cursors en adaptant éventuellement la taille de la SGA en conséquence.
En effet, ce paramètre permet d'indiquer le nombre de plans à garder dans la library cache.
Si, par contre ce problème n'apparaît que sur des requêtes très ciblées ou que le nombre de requête du système n'est pas maîtrisé, il existe alors la solution de stocker au sein du dictionnaire de données des plans d'exécution pour des requêtes données : les 'stored outlines'.


Vues du dictionnaire concernées :
- [all | dba | user]_outlines
- [all | dba | user]_outline_hints

Définition de la catégorie utilisée pour les stored outlines


- au niveau system :
ALTER SYSTEM SET USE_STORED_OUTLINES = ma_cat;
- au niveau session :
ALTER SESSION SET USE_STORED_OUTLINES = ma_cat;

Création d'un outline


- création simple (pour une requête) :
CREATE OR REPLACE [ PRIVATE ] OUTLINE mon_plan
FOR CATEGORY ma_cat ON [ mon_select ]

- collecte des plans pendant un laps de temps (plus gourmand, pour toutes les requêtes qui passent)
ALTER SYSTEM SET create_stored_outlines = ma_cat;
ou
ALTER SESSION SET create_stored_outlines = ma_cat;

Modification d’un outline


L’instruction alter sur outline permet de :
- renommer un plan :
ALTER OUTLINE mon_outline RENAME TO mon_outline_optimise;
- changer un plan de catégorie
ALTER OUTLINE mon_outline CHANGE CATEGORY TO ma_cat_prod;
- reconstruire (à l’image des index)
ALTER OUTLINE mon_outline REBUILD;


Modification de plans d’exécution stockés sous forme d’outline


Si, pour une raison ou une autre, l’optimiseur Oracle ne répond pas au exigence et construit un plan d’exécution inadapté à la requête que l’on exécute, il est possible, à partir de hints de modifier ce plan. Cependant, l’utilisation de hint impacte généralement l’applicatif dans lequel est appelé cette requête. Les outlines permettent de remédier à ce problème en stockant le plan d’exécution voulu pour une requête donnée.
Pour cela, il faut procéder à la création des deux outlines correspondant pour l’un à la requête originale et pour l’autre à la requête optimisée puis d’interchanger les plans d’exécution correspondant avant de supprimer l’outline qui ne sera pas utiliser.

Script de test :

prompt essai de permutation de plans
ALTER SESSION SET USE_STORED_OUTLINES = ma_cat;

CREATE OR REPLACE OUTLINE mon_plan
FOR CATEGORY ma_cat ON
select count(*) from (select * from tab1, tab2 where tab1.id= tab2.id);

CREATE OR REPLACE OUTLINE mon_plan2
FOR CATEGORY ma_cat ON
select count(*) from (select /*+ ordered */ * from tab1, tab2 where tab1.id= tab2.id);

select name, category, used from all_outlines;

UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'MON_PLAN2','MON_PLAN','MON_PLAN','MON_PLAN2')
WHERE OL_NAME IN ('MON_PLAN','MON_PLAN2');
commit;

drop OUTLINE mon_plan2;

select count(*) from (select * from tab1, tab2 where tab1.id= tab2.id);

select name, category, used from all_outlines;

prompt fin de l essai



Sortie du script :


essai de permutation de plans
ALTER SESSION SET réussi.
CREATE OR REPLACE OUTLINE réussi.
CREATE OR REPLACE OUTLINE réussi.
NAME CATEGORY USED
------------------------------ ------------------------------ ------
MON_PLAN MA_CAT UNUSED
MON_PLAN2 MA_CAT UNUSED

2 rows selected

24 lignes mis à jour
commit réussi.
drop OUTLINE réussi.

COUNT(*)
----------------------
352261

1 rows selected

NAME CATEGORY USED
------------------------------ ------------------------------ ------
MON_PLAN MA_CAT USED

1 rows selected

fin de l essai

Il existe cependant des outils un peu plus sympathiques, voir DBMS_OUTLN_EDIT.CHANGE_JOIN_POS.

Import / Export ou création des outlines dans un environnement de test


Les stored outlines peuvent également être construits dans un environnement (de préproduction) pour être ensuite exportés vers l’environnement de production.
Il faut alors exporter et importer les tables OL$, OL$NODES et OL$HINTS du schéma OUTLN :

export ORACLE_SID=PPROD
exp system/manager owner=outln file=mes_outline.dmp

export ORACLE_SID=PROD
imp outln/outln full=Y file= mes_outline.dmp


Voir aussi :
- Comparer l'exécution d'une requête avec 2 index différents
-
Les bind variables pour les requêtes courantes
-
CBO : les plans d'exécution selon la volumétrie cible
-
Les statistiques Oracle
- Hint oracle et choix du plan d'exécution




Vous n'avez pas trouver réponse à votre question ? Préciser votre recherche :

Catégories