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 :

• Oracle® Database PL/SQL Packages and Types Reference – DBMS_OUTLN
• Oracle® Database Performance Tuning Guide – Using Plan Stability
• Kerry Osborne’s Oracle Outlines – aka Plan Stability
• Editing Stored Outlines in Oracle10g and Oracle11g [ID 726802.1]
• How to Edit a Stored Outline to Use the Plan from Another Stored Outline [ID 730062.1]