vendredi 30 décembre 2011

Resetter une sequence

Une des applications sur laquelle je travaille a planté en production à cause d'une séquence qui avait atteint une valeur trop importante pour la variable Delphi qui la stockait.
La solution consistait à resetter cette sequence.
Ce qu'il faut savoir c'est qu'avec Oracle il n'existe pas d'option RESET associée à la commande ALTER SEQUENCE. Pour resetter une sequence il faut la dropper et la recréer.
Toutefois, sur le site de Tom Kyte j'ai pu trouver une astuce pour resetter une sequence sans avoir à la dropper.
Cette solution consiste tout bêtement à modifier la valeur d'incrémentation pour décrementer la séquence de la valeur du NEXTVAL.

Voici ci-dessous les caractéristiques de ma séquence avant le reset:

SQL> select sequence_name, min_value,max_value, increment_by, cache_size,last_number
  2  from user_sequences where sequence_name='SEQ_MUTEX';

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ ---------- -----------
SEQ_MUTEX                1 2147622080            1        500  2113785229

Il s'agit d'une sequence qui s'incrémente par une unité avec un cache de 500.

Je récupère ensuite la valeur du NEXTVAL:

SQL> select seq_mutex.nextval from dual;

   NEXTVAL
----------
2113785134

L'idée est de modifier la valeur de l'incrementation de la valeur du NEXTVAL moins 1 car le minvalue de ma sequence est à 1:

SQL> alter sequence seq_mutex increment by -2113785133;
alter sequence seq_mutex increment by -2113785133
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle

SQL> alter sequence seq_mutex increment by -2113785133 NOCYCLE;

Sequence altered.

SQL> select seq_mutex.nextval from dual;

   NEXTVAL
----------
         1

On voit que ma séquence a été resetté à 1.
Vous noterez que j'ai dû mettre temporairement ma séquence en mode NOCYCLE pour éviter l'erreur ORA-04013. Une autre solution aurait consisté à laisser la séquence en mode CYCLE mais à réduire la taille du CACHE.

Ensuite je peux redéfinir ma séquence comme elle était auparavant c'est à dire en mode CYCLE et avec un INCREMENT à 1:

SQL> alter sequence seq_mutex cycle increment by 1 minvalue 1 cache 500;

Sequence altered.

SQL> select sequence_name, min_value,max_value, increment_by, cache_size,last_number
  2  from user_sequences where sequence_name='SEQ_MUTEX';

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ ---------- -----------
SEQ_MUTEX                1 2147622080            1        500           2

Voilà comment j'ai pu resetter ma séquence sans la dropper.
Bien sûr il faut que pendant cette opération il n'y ait aucun autre session qui appelle cette séquence sinon on risque d'obtenir l'erreur suivante:

SQL> select seq_mutex.nextval from dual;
select seq_mutex.nextval from dual
       *
ERROR at line 1:
ORA-08004: sequence SEQ_MUTEX.NEXTVAL goes below MINVALUE and cannot be instantiated

jeudi 24 novembre 2011

"View merging" et "Predicate pushing"

Le principe du “view merging” correspond à la capacité pour l’optimiseur de pouvoir transformer une requête SQL qui appelle une vue afin de fusionner cette vue au sein de la requête. Lorsqu’une vue est mergée, le code de la vue n’est plus exécutée à part mais fait partie intégrante de la requête principale.

Le fait de merger une vue peut être choisi par l’optimiseur afin par exemple d’appliquer à la vue une clause WHERE se trouvant à l’extérieur de la vue et ainsi limiter le nombre de lignes retournées par une table de la vue.

Exemple :
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;

72706 rows created.

SQL> insert into t1 select * from t1;

145412 rows created.

SQL> create index idx_obj_id on t1(object_id);

Index created.

SQL> create index idx_obj_type on T1(object_type);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> create view v1 as select * from t1;

View created.

J’ai une table qui contient environ 290 000 lignes, un index B-TREE sur la colonne OBJECT_TYPE, un index B-TREE sur la colonne OBJECT_ID et une vue V1 qui sélectionne les lignes de la table T1.

La requête suivante effectue une jointure entre T1 et V1 en filtrant les objets de type EDITION :
select t1.object_name 
  from V1, T1 
 where t1.object_type = 'EDITION' 
   and t1.object_id = v1.object_id; 

Voyons ce que donne le plan lorsque la vue n’est pas mergée :
---------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
---------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |     16 |00:00:00.50 |    4147 |   4136 | 
|*  1 |  HASH JOIN                   |              |      1 |    106 |     16 |00:00:00.50 |    4147 |   4136 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.01 |       7 |      0 | 
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       3 |      0 | 
|   4 |   VIEW                       | V1           |      1 |    290K|    290K|00:00:00.31 |    4140 |   4136 | 
|   5 |    TABLE ACCESS FULL         | T1           |      1 |    290K|    290K|00:00:00.18 |    4140 |   4136 | 
---------------------------------------------------------------------------------------------------------------- 
 Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - access("T1"."OBJECT_ID"="V1"."OBJECT_ID") 
   3 - access("T1"."OBJECT_TYPE"='EDITION') 

L’opération 4 « VIEW » indique que le SELECT de la vue est exécuté à part et puisque dans le code de la vue il n’y a pas de clause WHERE, un Full Scan de la table T1 est effectué car toutes les lignes doivent être ramenées.

En revanche, la table T1 de la requête principale (outer query) est bien accédée via l’index IDX_OBJ_TYPE car le prédicat sur la colonne OBEJCT_TYPE est bien pris en compte.

Voyons maintenant le plan lorsque la vue est mergée :
---------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
---------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |     16 |00:00:00.04 |      21 |     10 | 
|   1 |  NESTED LOOPS                |              |      1 |    106 |     16 |00:00:00.04 |      21 |     10 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.02 |       9 |      7 | 
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       5 |      3 | 
|*  4 |   INDEX RANGE SCAN           | IDX_OBJ_ID   |      4 |      4 |     16 |00:00:00.01 |      12 |      3 | 
---------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   3 - access("T1"."OBJECT_TYPE"='EDITION') 
   4 - access("T1"."OBJECT_ID"="OBJECT_ID") 

Il n’y a plus d’opération VIEW dans le plan, c’est donc que la vue a été mergée.

La colonne Buffers du plan montre que le nombre de logical reads effectués est de 21. Avec la vue non mergée le coût était de 4147 logical reads.

Ce gain s’explique par le fait que le CBO a transformé la requête pour intégrer le code de la vue dans la requête principale. La requête devient en fait la suivante :
select t1.object_name 
from T1, T1 v1 
where t1.object_type = 'EDITION' 
and t1.object_id = v1.object_id; 

Cette transformation permet un accès indéxé pour les 2 tables beaucoup plus efficace que lorsque la vue doit être exécutée à part.

Pour obtenir le plan avec la vue non mergée dans mon exemple précédent, j’ai ajouté le hint NO_MERGE dans le code de la vue :
create or replace view v1 as select /*+ no_merge */ * from t1; 

Ce hint indique à l’optimiseur que la vue ne doit pas être mergée pendant la phase de transformation de la requête. Si vous êtes dans un cas où le CBO a décidé de ne pas merger la vue, vous pouvez forcer le merge en utilisant le hint MERGE.

Toutefois, il existe des cas où le merge peut être considéré comme non applicable par le CBO. C’est le cas parfois lorsque la vue contient par exemple:
  • une fonction analytique (ex : LEAD, LAG, RANK, ROW_NUMBER etc.)
  • une fonction d’agrégation (ex : SUM, AVG, MAX, MIN etc.)
  • la colonne ROWNUM
  • la clause ORDER BY

En effet, le fait de merger la vue dans ces cas là peut avoir une influence sur le résultat de la requête. Si le fait de merger peut influencer le résultat alors le CBO s’interdit de merger pendant la phase de transformation de la requête.

Exemple :
Je rajoute la colonne ROWNUM à ma vue V1 :
create or replace view v1 as select  t1.*,rownum RN from t1; 

J’exécute une requête simple avec un prédicat sur la colonne OBJECT_TYPE :
select * from v1 where object_type='EDITION'; 

  ----------------------------------------------------------------------------------------------- 
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
----------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT    |      |      1 |        |      4 |00:00:00.37 |    4139 |   4135 | 
|*  1 |  VIEW               | V1   |      1 |    290K|      4 |00:00:00.37 |    4139 |   4135 | 
|   2 |   COUNT             |      |      1 |        |    290K|00:00:00.27 |    4139 |   4135 | 
|   3 |    TABLE ACCESS FULL| T1   |      1 |    290K|    290K|00:00:00.14 |    4139 |   4135 | 
----------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - filter("OBJECT_TYPE"='EDITION') 

On s’aperçoit que la vue n’est pas mergée. Oracle doit attaquer la table T1 en Full Table Scan pour ensuite filtrer les lignes sur la colonne OBJECT_TYPE. Ce plan s’explique par la présence de la colonne ROWNUM qui serait différent si on appliquait très tôt la clause WHERE sur la colonne OBJECT_TYPE.

Il existe aussi des cas où même quand la vue ne peut être mergée le CBO peut tout de même se permettre d’appliquer les prédicats ,qui se trouvent au niveau de la requête principale, aux tables de la vue. Le but est de pouvoir filtrer les données au plus tôt au niveau du plan et/ou de pouvoir utiliser un index plus efficace.

Exemple :

La requête suivante donne un plan avec une vue complètement mergée et un accès indexé au niveau de la table T1 :
select * from ( select  t1.* from t1) v1 where object_type='EDITION'; 

------------------------------------------------------------------------------------------------------ 
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT            |              |      1 |        |      4 |00:00:00.01 |       8 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.01 |       8 | 
|*  2 |   INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       4 | 
------------------------------------------------------------------------------------------------------ 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
2 - access("T1"."OBJECT_TYPE"='EDITION') 

Voyons ce qui se passe si j’empêche le merge de la vue grâce au hint NO_MERGE :
select * from ( select /*+ no_merge */  t1.* from t1) v1 
where object_type='EDITION'; 
  
------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |      4 |00:00:00.01 |       8 | 
|   1 |  VIEW                        |              |      1 |     27 |      4 |00:00:00.01 |       8 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.01 |       8 | 
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       4 | 
------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   3 - access("T1"."OBJECT_TYPE"='EDITION') 

