vendredi 6 janvier 2012

Prédicats complexes et problèmes de performances

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

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

Voici le plan associé:

Plan hash value: 2475769236

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Plan hash value: 2752978458

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

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

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

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

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

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

1 commentaire: