jeudi 15 mars 2012

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.

vendredi 6 janvier 2012

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)

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.)