L’opération VIEW à la ligne 1 indique que la vue n’est pas mergée. Par contre on s’aperçoit qu’au lieu d’avoir un Full Table Scan on a un accès indexé. La clause WHERE sur la colonne OBJECT_TYPE a été pushée à l’intérieur de la vue afin de bénéficier de l’utilisation de l’index. Cette capacité à pusher les prédicats se nomme « Predicate Pushing ».

Normalement, le hint NO_PUSH_PRED devrait empêcher l’optimiseur de faire un « push down » des predicats, mais je ne sais pas pourquoi il ne fonctionne pas dans mon cas :
select /*+ NO_PUSH_PRED(v1) */ * from ( select /*+ no_merge */  t1.* 
from t1) v1 where object_type='EDITION'; 
------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |      4 |00:00:00.01 |       8 | 
|   1 |  VIEW                        |              |      1 |     27 |      4 |00:00:00.01 |       8 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.01 |       8 | 
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       4 | 
------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   3 - access("T1"."OBJECT_TYPE"='EDITION') 

Du coup pour avoir une idée du plan sans le predicate pushing, j’ai ajouté la colonne ROWNUM dans la vue :
select * from ( select /*+ no_merge */  t1.* from t1 WHERE rownum > 1) v1  
where object_type='EDITION'; 
----------------------------------------------------------------------------- 
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT     |      |   290K|    57M|  1127   (1)| 00:00:14 | 
|*  1 |  VIEW                |      |   290K|    57M|  1127   (1)| 00:00:14 | 
|   2 |   COUNT              |      |       |       |            |          | 
|*  3 |    FILTER            |      |       |       |            |          | 
|   4 |     TABLE ACCESS FULL| T1   |   290K|    26M|  1127   (1)| 00:00:14 | 
----------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - filter("OBJECT_TYPE"='EDITION') 
   3 - filter(ROWNUM>1) 

On voit bien que quand le prédicat sur la colonne OBJECT_TYPE n’est pas pushé dans la vue, l’index de T1 ne peut être utilisé et on obtient donc un TABLE ACCESS FULL.

J’ai pensé à écrire cet article suite à un problème de perf rencontrée sur une requête faisant référence à une vue contenant notamment une fonction analytique.

La requête était la suivante :
SELECT task_instance_id, task_class_id, status, class_rank, instance_number 
  FROM (select 
         ti.task_instance_id as task_instance_id, 
         tc.task_class_id as task_class_id, 
         ti.status, 
         Rank() over(PARTITION BY tc.task_class_id ORDER BY ti.task_instance_id) AS class_rank, 
tc.instance_number, 
         tc.poll_max_grab 
          from mdw_task_instance      ti, 
               mdw_task_type          tt, 
               mdw_task_class         tc, 
               mdw_batch_engine_group eg, 
               mdw_batch_engine       be 
         where ti.status = 0 
           and ti.app_code = tt.app_code 
           and ti.task_label = tt.task_label 
           and tt.task_class_id = tc.task_class_id 
           and tc.batch_group_id = eg.batch_group_id 
and eg.batch_engine_id = be.batch_engine_id 
           and be.label = 'RLX000') poll_req 
 WHERE (instance_number <= 0 OR 
       (class_rank <= poll_req.poll_max_grab AND 
       0 <= instance_number - class_rank - 
       (select count(*) 
                 from mdw_task_instance ti, mdw_task_type tt 
                where ti.status in (1) 
                  and ti.app_code = tt.app_code 
                  and ti.task_label = tt.task_label 
                  and tt.task_class_id = poll_req.task_class_id))) 
 order by task_instance_id; 

----------------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                           | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes | 
----------------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                    |                             |      1 |        |      0 |00:00:02.28 |    5102 |   6781 |   6778 | 
|   1 |  SORT ORDER BY                      |                             |      1 |  64486 |      0 |00:00:02.28 |    5102 |   6781 |   6778 | 
|*  2 |   FILTER                            |                             |      1 |        |      0 |00:00:02.28 |    5102 |   6781 |   6778 | 
|   3 |    VIEW                             |                             |      1 |  64486 |    311K|00:00:02.23 |    5102 |   6781 |   6778 | 
|   4 |     WINDOW SORT                     |                             |      1 |  64486 |    311K|00:00:02.15 |    5102 |   6781 |   6778 | 
|   5 |      NESTED LOOPS                   |                             |      1 |  64486 |    311K|00:00:00.29 |    5100 |      3 |      0 | 
|   6 |       NESTED LOOPS                  |                             |      1 |     29 |     93 |00:00:00.07 |       8 |      2 |      0 | 
|*  7 |        HASH JOIN                    |                             |      1 |    175 |    108 |00:00:00.07 |       5 |      2 |      0 | 
|   8 |         MERGE JOIN                  |                             |      1 |     97 |     98 |00:00:00.07 |       4 |      2 |      0 | 
|   9 |          TABLE ACCESS BY INDEX ROWID| MDW_TASK_CLASS              |      1 |     94 |     94 |00:00:00.05 |       3 |      1 |      0 | 
|  10 |           INDEX FULL SCAN           | MDW_TASK_CLASS_PK           |      1 |     94 |     94 |00:00:00.05 |       1 |      1 |      0 | 
|* 11 |          SORT JOIN                  |                             |     94 |     97 |     98 |00:00:00.02 |       1 |      1 |      0 | 
|* 12 |           INDEX FULL SCAN           | MDW_TASK_TYPE_IDX1          |      1 |     97 |     98 |00:00:00.02 |       1 |      1 |      0 | 
|  13 |         INDEX FULL SCAN             | MDW_BATCH_ENGINE_GROUP_IDX1 |      1 |     18 |     18 |00:00:00.01 |       1 |      0 |      0 | 
|* 14 |        INDEX RANGE SCAN             | MDW_BATCH_ENGINE_IDX1       |    108 |      1 |     93 |00:00:00.01 |       3 |      0 |      0 | 
|* 15 |       INDEX RANGE SCAN              | MDW_TASK_INSTANCE_IDX4      |     93 |   2216 |    311K|00:00:00.16 |    5092 |      1 |      0 | 
|  16 |    SORT AGGREGATE                   |                             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 | 
|  17 |     NESTED LOOPS                    |                             |      0 |     40 |      0 |00:00:00.01 |       0 |      0 |      0 | 
|* 18 |      INDEX RANGE SCAN               | MDW_TASK_TYPE_IDX2          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 | 
|* 19 |      INDEX RANGE SCAN               | MDW_TASK_INSTANCE_IDX4      |      0 |     36 |      0 |00:00:00.01 |       0 |      0 |      0 | 
----------------------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - filter(("INSTANCE_NUMBER"<=0 OR ("CLASS_RANK"<="POLL_REQ"."POLL_MAX_GRAB" AND "INSTANCE_NUMBER"-"CLASS_RANK"->=0))) 
   7 - access("TC"."BATCH_GROUP_ID"="EG"."BATCH_GROUP_ID") 
  11 - access("TT"."TASK_CLASS_ID"="TC"."TASK_CLASS_ID") 
       filter("TT"."TASK_CLASS_ID"="TC"."TASK_CLASS_ID") 
  12 - filter("TT"."TASK_CLASS_ID" IS NOT NULL) 
  14 - access("BE"."LABEL"='RLX000' AND "EG"."BATCH_ENGINE_ID"="BE"."BATCH_ENGINE_ID") 
  15 - access("TI"."STATUS"=0 AND "TI"."APP_CODE"="TT"."APP_CODE" AND "TI"."TASK_LABEL"="TT"."TASK_LABEL") 
  18 - access("TT"."TASK_CLASS_ID"=:B1) 
  19 - access("TI"."STATUS"=1 AND "TI"."APP_CODE"="TT"."APP_CODE" AND "TI"."TASK_LABEL"="TT"."TASK_LABEL") 

Le plan génère 5102 logical reads pour retourner 0 ligne. C’est beaucoup trop.

Le problème vient du fait que la clause la plus filtrante est la suivante : « instance_number <= 0 »

Cette clause se situe en dehors de la vue POLL_REQ mais s’applique à une colonne retournée par la vue. Comme la vue contient une fonction analytique le CBO ne peut ni merger la vue ni effectuer un push down des prédicats. Le filtrage des lignes ne s’effectue alors qu’après exécution de la vue. L’opération 2 du plan nommé VIEW indique que la vue n’a pas été mergée et la section « Predicate Information » du plan pour l’opération 2 indique que le filtrage des lignes sur notamment la colonne INSTANCE_NUMBER n’est effectué qu’après que la vue ait été exécutée.

En ajoutant le filtre sur la colonne INSTANCE_NUMBER à l’intérieur de la vue, on obtient un plan différent et beaucoup moins couteux :
SELECT  task_instance_id, task_class_id, status, class_rank, instance_number 
  FROM (select 
         ti.task_instance_id as task_instance_id, 
         tc.task_class_id as task_class_id, 
         ti.status, 
         Rank() over(PARTITION BY tc.task_class_id ORDER BY ti.task_instance_id) AS class_rank, 
         tc.instance_number, 
         tc.poll_max_grab 
          from mdw_task_instance      ti, 
               mdw_task_type          tt, 
               mdw_task_class         tc, 
               mdw_batch_engine_group eg, 
               mdw_batch_engine       be 
         where ti.status = 0 
           and ti.app_code = tt.app_code 
           and ti.task_label = tt.task_label 
           and tt.task_class_id = tc.task_class_id 
           and tc.batch_group_id = eg.batch_group_id 
           and eg.batch_engine_id = be.batch_engine_id 
           and be.label = 'RLX000' 
                                   and tc.instance_number <= 0 -- ajout 
                                   ) poll_req 
 WHERE (instance_number <= 0 OR 
       (class_rank <= poll_req.poll_max_grab AND 
       0 <= instance_number - class_rank - 
       (select count(*) 
                 from mdw_task_instance ti, mdw_task_type tt 
                where ti.status in (1) 
                  and ti.app_code = tt.app_code 
                  and ti.task_label = tt.task_label 
                  and tt.task_class_id = poll_req.task_class_id))) 
 order by task_instance_id; 
