Fixed tables et statistics

Les fixed tables appelées aussi "X$ tables" sont des tables appartenant à SYS et qui sont censées ne pas être modifiées par les utilisateurs d'Oracle (DBA ou pas). Il faut noter ici qu'il ne s'agit pas de tables comme celles qu'on crée avec la commande SQL CREATE TABLE mais plutôt une sorte d'objet permettant d'exposer aux DBAs les structures de données codées en C situées dans la SGA.

Les vues dynamiques de performances (les fameuses V$) sont basées sur ces tables là. La preuve, lorsqu'on regarde le plan d'exécution d'un SELECT basé sur la vue V$LOCK on s'aperçoit que plusieurs tables commençant par X$ sont impliquées:

SQL> explain plan for select * from v$lock; 

Explained. 

SQL> select * from table(dbms_xplan.display); 
-------------------------------------------------------------------------------------------- 
 Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT         |                 |     1 |   131 |     1 (100)| 00:00:01 | 
|   1 |  NESTED LOOPS            |                 |     1 |   131 |     1 (100)| 00:00:01 | 
|*  2 |   HASH JOIN              |                 |     1 |    98 |     1 (100)| 00:00:01 | 
|*  3 |    FIXED TABLE FULL      | X$KSUSE         |     1 |    30 |     0   (0)| 00:00:01 | 
|   4 |    VIEW                  | GV$_LOCK        |    10 |   680 |     0   (0)| 00:00:01 | 
|   5 |     UNION-ALL            |                 |       |       |            |          | 
|*  6 |      FILTER              |                 |       |       |            |          | 
|   7 |       VIEW               | GV$_LOCK1       |     2 |   136 |     0   (0)| 00:00:01 | 
|   8 |        UNION-ALL         |                 |       |       |            |          | 
|*  9 |         FIXED TABLE FULL | X$KDNSSF        |     1 |    94 |     0   (0)| 00:00:01 | 
|* 10 |         FIXED TABLE FULL | X$KSQEQ         |     1 |    94 |     0   (0)| 00:00:01 | 
|* 11 |      FIXED TABLE FULL    | X$KTADM         |     1 |    94 |     0   (0)| 00:00:01 | 
|* 12 |      FIXED TABLE FULL    | X$KTATRFIL      |     1 |    94 |     0   (0)| 00:00:01 | 
|* 13 |      FIXED TABLE FULL    | X$KTATRFSL      |     1 |    94 |     0   (0)| 00:00:01 | 
|* 14 |      FIXED TABLE FULL    | X$KTATL         |     1 |    94 |     0   (0)| 00:00:01 | 
|* 15 |      FIXED TABLE FULL    | X$KTSTUSC       |     1 |    94 |     0   (0)| 00:00:01 | 
|* 16 |      FIXED TABLE FULL    | X$KTSTUSS       |     1 |    94 |     0   (0)| 00:00:01 | 
|* 17 |      FIXED TABLE FULL    | X$KTSTUSG       |     1 |    94 |     0   (0)| 00:00:01 | 
|* 18 |      FIXED TABLE FULL    | X$KTCXB         |     1 |    94 |     0   (0)| 00:00:01 | 
|* 19 |   FIXED TABLE FIXED INDEX| X$KSQRS (ind:1) |     1 |    33 |     0   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------------

L’appel à V$LOCK implique l’accès à 11 fixed tables, et justement j’ai récemment repéré, dans une des bases sur lesquelles je travaille, une requête basée sur la vue V$LOCK et qui mettait une quarantaine de secondes à s’exécuter. Cette requête de monitoring utilisée par je ne sais quelle application était exécutée plusieurs milliers de fois par jour.

Voici la requête en question ainsi que son plan d’exécution mettant en évidence les temps réels d’exécution de chaque opération :

SQL> SELECT NVL(TRUNC(MAX(DECODE(W.REQUEST,0,0,W.CTIME))/60),0) FROM V$LOCK W WHERE W.REQUEST > 0;   

NVL(TRUNC(MAX(DECODE(W.REQUEST,0,0,W.CTIME))/60),0) 
--------------------------------------------------- 
                                                  0 

1 row selected. 

Elapsed: 00:00:48.10 

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

  Plan hash value: 2399206389 
