lundi 23 décembre 2013

Les statistiques étendues (2)

Il y'a un peu plus de 2 ans déjà j'avais rédigé un article décrivant le principe des statistiques étendues en 11g.
J'avais tenté d'expliquer comment les stats étendues pouvaient aider le CBO à estimer de meilleures cardinalités lorsqu'on avait des colonnes corrélées ou des expressions dans nos prédicats.

Ces derniers jours j'ai justement eu affaire à 2 problèmes de performances (sur des bases 11g) liés à l'absence de stats sur des colonnes corrélées et des fonctions appliquées à certaines colonnes.

Je me suis donc dit que ces 2 cas réels pouvaient constituer un second article permettant d'illustrer l'article que j'avais écrit.


Cas 1: Statistiques étendues sur une expression

Le premier problème concernait la requête suivante:
SELECT
   to_char(SYSDATE, 'YYYYMMDD') AS DATE_EXTRACTION,
   RBQ.RBQ_NUM_CONTR AS CPP_NUM_CONTR,
   RBQ.RBQ_NUM_CONTR,
   CB.CBQ_CD_ETABLISSEMENT,
   CB.CBQ_CD_GUICHET,
   CB.CBQ_CLE_COMPTE,
   RBQ.RBQ_CD_NAT_COORD_BQE,
   Substr(CB.CBQ_NUM_COMPTE,1,7) AS CBQ_NUM_COMPTE1,
   Substr(CB.CBQ_NUM_COMPTE,8,3) AS CBQ_NUM_COMPTE2,
   Substr(CB.CBQ_NUM_COMPTE,11,1) AS CBQ_NUM_COMPTE3 FROM
   TDO_D_ASSIN_COORD_BQE      CB,
   TDO_D_ASSIN_ROLE_COORD_BQE RBQ,
   TDO_D_ASSIN_CONTR_ASSU     CON,
   TDO_R_PDV_DISTRIB          PDVD
WHERE
   RBQ.RBQ_NUM_CONTR in  (select NUM_CONTRAT from    GIC_DEM_LISTE_CONTRATS  )
   AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '398'
   AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '399'
   AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '400'
   AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '412'
   --
   AND CB.CBQ_ID_PERSONNE = RBQ.RBQ_ID_PERSONNE
   AND CB.CBQ_ID_TEC_COORD_BQE = RBQ.RBQ_ID_TEC_COORD_BQE
   --
   AND CON_NUM_CONTR(+) = RBQ.RBQ_NUM_CONTR
   AND (      CON.CON_CD_PVE_SOUS IS NULL
            OR (       PDVD_CD_POINT_VENTE = CON.CON_CD_PVE_SOUS
                  AND PDVD_CD_DISTRIB = '400000'
                  )
            );
Cette requête existait depuis longtemps mais, suite à l'implémentation du partitioning sur certaines tables, elle s'était mise à tourner pendant des dizaines d'heures.
Même si la clause de partitioning n'était effectivement pas indiquée dans la clause WHERE de la requête (et ça c'est pas bien!!!) le client voulait quand même que cette requête puisse s'exécuter correctement en attendant que la correction soit apportée.
Dans la requête on peut noter les expressions suivantes:
substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '398'    AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '399'    AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '400'    AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '412'
En fait le critère de partitionnement est un champ qui correspond justement aux 3 premiers caractères du champ RBQ_NUM_CONTR manipulé dans les clauses ci-dessus.
Jetons un œil au plan d’exécution de la requête(la requête n'ayant pas pu aboutir j'ai uniquement le plan sans les stats d’exécutions):
----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |    89 | 12460 | 31322   (8)| 00:01:50 |       |       |
|   1 |  NESTED LOOPS                           |                              |    89 | 12460 | 31322   (8)| 00:01:50 |       |       |
|   2 |   NESTED LOOPS                          |                              |    89 | 12460 | 31322   (8)| 00:01:50 |       |       |
|*  3 |    HASH JOIN                            |                              |    89 |  7565 | 31056   (8)| 00:01:49 |       |       |
|   4 |     NESTED LOOPS                        |                              |    89 |  6675 | 31033   (8)| 00:01:49 |       |       |
|   5 |      NESTED LOOPS OUTER                 |                              |   115 |  7015 | 26689   (8)| 00:01:34 |       |       |
|   6 |       PARTITION LIST ALL                |                              |   115 |  5060 | 26459   (8)| 00:01:33 |     1 |   227 |
|*  7 |        TABLE ACCESS FULL                | TDO_D_ASSIN_ROLE_COORD_BQE   |   115 |  5060 | 26459   (8)| 00:01:33 |     1 |   227 |
|   8 |       TABLE ACCESS BY GLOBAL INDEX ROWID| TDO_D_ASSIN_CONTR_ASSU       |     1 |    17 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  9 |        INDEX UNIQUE SCAN                | PK_ASSIN_CON_NUM_CONTR       |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |      INDEX FAST FULL SCAN               | UK_TDO_R_PDV_DISTRIB         |     1 |    14 |    38   (6)| 00:00:01 |       |       |
|  11 |     INDEX FAST FULL SCAN                | PK_GIC_DEM_LISTE_CONTRATS    | 20000 |   195K|    22   (5)| 00:00:01 |       |       |
|* 12 |    INDEX RANGE SCAN                     | PK_ASSIN_CBQ_TEC_COORD_PERSO |     1 |       |     2   (0)| 00:00:01 |       |       |
|  13 |   TABLE ACCESS BY INDEX ROWID           | TDO_D_ASSIN_COORD_BQE        |     1 |    55 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------


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


   3 - access("RBQ"."RBQ_NUM_CONTR"="NUM_CONTRAT")
   7 - filter(SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'398' AND SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'399' AND
              SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'400' AND SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'412')
   9 - access("CON_NUM_CONTR"(+)="RBQ"."RBQ_NUM_CONTR")
  10 - filter("CON"."CON_CD_PVE_SOUS" IS NULL OR "PDVD_CD_POINT_VENTE"="CON"."CON_CD_PVE_SOUS" AND "PDVD_CD_DISTRIB"='400000')
  12 - access("CB"."CBQ_ID_PERSONNE"="RBQ"."RBQ_ID_PERSONNE" AND "CB"."CBQ_ID_TEC_COORD_BQE"="RBQ"."RBQ_ID_TEC_COORD_BQE")
On voit que le CBO a choisi d'attaquer en premier lieu la table TDO_D_ASSIN_ROLE_COORD_BQE en estimant que celle-ci retournerait (après avoir appliqué les critères avec la fonction SUBSTR) seulement 115 lignes .
Si on effectue un comptage sur cette table on se rend compte que le nombre de lignes retournées est en réalité de 18 millions:
select count(*) from TDO_D_ASSIN_ROLE_COORD_BQE RBQ
where substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '398'
AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '399'
AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '400'
AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '412';
   
  COUNT(*)
----------
  18449114
  
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |      1 |        |      1 |00:00:48.70 |   31949 |  31825 |
|   1 |  SORT AGGREGATE       |                        |      1 |      1 |      1 |00:00:48.70 |   31949 |  31825 |
|*  2 |   INDEX FAST FULL SCAN| CK_ASSIN_RBQ_NUM_CONTR |      1 |    115 |     18M|00:00:44.48 |   31949 |  31825 |
-------------------------------------------------------------------------------------------------------------------


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


   2 - filter((SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'398' AND SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'399' AND
              SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'400' AND SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'412'))
Puisque le CBO est incapable d'estimer une sélectivité correcte lorsqu'une fonction est appliquée à une colonne, d'où diable sort-il ces 115 lignes?
D'après le chapitre 5 du livre de Jonathan Lewis, l'optimiseur appliquerait une sélectivité de 5% pour chaque prédicat (avec expression) de type NOT EQUAL.
Vu qu'on a 4 prédicats on se retrouve au final avec une sélectivité de 0.05*0.05*0.05*0.05 = 0.00000625
La colonne NUM_ROWS de DBA_TABLES pour la table TDO_D_ASSIN_ROLE_COORD_BQE renvoyant 18 449 114 lignes, si on y applique la sélectivité précédente on obtient bien 115 lignes:
18449114*0.00000625 = 115.30

Cette mauvaise estimation conduit le CBO à choisir cette table comme table directrice du NESTED LOOP pour la joindre avec la table TDO_D_ASSIN_CONTR_ASSU.
Les estimations étant totalement biaisées c'est tout le reste du plan qui est faussé.
La solution en 11g consiste à calculer des statistiques étendues sur l'expression substr(RBQ.RBQ_NUM_CONTR, 1, 3):
BEGIN
 DBMS_STATS.gather_table_stats
 (ownname => 'ADWH00',
 tabname => 'TDO_D_ASSIN_ROLE_COORD_BQE',
 method_opt => 'FOR COLUMNS (substr(RBQ_NUM_CONTR, 1, 3)) size 1'
 );
END;
/
J'ai mis le paramètre d'instance ENABLE_DDL_LOGGING à TRUE et voici ce qu'on voit dans le fichier ALERT lorsqu'on calcule des stats étendues sur l'expression substr(RBQ_NUM_CONTR, 1, 3):
alter table "ADWH00"."TDO_D_ASSIN_ROLE_COORD_BQE" add (SYS_STUXQ$Y7DH65G7U2BC3$3Y_3NF as (substr(RBQ_NUM_CONTR, 1, 3)) virtual BY USER for statistics)

Le calcul de stats étendues sur l'expression génère en réalité la création d'une colonne virtuelle sur la table TDO_D_ASSIN_ROLE_COORD_BQE.

Une fois les stats étendues calculées (c-a-d une fois la colonne virtuelle créée), on obtient le plan suivant lorsqu'on exécute de nouveau la requête:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                              |      1 |        |  18229 |00:03:09.28 |     429K|    311K|       |       |          |
|   1 |  CONCATENATION                           |                              |      1 |        |  18229 |00:03:09.28 |     429K|    311K|       |       |          |
|*  2 |   HASH JOIN                              |                              |      1 |  20717 |  18229 |00:02:03.16 |     288K|    228K|  2494K|  1736K| 2540K (0)|
|*  3 |    INDEX FAST FULL SCAN                  | UK_TDO_R_PDV_DISTRIB         |      1 |  28501 |  28497 |00:00:00.02 |     167 |      0 |       |       |          |
|   4 |    NESTED LOOPS OUTER                    |                              |      1 |  20717 |  18229 |00:02:03.07 |     288K|    228K|       |       |          |
|*  5 |     HASH JOIN                            |                              |      1 |  20717 |  18229 |00:02:02.62 |     232K|    228K|  2327K|  1129K| 3297K (0)|
|*  6 |      HASH JOIN                           |                              |      1 |  20717 |  18261 |00:01:22.93 |   86307 |  83282 |  2129K|  2004K| 2289K (0)|
|   7 |       INDEX FAST FULL SCAN               | PK_GIC_DEM_LISTE_CONTRATS    |      1 |  20000 |  20000 |00:00:00.02 |    2718 |      0 |       |       |          |
|   8 |       PARTITION LIST ALL                 |                              |      1 |     18M|     18M|00:01:09.89 |   83589 |  83282 |       |       |          |
|*  9 |        TABLE ACCESS FULL                 | TDO_D_ASSIN_ROLE_COORD_BQE   |    227 |     18M|     18M|00:01:02.25 |   83589 |  83282 |       |       |          |
|  10 |      TABLE ACCESS FULL                   | TDO_D_ASSIN_COORD_BQE        |      1 |     16M|     16M|00:00:27.26 |     146K|    145K|       |       |          |
|  11 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TDO_D_ASSIN_CONTR_ASSU       |  18229 |      1 |  18229 |00:00:00.41 |   55907 |      0 |       |       |          |
|* 12 |      INDEX UNIQUE SCAN                   | PK_ASSIN_CON_NUM_CONTR       |  18229 |      1 |  18229 |00:00:00.23 |   37677 |      0 |       |       |          |
|  13 |   NESTED LOOPS                           |                              |      1 |      1 |      0 |00:01:06.09 |     141K|  83282 |       |       |          |
|  14 |    NESTED LOOPS                          |                              |      1 |      1 |      0 |00:01:06.09 |     141K|  83282 |       |       |          |
|  15 |     NESTED LOOPS                         |                              |      1 |      1 |      0 |00:01:06.09 |     141K|  83282 |       |       |          |
|* 16 |      FILTER                              |                              |      1 |        |      0 |00:01:06.09 |     141K|  83282 |       |       |          |
|  17 |       NESTED LOOPS OUTER                 |                              |      1 |      1 |  18261 |00:01:06.08 |     141K|  83282 |       |       |          |
|* 18 |        HASH JOIN                         |                              |      1 |  20717 |  18261 |00:01:05.80 |   86306 |  83282 |  2129K|  2004K| 2283K (0)|
|  19 |         INDEX FAST FULL SCAN             | PK_GIC_DEM_LISTE_CONTRATS    |      1 |  20000 |  20000 |00:00:00.02 |    2718 |      0 |       |       |          |
|  20 |         PARTITION LIST ALL               |                              |      1 |     18M|     18M|00:00:52.92 |   83588 |  83282 |       |       |          |
|* 21 |          TABLE ACCESS FULL               | TDO_D_ASSIN_ROLE_COORD_BQE   |    227 |     18M|     18M|00:00:45.32 |   83588 |  83282 |       |       |          |
|  22 |        TABLE ACCESS BY GLOBAL INDEX ROWID| TDO_D_ASSIN_CONTR_ASSU       |  18261 |      1 |  18261 |00:00:00.26 |   54798 |      0 |       |       |          |
|* 23 |         INDEX UNIQUE SCAN                | PK_ASSIN_CON_NUM_CONTR       |  18261 |      1 |  18261 |00:00:00.13 |   36537 |      0 |       |       |          |
|* 24 |      INDEX FAST FULL SCAN                | UK_TDO_R_PDV_DISTRIB         |      0 |  36746 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 25 |     INDEX RANGE SCAN                     | PK_ASSIN_CBQ_TEC_COORD_PERSO |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  26 |    TABLE ACCESS BY INDEX ROWID           | TDO_D_ASSIN_COORD_BQE        |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("PDVD_CD_POINT_VENTE"="CON"."CON_CD_PVE_SOUS")
   3 - filter("PDVD_CD_DISTRIB"='400000')
   5 - access("CB"."CBQ_ID_PERSONNE"="RBQ"."RBQ_ID_PERSONNE" AND "CB"."CBQ_ID_TEC_COORD_BQE"="RBQ"."RBQ_ID_TEC_COORD_BQE")
   6 - access("RBQ"."RBQ_NUM_CONTR"="NUM_CONTRAT")
   9 - filter((SUBSTR("RBQ_NUM_CONTR",1,3)<>'398' AND SUBSTR("RBQ_NUM_CONTR",1,3)<>'399' AND SUBSTR("RBQ_NUM_CONTR",1,3)<>'400' AND 
              SUBSTR("RBQ_NUM_CONTR",1,3)<>'412'))
  12 - access("CON_NUM_CONTR"="RBQ"."RBQ_NUM_CONTR")
  16 - filter("CON"."CON_CD_PVE_SOUS" IS NULL)
  18 - access("RBQ"."RBQ_NUM_CONTR"="NUM_CONTRAT")
  21 - filter((SUBSTR("RBQ_NUM_CONTR",1,3)<>'398' AND SUBSTR("RBQ_NUM_CONTR",1,3)<>'399' AND SUBSTR("RBQ_NUM_CONTR",1,3)<>'400' AND 
              SUBSTR("RBQ_NUM_CONTR",1,3)<>'412'))
  23 - access("CON_NUM_CONTR"="RBQ"."RBQ_NUM_CONTR")
  24 - filter((LNNVL("PDVD_CD_POINT_VENTE"="CON"."CON_CD_PVE_SOUS") OR LNNVL("PDVD_CD_DISTRIB"='400000')))
  25 - access("CB"."CBQ_ID_PERSONNE"="RBQ"."RBQ_ID_PERSONNE" AND "CB"."CBQ_ID_TEC_COORD_BQE"="RBQ"."RBQ_ID_TEC_COORD_BQE")