-------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                           | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
-------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                    |                             |      1 |        |      0 |00:00:00.05 |       1 |      1 | 
|   1 |  SORT ORDER BY                      |                             |      1 |    347 |      0 |00:00:00.05 |       1 |      1 | 
|*  2 |   FILTER                            |                             |      1 |        |      0 |00:00:00.05 |       1 |      1 | 
|   3 |    VIEW                             |                             |      1 |    347 |      0 |00:00:00.05 |       1 |      1 | 
|   4 |     WINDOW SORT                     |                             |      1 |    347 |      0 |00:00:00.05 |       1 |      1 | 
|   5 |      NESTED LOOPS                   |                             |      1 |    347 |      0 |00:00:00.05 |       1 |      1 | 
|   6 |       NESTED LOOPS                  |                             |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|   7 |        NESTED LOOPS                 |                             |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|   8 |         MERGE JOIN CARTESIAN        |                             |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|   9 |          TABLE ACCESS BY INDEX ROWID| MDW_TASK_CLASS              |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|* 10 |           INDEX SKIP SCAN           | MDW_TASK_CLASS_IDX1         |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|  11 |          BUFFER SORT                |                             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 12 |           INDEX RANGE SCAN          | MDW_BATCH_ENGINE_IDX1       |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 13 |         INDEX RANGE SCAN            | MDW_BATCH_ENGINE_GROUP_IDX1 |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 14 |        INDEX RANGE SCAN             | MDW_TASK_TYPE_IDX2          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 15 |       INDEX RANGE SCAN              | MDW_TASK_INSTANCE_IDX4      |      0 |   2216 |      0 |00:00:00.01 |       0 |      0 | 
|  16 |    SORT AGGREGATE                   |                             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|  17 |     NESTED LOOPS                    |                             |      0 |     40 |      0 |00:00:00.01 |       0 |      0 | 
|* 18 |      INDEX RANGE SCAN               | MDW_TASK_TYPE_IDX2          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 19 |      INDEX RANGE SCAN               | MDW_TASK_INSTANCE_IDX4      |      0 |     36 |      0 |00:00:00.01 |       0 |      0 | 
-------------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - filter(("INSTANCE_NUMBER"<=0 OR ("CLASS_RANK"<="POLL_REQ"."POLL_MAX_GRAB" AND "INSTANCE_NUMBER"-"CLASS_RANK"->=0))) 
  10 - access("TC"."INSTANCE_NUMBER"<=0) 
       filter("TC"."INSTANCE_NUMBER"<=0) 
  12 - access("BE"."LABEL"='RLX000') 
  13 - access("EG"."BATCH_ENGINE_ID"="BE"."BATCH_ENGINE_ID" AND "TC"."BATCH_GROUP_ID"="EG"."BATCH_GROUP_ID") 

  14 - access("TT"."TASK_CLASS_ID"="TC"."TASK_CLASS_ID") 
       filter("TT"."TASK_CLASS_ID" IS NOT NULL) 
  15 - access("TI"."STATUS"=0 AND "TI"."APP_CODE"="TT"."APP_CODE" AND "TI"."TASK_LABEL"="TT"."TASK_LABEL") 
  18 - access("TT"."TASK_CLASS_ID"=:B1) 
  19 - access("TI"."STATUS"=1 AND "TI"."APP_CODE"="TT"."APP_CODE" AND "TI"."TASK_LABEL"="TT"."TASK_LABEL") 

On passe de 5102 logical reads à 1 logical read. Bien sûr le fait d’ajouter la clause à l’intérieur de la vue a un impact sur la fonction analytique. C’est pour cela que le CBO s’interdit de merger la vue. Il revient au développeur d’estimer l’impact de cette solution.


Conclusion:

Depuis la version 10g, le CBO peut choisir de merger ou pas une vue en se basant sur le coût estimé de chaque plan. Il existe des cas où le CBO décide pour éviter d’impacter le résultat de la requête de ne pas merger la vue bien que le coût du plan aurait été bien plus intéressant (ex : ROWNUM ou fonction analytique impliqués dans la vue). Il existe aussi des cas où la vue n’est pas mergée mais où le CBO décide quand même d’effectuer un push down de certains prédicats à l’intérieur de la vue.

jeudi 10 novembre 2011

Exclure une table lors d'un import