------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation               | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp| 
------------------------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT        |            |      1 |        |      1 |00:00:47.99 |      11 |  16080 |  64350 |       |       |          |         | 
|   1 |  SORT AGGREGATE         |            |      1 |      1 |      1 |00:00:47.99 |      11 |  16080 |  64350 |       |       |          |         | 
|*  2 |   HASH JOIN             |            |      1 |      1 |      0 |00:00:47.99 |      11 |  16080 |  64350 |   675M|    18M|  202M (1)|     537K| 
|   3 |    MERGE JOIN CARTESIAN |            |      1 |    100 |     10M|00:00:06.87 |       0 |      0 |      0 |       |       |          |         | 
|*  4 |     FIXED TABLE FULL    | X$KSUSE    |      1 |      1 |   1536 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|   5 |     BUFFER SORT         |            |   1536 |    100 |     10M|00:00:03.34 |       0 |      0 |      0 |   337K|   337K|  299K (0)|         | 
|   6 |      FIXED TABLE FULL   | X$KSQRS    |      1 |    100 |   6976 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|   7 |    VIEW                 | GV$_LOCK   |      1 |     10 |      0 |00:00:00.08 |      11 |      0 |      0 |       |       |          |         | 
|   8 |     UNION-ALL           |            |      1 |        |      0 |00:00:00.08 |      11 |      0 |      0 |       |       |          |         | 
|*  9 |      FILTER             |            |      1 |        |      0 |00:00:00.03 |       0 |      0 |      0 |       |       |          |         | 
|  10 |       VIEW              | GV$_LOCK1  |      1 |      2 |      0 |00:00:00.03 |       0 |      0 |      0 |       |       |          |         | 
|  11 |        UNION-ALL        |            |      1 |        |      0 |00:00:00.03 |       0 |      0 |      0 |       |       |          |         | 
|* 12 |         FIXED TABLE FULL| X$KDNSSF   |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 13 |         FIXED TABLE FULL| X$KSQEQ    |      1 |      1 |      0 |00:00:00.02 |       0 |      0 |      0 |       |       |          |         | 
|* 14 |      FIXED TABLE FULL   | X$KTADM    |      1 |      1 |      0 |00:00:00.04 |       0 |      0 |      0 |       |       |          |         | 
|* 15 |      FIXED TABLE FULL   | X$KTATRFIL |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 16 |      FIXED TABLE FULL   | X$KTATRFSL |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 17 |      FIXED TABLE FULL   | X$KTATL    |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 18 |      FIXED TABLE FULL   | X$KTSTUSC  |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 19 |      FIXED TABLE FULL   | X$KTSTUSS  |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 20 |      FIXED TABLE FULL   | X$KTSTUSG  |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 21 |      FIXED TABLE FULL   | X$KTCXB    |      1 |      1 |      0 |00:00:00.02 |      11 |      0 |      0 |       |       |          |         | 
------------------------------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - access("SADDR"="S"."ADDR" AND TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR")) 
   4 - filter("S"."INST_ID"=USERENV('INSTANCE')) 
   9 - filter(USERENV('INSTANCE') IS NOT NULL) 
  12 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  13 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  14 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  15 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  16 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  17 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  18 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  19 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  20 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  21 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))

Le plan nous montre que le SELECT sur V$LOCK s’est exécutée en 48 secondes.
En regardant le plan de plus près on s’aperçoit qu’un MERGE JOIN CARTESIAN (opération 3 du plan) entre la fixed table X$KSUSE et X$KSQRS est utilisé à mauvais escient puisque la jointure retourne 10 millions de lignes (colonne A-ROWS) au lieu des 100 estimés par le CBO (colonne E-ROWS). Ce mauvais choix d’opération est dû au fait que la cardinalité sur la fixed table X$KSUSE est mal estimé. En effet, l’optimiseur estime que l’accès à cette table va retourner qu’une seule ligne au lieu de 1536. Le fait d’estimer une cardinalité de 1 explique le choix du MERGE JOIN CARTESIAN.
Je me suis rendu compte que pour cette base les statistiques sur les fixed tables et celles sur le dictionnaire de données n’avaient jamais été calculées par les DBAs de PROD chargés d’administrer cette base.

Après avoir demandé un calcul de stats sur ces tables via les procédures gather_fixed_objects_stats et gather_dictionary_stats du package DBMS_STATS, la requête s’est mise à s’exécuter instantanément.

Voici le nouveau plan après le calcul des stats sur les « fixed objects » :

Plan hash value: 2965011029 
---------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | 
---------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT         |            |      1 |        |      1 |00:00:00.08 |      10 |    |  |          | 
|   1 |  SORT AGGREGATE          |            |      1 |      1 |      1 |00:00:00.08 |      10 |    |  |          | 
|*  2 |   HASH JOIN              |            |      1 |    698 |      0 |00:00:00.08 |      10 |   816K|   816K|  203K (0)| 
|*  3 |    HASH JOIN             |            |      1 |     10 |      0 |00:00:00.08 |      10 |   845K|   845K|  190K (0)| 
|   4 |     VIEW                 | GV$_LOCK   |      1 |     10 |      0 |00:00:00.08 |      10 |    |  |          | 
|   5 |      UNION-ALL           |            |      1 |        |      0 |00:00:00.08 |      10 |    |  |          | 
|*  6 |       FILTER             |            |      1 |        |      0 |00:00:00.02 |       0 |    |  |          | 
|   7 |        VIEW              | GV$_LOCK1  |      1 |      2 |      0 |00:00:00.02 |       0 |    |  |          | 
|   8 |         UNION-ALL        |            |      1 |        |      0 |00:00:00.02 |       0 |    |  |          | 
|*  9 |          FIXED TABLE FULL| X$KDNSSF   |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 10 |          FIXED TABLE FULL| X$KSQEQ    |      1 |      1 |      0 |00:00:00.02 |       0 |    |  |          | 
|* 11 |       FIXED TABLE FULL   | X$KTADM    |      1 |      1 |      0 |00:00:00.04 |       0 |    |  |          | 
|* 12 |       FIXED TABLE FULL   | X$KTATRFIL |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 13 |       FIXED TABLE FULL   | X$KTATRFSL |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 14 |       FIXED TABLE FULL   | X$KTATL    |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 15 |       FIXED TABLE FULL   | X$KTSTUSC  |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 16 |       FIXED TABLE FULL   | X$KTSTUSS  |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 17 |       FIXED TABLE FULL   | X$KTSTUSG  |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 18 |       FIXED TABLE FULL   | X$KTCXB    |      1 |      1 |      0 |00:00:00.01 |      10 |    |  |          | 
|* 19 |     FIXED TABLE FULL     | X$KSUSE    |      0 |   1536 |      0 |00:00:00.01 |       0 |    |  |          | 
|  20 |    FIXED TABLE FULL      | X$KSQRS    |      0 |   6976 |      0 |00:00:00.01 |       0 |    |  |          | 
---------------------------------------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR")) 
   3 - access("SADDR"="S"."ADDR") 
   6 - filter(USERENV('INSTANCE') IS NOT NULL) 
   9 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  10 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  11 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  12 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  13 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  14 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  15 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  16 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  17 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  18 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  19 - filter("S"."INST_ID"=USERENV('INSTANCE'))

On s’aperçoit que le MERGE JOIN CARTESIAN a disparu car la cardinalité estimée sur la fixed table X$KSUSE est désormais bien de 1536 et non pas de 1 comme précédemment.

CONCLUSION :
Lorsque vous constatez des performances catastrophiques sur des requêtes impliquant des vues dynamiques de performances ou des vues du dictionnaire de données demandez-vous s’il ne serait pas nécessaire de lancer un calcul de stats sur ces objets.
Attention, la doc Oracle précise que ces stats doivent être calculées pendant une période représentative de l’activité de la base.

ORA-02266 lors d'un TRUNCATE TABLE

Si lors d'un TRUNCATE d'une table vous obtenez une erreur ORA-02266 c'est que la clé primaire de cette table est référencée par une autre table (ou plusieurs) via une clé étrangère. Cela arrive souvent et c'est ce qui m'est arrivé ce matin:

SQL> truncate table mdw_trace;
truncate table mdw_trace
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

La solution est simple et consiste à retrouver les tables enfants de la table que je veux truncater puis à désactiver les clés étrangères correspondantes.
Pour ce faire j'ai dans ma boite à scripts le script suivant que j'ai appelé find_child_table.sql:

-- find child tables when ORA-02292: integrity constraint
select constraint_name,table_name from user_constraints where r_constraint_name in(
select constraint_name from user_constraints where table_name = upper('&table_name')) ;

Selon la valeur de la variable TABLE_NAME cette requête retourne le nom de la table enfant ainsi que le nom de la Foreign key:

SQL> @find_child_table
SQL> -- find child tables when ORA-02292: integrity constraint
SQL> select constraint_name,table_name from user_constraints where r_constraint_name in(
  2  select constraint_name from user_constraints where table_name = upper('&table_name')) ;
Enter value for table_name: MDW_TRACE
old   2: select constraint_name from user_constraints where table_name = upper('&table_name'))
new   2: select constraint_name from user_constraints where table_name = upper('MDW_TRACE'))

CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------
MDW_TRACE_PARAMETER_FK_TRACE   MDW_TRACE_PARAMETER

1 row selected.

Dans mon cas la table référençant ma table à tuncater se nomme MDW_TRACE_PARAMETER et la clé étrangère MDW_TRACE_PARAMETER_FK_TRACE. Je peux donc maintenant désactiver cette contrainte pour effectuer mon TRUNCATE:

SQL> alter table MDW_TRACE_PARAMETER disable constraint MDW_TRACE_PARAMETER_FK_TRACE;

Table altered.

SQL> truncate table mdw_trace;

Table truncated.

J'ai pu truncater ma table.
Il ne me reste enfin plus qu'à réactiver la contrainte:

SQL> alter table MDW_TRACE_PARAMETER enable constraint MDW_TRACE_PARAMETER_FK_TRACE;

Table altered.

Problème de performances avec un UPDATE

Un problème de performance m’a été remonté cette semaine sur une procédure stockée qui consiste à enchainer une série d’updates telles que celle-ci-dessous :

UPDATE CALC_PRELOAD_DECADEDOWN 
   SET (STRAT_LIQUIDITY) = 
       (SELECT DDN_STRAT_LIQUIDITY 
          FROM VMUT_DECADEDOWN, MUT_OBJECTS 
         WHERE OBJ_ID = DDN_ID 
           AND OBJ_PARENT_ID = TIT_ID) 
 WHERE SESSION_ID = :B3 
   AND LYX_DAY = :B2 
   AND SCE_ID = :B1 ; 

Cet update s'exécute en 45 minutes environ.
Comme la procédure est composée de plusieurs requêtes de ce type là, son exécution totale met plus de 7 heures.
Pour pouvoir reproduire l’exécution de l’update il m’a d’abord fallu récupérer la valeur des binds variables :B3, :B2 et :B1. Pour ce faire on peut utiliser la vue v$sql_bind_capture de la manière suivante :

col VALUE_STRING for A20 
select name,position,datatype_string,value_string 
from v$sql_bind_capture where sql_id='80x62t50u2z28';              
NAME                             POSITION DATATYPE_STRING VALUE_STRING 
------------------------------ ---------- --------------- -------------------- 
:B3                                     1 VARCHAR2(128)   1329633681977 
:B2                                     2 DATE            02/15/12 00:00:00 
:B1                                     3 NUMBER          926082116

La colonne VALUE_STRING nous donne les valeurs des binds variables récupérées par Oracle lors de la première exécution de la requête (voir Bind Variable peeking).
Pour reproduire le problème de lenteur il suffit de remplacer dans la requête les binds variables par les valeurs ci-dessus :

UPDATE CALC_PRELOAD_DECADEDOWN 
   SET (STRAT_LIQUIDITY) = 
       (SELECT DDN_STRAT_LIQUIDITY 
          FROM VMUT_DECADEDOWN, MUT_OBJECTS 
         WHERE OBJ_ID = DDN_ID 
           AND OBJ_PARENT_ID = TIT_ID) 
 WHERE SESSION_ID = '1329460869262' 
   AND LYX_DAY = to_date('02/15/12','MM/DD/YY') 
   AND SCE_ID = 926082116; 

47289 rows updated. 

Elapsed: 00:44:17.32 

SQL> @plan_last 

Plan hash value: 2387757449 
--------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                     | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
--------------------------------------------------------------------------------------------------------------------------------- 
|   0 | UPDATE STATEMENT              |                              |      1 |        |      0 |00:43:11.49 |    2464K|    622K| 
|   1 |  UPDATE                       | CALC_PRELOAD_DECADEDOWN      |      1 |        |      0 |00:43:11.49 |    2464K|    622K| 
|*  2 |   TABLE ACCESS BY INDEX ROWID | CALC_PRELOAD_DECADEDOWN      |      1 |    126 |  47289 |00:00:04.28 |   41510 |   2085 | 
|*  3 |    INDEX RANGE SCAN           | PK_PRELOAD_DECADOWN_BY_TYPE1 |      1 |    126 |  47289 |00:00:01.06 |     357 |    355 | 
|   4 |   NESTED LOOPS                |                              |  47289 |      6 |  16708 |00:43:03.75 |    2341K|    620K| 
|   5 |    TABLE ACCESS BY INDEX ROWID| MUT_OBJECTS                  |  47289 |      6 |   1227K|00:42:41.11 |    1097K|    620K| 
|*  6 |     INDEX RANGE SCAN          | IDX_PARENT_ID                |  47289 |      4 |   1227K|00:00:06.45 |     145K|     89 | 
|   7 |    TABLE ACCESS BY INDEX ROWID| MUT_DECADEDOWN               |   1227K|      1 |  16708 |00:00:20.01 |    1243K|      0 | 
|*  8 |     INDEX UNIQUE SCAN         | PK_MUT_DECADEDOWN            |   1227K|      1 |  16708 |00:00:14.27 |    1227K|      0 | 
--------------------------------------------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - filter("SCE_ID"=:SYS_B_3) 
   3 - access("SESSION_ID"=TO_NUMBER(:SYS_B_0) AND "LYX_DAY"=TO_DATE(:SYS_B_1,:SYS_B_2)) 
   6 - access("OBJ_PARENT_ID"=:B1) 
   8 - access("OBJ_ID"="DDN_ID") 

Note 
----- 
   - cpu costing is off (consider enabling it) 

On s’aperçoit que la requête a mis 44 minutes pour modifier 47289 lignes dans la table CALC_PRELOAD_DECADEDOWN.

En regardant le plan à partir de l’opération 4, on voit que le NESTED LOOP, qui correspond à la sous requête de l’update, génère 2.4 millions de logical reads. Une moitié de ces logical reads pour accéder à la table MUT_OBJECTS via l’index IDX_PARENT_ID et l’autre moitié pour accéder à la table MUT_DECADEDOWN via l’index PK_MUT_DECADEDOWN.

La colonne STARTS du plan pour l’opération 4 indique que le NESTED LOOP est exécuté 47289 fois. Ceci s’explique par le fait que l’update modifie 47289 lignes et donc pour chaque ligne modifiée Oracle doit exécuter la sous-requête. C’est ce qui explique le problème de performance pour cette requête. En effet, lorsque vous avez une sous requête qui est appelée pour déterminer la valeur de la ou des colonnes à modifier il faut avoir à l’esprit que cette sous requête sera appelée autant de fois qu’il y’a de lignes à modifier.

Comme cet update n’est qu’une requête parmi beaucoup d’autres dans la procédure appelée et que quasiment toutes les requêtes d’update se basent sur la jointure entre MUT_OBEJCTS et MUT_DECADEDOWN, la solution que j’ai adopté consiste à stocker le résultat de la jointure entre ces 2 tables au début de la procédure dans une table temporaire et d’appeler la table temporaire dans la sous requête. Le résultat de la jointure est ainsi matérialisé dans une table temporaire ce qui évite d’avoir à effectuer la jointure N fois (N étant le nombre de lignes modifiées par l’ensemble des requêtes de la procédure).

Appliquons le principe de la table temporaire à ma requête précédente

SQL> create global temporary table TMP_MUT_OBJECTS1 on commit delete rows 
  2  as 
  3  SELECT  ddn.*, obj.OBJ_PARENT_ID "OBJ_PARENT_ID" 
  4  FROM VMUT_DECADEDOWN ddn, MUT_OBJECTS obj 
  5  where 1=2; 

Table created. 

Elapsed: 00:00:00.20 

SQL> create index IDX_TMP_MUT_OBJECTS1 on TMP_MUT_OBJECTS1(OBJ_PARENT_ID); 

Index created. 

Elapsed: 00:00:00.03 

SQL>  Insert into TMP_MUT_OBJECTS1 SELECT  ddn.*, obj.OBJ_PARENT_ID 
  2  FROM VMUT_DECADEDOWN ddn, MUT_OBJECTS obj 
  3  where obj.OBJ_ID = ddn.DDN_ID; 

19344 rows created. 

Elapsed: 00:00:37.87 

L’insert dans ma table temporaire ne m’a coûté que 37 secondes.
Maintenant exécutons l’update avec la table temporaire remplaçant la jointure :

SQL> UPDATE CALC_PRELOAD_DECADEDOWN 
  2     SET (STRAT_LIQUIDITY) = 
  3         (SELECT DDN_STRAT_LIQUIDITY 
  4            FROM TMP_MUT_OBJECTS1         WHERE OBJ_PARENT_ID = TIT_ID) 
  5   WHERE SESSION_ID = '1329460869262' 
  6     AND LYX_DAY = to_date('02/15/12','MM/DD/YY') 
  7     AND SCE_ID = 926082116; 

47289 rows updated. 

Elapsed: 00:00:00.88 

