Changer un plan d’exécution à l’aide d’un stored Outline

Certes, la mode n’est plus aux Stored Outlines. Ceux-ci souffrent de plusieurs limites, la principale étant qu’ils s’appuient sur la hash value, à la 9i, du SQL et non pas sur le SQL ID introduit en 10g. En outre, Oracle promet d’abandonner la fonctionnalité dans une prochaine version et recommande de migrer vos outlines dès à présent. Cette dernière nouvelle est assez cohérente avec le fait qu’il n’y a pas eu d’effort pour faire évoluer les outlines depuis 9i et que les manipulations sont bien plus compliquées qu’avec SQL Plan Management (SPM).

Bref, si vous cherchez à influencer un plan d’exécution vous préférerez utiliser une baseline SQL Plan Management. Dans ce cas, reférez-vous à cet ancien article : « Changer un plan d’exécution avec Oracle 11g et DBMS_SPM ». Et pourtant, il est toujours parfois utile de savoir manipuler des Stored Outlines:

  • d’abord, il reste encore quelques bases de données en 10g et donc sans SPM
  • ensuite, et c’est plus génant, la table des fonctionnalités par Edition indique que les baselines SPM ne sont disponibles qu’en version Enterprise Edition.

Cet article présente comment modifier un plan dans un outline et ceci sans avoir à générer ce plan de manière artificielle comme suggéré dans les exemple de la documentation…

Un exemple de schéma

Pour notre exemple, il nous allons reprendre l’exemple de l’article à propos de SQL Plan Management :

create user demo identified by demo 
default tablespace users
temporary tablespace temp;

grant connect, resource, dba to demo;

connect demo/demo

drop table t1 purge;

create table t1(id number,
text varchar2(1000));

insert into T1
(select case when mod(rownum,2)=0 then 1 else rownum end id,
rpad('X',1000,'X') text
from dual connect by level < 10000);

commit;

create index t1_idx on t1(id);

exec dbms_stats.gather_table_stats(-
ownname=>user, tabname=>'T1', -
method_opt=>'for all columns size 1')

Etant donné la méthode de collecte des statistiques, le plan proposé par le CBO passe par l’index et l’estimation des cardinalités est faux malgré le bind peeking :

var id number;
set tab off
set lines 120
set pages 1000
exec :id:=1;

select count(text) "COUNT"
from T1
where id=:id;

COUNT
-----
5000


col prev_sql_id format a15 new_value sql_id
select prev_sql_id from v$session
where sid=sys_context('userenv', 'sid');

PREV_SQL_ID
-------------
39mc7r8a1bwx8


col sql format a15
col child_number format 999 new_value cno
col hash_value format 9999999999999 new_value shv
select sql_id, hash_value, child_number, substr(sql_text, 1, 15) sql
from v$sql
where sql_id='&&sql_id';

SQL_ID HASH_VALUE CHILD_NUMBER SQL
------------- -------------- ------------ ---------------
39mc7r8a1bwx8 336982952 0 select count(te


select * from table(dbms_xplan.display_cursor
('&&sql_id',&&cno,'last basic +cost +row +note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(text) "COUNT" from T1 where id=:id

Plan hash value: 1563165360

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 2 (0)|
| 3 | INDEX RANGE SCAN | T1_IDX | 2 | 1 (0)|
--------------------------------------------------------------------

Créer et utiliser un stored outline

Vous pouvez créer l’outline avec le package dbms_outln comme ci-dessous :

exec dbms_outln.create_outline(&shv,&cno)

col sql_text format a30
col name new_value outlnname
select name, sql_text from user_outlines;

NAME SQL_TEXT
------------------------------ ------------------------------
SYS_OUTLINE_11061811035509901 select count(text) "COUNT"
from T1
where id=:id

Note:
Dans les versions qui précédent la version 10.2.0.5, il est possible que vous rencontriez le bug « 6336044 – Dump using DBMS_OUTLN.CREATE_OUTLINE [ID 6336044.8] » lorsque vous utilisez la procédure de création des outlines; dans ce cas, appliquez le patch correspondant. Cela se manifeste par un crash du process serveur lors de l’utilisation de la procédure :

exec dbms_outln.create_outline(&&shv,&&cno)

BEGIN dbms_outln.create_outline( 336982952, 0); END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Vous trouverez le message suivant dans l’alert.log :

Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/BLACK/udump/black_ora_1984.trc:
ORA-07445: exception encountered: core dump [solcre()+4746] [SIGSEGV] [Address not mapped to object] [0x44] [] []

Cependant, l’outline est créé malgré tout; vous pouvez donc travailler même sans le patch…

Pour simplifier la suite des opérations, on va renommer notre outline comme ceci:

alter outline &&outlnname rename to demo_outln;

col sql_text format a40
col name format a11
select name, sql_text from user_outlines;

NAME SQL_TEXT
----------- --------------------------
DEMO_OUTLN select count(text) "COUNT"
from T1
where id=:id

Créer un nouveau plan pour la requête

L’objectif étant de changer le plan de la requête, vous devrez trouver un moyen de générer le nouveau plan et ceci, même si le code SQL et donc le SQL_ID et HASH_VALUE sont différents. Voici un exemple avec un hint :

var id number;
set tab off
set lines 120
set pages 1000
exec :id:=1;

select /*+ full(T1) */ count(text) "COUNT"
from T1
where id=:id;

COUNT
-----
5000


col prev_sql_id format a15 new_value nsql_id
select prev_sql_id from v$session where sid=sys_context('userenv', 'sid');

PREV_SQL_ID
-------------
a8nj89j7011bb


col sql format a15
col child_number format 999 new_value ncno
col hash_value format 9999999999999 new_value nshv
select sql_id, hash_value, child_number, substr(sql_text, 1, 15) sql
from v$sql
where sql_id='&&nsql_id';

SQL_ID HASH_VALUE CHILD_NUMBER SQL
------------- -------------- ------------ ---------------
a8nj89j7011bb 1308657003 0 select /*+ full


select * from table(dbms_xplan.display_cursor
('&&nsql_id',&&ncno,'last basic +cost +row +note +outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ full(T1) */ count(text) "COUNT" from T1 where id=:id

Plan hash value: 3724264953

--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 317 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T1 | 2 | 317 (1)|
--------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/

Notez que les fonctions pipeline du package dbms_xplan permettent de récupérer l’outline dans la shared pool, awr, une baseline SPM ou un explain plan. Vous créerez un Outline pour cette nouvelle requête :

exec dbms_outln.create_outline(&&nshv,&&ncno)

col sql_text format a30
col name format a30 new_value outlnname
select name, sql_text from user_outlines
order by timestamp;

NAME SQL_TEXT
------------------------------ ------------------------------
DEMO_OUTLN select count(text) "COUNT"
from T1
where id=:id

SYS_OUTLINE_11061811490357602 select /*+ full(T1) */ count(t
ext) "COUNT"
from T1
where id=:id


alter outline &&outlnname rename to tget_outln;

col sql_text format a45
col name format a10
select name, sql_text from user_outlines
order by timestamp;NAME SQL_TEXT
---------- ---------------------------------------------
DEMO_OUTLN select count(text) "COUNT"
from T1
where id=:id
TGET_OUTLN select /*+ full(T1) */ count(text) "COUNT"
from T1
where id=:id

Remplacer le contenu de DEMO_OUTLN par TGET_OUTLN

L’étape suivante consiste à remplacer le contenu de DEMO_OUTLN par TGET_OUTLN. Pour cela, il faut :

  • Copier les 2 outlines dans son espace privé
  • Changer le contenu des outlines privés (en 10g et 11g, dans les tables temporaires associées) avec des commandes UPDATE
  • Rafraichir l’outline privé avec le contenu modifié des tables OL$, OL$HINTS, OL$NAME et OL$NODES
  • Recréer l’outline DEMO_OUTLN modifié avec le nouveau plan
create private outline FROM_OUTLN from DEMO_OUTLN;
create private outline TO_OUTLN from TGET_OUTLN;

update ol$
set hintcount=(select hintcount from ol$ where ol_name='TO_OUTLN')
where ol_name='FROM_OUTLN';

delete from ol$ where ol_name='TO_OUTLN';

update ol$
set ol_name='TO_OUTLN'
where ol_name='FROM_OUTLN';

commit;

execute dbms_outln_edit.refresh_private_outline('TO_OUTLN');

create or replace outline DEMO_OUTLN from private TO_OUTLN;
drop outline TGET_OUTLN;

Vous pouvez ensuite vérifier que l’outline contient les hints du nouveau plan :

select hint 
from USER_OUTLINE_HINTS
where name='DEMO_OUTLN';

HINT
--------------------------------------------------------------------------------
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
FULL(@"SEL$1" "T1"@"SEL$1")

Tester le nouveau plan

Une fois l’outline modifié, vérifiez qu’il est bien utilisé comme dans l’exemple ci-dessous :

var id number;
set tab off
set lines 120
set pages 1000
exec :id:=1;

SQL> SELECT name, category, used FROM user_outlines;

NAME CATEGORY USED
---------- ------------------------------ ------
DEMO_OUTLN DEFAULT UNUSED

alter session set use_stored_outlines=default;

select count(text) "COUNT"
from T1
where id=:id;

COUNT
-----
5000


col prev_sql_id format a15 new_value sql_id
select prev_sql_id from v$session where sid=sys_context('userenv', 'sid');

PREV_SQL_ID
-------------
39mc7r8a1bwx8


SELECT name, category, used FROM user_outlines;

NAME CATEGORY USED
---------- ------------------------------ ------
DEMO_OUTLN DEFAULT USED


col sql format a15
col child_number format 999 new_value cno
col hash_value format 9999999999999 new_value shv
select sql_id, hash_value, child_number, substr(sql_text, 1, 15) sql
from v$sql
where sql_id='&&sql_id'
order by child_number;

SQL_ID HASH_VALUE CHILD_NUMBER SQL
------------- -------------- ------------ ---------------
39mc7r8a1bwx8 336982952 0 select count(te
39mc7r8a1bwx8 336982952 1 select count(te


select * from table(dbms_xplan.display_cursor
('&&sql_id',&&cno,'last basic +cost +row +note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(text) "COUNT" from T1 where id=:id

Plan hash value: 3724264953

--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 317 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T1 | 2 | 317 (1)|
--------------------------------------------------------

Note
-----
- outline "DEMO_OUTLN" used for this statement

Conclusion

Evidemment, il s’agit d’un exemple et en aucun cas de discuter de la pertinence de créer un outline pour cette requête ! En l’occurrence, c’est probablement une mauvaise idée puisque la même requête manipule parfois 1 ou parfois 5000 lignes. C’est typiquement un cas où l’adaptive cursor sharing et la présence d’un histogramme sera utile en 11g ; SPM est compatible avec ces 2 fonctionnalités alors que l’outline pas du tout.

Pour terminer, supprimez le schéma et l’outline ; vous remarquerez un autre de ces comportements un peu bizarre ; le schéma est supprimé mais pas l’outline associé (dont le propriétaire est pourtant supprimé) :

connect / as sysdba
drop user demo cascade;
select name, owner from dba_outlines;

NAME OWNER
---------- ------------------------------
DEMO_OUTLN DEMO


drop outline DEMO_OUTLN;

Références:
Pour plus d’informations sur les outlines, regardez les documents suivants :

Gregory Guillou

About Gregory Guillou

Gregory Guillou has written 758 post in this blog.

Senior Technical Architect at Easyteam

8 thoughts on “Changer un plan d’exécution à l’aide d’un stored Outline

  1. TGA

    Bonjour,

    Une info trouvée sur le blog de Kerry Osborne : le support Oracle utilise un import manuel pour générer un SQL Profile(script coe_xfr_sql_profile.sql). Je pense que la notion de support/desupport devient obsolète..
    Thierry.

  2. TGA

    Merci Gregory;
    Si j’ai bien compris pour toi les impacts mesurés par Oracle sur un PROFILE sont des statistiques d’exécutions.
    Les profils sont là pour palier à des des limites de l’optimiseur (voire statistiques), dommage qu’il faille une EE Edition avec DIAG+Tuning Pack.
    Je vais me contenter d’une OULTLINE avec opt_estimate même sinon non supporté .. Merci.

  3. ArKZoYD

    Thierry,

    Il suffit d’afficher le plan avec dbms_xplan.display_cursor et de mettre ‘+outline’ dans le paramètre « format » pour récupérer l’outline. On peut alors créer simplement outline ou baseline SPM avec le hint généré.

    On fixe ainsi le plan obtenu par le tuning Advisor au lieu d’utiliser le SQL Profile. En aucun cas un outline ni un SPM de doivent utiliser le même mécanisme que le SQL Profile. Utiliser les hints opt_estimate en direct n’est pas supporté… D’ailleurs, ils ne sont pas documentés.

    Pourquoi un plan d’exécution est 100x plus rapide qu’un autre ? Pourquoi Oracle n’est pas toujours capable de trouver le meilleur plan sans un SQL profile, un histogramme, une statistique multicolonne ou fonction ? Pourquoi en serait-il autrement ?

    Gregory

  4. TGA

    Bonjour,
    Tu réponds toujours sur des problèmes que je rencontre :). C’est vrai qu’en 10g, on peut utiliser les OUTLINE en Standard Edition (merci à Laurent B qui m’a retrouvé l’information dans le guide de licences Oracle).
    As-tu trouvé un moyen facile d’implémenter un OUTLINE via un plan trouvé par un PROFILE ?.
    De plus je suis un peu perplexe sur les résultats de Profile (ex 99,9% d’amélioration), comment fait-il ? Je pense que tu ferai des heureux…
    Merci.
    Thierry.

  5. ArKZoYD

    Ahmed,

    Moi aussi je l’ai vu ;-) :
    http://blog.arkzoyd.com/2010/04/positionnez-un-hint-grace-au-sql.html

    Cette méthode
    - n’est pas supportée par Oracle ; la procédure n’est pas documentée dans Oracle® Database PL/SQL Packages and Types Reference – DBMS_SQLTUNE
    - nécessite d’être en 10g ou 11g EE avec le Tuning Pack.

    En outre, il s’agit clairement d’un détournement d’usage puisque les SQL profiles sont censés intégrer des hints OPT_ESTIMATE et pas des plans fixes ou des hints rock’n roll.

    Cela dit, je suis d’accord, c’est une tuerie ! Ca permet d’injecter certains hints comme monitor, dynamic_sampling; opt_param ou gather_plan_statistics sur une requête ! Ca marche même avec des valeurs litérales ce qui n’est pas le cas avec SPM qui quand à lui nécessite de modifier la valeur de cursor_sharing.

    Je reste donc sur mon idée que du besoin et du contexte dependent l’outil.

    Greg

  6. Ahmed AANGOUR

    On peut toutefois utiliser les SQL profiles pour forcer un plan d’exécution à la manière des OUTLINES en utilisant la procédure DBMS_SQLTUNE.import_sql_profile.

    J’ai découvert la 1ère fois cette astuce sur le blog de randolf GEIST et je l’ai repris sur mon blog il y’a quelques mois:
    http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html

    http://ahmedaangour.blogspot.com/2011/01/forcer-un-plan-dexecution-via-un-sql.html

    Donc pour moi:
    Si vous êtes en 11g => mieux vaut utiliser SPM
    Si vous êtes en 10g => mieux vaut utiliser DBMS_SQLTUNE.import_sql_profile
    Si vous êtes en 8i ou 9i => pas d’autres choix que les Outlines

  7. ArKZoYD

    Ce n’est pas du tout la même bête :
    - Le « stored outline » fixe le plan de manière directive.
    - Le « SQL Profile » modifie les estimations des cardinalités qui sont faites pour le SQL par le CBO (cf SQL #4 et SQL profile /*+Anti-Pattern*/). Cela abouti généralement à un changement de plan mais le CBO continue à faire son travail.

    En général, on essaiera d’éviter les stored outlines ! On préfèrera les baselines SPM (11.2 EE). La raison est que la baseline permet de fixer, non pas un mais plusieurs plans, pour le même ordre et que le CBO continue à fonctionner. En outre Oracle a annoncé qu’il allait abandonner les stored outlines et l’outil est loin d’être aisé à manipuler.

    Alors « baseline SPM » ou « SQL Profile » ? (Il faudrait ajouter « SQL Patch »). Evidemment ça dépend de ce qu’on veut faire ! « Le SQL profile » comme les statistiques multicolonnes ou le dynamic sampling aide le CBO. La baseline le bride. J’imagine que ça dépend si on est avide de liberté ;-) ou ce qu’on cherche à faire…