Un petit post juste pour partager une astuce que j'ai utilisé aujourd'hui lors d'un import à l'ancienne (c'est à dire sans DataPump).

Je devais importer un dump d'un schéma (dump généré par un export classique) dans un schéma d'une autre base. Toutefois une énorme table ne m'intéressait pas dans ce dump. Avec Datapump, il est facile d'exclure une table de l'import en utilisant le paramètre EXCLUDE. Avec l'import classique c'est plus compliqué car la seule chose qu'on puisse faire dans les règles de l'art c'est lister les tables à importer. Or il est plus facile d'exclure une table sur 1000 en la nommant explicitement que de lister les 999 tables qu'on souhaite importer.

L'astuce que j'ai utilisée consiste à créer dans le schéma cible une table du même nom que celle qu'on veut exclure mais avec une structure différente. Ainsi, une erreur sera générée lors de l'import de cette table mais il ira au bout. Ensuite il ne nous reste plus qu'à dropper la "fausse" table.

Exemple:
Je souhaite importer le schéma ADMRTI contenu dans le dump export_admrti_s11.dump vers le schéma RTI$OWNER de la base OPARLISD12 tout en excluant la table TLS_AUDIT.

Pour ce faire, je me connecte d'abord à la base cible pour créer une table portant le même nom que celle que je veux exclure:
SQL> create table tls_audit as select * from user_objects where 1=2;
 
 Table created.

Ensuite je peux effectuer mon import:
C:\HOMEWARE>imp userid=rti$owner/xxxxx@oparlisd12 file=export_admrti_s11.dump log=import_rti$owner_LISD12.log Fromuser=ADMRTI Touser=RTI$OWNER ignore=y
 

Voici le log d'import obtenu:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
 Export file created by EXPORT:V10.02.01 via direct path
 
 Warning: the objects were exported by ADMRTI, not by you
 
 import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
 import server uses AL32UTF8 character set (possible charset conversion)
 . . importing table                   "ADM_IMPORT"          2 rows imported
 . . importing table                      "CONTACT"          5 rows imported
 . . importing table                    "CO_SG_BDR"      11569 rows imported
 . . importing table                    "CO_SG_RTI"         62 rows imported
 . . importing table            "DATABASECHANGELOG"         28 rows imported
 . . importing table        "DATABASECHANGELOGLOCK"          1 rows imported
 . . importing table           "LNK_CONTACT_3PTYBR"          0 rows imported
 . . importing table               "LNK_CO_EXTCODE"         58 rows imported
 . . importing table       "LST_CONTACT_DEPARTMENT"         43 rows imported
 . . importing table         "LST_CONTACT_FUNCTION"         42 rows imported
 . . importing table            "LST_CONTACT_LEVEL"          4 rows imported
 . . importing table        "LST_INVESTOR_CATEGORY"         13 rows imported
 . . importing table                    "LST_TITLE"          3 rows imported
 . . importing table            "RTI_3PTY_EXT_CODE"       5239 rows imported
 . . importing table                 "RTI_BDR_3PTY"         92 rows imported
 . . importing table   "RTI_LNK_3PTY_BUSINESS_ROLE"       1007 rows imported
 . . importing table                 "RTI_LYX_3PTY"       2474 rows imported
 . . importing table           "RTI_REF_A_CLI_TYPE"          5 rows imported
 . . importing table        "RTI_REF_BUSINESS_ROLE"         18 rows imported
 . . importing table             "RTI_REF_CATEGORY"          3 rows imported
 . . importing table             "RTI_REF_CLI_TYPE"         68 rows imported
 . . importing table             "RTI_REF_EXT_CODE"         11 rows imported
 . . importing table         "RTI_REF_PRIMARY_ROLE"          9 rows imported
 . . importing table          "RTI_REF_SENSIBILITY"          6 rows imported
 . . importing table                 "RTI_SDD_3PTY"     333605 rows imported
 . . importing table         "RTI_TMP_SDD_3PTY_ADR"         17 rows imported
 . . importing table                    "TLS_AUDIT"
 IMP-00058: ORACLE error 904 encountered
 ORA-00904: "LATEST": invalid identifier
 IMP-00017: following statement failed with ORACLE error 904:
  "CREATE INDEX "IDX_TLS_AUDIT_CL_LA_PK" ON "TLS_AUDIT" ("ENTITY_CLASS_NAME" ,"
  " "LATEST" , "PK" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 5200"
  "93696 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDXRT"
  "I_L01" LOGGING"
 IMP-00003: ORACLE error 904 encountered
 ORA-00904: "PK": invalid identifier
 IMP-00017: following statement failed with ORACLE error 20000:
  "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"IDX_TLS_AUDIT_CL_LA_PK"',NULL,NULL"
  ",NULL,5679533,59956,429434,1,2,956931,3,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: INDEX "RTI$OWNER"."IDX_TLS_AUDIT_CL_LA_PK" does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 4455
 ORA-06512: at "SYS.DBMS_STATS", line 9464
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 904:
  "CREATE INDEX "IDX_TLS_AUDIT_CL_LA_OT_PK" ON "TLS_AUDIT" ("ENTITY_CLASS_NAME"
  "" , "LATEST" , "OTHER_PK" )  PCTFREE 10 INITRANS 2 MAXTRANS 155 STORAGE(INI"
  "TIAL 562036736 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPAC"
  "E "INDXRTI_L01" LOGGING"
 IMP-00003: ORACLE error 904 encountered
 ORA-00904: "OTHER_PK": invalid identifier
 IMP-00017: following statement failed with ORACLE error 20000:
  "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"IDX_TLS_AUDIT_CL_LA_OT_PK"',NULL,N"
  "ULL,NULL,5558841,61214,5481,3825,16419,262719,3,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: INDEX "RTI$OWNER"."IDX_TLS_AUDIT_CL_LA_OT_PK" does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 4455
 ORA-06512: at "SYS.DBMS_STATS", line 9464
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 904:
  "CREATE INDEX "IDX_TLS_AUDIT_ID" ON "TLS_AUDIT" ("AUDIT_ID" )  PCTFREE 10 IN"
  "ITRANS 2 MAXTRANS 255 STORAGE(INITIAL 173015040 FREELISTS 1 FREELIST GROUPS"
  " 1 BUFFER_POOL DEFAULT) TABLESPACE "INDXRTI_L01" LOGGING"
 IMP-00003: ORACLE error 904 encountered
 ORA-00904: "AUDIT_ID": invalid identifier
 IMP-00017: following statement failed with ORACLE error 20000:
  "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"IDX_TLS_AUDIT_ID"',NULL,NULL,NULL,"
  "5505116,20231,5486471,1,1,394696,2,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: INDEX "RTI$OWNER"."IDX_TLS_AUDIT_ID" does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 4455
 ORA-06512: at "SYS.DBMS_STATS", line 9464
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C31D374E'; SREC.MAX"
  "VAL := 'C40A0C5D11'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DB"
  "MS_STATS.NUMARRAY(285477,9119216); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1);"
  " SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"AUDIT_ID"'"
  ", NULL ,NULL,NULL,5486471,.000000182266524328662,0,srec,6,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column AUDIT_ID: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '786C0B14131C0F'; SR"
  "EC.MAXVAL := '786F0B0412301B'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NO"
  "VALS := DBMS_STATS.NUMARRAY(2454791.76891204,2455870.74127315); SREC.BKVALS"
  " := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NU"
  "LL,'"TLS_AUDIT"','"DATETIME"', NULL ,NULL,NULL,40852,.0000244786056986194,0"
  ",srec,8,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column DATETIME: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '494E53455254'; SREC"
  ".MAXVAL := '555044415445'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS"
  " := DBMS_STATS.NUMARRAY(380626296013610000000000000000000000,44297323347720"
  "5000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC "
  ":= 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"TRIGGER_EVENT"', NUL"
  "L ,NULL,NULL,2,.5,0,srec,7,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column TRIGGER_EVENT: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '424541312D303030363"
  "436354641303143453531444138423828343938313229'; SREC.MAXVAL := '424541312D3"
  "74646383230443939383342343336414133304328323138343430'; SREC.EAVS := 0; SRE"
  "C.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(3440962439759090000000"
  "00000000000000,344096243975942000000000000000000000); SREC.BKVALS := DBMS_S"
  "TATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_A"
  "UDIT"','"TX_ID"', NULL ,NULL,NULL,355868,.00000281003068553509,8537,srec,36"
  ",6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column TX_ID: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '3C616E6F6E796D6F757"
  "33E'; SREC.MAXVAL := '6E617468616E61656C2E697469632D73696D6F6E69'; SREC.EAV"
  "S := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(31351395482"
  "7912000000000000000000000,573129270941465000000000000000000000); SREC.BKVAL"
  "S := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(N"
  "ULL,'"TLS_AUDIT"','"USER_ID"', NULL ,NULL,NULL,12,.0833333333333333,0,srec,"
  "10,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column USER_ID: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '636F6D2E6C79786F722"
  "E7274692E64616F2E656E746974792E436F5367426472'; SREC.MAXVAL := '636F6D2E6C7"
  "9786F722E7274692E64616F2E656E746974792E52746953646433'; SREC.EAVS := 0; SRE"
  "C.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(5162973866981150000000"
  "00000000000000,516297386698115000000000000000000000); SREC.BKVALS := DBMS_S"
  "TATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_A"
  "UDIT"','"ENTITY_CLASS_NAME"', NULL ,NULL,NULL,4,.25,0,srec,37,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column ENTITY_CLASS_NAME: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '80'; SREC.MAXVAL :="
  " 'C102'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUM"
  "ARRAY(0,1); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_ST"
  "ATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"IS_LONG_PK"', NULL ,NULL,NULL,2,."
  "5,0,srec,3,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column IS_LONG_PK: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C20431'; SREC.MAXVA"
  "L := 'C422054140'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS"
  "_STATS.NUMARRAY(348,33046463); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SRE"
  "C.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"PK"', NULL ,NU"
  "LL,NULL,274069,.00000364871619920531,6214,srec,6,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column PK: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '286C797849643A312C6"
  "272436F64653A3129'; SREC.MAXVAL := '636F6D2E6C79786F722E7274692E64616F2E656"
  "E746974792E4C6E6B436F4578'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVAL"
  "S := DBMS_STATS.NUMARRAY(209891998413178000000000000000000000,5162973866981"
  "15000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC"
  " := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"OTHER_PK"', NULL ,N"
  "ULL,NULL,5481,.000182448458310527,5490846,srec,2,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column OTHER_PK: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '626472416374697665'"
  "; SREC.MAXVAL := '727469427573696E657373526F6C6573'; SREC.EAVS := 0; SREC.C"
  "HVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(5108823853440640000000000"
  "00000000000,594282940912058000000000000000000000); SREC.BKVALS := DBMS_STAT"
  "S.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDI"
  "T"','"PROPERTY_NAME"', NULL ,NULL,NULL,27,.037037037037037,0,srec,17,6); EN"
  "D;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column PROPERTY_NAME: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '6A6176612E6C616E672"
  "E4C6F6E67'; SREC.MAXVAL := '6A6176612E7574696C2E536574'; SREC.EAVS := 0; SR"
  "EC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(552360239735591000000"
  "000000000000000,552360239735634000000000000000000000); SREC.BKVALS := DBMS_"
  "STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_"
  "AUDIT"','"PROPERTY_CLASS_NAME"', NULL ,NULL,NULL,4,.25,12066,srec,17,6); EN"
  "D;"
 [...]
 . . importing table                      "WLSTORE"         36 rows imported
 About to enable constraints...
 Import terminated successfully with warnings.
 

On constate que toutes les tables ont été importées sauf la table TLS_AUDIT et ses objets associés (index, contraintes etc.)

mardi 20 septembre 2011

PL/SQL: clause AUTHID

Lorsque vous exécutez une procédure ou fonction PL/SQL le code s’exécute selon les privilèges du USER propriétaire du programme en question. Ce comportement peut être modifié en modifiant la clause AUTHID des procédures, packages et fonctions.

Par défaut la clause AUTHID a pour valeur DEFINER et elle peut être définie à CURRENT_USER lorsque vous désirez que le code PL/SQL exécutée par la procédure ou fonction s’exécute selon les privilèges du USER appelant.

Utiliser la clause AUTHID CURRENT_USER peut être pratique dans les cas où vous avez plusieurs schémas dans votre base avec des noms de tables identiques entre les schémas et que vous ne souhaitez pas dupliquer le code applicatif pour chaque schéma. En définissant, vos programmes PL/SQL en mode AUTHID CURRENT_USER il devient possible de les invoquer depuis les autres schémas de la base. Le code exécuté est alors basé sur les objets du schéma qui appelle le programme même si ce programme est stocké dans un autre schéma.

Voyons ça avec un exemple.

Je me connecte en SYS et je crée une procédure qui crée une table T1 :
SQL> conn / as sysdba
Connected.
 
SQL> create user user1 identified by user1;
 
User created.
 
SQL> grant DBA to user1;
 
Grant succeeded.
 
SQL> create or replace procedure create_table
  2  IS
  3  BEGIN
  4     EXECUTE IMMEDIATE 'CREATE TABLE T1 (C1 NUMBER)';
  5  END create_table;
  6  /
 
Procedure created.
 
SQL> grant execute on create_table to public;
 
Grant succeeded.

La clause AUTHID n’étant pas spécifié, la table T1 créée lors de l’appel à la procédure sera créée dans le schéma propriétaire de la procédure stockée à savoir SYS.
On peut le vérifier en appelant la procédure depuis le schéma USER1 :
SQL> conn user1/user1
Connected.
 
SQL> exec sys.create_table;
 
PL/SQL procedure successfully completed.
 
SQL> desc t1
ERROR:
ORA-04043: object t1 does not exist
 
SQL> conn / as sysdba
Connected.
SQL> desc T1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER

On voit bien que la table T1 n’existe pas sous le schéma USER1 mais bien sous le schéma SYS.
Maintenant je vais recréer la procédure stockée en spécifiant la clause AUTHID CURRENT_USER :
SQL> conn / as sysdba
Connected.
 
SQL> create or replace procedure create_table
  2  AUTHID CURRENT_USER
  3  IS
  4  BEGIN
  5     EXECUTE IMMEDIATE 'CREATE TABLE T1 (C1 NUMBER)';
  6  END create_table;
  7  /
 
Procedure created.

Si j’appelle la procédure depuis le user USER1, la table sera créée dans le schéma USER1 :
SQL> conn user1/user1
Connected.
SQL>  exec sys.create_table;
 
PL/SQL procedure successfully completed.
 
SQL> desc T1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER

Je pourrais appeler la même procédure depuis n’importe quel USER. J’ai ainsi une seule procédure à maintenir et qui est applicable sur tous les schémas.

Voyons maintenant un cas un peu plus épineux.
Imaginons que la procédure CREATE_TABLE soit créée sous le schéma USER2 en mode AUTHID DEFINER et que j’ai une procédure CREATE_TABLE en mode AUTHID CURRENT_USER sous le schéma USER1 qui appelle la procédure CREATE_TABLE de USER2. Si j’appelle depuis SYS la procédure CREATE_TABLE du USER1 qui appelle la procédure CREATE_TABLE du USER2, sous quel schéma sera créée la table T1 ? La réponse en exemple…
SQL> conn user2/user2
Connected.
SQL> create or replace procedure create_table
  2      IS
  3  BEGIN
  4     EXECUTE IMMEDIATE 'CREATE TABLE T1 (C1 NUMBER)';
  5  END create_table;
  6  /
 
Procedure created.
 
SQL> grant execute on create_table to public;
 
Grant succeeded.
 
SQL> conn user1/user1
Connected.
SQL> create or replace procedure create_table
  2  AUTHID CURRENT_USER
  3  IS
  4  BEGIN
  5     user2.create_table;
  6  END create_table;
  7  /
 
Procedure created.
 
SQL> grant execute on create_table to public;
 
Grant succeeded.
 
SQL> conn / as sysdba
Connected.
SQL> select owner from all_tables where table_name='T1';
 
no rows selected

Les procédures sont créées et il n’existe aucune table T1 dans la base.
J’appelle ensuite depuis SYS la procédure CREATE_TABLE de USER1 qui est en mode AUTHID CURRENT_USER en espérant que la table soit créée sous SYS :
SQL> exec user1.create_table;
 
PL/SQL procedure successfully completed.
 
SQL> select owner from all_tables where table_name='T1';
 
OWNER
------------------------------
USER2

Malheureusement la table a été créée sous le USER2, ce qui est en fait logique car la procédure de USER2 a été créée en mode DEFINER. Il faut donc être conscient qu’une procédure en mode CURRENT_USER ne garantit pas que ce mode soit appliqué aux procédures appelées par cette procédure.

PLS-00157: AUTHID only allowed on schema-level programs

Si vous obtenez ce message d’erreur lors de la compilation d’un package c’est surement que vous avez tenté de définir la clause AUTHID au niveau d’une procédure ou fonction appartenant à un package:
SQL>  Create or replace package pkg1
  2   IS
  3     procedure create_table;
  4   END pkg1;
  5   /
 
Package created.
 
SQL> Create or replace package body pkg1
  2   IS
  3     procedure create_table
  4     AUTHID CURRENT_USER
  5     IS
  6     BEGIN
  7             EXECUTE IMMEDIATE 'CREATE TABLE T1 (C1 NUMBER)';
  8     END create_table;
  9   END pkg1;
 10   /
 
Warning: Package Body created with compilation errors.
 
SQL> sho errors
Errors for PACKAGE BODY PKG1:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/9      PLS-00157: AUTHID only allowed on schema-level programs

Ce message indique qu’il n’est pas possible d’appliquer la clause AUTHID au niveau d’une seule procédure du package. La clause ne peut être spécifiée qu’au niveau de la spécification du package et s’applique donc à tous les programmes du package :
SQL> Create or replace package pkg1
  2   AUTHID CURRENT_USER
  3   IS
  4     procedure create_table;
  5   END pkg1;
  6   /
 
Package created.
 
SQL> Create or replace package body pkg1
  2   IS
  3     procedure create_table
  4     IS
  5     BEGIN
  6             EXECUTE IMMEDIATE 'CREATE TABLE T1 (C1 NUMBER)';
  7     END create_table;
  8   END pkg1;
  9   /
 
Package body created.

vendredi 26 août 2011

Restaurer des statistiques antérieures

Depuis la 10g, à chaque fois que vous calculez des statistiques dans Oracle via le package DBMS_STATS les statistiques précédentes sont sauvegardées dans le dictionnaire de données pendant une durée par défaut de 31 jours. L’intérêt de cette historisation des statistiques c’est qu’il est alors possible de les restaurer facilement.

La restauration de statistiques antérieures peut être utile lorsqu’après un calcul de stats sur une ou plusieurs tables on se retrouve avec des nouveaux plans moins performants. Le fait de restaurer les statistiques précédentes peut nous permettre de revenir aux performances correctes observées précédemment en attendant d’analyser le problème de performances lié à ces nouvelles stats.

Voici un exemple très simple pour comprendre le fonctionnement de l’historisation des statistiques.
SQL> select table_name,last_analyzed from user_tables where table_name='T1';
 
TABLE_NAME                     LAST_ANAL
------------------------------ ---------
T1                             25-JUL-11
 
SQL> select TABLE_NAME,NUM_ROWS
  2  from user_tab_statistics where table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                  72742

Je vois que les dernières stats sur ma table T1 datent du 25/07/2011 et que le nombre de lignes dans la table est estimé à 72742.
Maintenant je vide la table et je recalcule des stats :
SQL> truncate table T1;
 
Table truncated.
 
SQL> exec dbms_stats.gather_table_stats(USER,'T1');
 
PL/SQL procedure successfully completed.
 
SQL> select TABLE_NAME,NUM_ROWS
  2  from user_tab_statistics where table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                      0

J’ai bien maintenant 0 lignes dans ma table et les stats reflètent bien cet état de fait.

L’historique des statistiques est visualisable dans la vue USER_TAB_STATS_HISTORY :
SQL> select STATS_UPDATE_TIME
  2  from user_tab_stats_history where TABLE_NAME='T1';
 
STATS_UPDATE_TIME
---------------------------------------------------------------------------
25-JUL-11 02.40.18.819000 PM +02:00
25-JUL-11 02.45.31.305000 PM +02:00
25-JUL-11 02.45.47.274000 PM +02:00
25-AUG-11 01.44.58.074000 PM +02:00

En requêtant la vue USER_TAB_STATS_HISTORY on voit que lors des 31 derniers jours les statistiques ont été collectées 4 fois sur la table T1.

Ces stats sont sauvegardées et sont donc restaurables en utilisant la procédure RESTORE_TABLE_STATS du package DBMS_STATS.
SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(    OWNNAME=>USER, TABNAME=>'T1', as_of_timestamp=>sysdate-1);
  3  END;
  4  /
 
PL/SQL procedure successfully completed.

La requête ci-dessus restaure l’état des statistiques de la table T1 de la veille.
D’ailleurs si j’interroge la vue USER_TAB_STATISTICS je vois que les statistiques courante de T1 reflètent l’existence de 72742 lignes alors que ma table est vide.
SQL> select TABLE_NAME,NUM_ROWS
  2  from user_tab_statistics where table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                  72742

Il existe des procédures équivalentes à RESTORE_TABLE_STATS pour restaurer les statistiques au niveau de toute la base ou bien au niveau schéma, dictionnaire de données etc. (voir la doc Oracle).

jeudi 11 août 2011

Les statistiques étendues

Statistiques étendues sur des colonnes dépendantes

Ceux qui ont un peu d’expérience en matière de performance savent que les plans d’exécution non optimaux que peut être amené à choisir le CBO sont souvent liés à des erreurs dans l'estimation des cardinalités. La cardinalité d’une opération (par exemple un Full Table Scan) correspond au nombre de lignes retournées par cette opération après application des filtres. Le CBO estime cette cardinalité en multipliant le nombre de lignes de la table par la sélectivité de l’opération.

Exemple :
Soit une table CLIENT de 1 millions de lignes avec 90% d’hommes et 10% de femmes. Si j’exécute une requête me retournant la liste des clients hommes, le CBO va estimer le nombre de lignes retournées par cette requête afin de choisir un plan d’exécution adapté. Pour ce faire (en partant du principe qu’un histogramme a été calculé pour la colonne SEXE de la table CLIENT) il va multiplier la sélectivité (les 90%) par le nombre de lignes de la table. Il obtiendra une cardinalité de 900 000 et décidera surement d’opter pour un FULL TABLE SCAN pour récupérer ces lignes. Si à l’inverse, on voulait lister les clients femmes la cardinalité estimée aurait été de 100 000, et peut-être que le CBO aurait alors choisi un accès indexé.

Lorsqu’il y’a plusieurs colonnes impliquées dans la clause WHERE d’une requête le CBO va multiplier les sélectivités de chaque colonne pour calculer la cardinalité. Par exemple, si dans ma table j’ai 30% des clients qui vivent à Paris, on pourrait dire statistiquement parlant que j’ai 27% de clients hommes qui vivent à Paris (30% multiplié par 90%). Ce calcul est tout à fait raisonnable car les colonnes SEXE et VILLE ne sont pas directement liées.
Le problème se présente lorsque les filtres sont effectués sur des colonnes corrélées comme la VILLE et la REGION. Un client qui habite à Paris, habite forcément en Ile De France. C’est évident pour nous tous mais ça ne l’est pas pour l’optimiseur car il n’a aucune idée de ce que signifient les colonnes. Donc si on a dans nos stats 30% des clients qui vivent à Paris et 50% des clients qui vivent en Ile De France, et qu’on exécute une requête avec la clause WHERE suivante « WHERE VILLE = ’PARIS’ AND REGION = ‘IDF’ » l’optimiseur va calculer la cardinalité en utilisant une sélectivité de 15% (50% multiplié par 30%) au lieu de 30%.

Avant la 11g, pour obtenir une bonne cardinalité lorsqu’une requête impliquait des colonnes corrélées, il fallait qu’on ait un index composite sur ces deux colonnes ou bien qu’on force le Dynamic Sampling pour cette requête. Avec la 11g il est désormais possible de calculer des statistiques étendues sur ces colonnes corrélées (Multicolumn Extended Statistics) permettant à l’optimiseur de reconnaitre ces dépendances lors du parsing de la requête.

TEST CASE :
L’exemple ci-dessous montre comment les stats étendues permettent d’obtenir une cardinalité plus juste lorsque 2 colonnes dépendantes sont utilisées dans une requête.

Je vais d’abord créer ma table CLIENT en me basant sur la table CUSTOMER du schéma SH d’Oracle :
SQL> create table CLIENT(ID,PRENOM,NOM,SEXE,PAYS,VILLE,REGION)
  2  as
  3  select cu.CUST_ID, cu.CUST_FIRST_NAME, cu.CUST_LAST_NAME,
  4  cu.CUST_GENDER, co.COUNTRY_NAME, cu.CUST_CITY, cu.CUST_STATE_PROVINCE
  5  from customers cu, countries co
  6  where cu.COUNTRY_ID = co.COUNTRY_ID;
 
Table created.
 
SQL> select count(1) from CLIENT;
 
  COUNT(1)
----------
     55500
 
SQL> exec dbms_stats.gather_table_stats(user,'CLIENT');
 
PL/SQL procedure successfully completed.

Comme les colonnes VILLE et REGION sont skewed je vais calculer des histogrammes pour ces 2 colonnes:
SQL> exec dbms_stats.gather_table_stats(user,'CLIENT',method_opt => 'for columns size 254 VILLE, size 145 REGION');
 
PL/SQL procedure successfully completed.
 
SQL>  select column_name,histogram from user_tab_col_statistics where table_name='CLIENT';
 
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
ID                             NONE
PRENOM                         NONE
NOM                            NONE
SEXE                           NONE
PAYS                           NONE
VILLE                          HEIGHT BALANCED
REGION                         FREQUENCY

Voyons ce qu’estime le CBO pour les clients qui vivent à Paris :
SQL> select count(*) from client where ville='Paris';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  2stvn1str5n8g, child number 0
-------------------------------------
select count(*) from client where ville='Paris'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |     82 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("VILLE"='Paris')

Il y’a 77 clients qui vivent à Paris et le CBO en estime 82. On peut dire que l’estimation est juste.

Voyons ensuite les clients qui vivent en région Ile De France :
SQL> select count(*) from client where region='Ile-de-France';
 
  COUNT(*)
----------
      1034
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  bd28djabdux6b, child number 0
-------------------------------------
select count(*) from client where region='Ile-de-France'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |    892 |   1034 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("REGION"='Ile-de-France')

Le nombre de clients franciliens est de 1034 et le CBO en estime 892. Là aussi on a une estimation plutôt raisonnable.

Voyons maintenant ce que donne la requête lorsqu’on combine les 2 critères :
SQL> select count(*) from client where ville='Paris' and region='Ile-de-France';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  0y19xgnav3auh, child number 0
-------------------------------------
select count(*) from client where ville='Paris' and
region='Ile-de-France'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |      1 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("VILLE"='Paris' AND "REGION"='Ile-de-France'))

Là le CBO se trompe complètement car en multipliant les 2 sélectivités il en arrive à estimer que la requête ne retournera qu’une seule ligne au lieu de 77. Cette erreur vient du fait qu’il n’est pas informé de la corrélation qui existe entre la VILLE et la REGION.

Calculons des statistiques étendues pour ces 2 colonnes :
SQL> BEGIN
  2     DBMS_STATS.gather_table_stats(user,'CLIENT',
  3     method_opt => 'FOR ALL COLUMNS FOR COLUMNS (VILLE,REGION)',
  4     NO_INVALIDATE => FALSE
  5     );
  6  END;
  7  /
 
PL/SQL procedure successfully completed.

Le fait de calculer des stats étendues sur ces 2 colonnes va engendrer la création d’une colonne virtuelle appelée EXTENSION. La vue USER_STAT_EXTENSIONS permet d’avoir des informations sur ces extensions :
SQL> SELECT extension_name, extension
  2   FROM user_stat_extensions
  3   WHERE table_name = 'CLIENT';
 
EXTENSION_NAME                 EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STUE_OO2MD$OBR5EQSB7NBV$RO ("VILLE","REGION")

Le nom donné à l’extension correspond au nom de la colonne virtuelle qu’Oracle a automatiquement définie et qu’on peut également voir dans la liste des colonnes de la table :
SQL> select column_name,histogram from user_tab_col_statistics where table_name='CLIENT';
 
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
ID                             HEIGHT BALANCED
PRENOM                         HEIGHT BALANCED
NOM                            HEIGHT BALANCED
SEXE                           FREQUENCY
PAYS                           FREQUENCY
VILLE                          HEIGHT BALANCED
REGION                         HEIGHT BALANCED
SYS_STUE_OO2MD$OBR5EQSB7NBV$RO HEIGHT BALANCED