Tout d'abord on note que la requête s'exécute en 3 minutes (alors qu'elle n'aboutissait pas sans les stats étendues). Ensuite on voit que la cardinalité est bien estimée pour la table TDO_D_ASSIN_ROLE_COORD_BQE (18M pour les colonnes E-ROWS et A-ROWS).
Enfin, on constate que grâce à cette bonne estimation la table n'est plus attaquée via un NESTED LOOP mais que le CBO a opté pour un HASH JOIN tout à fait justifié.


Cas 2: Stats étendues sur des colonnes corrélées

Le second problème concernait la requête suivante qui mettait 2h34 pour s'exécuter:
SELECT DISTINCT
 FLC_DT_FIN_ECH_FLUX Date_arrete,
 ASS_NUM_CONTR_COLLECT_CNP Num_contrat,
 ASS_NUM_CONTRACTANT Id_coll,
 SUM (
 DECODE (ASS_NUM_CONTRACTANT,
 '00698', 12 * ADH_SOMME_MVT_RELATIFS_TTC,
 '77009', 12 * ADH_SOMME_MVT_RELATIFS_TTC,
 '90074', 12 * ADH_SOMME_MVT_RELATIFS_TTC,
 4 * ADH_SOMME_MVT_RELATIFS_TTC))
FROM odd_flux_cid a, odd_assure b, odd_info_adhesion d
where a.num_integration in (8118,8112,8069,8186,8148,8119,8094,8070,8187,8149,8120,8095,8071,8121,8096,8072,8188,8150,8122,8097,8073,8189,8151,8123,8098,8074,8190,8152,8125,8099,8075,8191,8153,8127,
8100,8192,8154,8129,8101,8077,8193,8155,8130,8102,8078,8194,8156,8131,8103,8079,8195,8158,8132,8104,8080,8197,8159,8133,8105,8081,8162,8134,8106,8082,8198,8163,8135,8107,8083,8200,8164,8136,8108,8084,
8137,8110,8085,8165,8138,8109,8086,8202,8166,8139,8111,8087)
 AND b.num_integration = d.num_integration
 AND ASS_ID_FLUX = ADH_ID_FLUX
 AND ASS_NUM_CCOLTE = ADH_NUM_CCOLTE
 AND ASS_NUM_REF_ASS = ADH_NUM_REF_ASS
 AND a.num_integration = b.num_integration
 AND ASS_ID_FLUX = flc_id_flux
 AND ASS_ID_DELEGATAIRE = flc_id_delegataire
GROUP BY FLC_DT_FIN_ECH_FLUX, ASS_NUM_CONTR_COLLECT_CNP, ASS_NUM_CONTRACTANT;




1469 rows selected.


Elapsed: 02:34:56.28


Plan hash value: 863059560


-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |      1 |        |   1469 |00:11:35.38 |      13M|    392K|       |       |          |
|   1 |  HASH GROUP BY                         |                      |      1 |  11546 |   1469 |00:11:35.38 |      13M|    392K|   855K|   855K| 2582K (0)|
|   2 |   NESTED LOOPS                         |                      |      1 |        |     11M|02:34:29.63 |      13M|    392K|       |       |          |
|   3 |    NESTED LOOPS                        |                      |      1 |  11546 |     11M|02:32:29.42 |    2327K|    258K|       |       |          |
|   4 |     NESTED LOOPS                       |                      |      1 |   7504 |   7949K|00:01:24.63 |    1442K|    184K|       |       |          |
|   5 |      PARTITION RANGE INLIST            |                      |      1 |   1310 |     86 |00:00:01.02 |     173 |    172 |       |       |          |
|*  6 |       TABLE ACCESS FULL                | ODD_FLUX_CID         |     86 |   1310 |     86 |00:00:01.02 |     173 |    172 |       |       |          |
|   7 |      PARTITION RANGE AND               |                      |     86 |      6 |   7949K|00:01:20.55 |    1442K|    184K|       |       |          |
|*  8 |       TABLE ACCESS BY LOCAL INDEX ROWID| ODD_ASSURE           |     86 |      6 |   7949K|00:01:17.40 |    1442K|    184K|       |       |          |
|*  9 |        INDEX RANGE SCAN                | PK_ASS_ASSURE        |     86 |     56 |   7949K|00:00:17.86 |   34929 |  34746 |       |       |          |
|  10 |     PARTITION RANGE AND                |                      |   7949K|      1 |     11M|02:30:54.44 |     884K|  74033 |       |       |          |
|* 11 |      INDEX RANGE SCAN                  | PK_ADH_INFO_ADHESION |   7949K|      1 |     11M|00:01:04.85 |     884K|  74033 |       |       |          |
|  12 |    TABLE ACCESS BY LOCAL INDEX ROWID   | ODD_INFO_ADHESION    |     11M|      2 |     11M|00:01:46.81 |      11M|    134K|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------


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


   6 - filter(("A"."NUM_INTEGRATION"=8069 OR "A"."NUM_INTEGRATION"=8070 OR "A"."NUM_INTEGRATION"=8071 OR "A"."NUM_INTEGRATION"=8072 OR
              "A"."NUM_INTEGRATION"=8073 OR "A"."NUM_INTEGRATION"=8074 OR "A"."NUM_INTEGRATION"=8075 OR "A"."NUM_INTEGRATION"=8077 OR "A"."NUM_INTEGRATION"=8078
              OR "A"."NUM_INTEGRATION"=8079 OR "A"."NUM_INTEGRATION"=8080 OR "A"."NUM_INTEGRATION"=8081 OR "A"."NUM_INTEGRATION"=8082 OR
              "A"."NUM_INTEGRATION"=8083 OR "A"."NUM_INTEGRATION"=8084 OR "A"."NUM_INTEGRATION"=8085 OR "A"."NUM_INTEGRATION"=8086 OR "A"."NUM_INTEGRATION"=8087
              OR "A"."NUM_INTEGRATION"=8094 OR "A"."NUM_INTEGRATION"=8095 OR "A"."NUM_INTEGRATION"=8096 OR "A"."NUM_INTEGRATION"=8097 OR
              "A"."NUM_INTEGRATION"=8098 OR "A"."NUM_INTEGRATION"=8099 OR "A"."NUM_INTEGRATION"=8100 OR "A"."NUM_INTEGRATION"=8101 OR "A"."NUM_INTEGRATION"=8102
              OR "A"."NUM_INTEGRATION"=8103 OR "A"."NUM_INTEGRATION"=8104 OR "A"."NUM_INTEGRATION"=8105 OR "A"."NUM_INTEGRATION"=8106 OR
              "A"."NUM_INTEGRATION"=8107 OR "A"."NUM_INTEGRATION"=8108 OR "A"."NUM_INTEGRATION"=8109 OR "A"."NUM_INTEGRATION"=8110 OR "A"."NUM_INTEGRATION"=8111
              OR "A"."NUM_INTEGRATION"=8112 OR "A"."NUM_INTEGRATION"=8118 OR "A"."NUM_INTEGRATION"=8119 OR "A"."NUM_INTEGRATION"=8120 OR
              "A"."NUM_INTEGRATION"=8121 OR "A"."NUM_INTEGRATION"=8122 OR "A"."NUM_INTEGRATION"=8123 OR "A"."NUM_INTEGRATION"=8125 OR "A"."NUM_INTEGRATION"=8127
              OR "A"."NUM_INTEGRATION"=8129 OR "A"."NUM_INTEGRATION"=8130 OR "A"."NUM_INTEGRATION"=8131 OR "A"."NUM_INTEGRATION"=8132 OR
              "A"."NUM_INTEGRATION"=8133 OR "A"."NUM_INTEGRATION"=8134 OR "A"."NUM_INTEGRATION"=8135 OR "A"."NUM_INTEGRATION"=8136 OR "A"."NUM_INTEGRATION"=8137
              OR "A"."NUM_INTEGRATION"=8138 OR "A"."NUM_INTEGRATION"=8139 OR "A"."NUM_INTEGRATION"=8148 OR "A"."NUM_INTEGRATION"=8149 OR
              "A"."NUM_INTEGRATION"=8150 OR "A"."NUM_INTEGRATION"=8151 OR "A"."NUM_INTEGRATION"=8152 OR "A"."NUM_INTEGRATION"=8153 OR "A"."NUM_INTEGRATION"=8154
              OR "A"."NUM_INTEGRATION"=8155 OR "A"."NUM_INTEGRATION"=8156 OR "A"."NUM_INTEGRATION"=8158 OR "A"."NUM_INTEGRATION"=8159 OR
              "A"."NUM_INTEGRATION"=8162 OR "A"."NUM_INTEGRATION"=8163 OR "A"."NUM_INTEGRATION"=8164 OR "A"."NUM_INTEGRATION"=8165 OR "A"."NUM_INTEGRATION"=8166
              OR "A"."NUM_INTEGRATION"=8186 OR "A"."NUM_INTEGRATION"=8187 OR "A"."NUM_INTEGRATION"=8188 OR "A"."NUM_INTEGRATION"=8189 OR
              "A"."NUM_INTEGRATION"=8190 OR "A"."NUM_INTEGRATION"=8191 OR "A"."NUM_INTEGRATION"=8192 OR "A"."NUM_INTEGRATION"=8193 OR "A"."NUM_INTEGRATION"=8194
              OR "A"."NUM_INTEGRATION"=8195 OR "A"."NUM_INTEGRATION"=8197 OR "A"."NUM_INTEGRATION"=8198 OR "A"."NUM_INTEGRATION"=8200 OR
              "A"."NUM_INTEGRATION"=8202))
   8 - filter("ASS_ID_DELEGATAIRE"="FLC_ID_DELEGATAIRE")
   9 - access("A"."NUM_INTEGRATION"="B"."NUM_INTEGRATION" AND "ASS_ID_FLUX"="FLC_ID_FLUX")
       filter(("B"."NUM_INTEGRATION"=8069 OR "B"."NUM_INTEGRATION"=8070 OR "B"."NUM_INTEGRATION"=8071 OR "B"."NUM_INTEGRATION"=8072 OR
              "B"."NUM_INTEGRATION"=8073 OR "B"."NUM_INTEGRATION"=8074 OR "B"."NUM_INTEGRATION"=8075 OR "B"."NUM_INTEGRATION"=8077 OR "B"."NUM_INTEGRATION"=8078
              OR "B"."NUM_INTEGRATION"=8079 OR "B"."NUM_INTEGRATION"=8080 OR "B"."NUM_INTEGRATION"=8081 OR "B"."NUM_INTEGRATION"=8082 OR
              "B"."NUM_INTEGRATION"=8083 OR "B"."NUM_INTEGRATION"=8084 OR "B"."NUM_INTEGRATION"=8085 OR "B"."NUM_INTEGRATION"=8086 OR "B"."NUM_INTEGRATION"=8087
              OR "B"."NUM_INTEGRATION"=8094 OR "B"."NUM_INTEGRATION"=8095 OR "B"."NUM_INTEGRATION"=8096 OR "B"."NUM_INTEGRATION"=8097 OR
              "B"."NUM_INTEGRATION"=8098 OR "B"."NUM_INTEGRATION"=8099 OR "B"."NUM_INTEGRATION"=8100 OR "B"."NUM_INTEGRATION"=8101 OR "B"."NUM_INTEGRATION"=8102
              OR "B"."NUM_INTEGRATION"=8103 OR "B"."NUM_INTEGRATION"=8104 OR "B"."NUM_INTEGRATION"=8105 OR "B"."NUM_INTEGRATION"=8106 OR
              "B"."NUM_INTEGRATION"=8107 OR "B"."NUM_INTEGRATION"=8108 OR "B"."NUM_INTEGRATION"=8109 OR "B"."NUM_INTEGRATION"=8110 OR "B"."NUM_INTEGRATION"=8111
              OR "B"."NUM_INTEGRATION"=8112 OR "B"."NUM_INTEGRATION"=8118 OR "B"."NUM_INTEGRATION"=8119 OR "B"."NUM_INTEGRATION"=8120 OR
              "B"."NUM_INTEGRATION"=8121 OR "B"."NUM_INTEGRATION"=8122 OR "B"."NUM_INTEGRATION"=8123 OR "B"."NUM_INTEGRATION"=8125 OR "B"."NUM_INTEGRATION"=8127
              OR "B"."NUM_INTEGRATION"=8129 OR "B"."NUM_INTEGRATION"=8130 OR "B"."NUM_INTEGRATION"=8131 OR "B"."NUM_INTEGRATION"=8132 OR
              "B"."NUM_INTEGRATION"=8133 OR "B"."NUM_INTEGRATION"=8134 OR "B"."NUM_INTEGRATION"=8135 OR "B"."NUM_INTEGRATION"=8136 OR "B"."NUM_INTEGRATION"=8137
              OR "B"."NUM_INTEGRATION"=8138 OR "B"."NUM_INTEGRATION"=8139 OR "B"."NUM_INTEGRATION"=8148 OR "B"."NUM_INTEGRATION"=8149 OR
              "B"."NUM_INTEGRATION"=8150 OR "B"."NUM_INTEGRATION"=8151 OR "B"."NUM_INTEGRATION"=8152 OR "B"."NUM_INTEGRATION"=8153 OR "B"."NUM_INTEGRATION"=8154
              OR "B"."NUM_INTEGRATION"=8155 OR "B"."NUM_INTEGRATION"=8156 OR "B"."NUM_INTEGRATION"=8158 OR "B"."NUM_INTEGRATION"=8159 OR
              "B"."NUM_INTEGRATION"=8162 OR "B"."NUM_INTEGRATION"=8163 OR "B"."NUM_INTEGRATION"=8164 OR "B"."NUM_INTEGRATION"=8165 OR "B"."NUM_INTEGRATION"=8166
              OR "B"."NUM_INTEGRATION"=8186 OR "B"."NUM_INTEGRATION"=8187 OR "B"."NUM_INTEGRATION"=8188 OR "B"."NUM_INTEGRATION"=8189 OR
              "B"."NUM_INTEGRATION"=8190 OR "B"."NUM_INTEGRATION"=8191 OR "B"."NUM_INTEGRATION"=8192 OR "B"."NUM_INTEGRATION"=8193 OR "B"."NUM_INTEGRATION"=8194
              OR "B"."NUM_INTEGRATION"=8195 OR "B"."NUM_INTEGRATION"=8197 OR "B"."NUM_INTEGRATION"=8198 OR "B"."NUM_INTEGRATION"=8200 OR
              "B"."NUM_INTEGRATION"=8202))
  11 - access("B"."NUM_INTEGRATION"="D"."NUM_INTEGRATION" AND "ASS_ID_FLUX"="ADH_ID_FLUX" AND "ASS_NUM_CCOLTE"="ADH_NUM_CCOLTE" AND
              "ASS_NUM_REF_ASS"="ADH_NUM_REF_ASS")
       filter(("D"."NUM_INTEGRATION"=8069 OR "D"."NUM_INTEGRATION"=8070 OR "D"."NUM_INTEGRATION"=8071 OR "D"."NUM_INTEGRATION"=8072 OR
              "D"."NUM_INTEGRATION"=8073 OR "D"."NUM_INTEGRATION"=8074 OR "D"."NUM_INTEGRATION"=8075 OR "D"."NUM_INTEGRATION"=8077 OR "D"."NUM_INTEGRATION"=8078
              OR "D"."NUM_INTEGRATION"=8079 OR "D"."NUM_INTEGRATION"=8080 OR "D"."NUM_INTEGRATION"=8081 OR "D"."NUM_INTEGRATION"=8082 OR
              "D"."NUM_INTEGRATION"=8083 OR "D"."NUM_INTEGRATION"=8084 OR "D"."NUM_INTEGRATION"=8085 OR "D"."NUM_INTEGRATION"=8086 OR "D"."NUM_INTEGRATION"=8087
              OR "D"."NUM_INTEGRATION"=8094 OR "D"."NUM_INTEGRATION"=8095 OR "D"."NUM_INTEGRATION"=8096 OR "D"."NUM_INTEGRATION"=8097 OR
              "D"."NUM_INTEGRATION"=8098 OR "D"."NUM_INTEGRATION"=8099 OR "D"."NUM_INTEGRATION"=8100 OR "D"."NUM_INTEGRATION"=8101 OR "D"."NUM_INTEGRATION"=8102
              OR "D"."NUM_INTEGRATION"=8103 OR "D"."NUM_INTEGRATION"=8104 OR "D"."NUM_INTEGRATION"=8105 OR "D"."NUM_INTEGRATION"=8106 OR
              "D"."NUM_INTEGRATION"=8107 OR "D"."NUM_INTEGRATION"=8108 OR "D"."NUM_INTEGRATION"=8109 OR "D"."NUM_INTEGRATION"=8110 OR "D"."NUM_INTEGRATION"=8111
              OR "D"."NUM_INTEGRATION"=8112 OR "D"."NUM_INTEGRATION"=8118 OR "D"."NUM_INTEGRATION"=8119 OR "D"."NUM_INTEGRATION"=8120 OR
              "D"."NUM_INTEGRATION"=8121 OR "D"."NUM_INTEGRATION"=8122 OR "D"."NUM_INTEGRATION"=8123 OR "D"."NUM_INTEGRATION"=8125 OR "D"."NUM_INTEGRATION"=8127
              OR "D"."NUM_INTEGRATION"=8129 OR "D"."NUM_INTEGRATION"=8130 OR "D"."NUM_INTEGRATION"=8131 OR "D"."NUM_INTEGRATION"=8132 OR
              "D"."NUM_INTEGRATION"=8133 OR "D"."NUM_INTEGRATION"=8134 OR "D"."NUM_INTEGRATION"=8135 OR "D"."NUM_INTEGRATION"=8136 OR "D"."NUM_INTEGRATION"=8137
              OR "D"."NUM_INTEGRATION"=8138 OR "D"."NUM_INTEGRATION"=8139 OR "D"."NUM_INTEGRATION"=8148 OR "D"."NUM_INTEGRATION"=8149 OR
              "D"."NUM_INTEGRATION"=8150 OR "D"."NUM_INTEGRATION"=8151 OR "D"."NUM_INTEGRATION"=8152 OR "D"."NUM_INTEGRATION"=8153 OR "D"."NUM_INTEGRATION"=8154
              OR "D"."NUM_INTEGRATION"=8155 OR "D"."NUM_INTEGRATION"=8156 OR "D"."NUM_INTEGRATION"=8158 OR "D"."NUM_INTEGRATION"=8159 OR
              "D"."NUM_INTEGRATION"=8162 OR "D"."NUM_INTEGRATION"=8163 OR "D"."NUM_INTEGRATION"=8164 OR "D"."NUM_INTEGRATION"=8165 OR "D"."NUM_INTEGRATION"=8166
              OR "D"."NUM_INTEGRATION"=8186 OR "D"."NUM_INTEGRATION"=8187 OR "D"."NUM_INTEGRATION"=8188 OR "D"."NUM_INTEGRATION"=8189 OR
              "D"."NUM_INTEGRATION"=8190 OR "D"."NUM_INTEGRATION"=8191 OR "D"."NUM_INTEGRATION"=8192 OR "D"."NUM_INTEGRATION"=8193 OR "D"."NUM_INTEGRATION"=8194
              OR "D"."NUM_INTEGRATION"=8195 OR "D"."NUM_INTEGRATION"=8197 OR "D"."NUM_INTEGRATION"=8198 OR "D"."NUM_INTEGRATION"=8200 OR
              "D"."NUM_INTEGRATION"=8202))
Si on regarde le plan on voit que sur les 2h34 d'exécution on a 2h30 passées sur l'accès à la table ODD_INFO_ADHESION via l'opération "PARTITION RANGE AND".
Cette opération est exécutée 7949000 fois (cf. colonne STARTS) car la jointure précédente entre les tables ODD_FLUX_CID et ODD_ASSURE retourne 7949000 lignes (cf. opération 4 du plan).
Si on regarde la colonne E-ROWS on voit que l'optimiseur estime que la jointure ne retournerait que 7504 lignes. C'est cette mauvaise estimation qui induit derrière le NESTED LOOP hyper couteux sur la table ODD_INFO_ADHESION.
La jointure avec la table ODD_ASSURE s'effectue sur les colonnes NUM_INTEGRATION, ASS_ID_FLUX et ASS_ID_DELEGATAIRE.
Effectuons un comptage sur la table ODD_ASSURE juste avec la clause NUM_INTEGRATION:
select count(*) from ODD_ASSURE b
where b.num_integration in (8118,8112,8069,8186,8148,8119,8094,8070,8187,8149,8120,8095,8071,8121,8096,8072,8188,8150,8122,8097,8073,8189,8151,8123,8098,8074,8190,8152,8125,8099,8075,8191,8153,8127,
   8100,8192,8154,8129,8101,8077,8193,8155,8130,8102,8078,8194,8156,8131,8103,8079,8195,8158,8132,8104,8080,8197,8159,8133,8105,8081,8162,8134,8106,8082,8198,8163,8135,8107,8083,8200,8164,8136,8108,8084,
   8137,8110,8085,8165,8138,8109,8086,8202,8166,8139,8111,8087);
   
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |      1 |00:00:12.96 |   34926 |  34925 |
|   1 |  SORT AGGREGATE            |               |      1 |      1 |      1 |00:00:12.96 |   34926 |  34925 |
|   2 |   INLIST ITERATOR          |               |      1 |        |   7949K|00:00:11.72 |   34926 |  34925 |
|   3 |    PARTITION RANGE ITERATOR|               |     86 |   7967K|   7949K|00:00:09.01 |   34926 |  34925 |
|*  4 |     INDEX RANGE SCAN       | PK_ASS_ASSURE |     86 |   7967K|   7949K|00:00:06.37 |   34926 |  34925 |
---------------------------------------------------------------------------------------------------------------
On voit que le nombre de lignes retournées est toujours de 7949K alors que cette fois on a utilisé uniqument la colonne NUM_INTEGRATION.
Il semble que les 2 autres colonnes n'influent pas sur la cardinalité. Cela revient donc à dire qu'il existe une corrélation entre les 3 colonnes NUM_INTEGRATION, ASS_ID_FLUX et ASS_ID_DELEGATAIRE.
Il serait donc intéressant de calculer des stats étendues pour la table ODD_ASSURE sur ces 3 colonnes ainsi que pour la table ODD_FLUX_CID:
BEGIN
 DBMS_STATS.gather_table_stats('AODD02','ODD_ASSURE',
 method_opt => 'FOR COLUMNS (NUM_INTEGRATION,ASS_ID_FLUX,ASS_ID_DELEGATAIRE) size 1',
 NO_INVALIDATE => FALSE,
 FORCE => TRUE
 );
END;
/


BEGIN
 DBMS_STATS.gather_table_stats('AODD02','ODD_FLUX_CID',
 method_opt => 'FOR COLUMNS (NUM_INTEGRATION,FLC_ID_FLUX,FLC_ID_DELEGATAIRE) size 1',
 NO_INVALIDATE => FALSE,
 FORCE => TRUE
 );
END;
/
Si on relance la requête on obtient désormais le plan suivant:
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |      1 |        |   1469 |00:01:35.85 |     283K|    288K|   5872 |       |       |          |         |
|   1 |  HASH GROUP BY        |                   |      1 |    355K|   1469 |00:01:35.85 |     283K|    288K|   5872 |   855K|   855K|   17M (0)|         |
|   2 |   PARTITION RANGE AND |                   |      1 |     53M|     11M|00:01:19.64 |     283K|    288K|   5872 |       |       |          |         |
|*  3 |    HASH JOIN          |                   |     86 |     53M|     11M|00:01:15.20 |     283K|    288K|   5872 |   972K|   972K|  372K (0)|         |
|*  4 |     TABLE ACCESS FULL | ODD_FLUX_CID      |     86 |   1322 |     86 |00:00:00.52 |     172 |    172 |      0 |       |       |          |         |
|*  5 |     HASH JOIN         |                   |     86 |     53M|     11M|00:01:01.45 |     282K|    288K|   5872 |  1889M|    35M|   20M (0)|   12288 |
|*  6 |      TABLE ACCESS FULL| ODD_ASSURE        |     86 |     37M|   7949K|00:00:11.45 |     146K|    146K|      0 |       |       |          |         |
|*  7 |      TABLE ACCESS FULL| ODD_INFO_ADHESION |     86 |     54M|     11M|00:00:19.97 |     136K|    136K|      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Les stats étendues ont permis d'obtenir une cardinalité pas tout à fait exact mais en tout cas plus proche de la réalité.
Les NESTED LOOP ont laissé place à des HASH JOIN ce qui permet de faire tourner la requête en 1 minute 35 au lieu de 2h34.

J'espère que ces 2 exemples tirés de la réalité vous permettront de comprendre comment grâce aux statistiques étendues on peut aider l'optimiseur à avoir une connaissance plus intelligente des données et ainsi lui permettre de nous trouver un plan adequat.

mardi 10 décembre 2013

Impact du Clustering Factor dans la mise à jour de tables


J'ai eu un petit débat avec mes collègues DBAs la semaine dernière sur le fait que le clustering factor d'un index pouvait expliquer de mauvaises performances sur la mise à jour d'une table. Pour eux, le clustering factor n'impactait que l'opération RANGE SCAN de l'index lors d'un SELECT, alors que j’avançais l'idée que les mauvaises performances d'un insert par exemple pouvait être lié au fait d'avoir un index (à mettre à jour suite à la mise à jour de la table), et que cette mise à jour générait plus de logical reads selon la qualité du clustering factor (CF).

Je n'ai donc pas pu résister à l'idée de faire un petit test case pour le leur prouver.

On commence par créér une table T1 avec 2 colonnes. La table sera ordonnée selon la première colonne tandis que la 2ème colonne sera l'inverse de la première colonne.

Bien sûr on va créer un index sur chacune des 2 colonnes et vous vous en doutez surement, la première colonne aura un clustering factor très bon c-a-d proche du nombre de blocs de la table (car l'ordre d'insertion dans la table correspond aux entrées du 1er index) tandis que la seconde colonne aura un CF très mauvais c’est-à-dire proche du nombre de lignes.
SQL> CREATE TABLE T1
2  AS
3  SELECT LPAD (object_id, 10, '0') AS C1, reverse (LPAD (object_id, 10, '0')) AS C2
4   FROM dba_objects where 1=2;

Table created.

SQL> create index idx1 on T1(C1);

Index created.

SQL> create index idx2 on T1(C2);

Index created.

Pour l'instant la table ne contient pas de lignes.
On va ensuite insérer les lignes (insert en mode conventionnel) et on va regarder le nombre de logical reads générés pour chaque index dans la vue V$SEGMENT_STATISTICS:
SQL> insert into T1
  2  SELECT LPAD (object_id, 10, '0') AS C1, reverse (LPAD (object_id, 10, '0')) AS C2
  3   FROM dba_objects
  4   order by 1;

57633 rows created.

SQL> commit;

Commit complete.

SQL> select statistic_name,object_name,value from v$segment_statistics
  2      where owner='APAE01' and object_name in ('T1','IDX1','IDX2') and value>0
  3      order by 1,2;

STATISTIC_NAME                                                   OBJECT_NAME                         VALUE
---------------------------------------------------------------- ------------------------------ ----------
db block changes                                                 IDX1                                 2272
db block changes                                                 IDX2                                59520
db block changes                                                 T1                                   1504
logical reads                                                    IDX1                                 3840
logical reads                                                    IDX2                               116752
logical reads                                                    T1                                   3328
space allocated                                                  IDX1                              2097152
space allocated                                                  IDX2                              3145728
space allocated                                                  T1                                2097152
space used                                                       IDX1                              1389672
space used                                                       IDX2                              1768402
space used                                                       T1                                1542726

BINGO!!!!
On s'aperçoit que le 2ème index a généré 30 fois plus de logical reads (116 752 vs 3 840)
Mais c'est en fait très logique: comme le premier index est trié selon le même ordre que les données insérées dans la table on a plus de chances d'insérer une entrée d'index dans le même bloc feuille que l'entrée précédente du coup on change beaucoup de moins de bloc à chaque mise à jour de l'index et on a ainsi moins de logical reads puisque le bloc feuille de l'index à modifier est déjà pinné dans le cache par le process server. A l'inverse, pour le 2ème index qui ne suit pas du tout l'ordre des données insérées dans la table, on aura à chaque nouvelle ligne insérée dans la table un nouveau logical read car la ligne a de fortes chances de se trouver dans un bloc feuille différent.

Un petit calcul de stats sur la table et ses index nous indique qu'effectivement l'index 1 a un clustering factor proche du nombre de blocs feuilles alors que l'index 2 a un CF proche du nombre de lignes:
SQL> exec dbms_stats.gather_table_stats('APAE01','T1',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select index_name,blevel,leaf_blocks,distinct_keys,clustering_factor,num_rows
2  from dba_indexes where index_name in ('IDX1','IDX2')
3  order by 1;

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ---------- ----------- ------------- ----------------- ----------
IDX1                                    1         160         57633               215      57633
IDX2                                    1         256         57633             57633      57633

La solution dans ce cas pour éviter le nombre important de logical reads consiste à effectuer un insert en mode DIRECT PATH car dans ce cas l'index est mis à jour seulement à la fin une fois que les données ont été triées pour chaque index.
SQL> insert /*+ APPEND */ into T1
  2  SELECT LPAD (object_id, 10, '0') AS C1, reverse (LPAD (object_id, 10, '0')) AS C2
3   FROM dba_objects
4   order by 1;

57633 rows created.

SQL> commit;

Commit complete.

SQL> select statistic_name,object_name,value from v$segment_statistics
2      where owner='APAE01' and object_name in ('T1','IDX1','IDX2') and value>0
3      order by 1,2;

STATISTIC_NAME                                                   OBJECT_NAME                         VALUE
---------------------------------------------------------------- ------------------------------ ----------
db block changes                                                 IDX1                                 6496
db block changes                                                 IDX2                                63488
db block changes                                                 T1                                   1536
logical reads                                                    IDX1                                 8944
logical reads                                                    IDX2                               122400
logical reads                                                    T1                                   3904
physical write requests                                          IDX1                                16
physical write requests                                          IDX2                                25
physical write requests                                          T1                                  31
physical writes                                                  IDX1                                  208
physical writes                                                  IDX2                                  336
physical writes                                                  T1                                    472
physical writes direct                                           T1                                    216
space allocated                                                  IDX1                              4194304
space allocated                                                  IDX2                              5242880
space allocated                                                  T1                                4194304
space used                                                       IDX1                              3004846
space used                                                       IDX2                              3326326
space used                                                       T1                                3312198


On voit en effet que le nombre de logical reads pour le 2ème index est passé de 116 752 à 122 400 soit seulement 5648 logical reads au lieu de 116 752 lors de l'insert en mode conventionnel pour le même nombre de lignes insérées.

CONCLUSION:

Il ne faut pas sous estimer le coût de maintenance d'un index notamment si cet index a un clustering factor qui est proche du nombre de lignes de la table.

mardi 19 novembre 2013

Attention au partitioning sans pruning

Ce matin mon client m'a remonté un problème sur 3 process qui tournaient depuis une dizaine d'heures sur une base venant juste d'être migrée en 11g R2 et dont les tables venaient d'être partitionnées.
En listant les sessions actives j'ai remarqué qu'effectivement on avait 3 requêtes qui tournaient respectivement depuis 33963, 36617 et 57709 secondes:
  SID PROG       ADDRESS          HASH_VALUE SQL_ID         CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME EVENT                SQL_TEXT
----- ---------- ---------------- ---------- ------------- ------ --------------- ---------- ----------- -------------------- -----------------------------------------
  606 sqlplus@ps C000000CE743FC08 1936645007 00jchz9tqxqwg      3      3981773947       3013       39.00 PL/SQL lock timer    SELECT DISTINCT H.GROUP# FROM SYS.WRI$_OP
  590 oracle@psp C000000CE743FC08 1936645007 00jchz9tqxqwg      3      3981773947       3013       39.00 PL/SQL lock timer    SELECT DISTINCT H.GROUP# FROM SYS.WRI$_OP
 1354 uvsh@psp20 C000000CE464D8A8 1685049779 8kgwdsdk6zndm      0       132338241          1   33,963.97 db file scattered re   SELECT  distinct  EVC.EVC_NUM_CONTR,
  408 uvsh@psp20 C0000002ADB65E28 3265361904 bwba60z1a2xzh      0       132338241          1   36,617.62 db file sequential r   SELECT  distinct  EVC.EVC_NUM_CONTR,
  969 uvsh@psp20 C000000C7E6776B0 1248359757 gwfkut956hxad      0       132338241          1   57,709.34 direct path read       SELECT  distinct  EVC.EVC_NUM_CONTR,
En regardant de plus près les 3 requêtes je me suis aperçu qu'elles étaient quasiment identiques.
Voilà justement à quoi ressemble une des 3 requêtes:
SELECT DISTINCT
EVC.EVC_NUM_CONTR,
--      NOM CLIENT par NI
(SELECT MAX (NVL (PPH.PPH_NOM_MARITAL, PPH.PPH_NOM_PATRONYMIQUE))
FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH,
TDO_D_ASSIN_ROLE_PERSONNE RPE
WHERE     RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
AND RPE.RPE_CD_ROLE LIKE 'O%'
AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION
AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION
AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE
AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN
(  SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR
FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2
WHERE RPE2.RPE_CD_ROLE LIKE 'O%'
GROUP BY rpe2.RPE_NUM_CONTR))
AS NOM_CONTRACTANT,
-- NOM personne morale
(SELECT PMO.PMO_RAISON_SOCIALE
FROM TDO_D_ASSIN_ROLE_PERSONNE RPE, TDO_D_ASSIN_PERS_MORA PMO
WHERE     RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION
AND PMO.NUM_INTEGRATION = RPE.NUM_INTEGRATION
AND PMO.PMO_ID_PERSONNE = RPE.RPE_ID_PERSONNE
AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN
(  SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR
FROM TDO_D_ASSIN_ROLE_PERSONNE RPE2
GROUP BY rpe2.RPE_NUM_CONTR))
AS NOM2_CONTRACTANT,
--      ID CLIENT par NI
(SELECT MAX (RPe.RPE_ID_PERSONNE)
FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE
WHERE     RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION
AND RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
AND RPE.RPE_CD_ROLE LIKE 'O%')
AS ID_CONTRACTANT,
--         ID CLIENT
(SELECT MAX (RPE.RPE_ID_PERSONNE)
FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE
WHERE     RPE.NUM_INTEGRATION <> EVC.NUM_INTEGRATION
AND RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
AND RPE.RPE_CD_ROLE LIKE 'O%')
AS ID2_CONTRACTANT,
--      PRENOM CLIENT par NI
(SELECT MAX (PPH.PPH_PRENOM)
FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH,
TDO_D_ASSIN_ROLE_PERSONNE RPE
WHERE     RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
AND RPE.RPE_CD_ROLE LIKE 'O%'
AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION
AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION
AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE
AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN
(  SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR
FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2
WHERE RPE2.RPE_CD_ROLE LIKE 'O%'
GROUP BY rpe2.RPE_NUM_CONTR))
AS PRENOM,
--      PRENOM CLIENT sans NI
(SELECT MAX (PPH.PPH_PRENOM)
FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH,
TDO_D_ASSIN_ROLE_PERSONNE RPE
WHERE     RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
AND RPE.RPE_CD_ROLE LIKE 'O%'
AND RPE.NUM_INTEGRATION <> EVC.NUM_INTEGRATION
AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION
AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE
AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN
(  SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR
FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2
WHERE RPE2.RPE_CD_ROLE LIKE 'O%'
GROUP BY rpe2.RPE_NUM_CONTR))
AS PRENOM2,
--      DATE NAISSANCE CLIENT par NI
(SELECT TO_CHAR (MAX (PPH.PPH_DT_NAISSANCE), 'YYYY-MM-DD HH24:MI:SS')
FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH,
TDO_D_ASSIN_ROLE_PERSONNE RPE
WHERE     RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
AND RPE.RPE_CD_ROLE LIKE 'O%'
AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION
AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION
AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE
AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN
(  SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR
FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2
WHERE RPE2.RPE_CD_ROLE LIKE 'O%'
GROUP BY rpe2.RPE_NUM_CONTR))
AS DT_NAISSANCE,
--      DATE NAISSANCE CLIENT sans NI
(SELECT TO_CHAR (MAX (PPH.PPH_DT_NAISSANCE), 'YYYY-MM-DD HH24:MI:SS')
FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH,
TDO_D_ASSIN_ROLE_PERSONNE RPE
WHERE     RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
AND RPE.RPE_CD_ROLE LIKE 'O%'
AND RPE.NUM_INTEGRATION <> EVC.NUM_INTEGRATION
AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION
AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE
AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN
(  SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR
FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2
WHERE RPE2.RPE_CD_ROLE LIKE 'O%'
GROUP BY rpe2.RPE_NUM_CONTR))
AS DT2_NAISSANCE
FROM AODS00.TDO_D_ASSIN_EVT_CONTR EVC
WHERE EVC.NUM_INTEGRATION = 597033 AND EVC.FLAG_MAJ_ODS = '1'
ORDER BY 1;
On note que la clause FROM ne contient qu'une seule table (TDO_D_ASSIN_EVT_CONTR) et que la complexité réside dans le fait que le SELECT contient plusieurs scalar subqueries.
La particularité des scalar subqueries dans une clause SELECT est qu'elles sont exécutées autant de fois qu'il y'a de lignes retournées par la requête principale.
Voici une des scalar subqueries executées dans cette requête:
(SELECT MAX (NVL (PPH.PPH_NOM_MARITAL, PPH.PPH_NOM_PATRONYMIQUE))
            FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH,
                 TDO_D_ASSIN_ROLE_PERSONNE RPE
           WHERE     RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
                 AND RPE.RPE_CD_ROLE LIKE 'O%'
                 AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION
                 AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION
                 AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE
                 AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN
                        (  SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR
                             FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2
                            WHERE RPE2.RPE_CD_ROLE LIKE 'O%'
                         GROUP BY rpe2.RPE_NUM_CONTR))
La table TDO_D_ASSIN_ROLE_PERSONNE est celle qui apparait dans chacune des scalar subqueries.
Elle apparait même 2 fois à chaque fois: une fois dans la clause FROM principal de la scalar subquery et une autre fois dans la sous-requête de la clause WHERE.

Essayons maintenant de voir ce que donne le plan d'exécution.
Vu que la requête était en train de tourner et afin d'avoir un plan avec des stats d'exécution j'ai récupéré le plan en utilisant le SQL Monitoring (DBMS_SQLTUNE.report_sql_monitor):

Global Stats
=================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes |
=================================================================================
|   38031 |   36675 |     5.71 |        0.19 |     1350 |    42M | 2094 | 237MB |
=================================================================================


SQL Plan Monitoring Details (Plan Hash Value=132338241)
=======================================================================================================================================================================================================================
| Id    |                 Operation                  |             Name             |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Mem | Activity |      Activity Detail       | Progress |
|       |                                            |                              | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |     |   (%)    |        (# samples)         |          |
=======================================================================================================================================================================================================================
|  -> 0 | SELECT STATEMENT                           |                              |         |       |     38026 |     +6 | 16483 |    16483 |      |       |     |          |                            |          |
|  -> 1 |   SORT AGGREGATE                           |                              |       1 |       |     38026 |     +6 | 16483 |    16483 |      |       |     |          |                            |          |
|     2 |    NESTED LOOPS                            |                              |         |       |           |        | 16483 |          |      |       |     |          |                            |          |
|     3 |     NESTED LOOPS                           |                              |       1 |   194 |           |        | 16483 |          |      |       |     |          |                            |          |
|     4 |      NESTED LOOPS                          |                              |       1 |   193 |     37989 |    +32 | 16483 |        0 |      |       |     |          |                            |          |
|     5 |       VIEW                                 | VW_NSO_1                     |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|     6 |        SORT GROUP BY                       |                              |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|     7 |         PARTITION RANGE ALL                |                              |       1 |   192 |     37989 |    +32 | 16483 |      508 |      |       |     |     2.21 | Cpu (195)                  |          |
|     8 |          TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |   192 |     37989 |    +32 |    4G |      508 |  389 |   6MB |     |    11.89 | Cpu (1050)                 |          |
|     9 |           INDEX SKIP SCAN                  | PK_ASSIN_RPE_CONTR_PERS      |       2 |   190 |     37989 |    +32 |    3M |      508 |   49 | 784KB |     |     0.32 | Cpu (28)                   |          |
|    10 |       PARTITION RANGE SINGLE               |                              |       1 |     1 |           |        |   392 |          |      |       |     |          |                            |          |
|    11 |        TABLE ACCESS BY LOCAL INDEX ROWID   | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |     1 |           |        |   392 |          |      |       |     |          |                            |          |
|    12 |         INDEX UNIQUE SCAN                  | PK_ASSIN_RPE_CONTR_PERS      |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    13 |      PARTITION RANGE SINGLE                |                              |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    14 |       INDEX UNIQUE SCAN                    | PK_ASSIN_PPH_ID_PERSONNE     |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    15 |     TABLE ACCESS BY LOCAL INDEX ROWID      | TDO_D_ASSIN_PERS_PHYS        |       1 |     1 |           |        |       |          |      |       |     |          |                            |          |
|    16 |   NESTED LOOPS                             |                              |         |       |           |        | 16483 |          |      |       |     |          |                            |          |
|    17 |    NESTED LOOPS                            |                              |       1 |   191 |           |        | 16483 |          |      |       |     |          |                            |          |
|    18 |     NESTED LOOPS                           |                              |       1 |   190 |     37989 |    +32 | 16483 |        0 |      |       |     |          |                            |          |
|    19 |      VIEW                                  | VW_NSO_2                     |       1 |   190 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    20 |       SORT GROUP BY                        |                              |       1 |   190 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    21 |        PARTITION RANGE ALL                 |                              |       2 |   190 |     37989 |    +32 | 16483 |      508 |      |       |     |     2.63 | Cpu (232)                  |          |
|    22 |         INDEX SKIP SCAN                    | PK_ASSIN_RPE_CONTR_PERS      |       2 |   190 |     37989 |    +32 |    4G |      508 |    1 | 16384 |     |     8.89 | Cpu (785)                  |          |
|    23 |      PARTITION RANGE SINGLE                |                              |       1 |       |           |        |   392 |          |      |       |     |          |                            |          |
|    24 |       INDEX UNIQUE SCAN                    | PK_ASSIN_RPE_CONTR_PERS      |       1 |       |           |        |   392 |          |      |       |     |          |                            |          |
|    25 |     PARTITION RANGE SINGLE                 |                              |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    26 |      INDEX UNIQUE SCAN                     | PK_ASSIN_PMO_ID_PERSONNE     |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    27 |    TABLE ACCESS BY LOCAL INDEX ROWID       | TDO_D_ASSIN_PERS_MORA        |       1 |     1 |           |        |       |          |      |       |     |          |                            |          |
| -> 28 |   SORT AGGREGATE                           |                              |       1 |       |     38026 |     +6 | 16483 |    16483 |      |       |     |          |                            |          |
|    29 |    PARTITION RANGE SINGLE                  |                              |       1 |     2 |         1 | +35732 | 16483 |        0 |      |       |     |     0.01 | Cpu (1)                    |          |
|    30 |     TABLE ACCESS BY LOCAL INDEX ROWID      | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |     2 |           |        | 16483 |          |      |       |     |          |                            |          |
|    31 |      INDEX RANGE SCAN                      | PK_ASSIN_RPE_CONTR_PERS      |       1 |     1 |           |        |       |          |      |       |     |          |                            |          |
| -> 32 |   SORT AGGREGATE                           |                              |       1 |       |     38026 |     +6 | 16483 |    16483 |      |       |     |          |                            |          |
|    33 |    PARTITION RANGE ALL                     |                              |       1 |   192 |     37989 |    +32 | 16483 |      508 |      |       |     |     2.25 | Cpu (199)                  |          |
|    34 |     TABLE ACCESS BY LOCAL INDEX ROWID      | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |   192 |     37989 |    +32 |    4G |      508 |      |       |     |    12.28 | Cpu (1085)                 |          |
|    35 |      INDEX SKIP SCAN                       | PK_ASSIN_RPE_CONTR_PERS      |       2 |   190 |     37989 |    +32 |    3M |      508 |      |       |     |     0.25 | Cpu (22)                   |          |
| -> 36 |   SORT AGGREGATE                           |                              |       1 |       |     38026 |     +6 | 16483 |    16483 |      |       |     |          |                            |          |
|    37 |    NESTED LOOPS                            |                              |         |       |           |        | 16483 |          |      |       |     |          |                            |          |
|    38 |     NESTED LOOPS                           |                              |       1 |   194 |           |        | 16483 |          |      |       |     |          |                            |          |
|    39 |      NESTED LOOPS                          |                              |       1 |   193 |     37989 |    +32 | 16483 |        0 |      |       |     |          |                            |          |
|    40 |       VIEW                                 | VW_NSO_3                     |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    41 |        SORT GROUP BY                       |                              |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    42 |         PARTITION RANGE ALL                |                              |       1 |   192 |     37989 |    +32 | 16483 |      508 |      |       |     |     2.58 | Cpu (228)                  |          |
|    43 |          TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |   192 |     37989 |    +32 |    4G |      508 |      |       |     |    11.96 | Cpu (1056)                 |          |
|    44 |           INDEX SKIP SCAN                  | PK_ASSIN_RPE_CONTR_PERS      |       2 |   190 |     37989 |    +32 |    3M |      508 |      |       |     |     0.15 | Cpu (13)                   |          |
|    45 |       PARTITION RANGE SINGLE               |                              |       1 |     1 |           |        |   392 |          |      |       |     |          |                            |          |
|    46 |        TABLE ACCESS BY LOCAL INDEX ROWID   | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |     1 |           |        |   392 |          |      |       |     |          |                            |          |
|    47 |         INDEX UNIQUE SCAN                  | PK_ASSIN_RPE_CONTR_PERS      |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    48 |      PARTITION RANGE SINGLE                |                              |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    49 |       INDEX UNIQUE SCAN                    | PK_ASSIN_PPH_ID_PERSONNE     |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    50 |     TABLE ACCESS BY LOCAL INDEX ROWID      | TDO_D_ASSIN_PERS_PHYS        |       1 |     1 |           |        |       |          |      |       |     |          |                            |          |
| -> 51 |   SORT AGGREGATE                           |                              |       1 |       |     38026 |     +6 | 16483 |    16483 |      |       |     |          |                            |          |
|    52 |    NESTED LOOPS                            |                              |         |       |     37989 |    +32 | 16483 |      508 |      |       |     |          |                            |          |
|    53 |     NESTED LOOPS                           |                              |       1 |   197 |     37989 |    +32 | 16483 |      508 |      |       |     |          |                            |          |
|    54 |      NESTED LOOPS                          |                              |       1 |   196 |     37989 |    +32 | 16483 |      508 |      |       |     |     0.01 | Cpu (1)                    |          |
|    55 |       VIEW                                 | VW_NSO_4                     |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    56 |        SORT GROUP BY                       |                              |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    57 |         PARTITION RANGE ALL                |                              |       1 |   192 |     37989 |    +32 | 16483 |      508 |      |       |     |     2.24 | Cpu (198)                  |          |
|    58 |          TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |   192 |     37989 |    +32 |    4G |      508 |      |       |     |    12.36 | Cpu (1092)                 |          |
|    59 |           INDEX SKIP SCAN                  | PK_ASSIN_RPE_CONTR_PERS      |       2 |   190 |     37989 |    +32 |    3M |      508 |      |       |     |     0.19 | Cpu (17)                   |          |
|    60 |       PARTITION RANGE ALL                  |                              |       1 |   207 |     37989 |    +32 |   392 |      508 |      |       |     |     0.10 | Cpu (9)                    |          |
|    61 |        TABLE ACCESS BY LOCAL INDEX ROWID   | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |   207 |     37989 |    +32 |  234M |      508 |      |       |     |     0.28 | Cpu (25)                   |          |
|    62 |         INDEX RANGE SCAN                   | I1_TDO_D_ASSIN_ROLE_PERSONNE |       1 |   206 |     37989 |    +32 | 79968 |      508 |   46 | 736KB |     |          |                            |          |
|    63 |      PARTITION RANGE ITERATOR              |                              |       1 |       |     37989 |    +32 |   584 |      508 |      |       |     |          |                            |          |
|    64 |       INDEX UNIQUE SCAN                    | PK_ASSIN_PPH_ID_PERSONNE     |       1 |       |     37989 |    +32 |   584 |      508 |  321 |   5MB |     |          |                            |          |
|    65 |     TABLE ACCESS BY LOCAL INDEX ROWID      | TDO_D_ASSIN_PERS_PHYS        |       1 |     1 |     37989 |    +32 |   566 |      508 |  302 |   5MB |     |          |                            |          |
| -> 66 |   SORT AGGREGATE                           |                              |       1 |       |     38026 |     +6 | 16483 |    16483 |      |       |     |          |                            |          |
|    67 |    NESTED LOOPS                            |                              |         |       |           |        | 16483 |          |      |       |     |          |                            |          |
|    68 |     NESTED LOOPS                           |                              |       1 |   194 |           |        | 16483 |          |      |       |     |          |                            |          |
|    69 |      NESTED LOOPS                          |                              |       1 |   193 |     37989 |    +32 | 16483 |        0 |      |       |     |          |                            |          |
|    70 |       VIEW                                 | VW_NSO_5                     |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    71 |        SORT GROUP BY                       |                              |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    72 |         PARTITION RANGE ALL                |                              |       1 |   192 |     37989 |    +32 | 16483 |      508 |      |       |     |     2.38 | Cpu (210)                  |          |
|    73 |          TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |   192 |     37989 |    +32 |    4G |      508 |      |       |     |    11.90 | Cpu (1051)                 |          |
|    74 |           INDEX SKIP SCAN                  | PK_ASSIN_RPE_CONTR_PERS      |       2 |   190 |     37989 |    +32 |    3M |      508 |      |       |     |     0.23 | Cpu (20)                   |          |
|    75 |       PARTITION RANGE SINGLE               |                              |       1 |     1 |           |        |   392 |          |      |       |     |          |                            |          |
|    76 |        TABLE ACCESS BY LOCAL INDEX ROWID   | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |     1 |           |        |   392 |          |      |       |     |          |                            |          |
|    77 |         INDEX UNIQUE SCAN                  | PK_ASSIN_RPE_CONTR_PERS      |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    78 |      PARTITION RANGE SINGLE                |                              |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    79 |       INDEX UNIQUE SCAN                    | PK_ASSIN_PPH_ID_PERSONNE     |       1 |       |           |        |       |          |      |       |     |          |                            |          |
|    80 |     TABLE ACCESS BY LOCAL INDEX ROWID      | TDO_D_ASSIN_PERS_PHYS        |       1 |     1 |           |        |       |          |      |       |     |          |                            |          |
| -> 81 |   SORT AGGREGATE                           |                              |       1 |       |     38026 |     +6 | 16483 |    16483 |      |       |     |          |                            |          |
|    82 |    NESTED LOOPS                            |                              |         |       |     37989 |    +32 | 16483 |      508 |      |       |     |          |                            |          |
|    83 |     NESTED LOOPS                           |                              |       1 |   197 |     37989 |    +32 | 16483 |      508 |      |       |     |          |                            |          |
|    84 |      NESTED LOOPS                          |                              |       1 |   196 |     37989 |    +32 | 16483 |      508 |      |       |     |          |                            |          |
|    85 |       VIEW                                 | VW_NSO_6                     |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    86 |        SORT GROUP BY                       |                              |       1 |   192 |     37989 |    +32 | 16483 |      392 |      |       |     |          |                            |          |
|    87 |         PARTITION RANGE ALL                |                              |       1 |   192 |     37989 |    +32 | 16483 |      508 |      |       |     |     2.30 | Cpu (203)                  |          |
|    88 |          TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |   192 |     37989 |    +32 |    4G |      508 |      |       |     |    11.96 | Cpu (1056)                 |          |
|    89 |           INDEX SKIP SCAN                  | PK_ASSIN_RPE_CONTR_PERS      |       2 |   190 |     37989 |    +32 |    3M |      508 |      |       |     |     0.25 | Cpu (22)                   |          |
|    90 |       PARTITION RANGE ALL                  |                              |       1 |   207 |     37989 |    +32 |   392 |      508 |      |       |     |     0.05 | Cpu (4)                    |          |
|    91 |        TABLE ACCESS BY LOCAL INDEX ROWID   | TDO_D_ASSIN_ROLE_PERSONNE    |       1 |   207 |     37989 |    +32 |  234M |      508 |      |       |     |     0.33 | Cpu (29)                   |          |
|    92 |         INDEX RANGE SCAN                   | I1_TDO_D_ASSIN_ROLE_PERSONNE |       1 |   206 |     37989 |    +32 | 79968 |      508 |      |       |     |          |                            |          |
|    93 |      PARTITION RANGE ITERATOR              |                              |       1 |       |     37989 |    +32 |   508 |      508 |      |       |     |          |                            |          |
|    94 |       INDEX UNIQUE SCAN                    | PK_ASSIN_PPH_ID_PERSONNE     |       1 |       |     37989 |    +32 |   508 |      508 |      |       |     |          |                            |          |
|    95 |     TABLE ACCESS BY LOCAL INDEX ROWID      | TDO_D_ASSIN_PERS_PHYS        |       1 |     1 |     37989 |    +32 |   508 |      508 |      |       |     |          |                            |          |
|    96 |   SORT ORDER BY                            |                              |   25124 | 13231 |           |        |     1 |          |      |       |     |          |                            |          |
| -> 97 |    HASH UNIQUE                             |                              |   25124 | 13223 |     38026 |     +6 |     1 |        0 |      |       |  3M |          |                            |          |
| -> 98 |     PARTITION RANGE SINGLE                 |                              |   39388 | 13082 |     38026 |     +6 |     1 |    16495 |      |       |     |          |                            |          |
| -> 99 |      TABLE ACCESS FULL                     | TDO_D_ASSIN_EVT_CONTR        |   39388 | 13082 |     38026 |     +6 |     1 |    16495 |  978 | 220MB |     |     0.01 | db file scattered read (1) |      36% |
=======================================================================================================================================================================================================================


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


   8 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
   9 - access("RPE2"."RPE_NUM_CONTR"=:B1)
       filter("RPE2"."RPE_NUM_CONTR"=:B1)
  11 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
  12 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR" AND
              "RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)")
       filter("RPE"."RPE_NUM_CONTR"=:B1)
  14 - access("PPH"."NUM_INTEGRATION"=:B1 AND "PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
       filter("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION")
  22 - access("RPE2"."RPE_NUM_CONTR"=:B1)
       filter("RPE2"."RPE_NUM_CONTR"=:B1)
  24 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR" AND
              "RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)")
       filter("RPE"."RPE_NUM_CONTR"=:B1)
  26 - access("PMO"."NUM_INTEGRATION"=:B1 AND "PMO"."PMO_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
       filter("PMO"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION")
  30 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
  31 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"=:B2)
  34 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
  35 - access("RPE"."RPE_NUM_CONTR"=:B1)
       filter(("RPE"."RPE_NUM_CONTR"=:B1 AND "RPE"."NUM_INTEGRATION"<>:B2))
  43 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
  44 - access("RPE2"."RPE_NUM_CONTR"=:B1)
       filter("RPE2"."RPE_NUM_CONTR"=:B1)
  46 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
  47 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR" AND
              "RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)")
       filter("RPE"."RPE_NUM_CONTR"=:B1)
  49 - access("PPH"."NUM_INTEGRATION"=:B1 AND "PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
       filter("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION")
  58 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
  59 - access("RPE2"."RPE_NUM_CONTR"=:B1)
       filter("RPE2"."RPE_NUM_CONTR"=:B1)
  61 - filter(("RPE"."RPE_CD_ROLE" LIKE 'O%' AND "RPE"."NUM_INTEGRATION"<>:B1 AND
              "RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)"))
  62 - access("RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR")
       filter("RPE"."RPE_NUM_CONTR"=:B1)
  64 - access("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION" AND
              "PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
       filter("PPH"."NUM_INTEGRATION"<>:B1)
  73 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
  74 - access("RPE2"."RPE_NUM_CONTR"=:B1)
       filter("RPE2"."RPE_NUM_CONTR"=:B1)
  76 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
  77 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR" AND
              "RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)")
       filter("RPE"."RPE_NUM_CONTR"=:B1)
  79 - access("PPH"."NUM_INTEGRATION"=:B1 AND "PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
       filter("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION")
  88 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
  89 - access("RPE2"."RPE_NUM_CONTR"=:B1)
       filter("RPE2"."RPE_NUM_CONTR"=:B1)
  91 - filter(("RPE"."RPE_CD_ROLE" LIKE 'O%' AND "RPE"."NUM_INTEGRATION"<>:B1 AND
              "RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)"))
  92 - access("RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR")
       filter("RPE"."RPE_NUM_CONTR"=:B1)
  94 - access("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION" AND
              "PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
       filter("PPH"."NUM_INTEGRATION"<>:B1)
  99 - filter(("EVC"."FLAG_MAJ_ODS"='1' AND "EVC"."NUM_INTEGRATION"=597033))

On constate que la table principale doit retourner 39 388 lignes (colonne Rows Estim) mais qu'au moment où j'ai généré le plan 16 495 lignes avaient été traitées (colonne Rows Actual).

Pour chacune de ces 39 388 lignes retournées Oracle doit exécuter les scalar subqueries présentes dans le SELECT principal.

On voit dans le plan que pour chaque scalar subquery on a un accès à TDO_D_ASSIN_ROLE_PERSONNE via l'index PK_ASSIN_RPE_CONTR_PERS en mode PARTITION RANGE ALL et PARTITION RANGE SINGLE.

Le PARTITION RANGE SINGLE a lieu lorsque la clause de partitionnement (colonne NUM_INTEGRATION) est précisée dans la clause WHERE alors que le PARTITION RANGE ALL a lieu lorsque la clause n'est pas précisée.

Quand on regarde la scalar subquery que j'ai postée plus haut on note que dans la clause WHERE principale on a bien un filtre sur le champ NUM_INTEGRATION d'où l'accès direct à la partition ciblée alors que dans la sous requête le predicat sur le NUM_INTEGRATION n'existe pas.

Le PARTITION RANGE ALL indique que pour récupérer les lignes de la table TDO_D_ASSIN_ROLE_PERSONNE toutes les partitions de l'index sont parcourues (il y'en a 224). Au moment où j'ai généré mon plan, le moteur SQL avait traité 16495 lignes.

Si on multiplie 16495 par 224, on obtient 3 694 880. Ca a l'air de correspondre aux 3 millions qu'on voit dans la colonne Execs du plan pour chaque accès à l'index PK_ASSIN_RPE_CONTR_PERS. 
Si on multiplie ces 3 millions d'accès par le nombre de scalar subqueries et par le fait que 3 requêtes similaires tournaient en même temps, ça donne une explication au problème de performances qu'a connu mon client ce matin.

Dans ce genre de requête, plus on a une table dans la requête principale retournant beaucoup de lignes ainsi qu'un nombre de partitions attaquées dans la scalar sbquery important et plus les performances sont dégradées du fait du nombre d'I/O générés.

Le but de cet article est de rappeler que lorsqu'on décide de partitionner une table et ses index (index locaux) il faut s'assurer que la clause de partitionnement soit bien prise en compte dans les requêtes de l'application car ainsi notre SGBD préféré sera en mesure de n'attaquer que la partition requise en éliminant à la source les partitions inutiles. C'est ce qu'on appelle le "partition pruning". Si vous partitionnez vos tables et index localement et que vos requêtes ne prunent pas vous risquez d'obtenir des problèmes de performances encore plus importants qu'avant d'avoir partitionné. Le problème rencontré par mon client en est un exemple flagrant. D'ailleurs ce problème m'a rappelé un article très intéressant de Mohamed Houri que j'ai lu récemment et qui traite d'un problème du même type.

mardi 6 août 2013

Supprimer un DB Link appartenant à un autre user

Si vous êtes DBA de prod  il est fort probable que pour supprimer des objets d'un schéma applicatif d'une base de données vous utilisiez un compte nominatif avec le role DBA, voir même  le compte SYS. La plupart du temps les mots de passe des schémas applicatifs ne sont pas connus des DBAs.

Presque tous les objets d'un schéma peuvent être supprimés avec un autre compte ayant le role DBA en préfixant l'objet par le nom du USER propriétaire. J'ai dit "presque" car ce n'est pas vrai pour les DB Links et les jobs. En effet, il est impossible même pour le user SYS de supprimer un DB Link privé ou un job appartenant à un autre USER.

Voici un exemple concret d'un DB Link que j'ai eu à supprimer ce matin:
SQL>select OWNER,DB_LINK from dba_db_links where DB_LINK='DBL_TEST';

OWNER DB_LINK
------------------------------ ------------------------------
ASDRVMAT DBL_TEST
Le DB Link DBL_TEST appartient au schema ASDRVMAT.
Si, en me connectant en sysdba, j'essaye de le supprimer en préfixant le nom du db link par le nom du schéma propriétaire j'obtiens l'erreur suivante:
 DROP DATABASE LINK ASDRVMAT.DBL_TEST;

ERREUR à la ligne 1 :
ORA-02024: lien de base de données introuvable
Oracle ne trouve pas le db link car il interprète "ASDRVMAT.DBL_TEST" comme étant le nom du db link.

En modifiant le schéma par défaut au niveau de la session j'obtiens un autre message d'erreur indiquant que je ne suis pas autorisé à supprimer le db link en question:
alter session set current_schema=ASDRVMAT;
DROP DATABASE LINK DBL_TEST;

ERREUR à la ligne 1 :
ORA-01031: privilèges insuffisants

Il n'est donc pas possible de supprimer un db link privé appartenant à un autre user sauf bien sûr en se connectant avec le user propriétaire directement.
D'ailleurs la doc Oracle est claire à ce sujet:
Restriction on Dropping Database Links You cannot drop a database link in another user's schema, and you cannot qualify dblink with the name of a schema, because periods are permitted in names of database links

La doc Oracle ne donne pas de solution de contournement mais comme souvent dans Oracle il existe un moyen officieux d'arriver à ses fins.
Ici la solution va consister à utiliser le package  non documenté DBMS_SYS_SQL.
En effet, dans ce package on a la fonction PARSE_AS_USER qui permet d’exécuter une commande SQL en tant que n'importe quel autre user.
Voici le script que j'utilise et que j'ai appelé drop_db_link.sql dans ma toolbox:
declare
    sql_text   varchar2(1000);
    cur     number;
    user_id     number;
    res      number;

begin

    select
            u.user_id into user_id
   from    dba_users u
   where   u.username = '&schema_name';
   
     sql_text := 'drop database link '||'&db_link';
     cur := SYS.DBMS_SYS_SQL.open_cursor;
     
     SYS.DBMS_SYS_SQL.parse_as_user(
           c => cur,
           statement => sql_text,
           language_flag => DBMS_SQL.native,
           userID => user_id  );
           
     res := SYS.DBMS_SYS_SQL.execute(cur);

     SYS.DBMS_SYS_SQL.close_cursor(cur);
end;
/
Ce script prend en paramètre le nom du schéma propriétaire et le nom du db link à dropper.
Grace au nom du schéma propriétaire il récupère le USER_ID qui sera utilisé comme un des paramètres de la fonction  SYS.DBMS_SYS_SQL.parse_as_user.
On peut ainsi exécuter la commande DROP DATABASE LINK comme si on était connecté avec le compte propriétaire:
SQL>declare
        sql_text   varchar2(1000);
        cur     number;
        user_id     number;
        res      number;
        --dblk varchar2(30);
begin
        --dblk :=
        select
                        u.user_id into user_id
   from    dba_users u
   where   u.username = '&schema_name';

         sql_text := 'drop database link '||'&db_link';
         cur := SYS.DBMS_SYS_SQL.open_cursor;

         SYS.DBMS_SYS_SQL.parse_as_user(
                   c => cur,
                   statement => sql_text,
                   language_flag => DBMS_SQL.native,
                   userID => user_id  );

         res := SYS.DBMS_SYS_SQL.execute(cur);

         SYS.DBMS_SYS_SQL.close_cursor(cur);
end;
/  
Entrez une valeur pour schema_name : ASDRVMAT
ancien  12 :    where   u.username = '&schema_name';
nouveau  12 :    where   u.username = 'ASDRVMAT';
Entrez une valeur pour db_link : DBL_TEST
ancien  14 :     sql_text := 'drop database link '||'&db_link';
nouveau  14 :    sql_text := 'drop database link '||'DBL_TEST';

Procédure PL/SQL terminée avec succès.

Après avoir exécuté le script on peut vérifier que le db link a bien été supprimé:
SQL>select OWNER,DB_LINK from dba_db_links where DB_LINK='DBL_TEST';
 
aucune ligne sélectionnée

dimanche 28 avril 2013

Régler un problème de performance en 5 étapes

Faire face à un problème de performance peut être une tâche extrêmement difficile car chaque problème peut avoir une nature et des causes diverses. Toutefois avec une bonne approche et une connaissance des outils à notre disposition on peut y faire face un peu plus aisément.

La méthode que j'utilise est une méthode très connue chez les experts Oracle. Elle est composée des 5 étapes suivantes:
1) Définition du problème
2) Investigation
3) Analyse
4) Résolution
5) Implementation