Plan hash value: 999872190 
----------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
----------------------------------------------------------------------------------------------------------------------- 
|   0 | UPDATE STATEMENT             |                              |      1 |        |      0 |00:00:00.83 |     186K| 
|   1 |  UPDATE                      | CALC_PRELOAD_DECADEDOWN      |      1 |        |      0 |00:00:00.83 |     186K| 
|*  2 |   TABLE ACCESS BY INDEX ROWID| CALC_PRELOAD_DECADEDOWN      |      1 |    126 |  47289 |00:00:00.09 |   40968 | 
|*  3 |    INDEX RANGE SCAN          | PK_PRELOAD_DECADOWN_BY_TYPE1 |      1 |    126 |  47289 |00:00:00.01 |     357 | 
|   4 |   TABLE ACCESS BY INDEX ROWID| TMP_MUT_OBJECTS1             |  47289 |    179 |  16708 |00:00:00.26 |   64268 | 
|*  5 |    INDEX RANGE SCAN          | IDX_TMP_MUT_OBJECTS1         |  47289 |     72 |  16708 |00:00:00.18 |   47560 | 
----------------------------------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - filter("SCE_ID"=:SYS_B_3) 
   3 - access("SESSION_ID"=TO_NUMBER(:SYS_B_0) AND "LYX_DAY"=TO_DATE(:SYS_B_1,:SYS_B_2)) 
   5 - access("OBJ_PARENT_ID"=:B1) 

L’update s’exécute instantanément.
On est passé de 2.4 millions de logical reads à seulement 186K.
Cette méthode appliqué à l’ensemble des updates de la procédure stockée a fait passer l’exécution du traitement de 7h à seulement une dizaine de minutes.

Prédicats complexes et problèmes de performances

On m'a remonté ce matin un problème de performance sur la requête suivante:

SELECT *
  from fund                       fd,
       fund_share                 fs,
       fund_company_link          fcl,
       lst_company_role           lcr,
       lst_marketing_product      prod,
       lst_marketing_product_type prodType,
       company                    c
 where fd.id = fcl.fund_id
   AND fd.id = fs.fund_id
   AND fcl.company_role_id = lcr.id
   AND fcl.company_id = c.id
   AND (fd.stop_date IS NULL OR fd.stop_date >= sysdate)
   AND (fs.stop_date IS NULL OR fs.stop_date >= sysdate)
   AND lcr.label = 'Investment Advisor'
   AND fd.marketing_product_id = prod.ID
   AND prod.marketing_type_id = prodtype.ID
   AND prod.label IN
       ('Fund of Hedge funds (Lyxor HF)',
        'Fund of Hedge funds (external HF)',
        'Fund of Hedge funds (Mix External and Lyxor HF)')
   AND upper(c.NAME) NOT LIKE '%SANS IA%'
   AND upper(c.NAME) NOT LIKE '%LYXOR%'
   AND upper(c.NAME) NOT LIKE '%SOCI%GEN%';

Voici le plan associé:

Plan hash value: 2475769236

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |      1 |        |    206 |00:00:48.59 |    3933K|
|   1 |  NESTED LOOPS                    |                               |      1 |        |    206 |00:00:48.59 |    3933K|
|   2 |   NESTED LOOPS                   |                               |      1 |      1 |    206 |00:00:48.59 |    3932K|
|   3 |    NESTED LOOPS                  |                               |      1 |      1 |    205 |00:00:48.59 |    3932K|
|   4 |     NESTED LOOPS                 |                               |      1 |      1 |    205 |00:00:48.58 |    3932K|
|   5 |      NESTED LOOPS                |                               |      1 |      1 |    239 |00:00:48.58 |    3932K|
|*  6 |       HASH JOIN                  |                               |      1 |      3 |   1765 |00:00:48.57 |    3929K|
|   7 |        NESTED LOOPS              |                               |      1 |     46 |  59169 |00:00:48.53 |    3929K|
|*  8 |         TABLE ACCESS FULL        | COMPANY                       |      1 |      1 |  10854 |00:00:00.03 |     106 |
|*  9 |         INDEX FULL SCAN          | PK_FUND_COMPANY_LINK          |  10854 |     33 |  59169 |00:00:48.48 |    3929K|
|* 10 |        TABLE ACCESS FULL         | LST_COMPANY_ROLE              |      1 |      1 |      1 |00:00:00.01 |       7 |
|* 11 |       TABLE ACCESS BY INDEX ROWID| FUND                          |   1765 |      1 |    239 |00:00:00.01 |    3157 |
|* 12 |        INDEX UNIQUE SCAN         | PK_FUND                       |   1765 |      1 |   1765 |00:00:00.01 |    1766 |
|* 13 |      TABLE ACCESS BY INDEX ROWID | LST_MARKETING_PRODUCT         |    239 |      1 |    205 |00:00:00.01 |      33 |
|* 14 |       INDEX UNIQUE SCAN          | PK_LST_MARKETING_PRODUCT      |    239 |      1 |    239 |00:00:00.01 |      18 |
|  15 |     TABLE ACCESS BY INDEX ROWID  | LST_MARKETING_PRODUCT_TYPE    |    205 |      1 |    205 |00:00:00.01 |     222 |
|* 16 |      INDEX UNIQUE SCAN           | PK_LST_MARKETING_PRODUCT_TYPE |    205 |      1 |    205 |00:00:00.01 |      17 |
|* 17 |    INDEX RANGE SCAN              | IDX2_FUNDSHARE                |    205 |      3 |    206 |00:00:00.01 |     225 |
|* 18 |   TABLE ACCESS BY INDEX ROWID    | FUND_SHARE                    |    206 |      3 |    206 |00:00:00.01 |     162 |
----------------------------------------------------------------------------------------------------------------------------

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

   6 - access("FCL"."COMPANY_ROLE_ID"="LCR"."ID")
   8 - filter((UPPER("C"."NAME") NOT LIKE '%SANS IA%' AND UPPER("C"."NAME") NOT LIKE '%LYXOR%' AND
              UPPER("C"."NAME") NOT LIKE '%SOCI%GEN%'))
   9 - access("FCL"."COMPANY_ID"="C"."ID")
       filter("FCL"."COMPANY_ID"="C"."ID")
  10 - filter("LCR"."LABEL"='Investment Advisor')
  11 - filter(("FD"."MARKETING_PRODUCT_ID" IS NOT NULL AND ("FD"."STOP_DATE" IS NULL OR
              "FD"."STOP_DATE">=SYSDATE@!)))
  12 - access("FD"."ID"="FCL"."FUND_ID")
  13 - filter(("PROD"."LABEL"='Fund of Hedge funds (Lyxor HF)' OR "PROD"."LABEL"='Fund of Hedge funds (Mix External
              and Lyxor HF)' OR "PROD"."LABEL"='Fund of Hedge funds (external HF)'))
  14 - access("FD"."MARKETING_PRODUCT_ID"="PROD"."ID")
  16 - access("PROD"."MARKETING_TYPE_ID"="PRODTYPE"."ID")
  17 - access("FD"."ID"="FS"."FUND_ID")
  18 - filter(("FS"."STOP_DATE" IS NULL OR "FS"."STOP_DATE">=SYSDATE@!))

Le plan consomme 3.9 millions de logical I/Os. Le coût est essentiellement lié à l'opération  9 (INDEX FULL SCAN de PK_FUND_COMPANY_LINK). Ce surcoût est lié au fait que l'optimiseur se trompe lorsqu'il estime la cardinalité de la table COMPANY. En effet il considère que seulement une ligne sera retournée (colonne E-ROWS = 1)  et commence donc par accéder à cette table pour boucler une fois (selon son estimation) sur l'index PK_FUND_COMPANY_LINK. Le soucis c'est que la cardinalité réelle de la table COMPANY est 10854 (colonne A-ROWS) et donc l'accès à l'index PK_FUND_COMPANY_LINK se fait en fait 10854 fois ce qui va engendrer un nombre très élevé de logical reads (3929K pour l'opération 9). La colonne STARTS du plan indique le nombre de fois où l'opération est exécutée.