Maintenant que le CBO a des informations sur la corrélation existante ente les colonnes VILLE et REGION, voyons ce que donne ses estimations pour notre requête précédente :
SQL> select count(*) from client where ville='Paris' and region='Ile-de-France';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  0y19xgnav3auh, child number 0
-------------------------------------
select count(*) from client where ville='Paris' and
region='Ile-de-France'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |     87 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("VILLE"='Paris' AND "REGION"='Ile-de-France'))

La cardinalité estimée est 87, ce qui est bien mieux que le 1 obtenu sans les stats étendues.
Bien sûr pour cette requête, la cardinalité estimée ne change rien au plan d’exécution mais imaginons qu’on ait des jointures en plus dans la requête avec des tables volumineuses: avec une cardinalité fausse on peut se retrouver facilement avec un plan complètement inappropriée avec par exemple un MERGE JOIN CARTESIAN entre 2 tables retournant chacune plusieurs milliers de lignes.

Au lieu de créer les extensions au moment du calcul de stats via la procédure GATHER_TABLE_STATS, on pourrait créer directement ces extensions en utilisant la nouvelle fonction CREATE_EXTENDED_STATS du package DBMS_STATS :
SQL> exec dbms_stats.drop_extended_stats(ownname => user,tabname => 'CLIENT',extension => '(VILLE,REGION)');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT
  2  dbms_stats.create_extended_stats(ownname => user,tabname => 'CLIENT',extension => '(VILLE,REGION)') EXTENSION_NAME
  3  FROM dual;
 
EXTENSION_NAME
-----------------------------------------------------------
SYS_STUE_OO2MD$OBR5EQSB7NBV$RO

La fonction retourne le nom de la colonne virtuelle (extension).
A cette étape les stats ne sont pas calculées pour l’extension. Elle le seront lors du prochain calcul de stats effectués sur la table.

Si vous êtes face à un problème lié à la dépendance de colonnes et que vous êtes en 10g il ne vous sera pas possible de créer des statistiques étendues. La solution consistera à forcer le dynamic sampling via un hint ou bien en ne calculant pas de stats pour la table.

Exemple avec dynamic sampling :
SQL> exec dbms_stats.drop_extended_stats(ownname => user,tabname => 'CLIENT',extension => '(VILLE,REGION)');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.delete_table_stats(user,'CLIENT');
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from client where ville='Paris' and region='Ile-de-France';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  0y19xgnav3auh, child number 0
-------------------------------------
select count(*) from client where ville='Paris' and
region='Ile-de-France'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |     77 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("VILLE"='Paris' AND "REGION"='Ile-de-France'))
 
Note
-----
   - dynamic sampling used for this statement (level=2)

La section NOTE à la fin du plan indique bien que le dynamic sampling a été utilisé pour cette requête et surtout on voit que la cardinalité estimée est parfaite.


Statistiques étendues sur une expression :

Un autre cas où le CBO est incapable d'estimer une bonne cardinalité c’est lorsqu’on applique une fonction à une colonne. Le CBO n’a alors aucune idée de l’impact de cette fonction sur la sélectivité de la colonne. Dans ce cas et d’après le livre de Jonathan LEWIS, le CBO applique une séléctivité de 1% c'est-à-dire que pour une table de 1000 lignes il va estimer qu’une requête avec une fonction appliquée sur une colonne va retourner 10 lignes.
Reprenons notre table CLIENT et appliquons dans notre requête la fonction UPPER à la colonne VILLE :
SQL> select count(*) from client where UPPER(ville)='PARIS';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  59bf7bswaqxvh, child number 0
-------------------------------------
select count(*) from client where UPPER(ville)='PARIS'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |    555 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CLIENT"."SYS_NC00008quot;='PARIS')

On voit bien que la séléctivité de 1% a été appliquée puisque la table CLIENT contient 55 500 lignes et que le CBO a estimé une cardinalité de 555 (55500*0.01=555).

Calculons maintenant des stats étendues sur la fonction UPPER appliquée à la colonne VILLE :
SQL> BEGIN
  2  DBMS_STATS.gather_table_stats
  3  (ownname => USER,
  4  tabname => 'CLIENT',
  5  method_opt => 'FOR ALL COLUMNS FOR COLUMNS (UPPER(VILLE))'
  6  );
  7  END;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT extension_name, extension
  2   FROM user_stat_extensions
  3   WHERE table_name = 'CLIENT';
 
EXTENSION_NAME                 EXTENSION
------------------------------ ----------------------------------------------------------
SYS_STUV9T3PPLW$4FMKHYOB4#YU$D (UPPER("VILLE"))
 

Une colonne virtuelle a été crée correspondant à la fonction « UPPER(VILLE)».
A noter qu’on aurait pu là aussi utiliser la fonction CREATE_EXTENDED_STATS pour créer l’extension correspondant à la fonction UPPER.
Si on exécute de nouveau la requête on verra que la cardinalité estimée est bien plus proche de la réalité :
SQL> select count(*) from client where UPPER(ville)='PARIS';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  59bf7bswaqxvh, child number 1
-------------------------------------
select count(*) from client where UPPER(ville)='PARIS'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |     87 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(UPPER("VILLE")='PARIS')

Ces stats étendues peuvent être très utiles pour les applications où on applique des fonctions « maison » dans les clauses WHERE des requêtes. Toutefois, pour que ces stats étendues soient applicables ces fonctions doivent absolument être de type DETERMINISTIC.

CONCLUSION :
Lorsque vous êtes face à un problème de performance lié à l’utilisation d’un plan non-optimal pour une requête donnée, avant d’accuser le CBO de tous les maux, demandez-vous si le CBO dispose bien de toutes les informations nécessaires pour accomplir son travail. La 11g avec la possibilité de calculer des stats étendues sur un groupe de colonnes ou sur une expression peut aider dans certains cas le CBO à estimer des cardinalités plus justes et donc proposer un plan plus optimal.

jeudi 28 juillet 2011

Un DELETE si long sur une petite table

On m’a remonté cette semaine un problème sur un DELETE qui mettait 2h20 pour supprimer 10 148 lignes dans une table en contenant 22 558.
SQL> delete from varvolcurvevaluehs where var_id= 101; 

10148 rows deleted. 

Elapsed: 02:19:52.86 
 

Comment une simple requête supprimant juste 10 148 lignes peut-elle mettre autant de temps à s’exécuter?
Jetons un œil sur l’évènement d’attente qui a le plus contribué à l’exécution de cette requête.
Pour obtenir cette information on peut par exemple interroger les données ASH (historique des sessions actives) pour cette requête :
SQL> select event, count(1) 
  2    from dba_hist_active_sess_history 
  3   where sql_id = '48cu5tz204jad' 
  4   group by event; 

EVENT                            COUNT(1) 
------------------------------ ---------- 
                                                     418 
db file sequential read                  1 
db file scattered read                418 
log buffer space                            1 

On voit que la requête a généré essentiellement une attente sur l’évènement « db file scattered read ». Cet évènement signifie que la requête pendant 2h20 a effectué essentiellement des Full Table Scan.

La table à deleter ne contenait que 22000 lignes et elle n’était pas fragmentée du tout ce qui signifie que tous les blocks alloués au segment contenaient des lignes. Le nombre de blocks pour cette table s’élevait à 226. De plus il n’existait pas de trigger ON DELETE sur cette table. Le Full Table Scan ne peut donc pas être lié à cette table. Mais alors quelle est la table concernée par ce Full Table Scan ?

Si j’exécute de nouveau la requête et que je regarde les paramètres P1 et P2 associés à cet évènement d’attente je peux savoir quelle est la table réellement incriminée.
SQL> select event,p1text,p1,p2text,p2,p3text,p3 
  2  from v$session where sid=441; 

EVENT                          P1TEXT                  P1 P2TEXT                  P2 P3TEXT               P3 
------------------------------ --------------- ---------- --------------- ---------- --------------- ---------- 
db file scattered read         file#                  117 block#               99305 blocks               32 