Je vais tenter à travers cet article d'expliquer cette approche et de l'illustrer avec un exemple de problème de performance que j'ai eu à régler il y' a quelques mois.

Etape 1: Définition du problème

Cette étape consiste à poser à son interlocuteur (un utilisateur, un développeur, un client etc.) les bonnes questions afin de définir le problème en question. On est ici un peu dans la peau du médecin qui, pour être sûr d'avoir bien compris ce dont souffre le patient, lui pose un certain nombre de questions.
On va se demander ici si le problème est en cours ou pas, si le problème de performance est général ou bien limité à un module applicatif. On va essayer de savoir si ce problème s'est déjà produit par le passé, si des changements ont eu lieu au niveau de l'application (nouvelle version applicative), au niveau de la base (ex:migration, modification de paramètres d'instance), au niveau de l'OS etc.
Toutes ces questions vont permettre de bien diagnostiquer la situation en forçant notre interlocuteur à bien exprimer son problème.

Exemple:
J'ai reçu un mail il y'a quelques mois d'un chef de projet qui se plaignait que la base de prod était "lente". Pour bien identifier son problème j'avais besoin de comprendre ce qu'il entendait par "lent". Lorsqu'il m'a dit "Depuis midi nos process de calculs mettent plus de temps à s'exécuter que d'habitude" j'avais déjà là quelque chose de plus précis. Ensuite en lui posant d'autres questions sur notamment si des changements avaient été opérés sur la base ou autre, j'ai pu découvrir que la volumétrie de données traitées par le programme avait en effet augmenté. Ce genre d'informations hyper importantes ne sont souvent pas mentionnées par nos interlocuteurs lorsqu'on nous fait part d'un problème de performance. C'est à nous de leur tirer les vers du nez...

Etape 2: Investigation

Une fois que le problème est bien défini vous devez vêtir l'imperméable du lieutenant Columbo et tenter de collecter le maximum d'information permettant de quantifier le problème. Au cours de cette étape vous allez choisir (en fonction des symptômes et des info récupérées à l'étape précédente) d'utiliser le ou les outils que vous avez à votre disposition.
Si par un exemple le problème de performance a eu lieu dans le passé vous pouvez éditer un rapport AWR ou statspacks. Si le problème est en cours vous allez plutôt requêter les vues V$SESSION et V$SQL pour voir les sessions actives et les évènement sur lesquelles elles sont en attente. A cette étape il est possible de constater qu'en réalité le problème de performance n'existe pas ou en tout cas pas lié à la base de données.

Exemple:
Pour étudier le problème de performance évoqué à l'étape précédente j'avais opté pour une génération de rapport AWR entre midi et 15h car mon échange avec le chef de projet m'avait permis de cerner le problème sur cette plage horaire.
Voici un extrait du rapport obtenu à l'époque:
               Snap Id      Snap Time      Sessions Curs/Sess
             --------- ------------------- -------- ---------
 Begin Snap:     16906 02-Jul-12 12:00:56       490      14.2
   End Snap:     16909 02-Jul-12 15:00:15       539      17.5
    Elapsed:              179.33 (mins)
    DB Time:            4,896.89 (mins)
 
 Top 5 Timed Events                                         Avg %Total
 ~~~~~~~~~~~~~~~~~~                                        wait   Call
 Event                                 Waits    Time (s)   (ms)   Time Wait Class
 ------------------------------ ------------ ----------- ------ ------ ----------
 CPU time                                        206,506          70.3
 db file sequential read           3,514,166      28,081      8    9.6   User I/O
 log file sync                     1,260,469      13,534     11    4.6     Commit
 latch: cache buffers chains       1,641,252      10,941      7    3.7 Concurrenc
 log file parallel write             912,993       4,260      5    1.4 System I/O
 
 Time Model Statistics              DB/Inst: LNSX11/LNSX11  Snaps: 16906-16909
 -> Total time in database user-calls (DB Time): 293813.4s
 -> Statistics including the word "background" measure background process
    time, and so do not contribute to the DB time statistic
 -> Ordered by % or DB time desc, Statistic name
 
 Statistic Name                                       Time (s) % of DB Time
 ------------------------------
------------ ------------------ ------------
 sql execute elapsed time                            270,349.0         92.0
 DB CPU                                              206,505.8         70.3
 parse time elapsed                                    4,037.1          1.4
 PL/SQL execution elapsed time                         2,661.8           .9
 hard parse elapsed time                               2,155.4           .7
 RMAN cpu time (backup/restore)                          440.9           .2
 PL/SQL compilation elapsed time                         424.5           .1
 connection management call elapsed time                 244.6           .1
 hard parse (sharing criteria) elapsed time              115.9           .0
 repeated bind elapsed time                               60.6           .0
 inbound PL/SQL rpc elapsed time                          59.5           .0
 hard parse (bind mismatch) elapsed time                  56.9           .0
 sequence load elapsed time                               14.9           .0
 failed parse elapsed time                                 0.1           .0
 DB time                                             293,813.4          N/A
 background elapsed time                               7,240.5          N/A
 background cpu time                                     844.5          N/A
           -------------------------------------------------------------
 

On  note que durant ces 3 heures on a eu 81 heures de DB time.

La section "Top 5 Timed Events" indique que 70% du DB Time concerne du CPU Time. La partie "Time Model Statistics", elle, montre que 92% de ce DB Time est lié à l'exécution de code SQL.
Pour avoir plus d'informations sur les requêtes SQL qui sont responsables de cette consommation accrue de DB Time il faut aller regarder du côté de la section "SQL ordered by Elapsed Time":
SQL ordered by Elapsed Time        DB/Inst: LNSX11/LNSX11  Snaps: 16906-16909
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100
-> Total DB Time (s):         293,813
-> Captured SQL account for      66.1% of Total

  Elapsed      CPU                  Elap per  % Total
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------
    86,698     69,051          674      128.6    29.5 5ydxnct2swxau
Module: LNS_X01_cal.exe
 Select HIR_MTM,ROO_ID,sja_fieldid FIELDID, (Select STV_VALUE from MUT_STATUS_
VALOSIMPLE, MUT_STATUS_PONDERATION, MUTATION Where STV_DATE=(Select Max(STV_DATE
) From MUT_STATUS_VALOSIMPLE, MUT_STATUS_PONDERATION, MUTATION Where STV_OBJ_ID
=SJA_ID And STV_DATE <=TO_DATE(:"SYS_B_00", :"SYS_B_01") And STV_STATUS_ID=PON_S

    45,771     36,420          458       99.9    15.6 g0kfhsfnh3kvv
Module: LNS_X01_cal.exe
 Select HIR_MTM,ROO_ID,sja_fieldid FIELDID, (Select STV_VALUE from MUT_STATUS_
VALOSIMPLE, MUT_STATUS_PONDERATION, MUTATION Where STV_DATE=(Select Max(STV_DATE
) From MUT_STATUS_VALOSIMPLE, MUT_STATUS_PONDERATION, MUTATION Where STV_OBJ_ID
=SJA_ID And STV_DATE <=TO_DATE(:"SYS_B_00", :"SYS_B_01") And STV_STATUS_ID=PON_S 
Les 2 requêtes ci dessus sont les requêtes à tuner car elles sont responsables à elles deux de 45% du DB Time (29.5+15.6).


Etape 3: Analyse

Après avoir collecté les informations permettant d'identifier le problème il faut entrer dans une étape d'analyse afin de trouver les causes principales de la lenteur de nos requêtes. Là aussi, une bonne connaissance des outils à notre disposition est primordial (traces 10046 et 10053, plans d'exécution, vues V$ etc.).
Cette étape est à la fois la plus difficile et la plus passionnante.

Concernant  notre exemple on sait grâce à l'étape précédente que le problème de performance est lié essentiellement à 2 requêtes. Il faut donc analyser l'exécution et le plan de ces 2 requêtes pour identifier la cause de la lenteur.

Analyse de la première requête (sql_id=5ydxnct2swxau)

Le rapport AWR nous indique que cette requête s'exécute en moyenne en 128.6 secondes. Avec 674 exécutions, le temps total d'exécution s'élève à  86 698 secondes (soit 24 heures).
Jetons un œil aux statistiques d'exécutions actuelles pour cette requête (stats récupérées dans V$SQL):
SQL> @sql_find_stats
Enter value for sql_text:
Enter value for sql_id: 5ydxnct2swxau

SQL_ID         CHILD PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_PIO    AVG_LIO
------------- ------ --------------- ---------- -------------- ----------  ---------- ----------
5ydxnct2swxau      0      3968018239        867        2197243     123.92         .49  1,632,031

Nous avons un temps d'exécution moyen de 123.92 secondes ce qui correspond pratiquement au temps d'exécution observé dans le rapport AWR.

Regardons de plus près l'historique d'exécution pour cette requête (grâce à la vue DBA_HIST_SQLSTAT):
 SQL> @awr_plan_change
Enter value for sql_id: 5ydxnct2swxau
Enter value for instance_number:

    SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME        AVG_LIO    AVG_PIO     AVG_ROWS
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ -------------- ---------- ------------
     16096      1 29-MAY-12 07.00.12.099 AM      5ydxnct2swxau      3968018239           92       36.012    2,100,168.0      54.43          848
     16120      1 30-MAY-12 07.00.08.939 AM      5ydxnct2swxau                           25       28.695    1,494,734.7     106.84          601
     16144      1 31-MAY-12 07.00.09.862 AM      5ydxnct2swxau                           67       33.522    1,986,261.7      44.06          800
     16262      1 05-JUN-12 07.00.03.542 AM      5ydxnct2swxau                           23       15.572      801,967.7      52.65          322
     16311      1 07-JUN-12 08.00.55.576 AM      5ydxnct2swxau                          151       44.639    2,538,922.9      36.52        1,020
     16428      1 12-JUN-12 07.00.15.397 AM      5ydxnct2swxau                           55       31.000    1,682,545.4     102.27          671
     16476      1 14-JUN-12 07.00.58.869 AM      5ydxnct2swxau                           62       33.211    1,841,182.8      79.66          735
     16618      1 20-JUN-12 07.00.09.276 AM      5ydxnct2swxau                           36       27.819    1,522,616.9     129.00          607
     16737      1 25-JUN-12 08.00.29.501 AM      5ydxnct2swxau                           12       27.617    1,628,256.7        .00          648
     16844      1 29-JUN-12 07.00.54.189 PM      5ydxnct2swxau                          492       84.000    4,824,879.5       4.89        1,837
     16845      1 29-JUN-12 08.00.57.512 PM      5ydxnct2swxau                          710       87.038    4,153,332.8        .14        1,601
     16846      1 29-JUN-12 09.00.00.273 PM      5ydxnct2swxau                          764       78.419    4,199,976.2        .00        1,593
     16847      1 29-JUN-12 10.00.06.863 PM      5ydxnct2swxau                          758       76.995    4,426,137.8        .05        1,691
     16848      1 29-JUN-12 11.00.10.768 PM      5ydxnct2swxau                          774       75.453    4,432,480.4        .01        1,689
     16849      1 30-JUN-12 12.00.13.539 AM      5ydxnct2swxau                          618       96.493    5,709,939.6        .00        2,197
     16850      1 30-JUN-12 01.00.16.443 AM      5ydxnct2swxau                          510      110.112    6,552,217.1        .00        2,515
     16851      1 30-JUN-12 02.00.19.187 AM      5ydxnct2swxau                          496      111.173    6,393,527.4        .00        2,447
     16901      1 02-JUL-12 06.00.20.638 AM      5ydxnct2swxau                          564       65.402    3,961,601.9      15.80        1,499
     16904      1 02-JUL-12 09.00.42.918 AM      5ydxnct2swxau                          680       74.424    4,268,174.0        .10        1,623
     16905      1 02-JUL-12 10.00.49.137 AM      5ydxnct2swxau                          644       85.137    4,495,420.0        .05        1,718
     16906      1 02-JUL-12 11.00.52.661 AM      5ydxnct2swxau                           87      127.208    6,788,947.7        .00        2,634
     16907      1 02-JUL-12 12.00.56.370 PM      5ydxnct2swxau                            3       62.627    3,607,740.0        .00        1,335
     16908      1 02-JUL-12 01.00.01.946 PM      5ydxnct2swxau                          347      121.837    6,638,634.2        .12        2,552
     16909      1 02-JUL-12 02.00.09.803 PM      5ydxnct2swxau                          324      136.520    6,896,529.5        .24        2,660
     16910      1 02-JUL-12 03.00.15.936 PM      5ydxnct2swxau                          230      107.049    5,809,572.0       1.39        2,228
 On peut noter qu'il n'y a pas de problème d'instabilité de plan pour cett requête puisque le même plan est utilisé à chaque fois. 
Il faut donc analyser de plus près ce plan d'exécution.
Tout d'abord vérifions qu'on obtient bien le même plan si on exécute cette requête de manière isolée.
Comme cette requête utilise des binds variables nous devons tenter de récupérer les valeurs enregistrées lors de l'opération de bind peeking en requêtant la vue V$SQL_BIND_CAPTURE:
 col VALUE_STRING for A20
select name,position,datatype_string,value_string
from V$SQL_BIND_CAPTURE where sql_id='5ydxnct2swxau';

NAME                             POSITION DATATYPE_STRING VALUE_STRING
------------------------------ ---------- --------------- --------------------
:SYS_B_00                               1 VARCHAR2(32)    29-06-2012
:SYS_B_01                               2 VARCHAR2(32)    dd-mm-yyyy
:SYS_B_02                               3 NUMBER          2
:SYS_B_03                               4 NUMBER          1
:SYS_B_04                               5 NUMBER          2
:SYS_B_05                               6 NUMBER          1
:SYS_B_06                               7 NUMBER          164826982
:SYS_B_07                               8 VARCHAR2(32)    29-06-2012
:SYS_B_08                               9 VARCHAR2(32)    dd-mm-yyyy
:SYS_B_09                              10 VARCHAR2(32)    29-06-2012
:SYS_B_10                              11 VARCHAR2(32)    dd-mm-yyyy
:SYS_B_11                              12 VARCHAR2(32)    29-06-2012
:SYS_B_12                              13 VARCHAR2(32)    dd-mm-yyyy
:SYS_B_13                              14 NUMBER          50000
:SYS_B_14                              15 NUMBER          40001
:SYS_B_15                              16 VARCHAR2(32)    29-06-2012
:SYS_B_16                              17 VARCHAR2(32)    dd-mm-yyyy
:SYS_B_17                              18 NUMBER          14
Maintenant qu'on a les dernières valeurs peekées on peut remplacer dans la requête les binds variables par les valeurs obtenues:
Select HIR_MTM,
       ROO_ID,
       sja_fieldid FIELDID,
       (Select STV_VALUE
          from MUT_STATUS_VALOSIMPLE, MUT_STATUS_PONDERATION, MUTATION
         Where STV_DATE =
               (Select Max(STV_DATE)
                  From MUT_STATUS_VALOSIMPLE,
                       MUT_STATUS_PONDERATION,
                       MUTATION
                 Where STV_OBJ_ID = SJA_ID
                   And STV_DATE <= TO_DATE('29-06-2012', 'dd-mm-yyyy')
                   And STV_STATUS_ID = PON_STATUS_ID
                   And MUT_ID = STV_STATUS_ID
                   And MUT_TYPESTATUS_ID = 2
                   And MUT_LASTFLAG = 1)
           And STV_OBJ_ID = SJA_ID
           And STV_STATUS_ID = PON_STATUS_ID
           And MUT_ID = STV_STATUS_ID
           And MUT_TYPESTATUS_ID = 2
           And MUT_LASTFLAG = 1) POND,
       SPO_SPOT
  From MUT_HISTO_ROOT histo, MUT_SOUS_JACENT, MUT_ROOT2, SPOT
 Where SJA_PARENT_ID = 164826982
   And SJA_STARTDATE <= TO_DATE('29-06-2012', 'dd-mm-yyyy')
   And (SJA_STOPDATE >= TO_DATE('29-06-2012', 'dd-mm-yyyy') or
       SJA_STOPDATE is NULL)
   And TRUNC(histo.LYX_DAY(+)) = TO_DATE('29-06-2012', 'dd-mm-yyyy')
   And SJA_ROOTID_SJ = histo.LYX_OWNER_ID(+)
   And HIR_TYPE_ID(+) = 50000
   And HIR_TIMETYPE_ID(+) = 40001
   And ROO_ID = SJA_ROOTID_SJ
   AND SPOT.LYX_DAY(+) = TO_DATE('29-06-2012', 'dd-mm-yyyy')
   AND SPO_DEVISE_A(+) = 14
   AND SPO_DEVISE_B(+) = ROO_DEVISE_ID;

1680 rows selected.

Elapsed: 00:01:12.56

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

SQL_ID  du7t6514za5rs, child number 1
-------------------------------------
Plan hash value: 3968018239

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                           |      1 |        |   1680 |00:00:00.20 |   10993 |
|   1 |  NESTED LOOPS                      |                           |   1680 |      1 |   1680 |00:01:09.95 |    4752K|
|   2 |   NESTED LOOPS                     |                           |   1680 |      1 |   1782 |00:01:09.91 |    4747K|
|   3 |    TABLE ACCESS BY INDEX ROWID     | MUT_STATUS_VALOSIMPLE     |   1680 |      1 |   1782 |00:01:09.88 |    4743K|
|*  4 |     INDEX RANGE SCAN               | IDX_OBJ_DATE              |   1680 |      1 |   1782 |00:01:09.85 |    4741K|
|   5 |      SORT AGGREGATE                |                           |   1680 |      1 |   1680 |00:01:09.80 |    4738K|
|   6 |       NESTED LOOPS                 |                           |   1680 |    529 |   4351 |00:01:09.79 |    4738K|
|   7 |        NESTED LOOPS                |                           |   1680 |    528 |   4623 |00:01:09.67 |    4724K|
|   8 |         TABLE ACCESS BY INDEX ROWID| MUT_STATUS_VALOSIMPLE     |   1680 |    528 |   4623 |00:01:09.59 |    4715K|
|*  9 |          INDEX SKIP SCAN           | IDX_OBJ_DATE              |   1680 |    528 |   4623 |00:01:09.54 |    4710K|
|* 10 |         INDEX UNIQUE SCAN          | PK_POND                   |   4623 |      1 |   4623 |00:00:00.06 |    9359 |
|* 11 |        TABLE ACCESS BY INDEX ROWID | MUTATION                  |   4623 |      1 |   4351 |00:00:00.10 |   13982 |
|* 12 |         INDEX UNIQUE SCAN          | PK_MUTATION               |   4623 |      1 |   4623 |00:00:00.06 |    9359 |
|* 13 |    INDEX UNIQUE SCAN               | PK_POND                   |   1782 |      1 |   1782 |00:00:00.02 |    3677 |
|* 14 |   TABLE ACCESS BY INDEX ROWID      | MUTATION                  |   1782 |      1 |   1680 |00:00:00.04 |    5459 |
|* 15 |    INDEX UNIQUE SCAN               | PK_MUTATION               |   1782 |      1 |   1782 |00:00:00.02 |    3677 |
|  16 |  NESTED LOOPS OUTER                |                           |      1 |    127 |   1680 |00:00:00.20 |   10993 |
|* 17 |   HASH JOIN OUTER                  |                           |      1 |     97 |   1680 |00:00:00.02 |    3406 |
|  18 |    NESTED LOOPS                    |                           |      1 |     97 |   1680 |00:00:00.01 |    3400 |
|* 19 |     TABLE ACCESS BY INDEX ROWID    | MUT_SOUS_JACENT           |      1 |     97 |   1680 |00:00:00.01 |      38 |
|* 20 |      INDEX RANGE SCAN              | IDX_SOUS_JACENT_PARENT_ID |      1 |    101 |   1791 |00:00:00.01 |       6 |
|  21 |     TABLE ACCESS BY INDEX ROWID    | MUT_ROOT2                 |   1680 |      1 |   1680 |00:00:00.01 |    3362 |
|* 22 |      INDEX UNIQUE SCAN             | PK_ROOT2                  |   1680 |      1 |   1680 |00:00:00.01 |    1682 |
|  23 |    TABLE ACCESS BY INDEX ROWID     | SPOT                      |      1 |      8 |     21 |00:00:00.01 |       6 |
|* 24 |     INDEX RANGE SCAN               | PK_SPOT                   |      1 |      8 |     21 |00:00:00.01 |       4 |
|  25 |   TABLE ACCESS BY INDEX ROWID      | MUT_HISTO_ROOT            |   1680 |      1 |   1563 |00:00:00.18 |    7587 |
|* 26 |    INDEX RANGE SCAN                | IDX_MHR_LOHTHTLD          |   1680 |      1 |   1563 |00:00:00.16 |    6024 |
--------------------------------------------------------------------------------------------------------------------------

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

   4 - access("STV_DATE"= AND "STV_OBJ_ID"=:B1)
   9 - access("STV_OBJ_ID"=:B1 AND "STV_DATE"<=TO_DATE(:SYS_B_00,:SYS_B_01))
       filter("STV_OBJ_ID"=:B1)
  10 - access("STV_STATUS_ID"="PON_STATUS_ID")
  11 - filter(("MUT_TYPESTATUS_ID"=:SYS_B_02 AND "MUT_LASTFLAG"=:SYS_B_03))
  12 - access("MUT_ID"="STV_STATUS_ID")
  13 - access("STV_STATUS_ID"="PON_STATUS_ID")
  14 - filter(("MUT_TYPESTATUS_ID"=:SYS_B_04 AND "MUT_LASTFLAG"=:SYS_B_05))
  15 - access("MUT_ID"="STV_STATUS_ID")
  17 - access("SPO_DEVISE_B"="ROO_DEVISE_ID")
  19 - filter(("SJA_STARTDATE"<=TO_DATE(:SYS_B_07,:SYS_B_08) AND ("SJA_STOPDATE">=TO_DATE(:SYS_B_09,:SYS_B_10) OR
              "SJA_STOPDATE" IS NULL)))
  20 - access("SJA_PARENT_ID"=:SYS_B_06)
  22 - access("ROO_ID"="SJA_ROOTID_SJ")
  24 - access("SPOT"."LYX_DAY"=TO_DATE(:SYS_B_15,:SYS_B_16) AND "SPO_DEVISE_A"=:SYS_B_17)
  26 - access("SJA_ROOTID_SJ"="HISTO"."LYX_OWNER_ID" AND "HIR_TIMETYPE_ID"=:SYS_B_14 AND "HIR_TYPE_ID"=:SYS_B_13)
       filter(TRUNC(INTERNAL_FUNCTION("HISTO"."LYX_DAY"))=TO_DATE(:SYS_B_11,:SYS_B_12))
La requête a mis plus d'une minute pour s'exécuter et a généré 4752K logical reads. Le problème se situe au niveau au niveau de l'INDEX SKIP SKAN de l'index  IDX_OBJ_DATE (operation 9). Cette opération génère à elle seule 4710K logical reads. Cet index permet d'accéder à la table MUT_STATUS_VALOSIMPLE qui est appelée au niveau de la scalar subquery de la requête.
Si on exécute la requête sans la scalar subquery le problème de performance ne se produit plus:
 Select HIR_MTM,
       ROO_ID,
       sja_fieldid FIELDID,
       SPO_SPOT
  From MUT_HISTO_ROOT histo, MUT_SOUS_JACENT, MUT_ROOT2, SPOT
 Where SJA_PARENT_ID = 164826982
   And SJA_STARTDATE <= TO_DATE('29-06-2012', 'dd-mm-yyyy')
   And (SJA_STOPDATE >= TO_DATE('29-06-2012', 'dd-mm-yyyy') or
       SJA_STOPDATE is NULL)
   And TRUNC(histo.LYX_DAY(+)) = TO_DATE('29-06-2012', 'dd-mm-yyyy')
   And SJA_ROOTID_SJ = histo.LYX_OWNER_ID(+)
   And HIR_TYPE_ID(+) = 50000
   And HIR_TIMETYPE_ID(+) = 40001
   And ROO_ID = SJA_ROOTID_SJ
   AND SPOT.LYX_DAY(+) = TO_DATE('29-06-2012', 'dd-mm-yyyy')
   AND SPO_DEVISE_A(+) = 14
   AND SPO_DEVISE_B(+) = ROO_DEVISE_ID;

1680 rows selected.

Elapsed: 00:00:01.01

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

SQL_ID  7y1fqw64vu4xv, child number 0
-------------------------------------


Plan hash value: 593715089

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |      1 |        |   1680 |00:00:00.33 |   10993 |
|   1 |  NESTED LOOPS OUTER            |                           |      1 |    127 |   1680 |00:00:00.33 |   10993 |
|*  2 |   HASH JOIN OUTER              |                           |      1 |     97 |   1680 |00:00:00.01 |    3406 |
|   3 |    NESTED LOOPS                |                           |      1 |     97 |   1680 |00:00:00.01 |    3400 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| MUT_SOUS_JACENT           |      1 |     97 |   1680 |00:00:00.01 |      38 |
|*  5 |      INDEX RANGE SCAN          | IDX_SOUS_JACENT_PARENT_ID |      1 |    101 |   1791 |00:00:00.01 |       6 |
|   6 |     TABLE ACCESS BY INDEX ROWID| MUT_ROOT2                 |   1680 |      1 |   1680 |00:00:00.01 |    3362 |
|*  7 |      INDEX UNIQUE SCAN         | PK_ROOT2                  |   1680 |      1 |   1680 |00:00:00.01 |    1682 |
|   8 |    TABLE ACCESS BY INDEX ROWID | SPOT                      |      1 |      8 |     21 |00:00:00.01 |       6 |
|*  9 |     INDEX RANGE SCAN           | PK_SPOT                   |      1 |      8 |     21 |00:00:00.01 |       4 |
|  10 |   TABLE ACCESS BY INDEX ROWID  | MUT_HISTO_ROOT            |   1680 |      1 |   1563 |00:00:00.31 |    7587 |
|* 11 |    INDEX RANGE SCAN            | IDX_MHR_LOHTHTLD          |   1680 |      1 |   1563 |00:00:00.29 |    6024 |
----------------------------------------------------------------------------------------------------------------------

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

   2 - access("SPO_DEVISE_B"="ROO_DEVISE_ID")
   4 - filter(("SJA_STARTDATE"<=TO_DATE(:SYS_B_01,:SYS_B_02) AND
              ("SJA_STOPDATE">=TO_DATE(:SYS_B_03,:SYS_B_04) OR "SJA_STOPDATE" IS NULL)))
   5 - access("SJA_PARENT_ID"=:SYS_B_00)
   7 - access("ROO_ID"="SJA_ROOTID_SJ")
   9 - access("SPOT"."LYX_DAY"=TO_DATE(:SYS_B_09,:SYS_B_10) AND "SPO_DEVISE_A"=:SYS_B_11)
  11 - access("SJA_ROOTID_SJ"="HISTO"."LYX_OWNER_ID" AND "HIR_TIMETYPE_ID"=:SYS_B_08 AND
              "HIR_TYPE_ID"=:SYS_B_07)
       filter(TRUNC(INTERNAL_FUNCTION("HISTO"."LYX_DAY"))=TO_DATE(:SYS_B_05,:SYS_B_06))
La requête a généré seulement 10K logical reads au lieu de 4752K lorsqu'on avait la scalar subquery.
Maintenant qu'on a isolé le problème on peut regarder de plus près comment améliorer l'accès à la table MUT_STATUS_VALOSIMPLE sans passer par un INDEX SKIP SCAN.
Collectons d'abord quelques informations sur les index existants pour cette table:
Index Name                           Pos# Order Column Name
------------------------------ ---------- ----- ------------------------------
idx_obj_date                            1 ASC   stv_date
                                        2 ASC   stv_obj_id

pk_valosimple                           1 ASC   stv_status_id

 L'index utilisé via l'opération SKIP SCAN est l'index IDX_OBJ_DATE. Cet index est en fait un index composite sur les colonnes STV_DATE et STV_OBJ_ID.
La requête utilise un prédicat sur STV_OBJ_ID qui correspond à la deuxième colonne de l'index. D'où un accès en INDEX SKIP SCAN beaucoup moins efficace qu'un INDEX RANGE SCAN.

 Etape 4: Trouver une solution

Une fois le problème identifié et la cause du problème isolé, cette étape consiste à trouver une solution à la cause du problème qui puisse être implémentable.
Dans notre exemple cette étape va consister à trouver un moyen d'optimiser l'accès à la table MUT_STATUS_VALOSIMPLE.
Crééons un index sur la colonne  STV_OBJ_ID uniquement et voyons ce que donne la requête lorsqu'on l'exécute:
 create index IDX_STV_OBJ_ID on MUT_STATUS_VALOSIMPLE(STV_OBJ_ID);

Plan hash value: 1150780941

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                           |      1 |        |   1680 |00:00:00.38 |    9320 |      0 |
|   1 |  NESTED LOOPS                      |                           |   1680 |      1 |   1680 |00:00:00.65 |   45714 |     37 |
|   2 |   NESTED LOOPS                     |                           |   1680 |      1 |   1782 |00:00:00.61 |   40255 |     37 |
|   3 |    TABLE ACCESS BY INDEX ROWID     | MUT_STATUS_VALOSIMPLE     |   1680 |      1 |   1782 |00:00:00.59 |   36578 |     37 |
|*  4 |     INDEX RANGE SCAN               | IDX_OBJ_DATE              |   1680 |      1 |   1782 |00:00:00.56 |   34832 |     37 |
|   5 |      SORT AGGREGATE                |                           |   1680 |      1 |   1680 |00:00:00.52 |   31355 |     37 |
|   6 |       NESTED LOOPS                 |                           |   1680 |    519 |   4351 |00:00:00.51 |   31355 |     37 |
|   7 |        NESTED LOOPS                |                           |   1680 |    518 |   4623 |00:00:00.39 |   17373 |     36 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| MUT_STATUS_VALOSIMPLE     |   1680 |    518 |   4623 |00:00:00.33 |    8014 |     36 |
|*  9 |          INDEX RANGE SCAN          | IDX_STV_OBJ_ID            |   1680 |    518 |   4623 |00:00:00.28 |    3485 |     36 |
|* 10 |         INDEX UNIQUE SCAN          | PK_POND                   |   4623 |      1 |   4623 |00:00:00.05 |    9359 |      0 |
|* 11 |        TABLE ACCESS BY INDEX ROWID | MUTATION                  |   4623 |      1 |   4351 |00:00:00.10 |   13982 |      1 |
|* 12 |         INDEX UNIQUE SCAN          | PK_MUTATION               |   4623 |      1 |   4623 |00:00:00.07 |    9359 |      1 |
|* 13 |    INDEX UNIQUE SCAN               | PK_POND                   |   1782 |      1 |   1782 |00:00:00.02 |    3677 |      0 |
|* 14 |   TABLE ACCESS BY INDEX ROWID      | MUTATION                  |   1782 |      1 |   1680 |00:00:00.03 |    5459 |      0 |
|* 15 |    INDEX UNIQUE SCAN               | PK_MUTATION               |   1782 |      1 |   1782 |00:00:00.02 |    3677 |      0 |
|  16 |  NESTED LOOPS OUTER                |                           |      1 |     86 |   1680 |00:00:00.38 |    9320 |      0 |
|* 17 |   HASH JOIN OUTER                  |                           |      1 |     81 |   1680 |00:00:00.04 |    3406 |      0 |
|  18 |    NESTED LOOPS                    |                           |      1 |     81 |   1680 |00:00:00.04 |    3400 |      0 |
|* 19 |     TABLE ACCESS BY INDEX ROWID    | MUT_SOUS_JACENT           |      1 |     81 |   1680 |00:00:00.01 |      38 |      0 |
|* 20 |      INDEX RANGE SCAN              | IDX_SOUS_JACENT_PARENT_ID |      1 |     85 |   1791 |00:00:00.01 |       6 |      0 |
|  21 |     TABLE ACCESS BY INDEX ROWID    | MUT_ROOT2                 |   1680 |      1 |   1680 |00:00:00.02 |    3362 |      0 |
|* 22 |      INDEX UNIQUE SCAN             | PK_ROOT2                  |   1680 |      1 |   1680 |00:00:00.01 |    1682 |      0 |
|  23 |    TABLE ACCESS BY INDEX ROWID     | SPOT                      |      1 |      7 |     21 |00:00:00.01 |       6 |      0 |
|* 24 |     INDEX RANGE SCAN               | PK_SPOT                   |      1 |      7 |     21 |00:00:00.01 |       4 |      0 |
|  25 |   TABLE ACCESS BY INDEX ROWID      | MUT_HISTO_ROOT            |   1680 |      1 |      0 |00:00:00.33 |    5914 |      0 |
|* 26 |    INDEX RANGE SCAN                | IDX_MHR_LOHTHTLD          |   1680 |      1 |      0 |00:00:00.33 |    5914 |      0 |
-----------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("STV_DATE"= AND "STV_OBJ_ID"=:B1)
   8 - filter("STV_DATE"<=TO_DATE(:SYS_B_00,:SYS_B_01))
   9 - access("STV_OBJ_ID"=:B1)
  10 - access("STV_STATUS_ID"="PON_STATUS_ID")
  11 - filter(("MUT_TYPESTATUS_ID"=:SYS_B_02 AND "MUT_LASTFLAG"=:SYS_B_03))
  12 - access("MUT_ID"="STV_STATUS_ID")
  13 - access("STV_STATUS_ID"="PON_STATUS_ID")
  14 - filter(("MUT_TYPESTATUS_ID"=:SYS_B_04 AND "MUT_LASTFLAG"=:SYS_B_05))
  15 - access("MUT_ID"="STV_STATUS_ID")
  17 - access("SPO_DEVISE_B"="ROO_DEVISE_ID")
  19 - filter(("SJA_STARTDATE"<=TO_DATE(:SYS_B_07,:SYS_B_08) AND ("SJA_STOPDATE">=TO_DATE(:SYS_B_09,:SYS_B_10) OR
              "SJA_STOPDATE" IS NULL)))
  20 - access("SJA_PARENT_ID"=:SYS_B_06)
  22 - access("ROO_ID"="SJA_ROOTID_SJ")
  24 - access("SPOT"."LYX_DAY"=TO_DATE(:SYS_B_15,:SYS_B_16) AND "SPO_DEVISE_A"=:SYS_B_17)
  26 - access("SJA_ROOTID_SJ"="HISTO"."LYX_OWNER_ID" AND "HIR_TIMETYPE_ID"=:SYS_B_14 AND "HIR_TYPE_ID"=:SYS_B_13)
       filter(TRUNC(INTERNAL_FUNCTION("HISTO"."LYX_DAY"))=TO_DATE(:SYS_B_11,:SYS_B_12))
La requête ne génère plus que 45K logical reads et on a bien un INDEX RANGE SCAN sur notre nouvel index à l'opération 9.
On peut noter aussi que des tables sont accédées 2 fois à cause la subquery retournant le MAX(STV_DATE). On peut éviter ce double accès sur ces tables en utilisant une requête analytique:
 Select HIR_MTM,
 ROO_ID,
 sja_fieldid FIELDID,
 (select STV_VALUE from
 (select STV_VALUE,STV_DATE,STV_OBJ_ID,max(STV_DATE) over (partition by STV_OBJ_ID) THE_MAX
 From MUT_STATUS_VALOSIMPLE,
 MUT_STATUS_PONDERATION,
 MUTATION
 Where STV_DATE <= TO_DATE('29-06-2012', 'dd-mm-yyyy')
 And STV_STATUS_ID = PON_STATUS_ID
 And MUT_ID = STV_STATUS_ID
 And MUT_TYPESTATUS_ID = 2
 And MUT_LASTFLAG = 1) v
 where v.STV_DATE=v.THE_MAX and v.STV_OBJ_ID=SJA_ID) POND,
 SPO_SPOT
From MUT_HISTO_ROOT histo, MUT_SOUS_JACENT, MUT_ROOT2, SPOT
 Where SJA_PARENT_ID = 164826982
 And SJA_STARTDATE <= TO_DATE('29-06-2012', 'dd-mm-yyyy')
 And (SJA_STOPDATE >= TO_DATE('29-06-2012', 'dd-mm-yyyy') or
 SJA_STOPDATE is NULL)
 And TRUNC(histo.LYX_DAY(+)) = TO_DATE('29-06-2012', 'dd-mm-yyyy')
 And SJA_ROOTID_SJ = histo.LYX_OWNER_ID(+)
 And HIR_TYPE_ID(+) = 50000
 And HIR_TIMETYPE_ID(+) = 40001
 And ROO_ID = SJA_ROOTID_SJ
 AND SPOT.LYX_DAY(+) = TO_DATE('29-06-2012', 'dd-mm-yyyy')
 AND SPO_DEVISE_A(+) = 14
 AND SPO_DEVISE_B(+) = ROO_DEVISE_ID;

Plan hash value: 1110971357

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           |      1 |        |   1680 |00:00:00.34 |    9320 |
|*  1 |  VIEW                           |                           |   1680 |    519 |   1680 |00:00:00.32 |   31355 |
|   2 |   WINDOW BUFFER                 |                           |   1680 |    519 |   4351 |00:00:00.31 |   31355 |
|   3 |    NESTED LOOPS                 |                           |   1680 |    519 |   4351 |00:00:00.25 |   31355 |
|   4 |     NESTED LOOPS                |                           |   1680 |    518 |   4623 |00:00:00.14 |   17373 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| MUT_STATUS_VALOSIMPLE     |   1680 |    518 |   4623 |00:00:00.08 |    8014 |
|*  6 |       INDEX RANGE SCAN          | IDX_STV_OBJ_ID            |   1680 |    518 |   4623 |00:00:00.03 |    3485 |
|*  7 |      INDEX UNIQUE SCAN          | PK_POND                   |   4623 |      1 |   4623 |00:00:00.05 |    9359 |
|*  8 |     TABLE ACCESS BY INDEX ROWID | MUTATION                  |   4623 |      1 |   4351 |00:00:00.09 |   13982 |
|*  9 |      INDEX UNIQUE SCAN          | PK_MUTATION               |   4623 |      1 |   4623 |00:00:00.06 |    9359 |
|  10 |  NESTED LOOPS OUTER             |                           |      1 |     86 |   1680 |00:00:00.34 |    9320 |
|* 11 |   HASH JOIN OUTER               |                           |      1 |     81 |   1680 |00:00:00.02 |    3406 |
|  12 |    NESTED LOOPS                 |                           |      1 |     81 |   1680 |00:00:00.02 |    3400 |
|* 13 |     TABLE ACCESS BY INDEX ROWID | MUT_SOUS_JACENT           |      1 |     81 |   1680 |00:00:00.01 |      38 |
|* 14 |      INDEX RANGE SCAN           | IDX_SOUS_JACENT_PARENT_ID |      1 |     85 |   1791 |00:00:00.01 |       6 |
|  15 |     TABLE ACCESS BY INDEX ROWID | MUT_ROOT2                 |   1680 |      1 |   1680 |00:00:00.01 |    3362 |
|* 16 |      INDEX UNIQUE SCAN          | PK_ROOT2                  |   1680 |      1 |   1680 |00:00:00.01 |    1682 |
|  17 |    TABLE ACCESS BY INDEX ROWID  | SPOT                      |      1 |      7 |     21 |00:00:00.01 |       6 |
|* 18 |     INDEX RANGE SCAN            | PK_SPOT                   |      1 |      7 |     21 |00:00:00.01 |       4 |
|  19 |   TABLE ACCESS BY INDEX ROWID   | MUT_HISTO_ROOT            |   1680 |      1 |      0 |00:00:00.32 |    5914 |
|* 20 |    INDEX RANGE SCAN             | IDX_MHR_LOHTHTLD          |   1680 |      1 |      0 |00:00:00.31 |    5914 |
-----------------------------------------------------------------------------------------------------------------------

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

   1 - filter("V"."STV_DATE"="V"."THE_MAX")
   5 - filter("STV_DATE"<=TO_DATE(:SYS_B_00,:SYS_B_01))
   6 - access("STV_OBJ_ID"=:B1)
   7 - access("STV_STATUS_ID"="PON_STATUS_ID")
   8 - filter(("MUT_TYPESTATUS_ID"=:SYS_B_02 AND "MUT_LASTFLAG"=:SYS_B_03))
   9 - access("MUT_ID"="STV_STATUS_ID")
  11 - access("SPO_DEVISE_B"="ROO_DEVISE_ID")
  13 - filter(("SJA_STARTDATE"<=TO_DATE(:SYS_B_05,:SYS_B_06) AND ("SJA_STOPDATE">=TO_DATE(:SYS_B_07,:SYS_B_08)
              OR "SJA_STOPDATE" IS NULL)))
  14 - access("SJA_PARENT_ID"=:SYS_B_04)
  16 - access("ROO_ID"="SJA_ROOTID_SJ")
  18 - access("SPOT"."LYX_DAY"=TO_DATE(:SYS_B_13,:SYS_B_14) AND "SPO_DEVISE_A"=:SYS_B_15)
  20 - access("SJA_ROOTID_SJ"="HISTO"."LYX_OWNER_ID" AND "HIR_TIMETYPE_ID"=:SYS_B_12 AND
              "HIR_TYPE_ID"=:SYS_B_11)
       filter(TRUNC(INTERNAL_FUNCTION("HISTO"."LYX_DAY"))=TO_DATE(:SYS_B_09,:SYS_B_10))
 Cette réecriture de la requête n'entraine plus qu'un seul accès aux tables MUT_STATUS_VALOSIMPLE, MUT_STATUS_PONDERATION et MUTATION.
Et le nombre de logical reads n'est plus que de 31K.


Etape 5: Implementer la solution

Cette étape consiste à implémenter la solution de manière contrôlée afin de s'assurer que le problème est bien résolu et que des regressions ne sont pas notées.
Dans notre exemple cette étape a consisté à voir avec le chef de projet si l'index pouvait être crée directement en prod en tant que patch ou bien s'il était préférable d'attendre d'effectuer une batterie de tests supplémentaires.
Vu la criticité du problème la création de l'index en prod avait été décidé ce qui a conduit à une nette amélioration du process applicatif. Par contre, la réecriture de la requête avait été laissée à plus tard lors d'une livraison applicative.
Enfin, lors de cette étape on tente de quantifier l'amélioration apportée pour remonter l'information aux managers et recevoir en retour des compliments méritées RIEN.