Le développeur en me remontant son soucis a remarqué que lorsqu'il enlevait le dernier filtre sur la table COMPANY (la dernière ligne de la requête), la requête s'exécutait instantanément:

SELECT *
  from fund                       fd,
       fund_share                 fs,
       fund_company_link          fcl,
       lst_company_role           lcr,
       lst_marketing_product      prod,
       lst_marketing_product_type prodType,
       company                    c
 where fd.id = fcl.fund_id
   AND fd.id = fs.fund_id
   AND fcl.company_role_id = lcr.id
   AND fcl.company_id = c.id
   AND (fd.stop_date IS NULL OR fd.stop_date >= sysdate)
   AND (fs.stop_date IS NULL OR fs.stop_date >= sysdate)
   AND lcr.label = 'Investment Advisor'
   AND fd.marketing_product_id = prod.ID
   AND prod.marketing_type_id = prodtype.ID
   AND prod.label IN
       ('Fund of Hedge funds (Lyxor HF)',
        'Fund of Hedge funds (external HF)',
        'Fund of Hedge funds (Mix External and Lyxor HF)')
   AND upper(c.NAME) NOT LIKE '%SANS IA%'
   AND upper(c.NAME) NOT LIKE '%LYXOR%';

Plan hash value: 3643913872
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |      1 |        |    209 |00:00:00.05 |    1426 |
|   1 |  NESTED LOOPS                    |                               |      1 |        |    209 |00:00:00.05 |    1426 |
|   2 |   NESTED LOOPS                   |                               |      1 |      6 |    209 |00:00:00.05 |    1295 |
|   3 |    NESTED LOOPS                  |                               |      1 |      2 |    208 |00:00:00.05 |    1082 |
|*  4 |     HASH JOIN                    |                               |      1 |      2 |    208 |00:00:00.05 |     857 |
|   5 |      NESTED LOOPS                |                               |      1 |        |    243 |00:00:00.04 |     848 |
|   6 |       NESTED LOOPS               |                               |      1 |     17 |   1775 |00:00:00.04 |     611 |
|*  7 |        HASH JOIN                 |                               |      1 |     61 |   1775 |00:00:00.04 |     476 |
|*  8 |         TABLE ACCESS FULL        | COMPANY                       |      1 |     28 |  10891 |00:00:00.02 |     106 |
|   9 |         NESTED LOOPS             |                               |      1 |   4176 |   2115 |00:00:00.01 |     370 |
|* 10 |          TABLE ACCESS FULL       | LST_COMPANY_ROLE              |      1 |      1 |      1 |00:00:00.01 |       6 |
|* 11 |          INDEX FULL SCAN         | PK_FUND_COMPANY_LINK          |      1 |   4176 |   2115 |00:00:00.01 |     364 |
|* 12 |        INDEX UNIQUE SCAN         | PK_FUND                       |   1775 |      1 |   1775 |00:00:00.01 |     135 |
|* 13 |       TABLE ACCESS BY INDEX ROWID| FUND                          |   1775 |      1 |    243 |00:00:00.01 |     237 |
|* 14 |      TABLE ACCESS FULL           | LST_MARKETING_PRODUCT         |      1 |      3 |      3 |00:00:00.01 |       9 |
|  15 |     TABLE ACCESS BY INDEX ROWID  | LST_MARKETING_PRODUCT_TYPE    |    208 |      1 |    208 |00:00:00.01 |     225 |
|* 16 |      INDEX UNIQUE SCAN           | PK_LST_MARKETING_PRODUCT_TYPE |    208 |      1 |    208 |00:00:00.01 |      17 |
|* 17 |    INDEX RANGE SCAN              | IDX2_FUNDSHARE                |    208 |      3 |    209 |00:00:00.01 |     213 |
|* 18 |   TABLE ACCESS BY INDEX ROWID    | FUND_SHARE                    |    209 |      3 |    209 |00:00:00.01 |     131 |
----------------------------------------------------------------------------------------------------------------------------

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

   4 - access("FD"."MARKETING_PRODUCT_ID"="PROD"."ID")
   7 - access("FCL"."COMPANY_ID"="C"."ID")
   8 - filter((UPPER("C"."NAME") NOT LIKE '%SANS IA%' AND UPPER("C"."NAME") NOT LIKE '%LYXOR%'))
  10 - filter("LCR"."LABEL"='Investment Advisor')
  11 - access("FCL"."COMPANY_ROLE_ID"="LCR"."ID")
       filter("FCL"."COMPANY_ROLE_ID"="LCR"."ID")
  12 - access("FD"."ID"="FCL"."FUND_ID")
  13 - filter(("FD"."MARKETING_PRODUCT_ID" IS NOT NULL AND ("FD"."STOP_DATE" IS NULL OR
              "FD"."STOP_DATE">=SYSDATE@!)))
  14 - filter(("PROD"."LABEL"='Fund of Hedge funds (Lyxor HF)' OR "PROD"."LABEL"='Fund of Hedge funds (Mix External
              and Lyxor HF)' OR "PROD"."LABEL"='Fund of Hedge funds (external HF)'))
  16 - access("PROD"."MARKETING_TYPE_ID"="PRODTYPE"."ID")
  17 - access("FD"."ID"="FS"."FUND_ID")
  18 - filter(("FS"."STOP_DATE" IS NULL OR "FS"."STOP_DATE">=SYSDATE@!))

Le plan est en effet bien meilleur puisque le nombre de logical I/Os n'est plus que de 1426 (au lieu de 3.9 millions) pour retourner plus de lignes.
Si on regarde de plus près le plan on constate que le CBO n'a cette fois pas choisi de commencer par la table COMPANY car il estime que l'accès à cette table va retourner 28 lignes. On est loin des 10891 lignes réellement retournées mais c'est en tout cas plus que la cardinalité de 1 estimé dans le premier plan. C'est cette différence qui fait que le CBO ne commence pas par accéder à la table COMPANY. Donc même si la source du problème est toujours présente, elle n'a dans ce cas pas la même incidence que dans le premier plan.

Voyons de plus près les 3 clauses sur la table COMPANY:

... 
AND upper(c.NAME) NOT LIKE '%SANS IA%'
AND upper(c.NAME) NOT LIKE '%LYXOR%' 
AND upper(c.NAME) NOT LIKE '%SOCI%GEN%';

Il s'agit ici de prédicats de type NOT LIKE '%%' avec une fonction UPPER appliquée à la colonne. Ce sont typiquement des clauses pour lesquelles les statistiques sur les colonnes ne peuvent aider le CBO à estimer la cardinalité retournée. Dans ce type de cas le CBO applique une séléctivité de 5% pour chaque prédicat (je vous renvoie au livre de Jonathan LEWIS pour approfondir le sujet ). Comme chacun des prédicats sont liés par des clauses "AND" il suffit de multiplier la séléctivité de chacun des prédicats pour obtenir la sélectivité totale. Ensuite on applique la sélectivité au nombre de lignes dans la table pour avoir la cardinalité estimée.
Exemple:
La colonne NUM_ROWS dans USER_TABLES pour la table COMPANY est égale à11093 lignes.
Pour la 2ème requête qui ne contenait que 2 prédicats sur la colonne NAME la cardinalité estimée est donc de: 11093*5%*5% = 27.73.
En arrondissant à l'entier supérieur on retrouve bien les 28 lignes du 2ème plan.
Si on applique la même logique à la 1ère requête qui contenait les 3 prédicats on obtient bien la cardinalité de 1 visible dans le 1er plan:
11093*5%*5%*5% = 1.26

Maintenant qu'on sait d'où vient le problème, comment peut-on faire pour aider l'optimiseur à choisir le bon plan?
Lorsque les filters predicates sont trop compliqués pour obtenir une bonne estimation à partir des stats objets à disposition du CBO, la meilleure solution consiste à utiliser le dynamic sampling pour la table concernée.
Le dynamic sampling consiste à appliquer les prédicats de la requête sur un échantillon de la table pour obtenir une cardinalité plus juste.
Pour appliquer le dynamic sampling il suffit d'utiliser le hint DYNAMIC_SAMPLING avec en paramètre l'alias de la table et le degré de dynamic sampling qu'on veut appliquer.

Voyons ce que donne le plan avec le dynamic sampling appliqué à la table COMPANY:

 SELECT /*+ DYNAMIC_SAMPLING(c 2) */
 *
  from fund                       fd,
       fund_share                 fs,
       fund_company_link          fcl,
       lst_company_role           lcr,
       lst_marketing_product      prod,
       lst_marketing_product_type prodType,
       company                    c
 where fd.id = fcl.fund_id
   AND fd.id = fs.fund_id
   AND fcl.company_role_id = lcr.id
   AND fcl.company_id = c.id
   AND (fd.stop_date IS NULL OR fd.stop_date >= sysdate)
   AND (fs.stop_date IS NULL OR fs.stop_date >= sysdate)
   AND lcr.label = 'Investment Advisor'
   AND fd.marketing_product_id = prod.ID
   AND prod.marketing_type_id = prodtype.ID
   AND prod.label IN
       ('Fund of Hedge funds (Lyxor HF)',
        'Fund of Hedge funds (external HF)',
        'Fund of Hedge funds (Mix External and Lyxor HF)')
   AND upper(c.NAME) NOT LIKE '%SANS IA%'
   AND upper(c.NAME) NOT LIKE '%LYXOR%'
   AND upper(c.NAME) NOT LIKE '%SOCI%GEN%';


Plan hash value: 2752978458

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                               |      1 |        |    206 |00:00:00.02 |    1895 |
|   1 |  NESTED LOOPS                      |                               |      1 |        |    206 |00:00:00.02 |    1895 |
|   2 |   NESTED LOOPS                     |                               |      1 |    393 |    206 |00:00:00.01 |    1759 |
|   3 |    NESTED LOOPS                    |                               |      1 |    119 |    205 |00:00:00.01 |    1544 |
|*  4 |     HASH JOIN                      |                               |      1 |    119 |    336 |00:00:00.01 |     860 |
|*  5 |      TABLE ACCESS FULL             | LST_COMPANY_ROLE              |      1 |      1 |      1 |00:00:00.01 |       6 |
|   6 |      NESTED LOOPS                  |                               |      1 |   1780 |   3952 |00:00:00.01 |     854 |
|   7 |       NESTED LOOPS                 |                               |      1 |    253 |    482 |00:00:00.01 |     326 |
|   8 |        MERGE JOIN                  |                               |      1 |      3 |      3 |00:00:00.01 |      10 |
|   9 |         TABLE ACCESS BY INDEX ROWID| LST_MARKETING_PRODUCT_TYPE    |      1 |     17 |      3 |00:00:00.01 |       4 |
|  10 |          INDEX FULL SCAN           | PK_LST_MARKETING_PRODUCT_TYPE |      1 |     17 |      3 |00:00:00.01 |       2 |
|* 11 |         SORT JOIN                  |                               |      3 |      3 |      3 |00:00:00.01 |       6 |
|* 12 |          TABLE ACCESS FULL         | LST_MARKETING_PRODUCT         |      1 |      3 |      3 |00:00:00.01 |       6 |
|* 13 |        TABLE ACCESS BY INDEX ROWID | FUND                          |      3 |     84 |    482 |00:00:00.01 |     316 |
|* 14 |         INDEX RANGE SCAN           | IDX14_FUND                    |      3 |    334 |    900 |00:00:00.01 |      23 |
|* 15 |       INDEX RANGE SCAN             | PK_FUND_COMPANY_LINK          |    482 |      7 |   3952 |00:00:00.01 |     528 |
|* 16 |     TABLE ACCESS BY INDEX ROWID    | COMPANY                       |    336 |      1 |    205 |00:00:00.01 |     684 |
|* 17 |      INDEX UNIQUE SCAN             | PK_COMPANY                    |    336 |      1 |    336 |00:00:00.01 |     348 |
|* 18 |    INDEX RANGE SCAN                | IDX2_FUNDSHARE                |    205 |      3 |    206 |00:00:00.01 |     215 |
|* 19 |   TABLE ACCESS BY INDEX ROWID      | FUND_SHARE                    |    206 |      3 |    206 |00:00:00.01 |     136 |
------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("FCL"."COMPANY_ROLE_ID"="LCR"."ID")
   5 - filter("LCR"."LABEL"='Investment Advisor')
  11 - access("PROD"."MARKETING_TYPE_ID"="PRODTYPE"."ID")
       filter("PROD"."MARKETING_TYPE_ID"="PRODTYPE"."ID")
  12 - filter(("PROD"."LABEL"='Fund of Hedge funds (Lyxor HF)' OR "PROD"."LABEL"='Fund of Hedge funds (Mix External
              and Lyxor HF)' OR "PROD"."LABEL"='Fund of Hedge funds (external HF)'))
  13 - filter(("FD"."STOP_DATE" IS NULL OR "FD"."STOP_DATE">=SYSDATE@!))
  14 - access("FD"."MARKETING_PRODUCT_ID"="PROD"."ID")
       filter("FD"."MARKETING_PRODUCT_ID" IS NOT NULL)
  15 - access("FD"."ID"="FCL"."FUND_ID")
  16 - filter((UPPER("C"."NAME") NOT LIKE '%SANS IA%' AND UPPER("C"."NAME") NOT LIKE '%LYXOR%' AND UPPER("C"."NAME")
              NOT LIKE '%SOCI%GEN%'))
  17 - access("FCL"."COMPANY_ID"="C"."ID")
  18 - access("FD"."ID"="FS"."FUND_ID")
  19 - filter(("FS"."STOP_DATE" IS NULL OR "FS"."STOP_DATE">=SYSDATE@!))

Note
-----
   - dynamic sampling used for this statement (level=2)

On constate que le plan est bien meilleur que le premier puisqu'on passe de 3.9 millions de logical reads à seulement 1895. On voit aussi que le CBO ne fait plus l'erreur d'accéder en premier à la table COMPANY.
La dernière partie du plan d'exécution montre bien que le dynamic sampling a bien été utilisé lors du parsing de cette requête:

Note
-----
   - dynamic sampling used for this statement (level=2)