A noter que ces informations sont aussi disponibles dans les données ASH (V$ACTIVE_SESSION_HISTORY et/ou DBA_HIST_ACTIVE_SESS_HISTORY).

Avec le numéro du datafile et le numéro du block ci-dessus il est possible d’interroger la vue DBA_EXTENTS pour retrouver le nom de l’objet concerné par le Full Table Scan :
SQL> select segment_name 
  2  from   dba_extents 
  3  where  73385 between block_id and (block_id + blocks - 1) 
  4  and    file_id = 117; 

SEGMENT_NAME 
--------------------------------------------------------------------------------- 
VARVOLCURVEPOINTVALUEHS 

Il ne s’agit donc pas de la table VARVOLCURVEVALUEHS mais d’une autre table (même si les 2 tables ont un nom très similaire).
Cette table doit surement être une table enfant de la table VARVOLCURVEVALUEHS référençant la Primary Key via une clé étrangère.

La requête ci-dessous nous permet de récupérer les Foreign Key référençant notre table VARVOLCURVEVALUEHS :
SQL> select table_name, CONSTRAINT_NAME 
  2    from user_constraints 
  3   where R_CONSTRAINT_NAME in 
  4         (select CONSTRAINT_NAME 
  5            from user_constraints 
  6           where table_name = 'VARVOLCURVEVALUEHS') 
  7     and CONSTRAINT_TYPE = 'R'; 

TABLE_NAME                     CONSTRAINT_NAME 
------------------------------ ------------------------------ 
VARVOLCURVEPOINTVALUEHS        FK1_VVCPVH_VVCVH 

Ce qu’il se passe en fait au moment du DELETE c’est qu’Oracle va vérifier pour chaque ligne supprimée dans VARVOLCURVEVALUEHS s’il n’existe pas de lignes dans la table enfant VARVOLCURVEPOINTVALUEHS qui référencent la ligne à supprimer. Pour effectuer cette vérification Oracle va exécuter une requête dite récursive sur la table VARVOLCURVEPOINTVALUEHS. Comme il n’existe pas d’index sur la FK, un Full Scan est effectué pour chaque ligne à supprimer (environ 10 000 fois). Comme le nombre de blocks dans la table VARVOLCURVEPOINTVALUEHS est très important (environ 50 000 blocks), ces 10 000 full table scans sont extrêmement couteux. De plus comme le buffer cache est assez petit sur la base concernée, ces blocks ne sont pas gardés en cache et des I/O physiques sont effectués à chaque Full Scan pour remonter les blocks du disque. 

 
J'avais écrit il y' a quelques semaines un article sur l'intérêt d’indexer les FK. Ce problème de performance en est une bonne illustration.

En créant un index sur la FK la requête s’exécute en moins d’une seconde :
SQL> create index IDX_CURVE_ID on VARVOLCURVEPOINTVALUEHS(CURVE_ID); 

Index created. 

SQL> set timin on 

SQL> delete from varvolcurvevaluehs where var_id= 101; 

10148 rows deleted. 

Elapsed: 00:00:00.89 

lundi 25 juillet 2011

Forcer un hard parse

Une des bonnes pratiques que tout le monde connait en matière de performances c’est de limiter le nombre de Hard Parses. L’idéal étant d’avoir juste un hard parse lors de la première exécution puis uniquement des soft parses (voire pas de parse call du tout) pour les autres exécutions.

Toutefois, il peut arriver qu’on souhaite forcer le hard parse à chaque exécution pour une requête en particulier.
Imaginons que vous ayez une requête qui se base notamment sur une table temporaire alimentée préalablement par la session. Le contenu d’une table temporaire étant propre à chaque session, vous pouvez avoir une première session exécutant cette requête avec une table temporaire contenant 100 lignes. Comme c’est la première fois que la requête est exécutée celle-ci sera hard parsée et un plan tenant compte des 100 lignes dans la table temporaire sera calculé par le CBO (dynamic sampling effectué sur la table temporaire pour estimer le nombre de lignes retournées). Maintenant une autre session va exécuter la même requête mais cette fois la table temporaire contiendra 1 million de lignes. Comme la même requête a déjà été exécutée auparavant par l’autre session (au niveau du texte c’est exactement la même requête) et qu’un curseur partageable est déjà présent dans la library cache, la requête sera seulement soft parsée et donc le plan qui avait été calculé pour la première session sera réutilisé. Ce plan qui avait été calculé en partant du principe que la table temporaire ne contenait que 100 lignes risque fortement de ne pas être approprié pour cette exécution. Il serait en effet moins couteux ici d’avoir un hard parse supplémentaire avec un plan optimal, plutôt qu’un soft parse mais avec un plan désastreux.

Voici un petit cas simple pour illustrer ce genre de situation que j’ai personnellement déjà rencontré par le passé.
Je crée d’abord une table temporaire T1_TEMP et une table T1 contenant 100 lignes :
SQL> CREATE GLOBAL TEMPORARY TABLE T1_TEMP
  2  ON COMMIT PRESERVE ROWS
  3  AS SELECT * FROM DBA_OBJECTS WHERE 1=2;

Table created.

SQL> create index IDX_OBJ_TYP on T1_TEMP(OBJECT_TYPE);

Index created.

SQL> create table t1
  2  as select * from dba_objects
  3  where rownum<=100;

Table created.
Dans une session 1 j’insère dans la table temporaire 100 lignes et j’exécute une requête basée sur cette table temporaire :
SQL> -- session1
SQL> insert into T1_TEMP
  2  SELECT * FROM T1;

100 rows created.

SQL> insert into t1
  2  select * from T1_TEMP
  3  where OBJECT_TYPE='CLUSTER';

5 rows created.

Elapsed: 00:00:00.01
  
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  7df6acdvufgud, child number 0
-------------------------------------
insert into t1 select * from T1_TEMP where OBJECT_TYPE='CLUSTER'

Plan hash value: 3185690956

-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |      1 |        |      0 |00:00:00.02 |      28 |      2 |
|   1 |  LOAD TABLE CONVENTIONAL |         |      1 |        |      0 |00:00:00.02 |      28 |      2 |
|*  2 |   TABLE ACCESS FULL      | T1_TEMP |      1 |      5 |      5 |00:00:00.01 |       4 |      0 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
La partie SELECT de la requête retourne 5 lignes mais comme la table temporaire contient peu de lignes, le CBO a judicieusement opté pour un Full Table Scan. D’ailleurs le nombre de lignes estimées par le CBO ( E-ROWS) correspond bien au nombre de lignes réellement traitées par le moteur SQL (A-ROWS). Maintenant j’ouvre une autre session et je vais exécuter la même requête sauf que cette fois je vais insérer 1 million de lignes dans la table temporaire :
SQL> -- session2
SQL> insert into t1_temp
  2  select t1.* from t1,t1 t2,t1 t3;

1000000 rows created.

SQL> insert into t1
  2  select * from T1_TEMP
  3  where OBJECT_TYPE='CLUSTER';

50000 rows created.

Elapsed: 00:00:02.93
  
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  7df6acdvufgud, child number 0
-------------------------------------
insert into t1 select * from T1_TEMP where OBJECT_TYPE='CLUSTER'

Plan hash value: 3185690956

-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |      1 |        |      0 |00:00:02.94 |   18507 |  10198 |
|   1 |  LOAD TABLE CONVENTIONAL |         |      1 |        |      0 |00:00:02.94 |   18507 |  10198 |
|*  2 |   TABLE ACCESS FULL      | T1_TEMP |      1 |      5 |  50000 |00:00:02.83 |   11314 |  10196 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
Comme la même requête avait déjà été exécutée par une autre session, cette requête n’a pas été hard parsée et c’est donc le plan déjà existant dans la shared pool qui a été utilisé. Le Full San n’est pas approprié et on voit que le nombre de lignes estimées correspond aux lignes retournées par la requête de la première session. Si cette requête avait été hard parsée on aurait surement eu un plan avec un accès indexé. Pour s’en assurer on peut flusher la shared pool et relancer la même requête :
SQL> rollback;

Rollback complete.

Elapsed: 00:00:36.39   

SQL> alter system flush shared_pool;

System altered.

SQL> insert into t1
  2  select * from T1_TEMP
  3  where OBJECT_TYPE='CLUSTER';

50000 rows created.

Elapsed: 00:00:01.92

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  7df6acdvufgud, child number 0
-------------------------------------
insert into t1 select * from T1_TEMP where OBJECT_TYPE='CLUSTER'

Plan hash value: 1045437032

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |             |      1 |        |      0 |00:00:01.68 |    8285 |    747 |
|   1 |  LOAD TABLE CONVENTIONAL     |             |      1 |        |      0 |00:00:01.68 |    8285 |    747 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1_TEMP     |      1 |  29962 |  50000 |00:00:01.53 |    1027 |    744 |
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYP |      1 |  29962 |  50000 |00:00:00.62 |     441 |    195 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
Un hard parse a été effectué et le CBO a estimé une cardinalité plus proche de la réalité. Le plan contient un accès indexé et est bien meilleur puisqu’on passe de 18507 logical reads à 8285. Cet exemple est assez basique mais il montre bien que dans certains cas il peut être intéressant d’avoir un hard parse à chaque exécution plutôt que de réutiliser toujours le même plan.  

Comment donc forcer un hard parse ?

Il n’existe pas dans Oracle de moyen explicite de le faire : pas de hint /*+ FORCE_HARD_PARSE */ ni de paramètre caché « _HARD_PARSE » qu’on pourrait mettre à TRUE au niveau de la session. Il faut donc réfléchir un peu et se poser la question suivante: Quand est-ce qu’un Hard parse est effectué ?
Réponses :
1) Lorsqu’il n’existe pas de curseur associé à la requête dans la library cache c'est-à-dire lorsque la requête n’a jamais été exécutée ou bien que le curseur a été flushé de la shared pool pour faire de la place.
2) Lorsque le curseur a été invalidé à cause par exemple d’un calcul de stats sur une table référencée dans la requête
3) Lorsque l’environnement d’exécution est différent (ex : modification d’un paramètre de l’optimiseur au niveau de la session)

Dans mon exemple précédent j’ai utilisé la commande « ALTER SYSTEM FLUSH SHARED_POOL », mais il s‘agit là d’une solution un peu « bourrin » qu’il ne faut surtout pas utiliser dans une application puisqu’il vide tous les curseurs partagés dans la library cache. Avec cette solution on forcerait le hard parse pour toutes les requêtes ce qui dégraderait fortement les performances générales (latches, CPU….).

Solution 1 : Générer un SQL_ID différent
L’idéal pour forcer le hard parse c’est de faire en sorte que chaque exécution de la même requête devienne aux yeux d’Oracle une exécution différente. On pourrait par exemple utiliser une requête SQL dynamique avec une clause WHERE qui serait toujours vraie mais serait différente pour chaque exécution. Par exemple on pourrait ajouter une clause « WHERE :random_value = :random_value » où la variable random_value contiendrait un nombre aléatoire. Cette clause étant toujours vraie, elle n’aurait pas d’incidence sur le résultat de la requête et permettrait de générer un SQL_ID différent à chaque exécution. Imaginons que la requête soit exécutée dans du code PL/SQL, la partie du code qui exécute la requête pourrait ressembler à ceci :
DECLARE
 random_value number;
 BEGIN
 random_value := dbms_random.value;
 execute immediate 'insert /*test_PL */ into t1_temp select * from T1_TEMP where OBJECT_TYPE=''CLUSTER'' and ' || random_value ||  '=' || random_value;
 END;
 /
Rejouons notre test précédent en utilisant ce bloc PL/SQL pour l’exécution de la requête qu’on souhaiterait voir hard parsée à chaque exécution :
-- session 1
SQL> insert into T1_TEMP
  2  SELECT * FROM T1;

100 rows created.

Elapsed: 00:00:00.04
SQL>  Declare
  2     random_value number;
  3   BEGIN
  4     random_value := dbms_random.value;
  5     execute immediate 'insert /*test_PL */ into t1_temp select * from T1_TEMP where OBJECT_TYPE=''CLUSTER'' and ' || random_value ||  '=' || random_value;
  6   END;
  7   /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> select sql_id from v$sql where sql_text like 'insert /*test_PL%';

SQL_ID
-------------
80y71zqh47kr4

1 row selected.

Elapsed: 00:00:00.01
SQL> @plan_curs
SQL> SET LINES 500
SQL> SET PAGES 500
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'));
Enter value for sql_id: 80y71zqh47kr4
Enter value for child_number:
old   1: select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'))
new   1: select * from table(dbms_xplan.display_cursor('80y71zqh47kr4','','allstats last'))

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  80y71zqh47kr4, child number 0
-------------------------------------
insert /*test_PL */ into t1_temp select * from T1_TEMP where
OBJECT_TYPE='CLUSTER' and .63062214722457742739970953577612375345=.63062
214722457742739970953577612375345

Plan hash value: 3185690956

----------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |      1 |        |      0 |00:00:00.01 |       8 |
|   1 |  LOAD TABLE CONVENTIONAL |         |      1 |        |      0 |00:00:00.01 |       8 |
|*  2 |   TABLE ACCESS FULL      | T1_TEMP |      1 |      5 |      5 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
On voit que pour la session où la table temporaire contient 100 lignes on a bien un plan avec le Full table Scan. Le SQL_ID correspondant à cette requête est le 80y71zqh47kr4. Maintenant exécutons la requête pour la session 2 (celle qui contient 1 millions de lignes) :
-- session 2
SQL> insert into t1_temp
  2  select t1.* from t1,t1 t2,t1 t3;

1000000 rows created.

Elapsed: 00:00:43.79
SQL> Declare
  2     random_value number;
  3   BEGIN
  4     random_value := dbms_random.value;
  5     execute immediate 'insert /*test_PL */ into t1_temp select * from T1_TEMP where OBJECT_TYPE=''CLUSTER'' and ' || random_value ||  '=' || random_value;
  6   END;
  7   /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.12
SQL> select sql_id from v$sql where sql_text like 'insert /*test_PL%';

SQL_ID
-------------
5a5mryn0ug6sg
80y71zqh47kr4

2 rows selected.

Elapsed: 00:00:00.00
SQL> @plan_curs
SQL> SET LINES 500
SQL> SET PAGES 500
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'));
Enter value for sql_id: 5a5mryn0ug6sg
Enter value for child_number:
old   1: select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'))
new   1: select * from table(dbms_xplan.display_cursor('5a5mryn0ug6sg','','allstats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  5a5mryn0ug6sg, child number 0
-------------------------------------
insert /*test_PL */ into t1_temp select * from T1_TEMP where
OBJECT_TYPE='CLUSTER' and .97829861972676033156035029701963270324=.97829
861972676033156035029701963270324

Plan hash value: 1045437032

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |             |      1 |        |      0 |00:00:02.75 |   13565 |    775 |
|   1 |  LOAD TABLE CONVENTIONAL     |             |      1 |        |      0 |00:00:02.75 |   13565 |    775 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1_TEMP     |      1 |    109K|  50000 |00:00:02.51 |    1047 |    775 |
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYP |      1 |    109K|  50000 |00:00:01.04 |     461 |    206 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
La requête a bien été hard parsée puisqu’on obtient directement notre plan avec accès indexé. Le SQL_ID vaut 5a5mryn0ug6sg et est bien différent du premier SQL_ID. Au fur et à mesure des exécutions le SQL_ID générée sera différent et donc un hard parse sera effectué à chaque fois. Il est important dans le bloc PL/SQL de ne pas utiliser de clause USING et d’avoir un paramètre CURSOR_SHARING à EXACT, sinon la variable contenant la valeur aléatoire deviendrait une Bind Variable, et on aurait un seul curseur partagée réutilisée (et donc un même plan) à chaque exécution de la requête.

Solution 2 : Invalider le curseur
Au lieu de générer un nouveau SQL_ID à chaque exécution une autre solution consisterait à invalider le curseur déjà dans la shared pool. Pour ce faire vous pouvez utiliser la procédure SET_TABLE_STATS du package DBMS_STATS.
Voyons ça avec un exemple :
-- session 1
SQL> drop table t1;

Table dropped.

SQL>
SQL> create table t1 as select * from dba_objects;

Table created.

SQL>
SQL> create index t1_idx on t1(object_id);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       136
Dans une session 1 j’ai crée une table T1et j’ai récupéré l’identifiant de ma session (SID=136). Dans une 2ème session je regarde les statistiques « parse count (hard) » et « parse count (total) » pour ma première session :
-- session 2
SQL> SELECT sn.name, ss.value
  2   FROM v$statname sn, v$sesstat ss
  3   WHERE sn.statistic# = ss.statistic#
  4   AND sn.name IN (
  5   'parse count (hard)','parse count (total)')
  6   AND ss.sid =136;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                    1500
parse count (hard)                                                      269
Dans ma session 1 j’exécute une requête sur la table T1 :
-- session 1
SQL> select object_id from t1 where object_id= 20;

 OBJECT_ID
----------
        20
Je regarde maintenant les stats sur mon autre session et je constate qu’un Hard Parse a bien été effectué (la statistique « parse count (hard) » a été incrémenté de 1) :
-- session 2
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                    1501
parse count (hard)                                                      270
J‘exécute de nouveau ma requête dans ma 1ère session, puis je regarde les stats :
-- session 1
SQL> select object_id from t1 where object_id= 20;

 OBJECT_ID
----------
        20
  
-- Session 2
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                    1502
parse count (hard)                                                      270
Cette fois la statistique « parse count (total) » a été incrémenté mais pas la stats « parse count (hard) », il s’agit donc d’un soft parse. C’est normal puisque un curseur partageable existait déjà dans la library cache depuis que la requête a été exécutée la 1ère fois.
Maintenant nous allons invalider ce curseur grâce au package DBMS_STATS :
SQL> -- invalidation curseur
SQL>  exec dbms_stats.set_table_stats(USER, 'T1', numrows=>null, no_invalidate=>false);


-- Session 1
SQL> select object_id from t1 where object_id= 20;

 OBJECT_ID
----------
        20
  
-- Session 2
SQL> SELECT sn.name, ss.value
  2   FROM v$statname sn, v$sesstat ss
  3   WHERE sn.statistic# = ss.statistic#
  4   AND sn.name IN (
  5   'parse count (hard)','parse count (total)')
  6   AND ss.sid =136;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                    1503
parse count (hard)                                                      271
Après avoir invalidé le curseur et relancer la même requête sur T1 on constate que la statistique « parse count (hard) » a de nouveau été incrémentée de 1. C’est donc que le Hard Parse a bien eu lieu. Il est important de mettre le paramètre NO_INVALIDATE de la procédure à FALSE afin de forcer l’invalidation du curseur. Vous noterez comme moi qu’Oracle aime parfois compliquer les choses. Au lieu d’avoir un paramètre INVALIDATE à TRUE ou FALSE ils ont préféré un paramètre NO_INVALIDATE. C’est de la triple négation que j’ai du mal à comprendre…enfin bref. L’inconvénient de cette solution c’est que tous les curseurs correspondant aux requêtes qui référencent la table T1 seront invalidés. Alors qu’on souhaiterait uniquement agir sur une requête en particulier, avec cette solution on risque d’impacter beaucoup plus de requêtes.

3ème solution : Flusher le SQL_ID
La 3ème solution qui me vient à l’esprit serait de récupérer avant chaque exécution l’identifiant de la requête à hard parser et de flusher le curseur associé grâce à la procédure dbms_shared_pool.purge. J’avais déjà parlé de cette astuce en Décembre 2010 dans un article intitulé « Flusher un curseur de la shared pool avec DBMS_SHARED_POOL».


CONCLUSION :

Il existe différents moyens de forcer un hard parse pour une requête donnée :
- Faire en sorte de générer un SQL_ID différent à chaque exécution
- Invalider le curseur présent dans la shared pool
- Flusher le curseur de la shared ppol

Lorsque vous décidez de forcer un hard parse pour une requête donnée il faut bien avoir à l’esprit du risque encouru : contentions au niveau de la library cache, parse call plus long et consommation accrue de CPU.
Le hard parse forcé est justifié quand l’utilisation d’un unique plan pour une requête donnée conduit à des performances désastreuses. Il peut arriver aussi qu’on souhaite forcer un hard parse mais sans qu’on ait la possibilité de toucher au code. Randolf Geist a mis en évidence dans son blog comment il était possible de générer des clauses WHERE de manière dynamique en utilisant le VPD (Virtual Private Database). Cet article est disponible ici.