lundi 18 juillet 2011

Ajouter un hint sans modifier la requête

Dans un post précédent j’avais montré comment forcer, pour une requête donnée, un plan d’exécution déjà existant en base en utilisant la procédure dbms_sqltune.import_sql_profile.
Dans ce post je vais tenter d'expliquer comment on peut forcer l’utilisation d’un hint sans toucher au corps de la requête en utilisant là aussi la procédure dbms_sqltune.import_sql_profile.

Pour illustrer cette astuce je vais partir d’une optimisation que j’ai eu à réaliser récemment sur une base 10g.

La requête à optimiser était la suivante (j’ai supprimé volontairement la partie SELECT qui était trop longue) :
SELECT
….
FROM ORDER_DISPATCH_STATUS, VMAT_FLAT_EVENT_DESCS
 WHERE ods_order_sending_type_id = :"SYS_B_22"
   AND ods_state = :"SYS _B_23"
   AND viev_root_id = ods_event_id
   AND TRUNC(lyx_day) = TRUNC(SYSDATE)
   AND (TO_NUMBER(TO_CHAR(SYSDATE, :"SYS_B_24")) -
       TO_NUMBER(TO_CHAR(ods_sending_date_theo, :"SYS_B_25"))) *
       :"SYS_B_26" + TO_NUMBER(TO_CHAR(SYSDATE, :"SYS_B_27")) -
       TO_NUMBER(TO_CHAR(ods_sending_date_theo, :"SYS_B_28")) > :"SYS_B_29"
   AND ods_message IS NULL;

Les stats ci-dessous montrent que cette requête a toujours utilisé un seul plan d ‘exécution et que ce plan a été exécuté 47 621 lors des 40 derniers jours (40 jours est la durée de rétention du référentiel AWR appliquée à cette base) et génère en moyenne 61562 logical reads par exécution :

SQL> @awr_plan_stats
Enter value for sql_id: 0tsubdyu2zbaz

SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME AVG_CPU_TIME        AVG_LIO      AVG_PIO
------------- --------------- ------------ ------------ ------------ -------------- ------------
0tsubdyu2zbaz      2703446289       47,621        1.582        1.608       61,562.2           .4

En exécutant cette requête avec les bonnes valeurs des binds variables, j’obtiens 61000 logical reads pour aucune ligne retournée. C’est très très cher payé.
Plan hash value: 2703446289

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |      1 |        |      0 |00:00:02.10 |   61413 |       |       |          |
|*  1 |  HASH JOIN                   |                       |      1 |    481 |      0 |00:00:02.10 |   61413 |   894K|   894K|  207K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| ORDER_DISPATCH_STATUS |      1 |    481 |      0 |00:00:02.10 |   61413 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | IDX_ODS_SND_TYPE_ID   |      1 |   4163 |   2367K|00:00:00.01 |    6280 |       |       |          |
|   4 |   TABLE ACCESS BY INDEX ROWID| VMAT_FLAT_EVENT_DESCS |      0 |    313K|      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | IDX_TRUNC_LYX_DAY     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("VIEV_ROOT_ID"="ODS_EVENT_ID")
   2 - filter(("ODS_STATE"=:SYS_B_23 AND (TO_NUMBER(TO_CHAR(SYSDATE@!,:SYS_B_24))-TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("ODS_SENDING_
              DATE_THEO"),:SYS_B_25)))*:SYS_B_26+TO_NUMBER(TO_CHAR(SYSDATE@!,:SYS_B_27))-TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("ODS_SENDING_DATE_TH
              EO"),:SYS_B_28))>:SYS_B_29 AND "ODS_MESSAGE" IS NULL))
   3 - access("ORDER_DISPATCH_STATUS"."SYS_NC00012$"=SYS_OP_DESCEND(:SYS_B_22))
       filter(SYS_OP_UNDESCEND("ORDER_DISPATCH_STATUS"."SYS_NC00012$")=:SYS_B_22)
   5 - access("VMAT_FLAT_EVENT_DESCS"."SYS_NC00116$"=SYS_OP_DESCEND(TRUNC(SYSDATE@!)))
       filter(SYS_OP_UNDESCEND("VMAT_FLAT_EVENT_DESCS"."SYS_NC00116$")=TRUNC(SYSDATE@!))

La complexité des clauses WHERE pour cette requête fait que le CBO estime mal les cardinalités retournées pour chaque opération et choisit donc la mauvaise table comme table leader, en l’occurrence la table ORDER_DISPATCH_STATUS au lieu de VMAT_FLAT_EVENT_DESCS.

Voyons ce que donne le plan en forçant le CBO à choisir la table VMAT_FLAT_EVENT_DESCS comme table directrice. Pour ce faire on peut utiliser le hint LEADING : /*+ leading(VMAT_FLAT_EVENT_DESCS) */
Plan hash value: 1318622414

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |      1 |        |      0 |00:00:00.02 |       4 |      8 |       |       |          |
|*  1 |  HASH JOIN                   |                       |      1 |    481 |      0 |00:00:00.02 |       4 |      8 |  1011K|  1011K|  554K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| VMAT_FLAT_EVENT_DESCS |      1 |    313K|      0 |00:00:00.02 |       4 |      8 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | IDX_TRUNC_LYX_DAY     |      1 |      1 |      0 |00:00:00.02 |       4 |      8 |       |       |          |
|*  4 |   TABLE ACCESS BY INDEX ROWID| ORDER_DISPATCH_STATUS |      0 |    481 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | IDX_ODS_SND_TYPE_ID   |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------

On voit que cette fois la table conductrice est bien VMAT_FLAT_EVENT_DESCS et que le nombre de logical reads est passé de 61413 à 4.

Mon souci ici était que bien que j’avais la solution je ne pouvais pas toucher au corps de la requête pour implémenter ce hint. Il me fallait donc un moyen d’ajouter ce hint sans toucher au code SQL.
La solution consiste à créer un SQL profile qui va contenir ce hint dans l’OUTLINE DATA. L’OUTLINE DATA contient l’ensemble des hints qui déterminent le plan d’exécution d’une requête.

Affichons l’OUTLINE DATA de la requête contenant le hint LEADING :
select * from table(dbms_xplan.display_cursor('6s3y9wn6basrf',0,'OUTLINE'));

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      OPT_PARAM('_optimizer_cost_model' 'io')
      OPT_PARAM('_complex_view_merging' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 20)
      OPT_PARAM('optimizer_index_caching' 30)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "VMAT_FLAT_EVENT_DESCS"@"SEL$1" "IDX_TRUNC_LYX_DAY")
      INDEX_RS_ASC(@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1" "IDX_ODS_SND_TYPE_ID")
      LEADING(@"SEL$1" "VMAT_FLAT_EVENT_DESCS"@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1")
      USE_HASH(@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1")
      END_OUTLINE_DATA
  */
  

La partie de l’outline data qui nous interesse est celle contenant le mot LEADING :
LEADING(@"SEL$1" "VMAT_FLAT_EVENT_DESCS"@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1")

C’est ce hint qu’on souhaiterait associer via un profile à la requête exécutée par l’application c'est-à-dire celle qui ne contient pas le hint. Pour atteindre ce but on peut utiliser le script de Kerry OSBORNE que j'ai recopié ici:
accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept profile_name -
       prompt 'Enter value for profile_name (PROFILE_sqlid_MANUAL): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (false): ' -
       default 'false'


set sqlblanklines on

declare
l_profile_name varchar2(30);
cl_sql_text clob;
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin

select
sql_text
into
cl_sql_text
from
dba_hist_sqltext
where
sql_id = '&&sql_id';

select decode('&&profile_name','X0X0X0X0','PROFILE_'||'&&sql_id'||'_MANUAL','&&profile_name')
into l_profile_name
from dual;

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text, 
profile => sqlprof_attr('&hint'),
category => '&&category',
name => l_profile_name,
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
force_match => &&force_matching
);

dbms_output.put_line('Profile '||l_profile_name||' created.');

end;
/

undef profile_name
undef sql_id
undef category
undef force_matching

La variable SQL_ID doit contenir le SQL_ID de la requête qui s ‘exécute mal en production.
La variable PROFILE_NAME contient le nom du SQL profile qu’on souhaite créer.
La variable CATEGORY correspond à la catégorie du SQL profile qu’on veut créer.
La variable FORCE_MATCHING permet d’indiquer si on souhaite forcer le plan aux autres requêtes qui diffèrent sur la valeur littéral utilisée dans la clause WHERE.
La variable HINT doit être settée avec le hint qu’on a récupéré précédemment de l’OUTLINE DATA du plan contenant le bon ordre de jointure.

Dans mon cas voici les valeurs que j’ai indiquées pour ces variables :
SQL_ID = 0tsubdyu2zbaz      
PROFILE_NAME = PROFILE_0tsubdyu2zbaz_MANUAL
CATEGORY = DEFAULT
FORCE_MATCHING = TRUE
HINT= LEADING(@"SEL$1" "VMAT_FLAT_EVENT_DESCS"@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1")

Une fois le script exécuté, je vérifie que le SQL profile existe bien en base:
SQL> select name,category,created,force_matching
  2  from dba_sql_profiles
  3  where name='PROFILE_0tsubdyu2zbaz_MANUAL';

NAME                           CATEGORY                       CREATED   FOR
------------------------------ ------------------------------ --------- ---
PROFILE_0tsubdyu2zbaz_MANUAL   DEFAULT                        18-JUL-11 YES

Maintenant lorsque je réexecute ma requête initiale voici ce que j’obtiens :
Plan hash value: 1318622414

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |      1 |        |      0 |00:00:00.01 |       4 |       |       |          |
|*  1 |  HASH JOIN                   |                       |      1 |    516 |      0 |00:00:00.01 |       4 |  1011K|  1011K|  572K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| VMAT_FLAT_EVENT_DESCS |      1 |    314K|      0 |00:00:00.01 |       4 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | IDX_TRUNC_LYX_DAY     |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|*  4 |   TABLE ACCESS BY INDEX ROWID| ORDER_DISPATCH_STATUS |      0 |    516 |      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | IDX_ODS_SND_TYPE_ID   |      0 |   4181 |      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("VIEV_ROOT_ID"="ODS_EVENT_ID")
   3 - access("VMAT_FLAT_EVENT_DESCS"."SYS_NC00116$"=SYS_OP_DESCEND(TRUNC(SYSDATE@!)))
       filter(SYS_OP_UNDESCEND("VMAT_FLAT_EVENT_DESCS"."SYS_NC00116$")=TRUNC(SYSDATE@!))
   4 - filter(("ODS_STATE"=:SYS_B_23 AND (TO_NUMBER(TO_CHAR(SYSDATE@!,:SYS_B_24))-TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("ODS_SENDING_
              DATE_THEO"),:SYS_B_25)))*:SYS_B_26+TO_NUMBER(TO_CHAR(SYSDATE@!,:SYS_B_27))-TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("ODS_SENDING_DATE
              EO"),:SYS_B_28))>:SYS_B_29 AND "ODS_MESSAGE" IS NULL))
   5 - access("ORDER_DISPATCH_STATUS"."SYS_NC00012$"=SYS_OP_DESCEND(:SYS_B_22))
       filter(SYS_OP_UNDESCEND("ORDER_DISPATCH_STATUS"."SYS_NC00012$")=:SYS_B_22)

Note
-----
   - cpu costing is off (consider enabling it)
   - SQL profile "PROFILE_0tsubdyu2zbaz_MANUAL" used for this statement

On voit que le bon plan est pris en compte.
Dans la partie Note tout en bas du plan d’exécution il est indiqué que le SQL profile PROFILE_0tsubdyu2zbaz_MANUAL a été utilisée pour l’exécution de cette requête.


CONCLUSION :
En utilisant des SQL profile "manuels", il est tout à fait possible d’ajouter des hints à une requête sans avoir à toucher au corps de la requête.
Ce genre de solutions est pratique lorsqu’on utilise un progiciel pour lequel on ne peut pas toucher au code source ou bien lorsqu’on veut rapidement améliorer l’exécution d’une requête en production sans attendre la livraison d’un patch ou la prochaine livraison applicative.

jeudi 7 juillet 2011

BITMAP CONVERSION FROM ROWIDS

J’ai rencontré un problème de performance cette semaine sur une base 10g pour une requête me donnant le plan d’exécution suivant :
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                             |      1 |        |   2890 |00:30:05.06 |      24M|    182K|       |       |          |
|   1 |  SORT AGGREGATE                        |                             |   2890 |      1 |   2890 |00:00:24.11 |    8865 |   4811 |       |       |          |
|*  2 |   FILTER                               |                             |   2890 |        |   2890 |00:00:24.09 |    8865 |   4811 |       |       |          |
|   3 |    FIRST ROW                           |                             |   2890 |      1 |   2890 |00:00:24.07 |    8865 |   4811 |       |       |          |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)         | IDX_CTP_POSITIONS_NAV       |   2890 |      1 |   2890 |00:00:24.06 |    8865 |   4811 |       |       |          |
|   5 |    SORT AGGREGATE                      |                             |    233 |      1 |    233 |00:00:00.01 |     771 |      0 |       |       |          |
|*  6 |     FILTER                             |                             |    233 |        |    233 |00:00:00.01 |     771 |      0 |       |       |          |
|   7 |      FIRST ROW                         |                             |    233 |      1 |    233 |00:00:00.01 |     771 |      0 |       |       |          |
|*  8 |       INDEX RANGE SCAN (MIN/MAX)       | IDX_CTP_POSITIONS_NAV       |    233 |      1 |    233 |00:00:00.01 |     771 |      0 |       |       |          |
|*  9 |  COUNT STOPKEY                         |                             |    515 |        |    321 |00:00:01.77 |    2820 |    609 |       |       |          |
|* 10 |   TABLE ACCESS BY INDEX ROWID          | MUT_EXCHANGE_NORM_LINK      |    515 |      1 |    321 |00:00:01.77 |    2820 |    609 |       |       |          |
|* 11 |    INDEX RANGE SCAN                    | IDX_ENL_OBJ_ID              |    515 |      3 |   1968 |00:00:00.53 |    1553 |    171 |       |       |          |
|* 12 |  COUNT STOPKEY                         |                             |    515 |        |    320 |00:00:00.20 |    2901 |    254 |       |       |          |
|* 13 |   TABLE ACCESS BY INDEX ROWID          | MUT_EXCHANGE_NORM_LINK      |    515 |      1 |    320 |00:00:00.20 |    2901 |    254 |       |       |          |
|* 14 |    INDEX RANGE SCAN                    | IDX_ENL_OBJ_ID              |    515 |      3 |   2459 |00:00:00.01 |    1550 |      4 |       |       |          |
|* 15 |  COUNT STOPKEY                         |                             |   2286 |        |    255 |00:00:06.17 |   14003 |   1748 |       |       |          |
|* 16 |   TABLE ACCESS BY INDEX ROWID          | MUT_EXCHANGE_NORM_LINK      |   2286 |      1 |    255 |00:00:06.16 |   14003 |   1748 |       |       |          |
|* 17 |    INDEX RANGE SCAN                    | IDX_ENL_OBJ_ID              |   2286 |      3 |   8135 |00:00:01.52 |    6892 |    551 |       |       |          |
|* 18 |  COUNT STOPKEY                         |                             |   2286 |        |   1920 |00:00:00.10 |   11594 |     17 |       |       |          |
|* 19 |   TABLE ACCESS BY INDEX ROWID          | MUT_EXCHANGE_NORM_LINK      |   2286 |      1 |   1920 |00:00:00.09 |   11594 |     17 |       |       |          |
|* 20 |    INDEX RANGE SCAN                    | IDX_ENL_OBJ_ID              |   2286 |      3 |   5128 |00:00:00.03 |    6876 |      0 |       |       |          |
|  21 |  TABLE ACCESS BY INDEX ROWID           | VMAT_TITRE                  |   2286 |      1 |   2286 |00:00:04.90 |    4766 |   1263 |       |       |          |
|* 22 |   INDEX UNIQUE SCAN                    | PK_VMAT_TITRE               |   2286 |      1 |   2286 |00:00:00.59 |    2480 |    173 |       |       |          |
|  23 |  NESTED LOOPS                          |                             |    515 |      1 |    505 |00:00:00.04 |    1845 |      2 |       |       |          |
|* 24 |   TABLE ACCESS BY INDEX ROWID          | MUT_ROOT2                   |    515 |      1 |    505 |00:00:00.01 |    1186 |      0 |       |       |          |
|* 25 |    INDEX UNIQUE SCAN                   | PK_ROOT2                    |    515 |      1 |    515 |00:00:00.01 |     671 |      0 |       |       |          |
|  26 |   TABLE ACCESS BY INDEX ROWID          | MUT_COUNTRY_ISO2            |    505 |    268 |    505 |00:00:00.02 |     659 |      2 |       |       |          |
|* 27 |    INDEX UNIQUE SCAN                   | PK_MUT_COUNTRY_ISO2         |    505 |      1 |    505 |00:00:00.01 |     154 |      1 |       |       |          |
|  28 |  NESTED LOOPS                          |                             |      1 |      1 |   2890 |00:30:05.06 |      24M|    182K|       |       |          |
|  29 |   NESTED LOOPS                         |                             |      1 |      1 |  10824 |00:30:04.25 |      24M|    182K|       |       |          |
|  30 |    NESTED LOOPS                        |                             |      1 |      1 |  10824 |00:30:04.11 |      24M|    182K|       |       |          |
|  31 |     NESTED LOOPS                       |                             |      1 |      1 |  10824 |00:29:57.30 |      24M|    181K|       |       |          |
|  32 |      NESTED LOOPS                      |                             |      1 |      1 |  10965 |00:29:54.73 |      24M|    180K|       |       |          |
|  33 |       NESTED LOOPS                     |                             |      1 |      1 |  10965 |00:29:53.98 |      24M|    180K|       |       |          |
|* 34 |        TABLE ACCESS FULL               | MUT_INVESTMENT_LINK         |      1 |  61863 |  61846 |00:00:00.25 |     698 |      0 |       |       |          |
|* 35 |        TABLE ACCESS BY INDEX ROWID     | MUT_COMPTE_TITRE_POSITION_2 |  61846 |      1 |  10965 |00:29:53.69 |      24M|    180K|       |       |          |
|  36 |         BITMAP CONVERSION TO ROWIDS    |                             |  61846 |        |  25438 |00:28:56.72 |      24M|    169K|       |       |          |
|  37 |          BITMAP AND                    |                             |  61846 |        |  25438 |00:28:56.57 |      24M|    169K|       |       |          |
|  38 |           BITMAP CONVERSION FROM ROWIDS|                             |  61846 |        |  61694 |00:06:30.95 |     350K|    169K|       |       |          |
|* 39 |            INDEX RANGE SCAN            | IDX_CTP2_TITRE_COMPTE_ID    |  61846 |      1 |     36M|00:06:03.37 |     350K|    169K|       |       |          |
|  40 |           BITMAP CONVERSION FROM ROWIDS|                             |  61846 |        |    107K|00:22:18.21 |      24M|      0 |       |       |          |
|* 41 |            INDEX RANGE SCAN            | IDX_DAY                     |  61846 |      1 |   2095M|00:00:01.14 |      24M|      0 |       |       |          |
|* 42 |       TABLE ACCESS BY INDEX ROWID      | MUT_INVESTMENT_LINK         |  10965 |      1 |  10965 |00:00:00.73 |   22125 |    149 |       |       |          |
|* 43 |        INDEX UNIQUE SCAN               | PK_INVESTMENTLINK           |  10965 |      1 |  10965 |00:00:00.62 |   11160 |    149 |       |       |          |
|* 44 |      TABLE ACCESS BY INDEX ROWID       | MUT_ROOT2                   |  10965 |      1 |  10824 |00:00:02.55 |   22125 |    388 |       |       |          |
|* 45 |       INDEX UNIQUE SCAN                | PK_ROOT2                    |  10965 |      1 |  10965 |00:00:00.24 |   11160 |     61 |       |       |          |
|  46 |     TABLE ACCESS BY INDEX ROWID        | MUT_ROOT2                   |  10824 |      1 |  10824 |00:00:06.79 |   21844 |    995 |       |       |          |
|* 47 |      INDEX UNIQUE SCAN                 | PK_ROOT2                    |  10824 |      1 |  10824 |00:00:00.37 |   11019 |     90 |       |       |          |
|  48 |    TABLE ACCESS BY INDEX ROWID         | DEVISE                      |  10824 |      1 |  10824 |00:00:00.12 |   11019 |      7 |       |       |          |
|* 49 |     INDEX UNIQUE SCAN                  | PK_DEVISE                   |  10824 |      1 |  10824 |00:00:00.06 |     195 |      0 |       |       |          |
|  50 |   VIEW                                 |                             |  10824 |      1 |   2890 |00:00:00.78 |   43296 |     65 |       |       |          |
|  51 |    SORT UNIQUE                         |                             |  10824 |      2 |   2890 |00:00:00.76 |   43296 |     65 |  2048 |  2048 |          |
|  52 |     UNION ALL PUSHED PREDICATE         |                             |  10824 |        |   2890 |00:00:00.55 |   43296 |     65 |       |       |          |
|* 53 |      INDEX UNIQUE SCAN                 | PK_MUT_SERIE                |  10824 |      1 |   2219 |00:00:00.38 |   21648 |     64 |       |       |          |
|* 54 |      INDEX UNIQUE SCAN                 | PK_MUT_MUTUALFUND_CLASS     |  10824 |      1 |    671 |00:00:00.09 |   21648 |      1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TO_DATE(:SYS_B_00,:SYS_B_01)<=TO_DATE(:SYS_B_02,:SYS_B_03))
   4 - access("TOTO"."CTP_COMPTE_ID"=:B1 AND "TOTO"."CTP_TITRE_ID"=:B2 AND "LYX_DAY">=TO_DATE(:SYS_B_00,:SYS_B_01) AND
              "LYX_DAY"<=TO_DATE(:SYS_B_02,:SYS_B_03))
       filter("TOTO"."CTP_POS_GLOBAL"=:B1)
   6 - filter(TO_DATE(:SYS_B_08,:SYS_B_09)<=TO_DATE(:SYS_B_10,:SYS_B_11))
   8 - access("TOTO"."CTP_COMPTE_ID"=:B1 AND "TOTO"."CTP_TITRE_ID"=:B2 AND "LYX_DAY">=TO_DATE(:SYS_B_08,:SYS_B_09) AND
              "LYX_DAY"<=TO_DATE(:SYS_B_10,:SYS_B_11))
       filter("TOTO"."CTP_POS_GLOBAL"=:B1)
   9 - filter(ROWNUM=:SYS_B_15)
  10 - filter("ENL_EXCHANGE_NORM_ID"=:SYS_B_14)
  11 - access("ENL_OBJECT_ID"=:B1)
  12 - filter(ROWNUM=:SYS_B_17)
  13 - filter("ENL_EXCHANGE_NORM_ID"=:SYS_B_16)
  14 - access("ENL_OBJECT_ID"=:B1)
  15 - filter(ROWNUM=:SYS_B_21)
  16 - filter("ENL_EXCHANGE_NORM_ID"=:SYS_B_20)
  17 - access("ENL_OBJECT_ID"=:B1)
  18 - filter(ROWNUM=:SYS_B_23)
  19 - filter("ENL_EXCHANGE_NORM_ID"=:SYS_B_22)
  20 - access("ENL_OBJECT_ID"=:B1)
  22 - access("TIT_ID"=:B1)
  24 - filter("ROO_DROIT_COUNTRY_ID" IS NOT NULL)
  25 - access("ROO_ID"=:B1)
  27 - access("ROO_DROIT_COUNTRY_ID"="CRY_ID")
  34 - filter("GRL_STARTDATE"<=TO_DATE(:SYS_B_36,:SYS_B_37))
  35 - filter(("POSITION"."CTP_POS_GLOBAL">:SYS_B_32 AND ("GRL_STOPDATE" IS NULL OR "GRL_STOPDATE">="LYX_DAY")))
  39 - access("GRL_SLAVEINTERFACE_ID"="CTP_TITRE_ID" AND "GRL_COMPTE_ID"="CTP_COMPTE_ID")
  41 - access("POSITION"."LYX_DAY"=TO_DATE(:SYS_B_36,:SYS_B_37))
       filter("GRL_STARTDATE"<="LYX_DAY")
  42 - filter("GRL_MASTERINTERFACE_ID"<>:SYS_B_27)
  43 - access("GRL_ID"="GRL_ID")
  44 - filter(("VCOMP"."ROO_FUND_ID"<>:SYS_B_33 AND "VCOMP"."ROO_FUND_ID"<>:SYS_B_34 AND "VCOMP"."ROO_FUND_ID"<>:SYS_B_35))
  45 - access("VCOMP"."ROO_ID"="GRL_MASTERINTERFACE_ID")
       filter(("VCOMP"."ROO_ID"<>:SYS_B_27 AND "VCOMP"."ROO_ID"<>:SYS_B_28 AND "VCOMP"."ROO_ID"<>:SYS_B_29 AND "VCOMP"."ROO_ID"<>:SYS_B_30 AND
              "VCOMP"."ROO_ID"<>:SYS_B_31))
  47 - access("VFUNDSHARES"."ROO_ID"="GRL_SLAVEINTERFACE_ID")
  49 - access("DEVISE"."DEV_ID"="VFUNDSHARES"."ROO_DEVISE_ID")
  53 - access("SER_ID"="VFUNDSHARES"."ROO_ID")
  54 - access("MFC_ID"="VFUNDSHARES"."ROO_ID")
En regardant la première ligne du plan on voit que la requête a généré environ 24 millions de logical reads (colonne BUFFERS du plan) pour retourner 2890 lignes (colonne A-ROWS du plan). Le temps réel base de données consommé pour l’exécution de cette requête a été de 30 minutes (colonne A-TIME).

En regardant plus bas dans le plan on se rend compte que le coût de cette requête est lié aux opérations « BITMAP CONVERSION FROM ROWIDS » (OPERATION ID 38 et 40).

Cette opération s’effectue lorsque le CBO a décidé lors de son calcul de plan qu’il était judicieux de convertir les deux index b-tree IDX_DAY et IDX_CTP2_TITRE_COMPTE_ID en index BITMAP pour pouvoir les combiner avec l’opération BITMAP AND. Il faut se souvenir que l’une des grandes forces des index bitmap réside dans le fait de pouvoir combiner ces index efficacement lorsque dans la clause WHERE d’une requête on combine les prédicats via des clauses « AND » et/ou « OR »(opérations bit-and et bit-or). C’est d’ailleurs pour cela que vous ne verrez presque jamais d’index bitmap composites. Il est plus intéressant d'avoir des index bitmap pour une seule colonne.

L'inconvénient est que cette opération est très consommatrice notamment en CPU, et lorsque cette conversion d’index B-TREE en index BITMAP est effectuée de manière inappropriée (comme c’est le cas ici) les conséquences peuvent être désastreuses.

Dans mon exemple, le CBO semble avoir choisi d’effectuer cette conversion parce qu’il se trompe sur les cardinalités retournées par les INDEX RANGE SCAN. Par exemple, le CBO estime que chaque accès à l'index IDX_CTP2_TITRE_COMPTE_ID va retourner une seule ligne au lieu de 582 lignes (36M/61846). Cette erreur dans l'estimation des cardinalités va induire une erreur dans le choix des types d'accès et des méthodes de jointures.

La prise en compte par le CBO de cette conversion à la volée d’index B-TREE en index BITMAP est contrôlé par le paramètre caché « _B_TREE_BITMAP_PLANS » qui est à TRUE en 10g.

En désactivant ce paramètre au niveau de ma session il m’est possible de me débarrasser de l’opération « BITMAP CONVERSION FROM ROWIDS » pour cette requête:

SQL> alter session set "_B_TREE_BITMAP_PLANS"=FALSE;

Session altered.


Plan hash value: 2287023889

---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |      1 |        |   2890 |00:00:04.88 |     122K|   2754 |       |       |          |
|   1 |  SORT AGGREGATE                   |                             |   2890 |      1 |   2890 |00:00:00.12 |    8864 |      0 |       |       |          |
|*  2 |   FILTER                          |                             |   2890 |        |   2890 |00:00:00.10 |    8864 |      0 |       |       |          |
|   3 |    FIRST ROW                      |                             |   2890 |      1 |   2890 |00:00:00.09 |    8864 |      0 |       |       |          |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)    | IDX_CTP_POSITIONS_NAV       |   2890 |      1 |   2890 |00:00:00.08 |    8864 |      0 |       |       |          |
|   5 |    SORT AGGREGATE                 |                             |    233 |      1 |    233 |00:00:00.01 |     771 |      0 |       |       |          |
|*  6 |     FILTER                        |                             |    233 |        |    233 |00:00:00.01 |     771 |      0 |       |       |          |
|   7 |      FIRST ROW                    |                             |    233 |      1 |    233 |00:00:00.01 |     771 |      0 |       |       |          |
|*  8 |       INDEX RANGE SCAN (MIN/MAX)  | IDX_CTP_POSITIONS_NAV       |    233 |      1 |    233 |00:00:00.01 |     771 |      0 |       |       |          |
|*  9 |  COUNT STOPKEY                    |                             |    515 |        |    321 |00:00:00.03 |    2819 |      0 |       |       |          |
|* 10 |   TABLE ACCESS BY INDEX ROWID     | MUT_EXCHANGE_NORM_LINK      |    515 |      1 |    321 |00:00:00.02 |    2819 |      0 |       |       |          |
|* 11 |    INDEX RANGE SCAN               | IDX_ENL_OBJ_ID              |    515 |      3 |   1968 |00:00:00.01 |    1553 |      0 |       |       |          |
|* 12 |  COUNT STOPKEY                    |                             |    515 |        |    320 |00:00:00.02 |    2899 |      0 |       |       |          |
|* 13 |   TABLE ACCESS BY INDEX ROWID     | MUT_EXCHANGE_NORM_LINK      |    515 |      1 |    320 |00:00:00.02 |    2899 |      0 |       |       |          |
|* 14 |    INDEX RANGE SCAN               | IDX_ENL_OBJ_ID              |    515 |      3 |   2459 |00:00:00.01 |    1550 |      0 |       |       |          |
|* 15 |  COUNT STOPKEY                    |                             |   2286 |        |    255 |00:00:00.11 |   13998 |      0 |       |       |          |
|* 16 |   TABLE ACCESS BY INDEX ROWID     | MUT_EXCHANGE_NORM_LINK      |   2286 |      1 |    255 |00:00:00.11 |   13998 |      0 |       |       |          |
|* 17 |    INDEX RANGE SCAN               | IDX_ENL_OBJ_ID              |   2286 |      3 |   8135 |00:00:00.05 |    6891 |      0 |       |       |          |
|* 18 |  COUNT STOPKEY                    |                             |   2286 |        |   1920 |00:00:00.07 |   11594 |      0 |       |       |          |
|* 19 |   TABLE ACCESS BY INDEX ROWID     | MUT_EXCHANGE_NORM_LINK      |   2286 |      1 |   1920 |00:00:00.06 |   11594 |      0 |       |       |          |
|* 20 |    INDEX RANGE SCAN               | IDX_ENL_OBJ_ID              |   2286 |      3 |   5128 |00:00:00.03 |    6876 |      0 |       |       |          |
|  21 |  TABLE ACCESS BY INDEX ROWID      | VMAT_TITRE                  |   2286 |      1 |   2286 |00:00:00.05 |    4766 |      0 |       |       |          |
|* 22 |   INDEX UNIQUE SCAN               | PK_VMAT_TITRE               |   2286 |      1 |   2286 |00:00:00.03 |    2480 |      0 |       |       |          |
|  23 |  NESTED LOOPS                     |                             |    515 |      1 |    505 |00:00:00.02 |    1845 |      0 |       |       |          |
|* 24 |   TABLE ACCESS BY INDEX ROWID     | MUT_ROOT2                   |    515 |      1 |    505 |00:00:00.01 |    1186 |      0 |       |       |          |
|* 25 |    INDEX UNIQUE SCAN              | PK_ROOT2                    |    515 |      1 |    515 |00:00:00.01 |     671 |      0 |       |       |          |
|  26 |   TABLE ACCESS BY INDEX ROWID     | MUT_COUNTRY_ISO2            |    505 |    268 |    505 |00:00:00.01 |     659 |      0 |       |       |          |
|* 27 |    INDEX UNIQUE SCAN              | PK_MUT_COUNTRY_ISO2         |    505 |      1 |    505 |00:00:00.01 |     154 |      0 |       |       |          |
|  28 |  NESTED LOOPS                     |                             |      1 |      1 |   2890 |00:00:04.88 |     122K|   2754 |       |       |          |
|  29 |   NESTED LOOPS                    |                             |      1 |      1 |   2890 |00:00:04.83 |     119K|   2754 |       |       |          |
|  30 |    NESTED LOOPS                   |                             |      1 |      1 |  10824 |00:00:04.43 |   76378 |   2754 |       |       |          |
|  31 |     NESTED LOOPS                  |                             |      1 |      1 |  10824 |00:00:04.24 |   54535 |   2754 |       |       |          |
|  32 |      NESTED LOOPS                 |                             |      1 |      1 |  10965 |00:00:04.04 |   32410 |   2754 |       |       |          |
|* 33 |       HASH JOIN                   |                             |      1 |      1 |  10965 |00:00:03.86 |   10285 |   2754 |  2190K|  1131K| 2911K (0)|
|* 34 |        TABLE ACCESS BY INDEX ROWID| MUT_COMPTE_TITRE_POSITION_2 |      1 |   9796 |  21293 |00:00:03.53 |    9588 |   2754 |       |       |          |
|* 35 |         INDEX RANGE SCAN          | IDX_DAY                     |      1 |  14258 |  34240 |00:00:00.03 |     400 |      0 |       |       |          |
|* 36 |        TABLE ACCESS FULL          | MUT_INVESTMENT_LINK         |      1 |  61863 |  61846 |00:00:00.07 |     697 |      0 |       |       |          |
|* 37 |       TABLE ACCESS BY INDEX ROWID | MUT_INVESTMENT_LINK         |  10965 |      1 |  10965 |00:00:00.16 |   22125 |      0 |       |       |          |
|* 38 |        INDEX UNIQUE SCAN          | PK_INVESTMENTLINK           |  10965 |      1 |  10965 |00:00:00.09 |   11160 |      0 |       |       |          |
|* 39 |      TABLE ACCESS BY INDEX ROWID  | MUT_ROOT2                   |  10965 |      1 |  10824 |00:00:00.17 |   22125 |      0 |       |       |          |
|* 40 |       INDEX UNIQUE SCAN           | PK_ROOT2                    |  10965 |      1 |  10965 |00:00:00.09 |   11160 |      0 |       |       |          |
|  41 |     TABLE ACCESS BY INDEX ROWID   | MUT_ROOT2                   |  10824 |      1 |  10824 |00:00:00.16 |   21843 |      0 |       |       |          |
|* 42 |      INDEX UNIQUE SCAN            | PK_ROOT2                    |  10824 |      1 |  10824 |00:00:00.08 |   11019 |      0 |       |       |          |
|  43 |    VIEW                           |                             |  10824 |      1 |   2890 |00:00:00.39 |   43296 |      0 |       |       |          |
|  44 |     SORT UNIQUE                   |                             |  10824 |      2 |   2890 |00:00:00.36 |   43296 |      0 |  2048 |  2048 | 2048  (0)|
|  45 |      UNION ALL PUSHED PREDICATE   |                             |  10824 |        |   2890 |00:00:00.28 |   43296 |      0 |       |       |          |
|* 46 |       INDEX UNIQUE SCAN           | PK_MUT_SERIE                |  10824 |      1 |   2219 |00:00:00.10 |   21648 |      0 |       |       |          |
|* 47 |       INDEX UNIQUE SCAN           | PK_MUT_MUTUALFUND_CLASS     |  10824 |      1 |    671 |00:00:00.08 |   21648 |      0 |       |       |          |
|  48 |   TABLE ACCESS BY INDEX ROWID     | DEVISE                      |   2890 |      1 |   2890 |00:00:00.04 |    3084 |      0 |       |       |          |
|* 49 |    INDEX UNIQUE SCAN              | PK_DEVISE                   |   2890 |      1 |   2890 |00:00:00.02 |     194 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TO_DATE(:SYS_B_00,:SYS_B_01)<=TO_DATE(:SYS_B_02,:SYS_B_03))
   4 - access("TOTO"."CTP_COMPTE_ID"=:B1 AND "TOTO"."CTP_TITRE_ID"=:B2 AND "LYX_DAY">=TO_DATE(:SYS_B_00,:SYS_B_01) AND
              "LYX_DAY"<=TO_DATE(:SYS_B_02,:SYS_B_03))
       filter("TOTO"."CTP_POS_GLOBAL"=:B1)
   6 - filter(TO_DATE(:SYS_B_08,:SYS_B_09)<=TO_DATE(:SYS_B_10,:SYS_B_11))
   8 - access("TOTO"."CTP_COMPTE_ID"=:B1 AND "TOTO"."CTP_TITRE_ID"=:B2 AND "LYX_DAY">=TO_DATE(:SYS_B_08,:SYS_B_09) AND
              "LYX_DAY"<=TO_DATE(:SYS_B_10,:SYS_B_11))
       filter("TOTO"."CTP_POS_GLOBAL"=:B1)
   9 - filter(ROWNUM=:SYS_B_15)
  10 - filter("ENL_EXCHANGE_NORM_ID"=:SYS_B_14)
  11 - access("ENL_OBJECT_ID"=:B1)
  12 - filter(ROWNUM=:SYS_B_17)
  13 - filter("ENL_EXCHANGE_NORM_ID"=:SYS_B_16)
  14 - access("ENL_OBJECT_ID"=:B1)
  15 - filter(ROWNUM=:SYS_B_21)
  16 - filter("ENL_EXCHANGE_NORM_ID"=:SYS_B_20)
  17 - access("ENL_OBJECT_ID"=:B1)
  18 - filter(ROWNUM=:SYS_B_23)
  19 - filter("ENL_EXCHANGE_NORM_ID"=:SYS_B_22)
  20 - access("ENL_OBJECT_ID"=:B1)
  22 - access("TIT_ID"=:B1)
  24 - filter("ROO_DROIT_COUNTRY_ID" IS NOT NULL)
  25 - access("ROO_ID"=:B1)
  27 - access("ROO_DROIT_COUNTRY_ID"="CRY_ID")
  33 - access("GRL_COMPTE_ID"="CTP_COMPTE_ID" AND "GRL_SLAVEINTERFACE_ID"="CTP_TITRE_ID")
       filter(("GRL_STARTDATE"<="LYX_DAY" AND ("GRL_STOPDATE" IS NULL OR "GRL_STOPDATE">="LYX_DAY")))
  34 - filter("POSITION"."CTP_POS_GLOBAL">:SYS_B_32)
  35 - access("POSITION"."LYX_DAY"=TO_DATE(:SYS_B_36,:SYS_B_37))
  36 - filter("GRL_STARTDATE"<=TO_DATE(:SYS_B_36,:SYS_B_37))
  37 - filter("GRL_MASTERINTERFACE_ID"<>:SYS_B_27)
  38 - access("GRL_ID"="GRL_ID")
  39 - filter(("VCOMP"."ROO_FUND_ID"<>:SYS_B_33 AND "VCOMP"."ROO_FUND_ID"<>:SYS_B_34 AND "VCOMP"."ROO_FUND_ID"<>:SYS_B_35))
  40 - access("VCOMP"."ROO_ID"="GRL_MASTERINTERFACE_ID")
       filter(("VCOMP"."ROO_ID"<>:SYS_B_27 AND "VCOMP"."ROO_ID"<>:SYS_B_28 AND "VCOMP"."ROO_ID"<>:SYS_B_29 AND "VCOMP"."ROO_ID"<>:SYS_B_30 AND
              "VCOMP"."ROO_ID"<>:SYS_B_31))
  42 - access("VFUNDSHARES"."ROO_ID"="GRL_SLAVEINTERFACE_ID")
  46 - access("SER_ID"="VFUNDSHARES"."ROO_ID")
  47 - access("MFC_ID"="VFUNDSHARES"."ROO_ID")
  49 - access("DEVISE"."DEV_ID"="VFUNDSHARES"."ROO_DEVISE_ID")

Le résultat est impressionnant: la requête s'exécute en un peu plus de 4 secondes et n'a généré que 122 000 logical reads au lieu de 24 millions avec le plan précédent. On note surtout que les bitmap conversions ont disparus du plan.

J'ai corrigé cette requête en production en ajoutant un hint OPT_PARAM permettant de désactiver le paramètre caché "_B_TREE_BITMAP_PLANS" uniquement au niveau de ma requête. J'évite ainsi d'impacter les plans des autres requêtes de ma session. J'ai rencontré récemment sur une autre requête de la même base le phénomène inverse où le CBO alternait entre 2 plans selon les jours. Le bon plan était celui qui utilisait la conversion à la volée de certains index b-tree. C'est pour cela que je déconseille fortement la désactivation au niveau de l'instance de ce paramètre caché, à moins bien sûr que vous ayez des raisons valables émanant notamment du support Oracle et que les éventuels régressions au niveau des plans aient été testées comme il se doit.

Pour info voici la syntaxe pour désactiver un paramètre via le hint OPT_PARAM:
/*+ OPT_PARAM('_b_tree_bitmap_plans' 'false') */

lundi 4 juillet 2011

Performances et DELETE massifs

Il arrive souvent d’être amené à supprimer un grand nombre de lignes dans une ou plusieurs tables d’une base de données. Ces suppressions massives deviennent souvent de gros soucis en termes de performance.

Il faut bien avoir à l’esprit que le DELETE est surement l’instruction la plus couteuse dans une base Oracle, et ce pour plusieurs raisons :
- Le DELETE implique une recherche des lignes à updater (équivalent à une instruction SELECT)
- Les entrées d’index qui référencent les lignes updater doivent être supprimées de l’index
- Le code des triggers BEFORE ou AFTER DELETE est exécuté
- Si des lignes d’une autre table référencent les lignes à updater et que la FK est définie en mode « ON DELETE CASCADE » alors ces lignes doivent aussi être supprimées de la table et de ses index. Les triggers basés sur les instructions DELETE pour ces tables « filles » sont aussi déclenchés.
- De l’UNDO et du REDO sont générés

Donc à cause de tout ce travail qu’Oracle doit effectuer lors d’un DELETE il est recommandé d’éviter tout DELETE massif. J’entend par DELETE massif la suppression d’une grosse (voire d’une majeure) partie de la table.

A la place d’effectuer un DELETE il est préférable de créer une autre table qui va contenir uniquement les lignes à conserver pour pouvoir ensuite soit truncater soit dropper la table initiale.

TEST CASE :

Voici un petit test case pour illustrer tout ça.
Tout d’abord on construit une table volumineuse qui servira au DELETE :
SQL> create table t1 as select * from all_objects;

Table created.

Elapsed: 00:00:02.45

SQL> insert into t1 select * from t1;

71718 rows created.

Elapsed: 00:00:00.92
SQL> /

143436 rows created.

Elapsed: 00:00:02.40
SQL> /

286872 rows created.

Elapsed: 00:00:06.03
SQL> /

573744 rows created.

Elapsed: 00:00:11.29
SQL> /

1147488 rows created.

Elapsed: 00:00:21.04

SQL> select count(1) from t1;

  COUNT(1)
----------
   2294976

1 row selected.

SQL> select count(1) from t1 where object_type in ('SYNONYM','JAVA CLASS','VIEW','INDEX');

  COUNT(1)
----------
   1908864

SQL> create index idx_obj_typ on t1(OBJECT_TYPE);

Index created.

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

PL/SQL procedure successfully completed.

On a là une table T1 d’environ 2 millions de lignes et on souhaiterait supprimer les lignes dont la colonne OBJECT_TYPE est l’un des types suivant : SYNONYM, JAVA CLASS, VIEW et INDEX.
Ces lignes représentent à peu près 80% de la table.

Effectuons d’abord le test du DELETE:

SQL> DELETE T1 where object_type in ('SYNONYM','JAVA CLASS','VIEW','INDEX');
Elapsed: 00:02:16.59

Le DELETE a mis plus de 2 minutes à s’exécuter sur ma petite base 11g R2.

Voyons maintenant ce que donne la méthode du CTAS (CREATE TABLE AS SELECT) après avoir rollbacké la transaction précédente :

SQL> rollback;
Elapsed: 00:02:58.71

SQL> create table t1_new as select * from t1 where OBJECT_TYPE not in ('SYNONYM','JAVA CLASS','VIEW','INDEX');
Elapsed: 00:00:04.65
SQL> truncate table t1;
Elapsed: 00:00:00.34

La sauvegarde des lignes à conserver a mis plus de 4 secondes, et le TRUNCATE de la table T1 est quasi-instantanée.
Ensuite pour obtenir une table T1 avec uniquement les lignes à conserver vous avez 2 choix :
- Le premier choix consiste à renommer la table T1_NEW en T1 après avoir droppé la table T1 d’origine. Bien sûr cela implique de recréer les objets liés à la table (contraintes, indexes, triggers etc.)
- Le deuxième option consiste à réinsérer les lignes sauvegardées depuis T1_NEW vers T1. Pour accélérer l’insert on peut utiliser l’INSERT en mode DIRECT PATH via le hint APPEND. Cette méthode permet d’écrire directement après le High Water Mark en bypasant le buffer cache.

Voici ce que donne la 2ème méthode :

SQL> insert /*+ append */ into t1 select * from t1_new;
Elapsed: 00:00:05.31
SQL> commit;
Elapsed: 00:00:00.26

L’insert a pris 5 secondes.
Si on comptabilise le temps lié au CTAS+TRUNCATE+INSERT on arrive à environ 10 secondes ce qui est bien mieux que les 2 minutes du DELETE d’autant plus que plus la table est volumineuse et plus le DELETE sera long comparé au CTAS.

L’utilisation de cette méthode permet aussi d’éviter la fragmentation liée aux opérations DELETE. En effet, les DELETE ne réinitialisent pas le High Water Mark (HWM) à l’inverse du TRUNCATE. Un HWM non resetté rend les futurs accès en Full Table Scan beaucoup plus couteux puisqu’on va récupérer en plus des blocks contenant des lignes tous les blocks vides qui contenaient les lignes supprimées

Il existe une manière encore plus rapide que celle que j’ai décrite précédemment mais cela concerne uniquement les tables partitionnées.
En effet, il est possible de se débarrasser aisément des lignes qui ne sont plus désirées dans la table en supprimant directement la ou les partitions qui les référencent.
Imaginons une table historisant les ventes de voitures d’un concessionnaire. La table est partitionnée « par mois » selon la date de vente du véhicule. La société ne désire conserver que 2 mois d’historique. Au lieu d’effectuer des DELETE il est possible de dropper directement les partitions correspondant à des mois antérieures aux 2 derniers mois. Cette opération est instantanée.

Pour illustrer le DROP de partitions reconstruisons notre table T1 en effectuant un partitionnement « BY LIST » sur la colonne OBJECT_TYPE :

SQL> drop table t1 purge;
Elapsed: 00:00:00.07
 
SQL> create table t1
  2  (
  3    OWNER          VARCHAR2(30) not null,
  4    OBJECT_NAME    VARCHAR2(30) not null,
  5    SUBOBJECT_NAME VARCHAR2(30),
  6    OBJECT_ID      NUMBER not null,
  7    DATA_OBJECT_ID NUMBER,
  8    OBJECT_TYPE    VARCHAR2(19),
  9    CREATED        DATE not null,
 10    LAST_DDL_TIME  DATE not null,
 11    TIMESTAMP      VARCHAR2(19),
 12    STATUS         VARCHAR2(7),
 13    TEMPORARY      VARCHAR2(1),
 14    GENERATED      VARCHAR2(1),
 15    SECONDARY      VARCHAR2(1),
 16    NAMESPACE      NUMBER not null,
 17    EDITION_NAME   VARCHAR2(30)
 18  )
 19   partition by list(object_type)
 20   ( partition p_SYNONYM values ( 'SYNONYM' ),
 21   partition p_JAVA_CLASS values ( 'JAVA CLASS' ),
 22   partition p_VIEW values ( 'VIEW' ),
 23   partition p_INDEX values ( 'INDEX' ),
 24   partition p_TABLE values ( 'TABLE' ),
 25   partition p_TYPE values ( 'TYPE' ),
 26   partition p_PACKAGE values ( 'PACKAGE' ),
 27   partition p_PACKAGE_BODY values ( 'PACKAGE BODY' ),
 28   partition p_JAVA_RESOURCE values ( 'JAVA RESOURCE' ),
 29   partition p_TRIGGER values ( 'TRIGGER' ),
 30   partition p_INDEX_PARTITION values ( 'INDEX PARTITION' ),
 31   partition p_FUNCTION values ( 'FUNCTION' ),
 32   partition p_JAVA_DATA values ( 'JAVA DATA' ),
 33   partition p_TABLE_PARTITION values ( 'TABLE PARTITION' ),
 34   partition p_TYPE_BODY values ( 'TYPE BODY' ),
 35   partition p_SEQUENCE values ( 'SEQUENCE' ),
 36   partition p_LIBRARY values ( 'LIBRARY' ),
 37   partition p_PROCEDURE values ( 'PROCEDURE' ),
 38   partition p_XML_SCHEMA values ( 'XML SCHEMA' ),
 39   partition p_OPERATOR values ( 'OPERATOR' ),
 40   partition p_RULE_SET values ( 'RULE SET' ),
 41   partition p_PROGRAM values ( 'PROGRAM' ),
 42   partition p_EVALUATION_CONTEXT values ( 'EVALUATION CONTEXT' ),
 43   partition p_JOB values ( 'JOB' ),
 44   partition p_JOB_CLASS values ( 'JOB CLASS' ),
 45   partition p_CLUSTER values ( 'CLUSTER' ),
 46   partition p_WINDOW values ( 'WINDOW' ),
 47   partition p_CONTEXT values ( 'CONTEXT' ),
 48   partition p_DIRECTORY values ( 'DIRECTORY' ),
 49   partition p_INDEXTYPE values ( 'INDEXTYPE' ),
 50   partition p_SCHEDULER_GROUP values ( 'SCHEDULER GROUP' ),
 51   partition p_MATERIALIZED_VIEW values ( 'MATERIALIZED VIEW' ),
 52   partition p_SCHEDULE values ( 'SCHEDULE' ),
 53   partition p_DESTINATION values ( 'DESTINATION' ),
 54   partition p_JAVA_SOURCE values ( 'JAVA SOURCE' ),
 55   partition p_CONSUMER_GROUP values ( 'CONSUMER GROUP' ),
 56   partition p_RULE values ( 'RULE' ),
 57   partition p_EDITION values ( 'EDITION' ),
 58    partition p_DEFAULT values ( DEFAULT )
 59   )
 60   ;
Elapsed: 00:00:00.06

Insert into t1 select * from t1_save;

SQL> Insert into t1 select * from t1_save;
Elapsed: 00:01:54.01

Pour supprimer les mêmes lignes que dans notre premier test, il suffit de supprimer les partitions correspondants aux OBJECT_TYPE concernés par la suppression. Pour ce faire on utilise la clause DROP PARTITION de la commande ALTER TABLE :
SQL> alter table T1 drop partition (p_SYNONYM);
Elapsed: 00:00:00.26
SQL> alter table T1 drop partition (p_JAVA_CLASS);
Elapsed: 00:00:00.00
SQL> alter table T1 drop partition (p_VIEW);
Elapsed: 00:00:00.00
SQL> alter table T1 drop partition (p_INDEX);
Elapsed: 00:00:00.01

Peu importe la taille des partitions le DROP de partitions sera toujours quasiment instantanée.
Comparé au DELETE il n’y a pas photo.


CONCLUSION :
Pour supprimer un grand nombre de lignes dans une table, le DELETE constitue presque toujours la plus mauvaise solution. La bonne approche consiste soit à privilégier la suppression de partition dans le cas où la table est partitionnée, soit à reconstruire la table avec uniquement les lignes à conserver. En plus d’un gain en temps d’exécution cette approche permet d’éviter la fragmentation des tables.

jeudi 23 juin 2011

SESSION_CACHED_CURSORS

Avant d’en savoir plus sur ce paramètre d’instance il est nécessaire de faire un rappel sur le concept de Parsing des requêtes SQL dans Oracle et sur la notion de curseur.

Le parsing est la première étape dans le process d’exécution d’une requête SQL.
Durant cette étape Oracle effectue d’abord les tâches suivantes :
- Il vérifie la syntaxe de la requête
- Il vérifie que l’utilisateur exécutant la requête a bien les droits nécessaires pour accéder aux objets référencés.
- Il vérifie si cette même requête a déjà été exécutée auparavant et si un curseur partageable existe déjà dans la shared pool.

Si durant la dernière étape Oracle trouve un curseur partageable dans la SQL Area de la shared pool, alors il s’agit d’un SOFT PARSE c'est-à-dire qu’Oracle n’a pas besoin de demander au CBO de lui calculer un plan pour cette requête car il en existe déjà un en mémoire. Par contre s’il n’existe pas de curseur partageable alors le CBO doit calculer un plan pour la requête et stocker toutes les informations du nouveau curseur dans la shared pool pour qu’il soit réutilisable par la suite. Ce type de parsing est appelé HARD PARSE et est très gourmand en CPU.

Idéalement une requête SQL est hard parsée une seule fois lors de la première exécution, et uniquement soft parsée lors des exécutions suivantes. Toutefois le SOFT PARSE bien que beaucoup moins gourmand que le HARD PARSE consomme de la CPU et est également synonyme de latch au niveau de la « library cache ».

Comment faire mieux que le SOFT PARSE ?
C’est là où la notion de « SESSION CACHED CURSORS » entre en jeu.

Ce qu’il faut savoir c’est que lorsque la requête est exécutée la première fois un curseur est ouvert dans la mémoire privée de la session. Cette mémoire est appelée UGA et se trouve dans la PGA lorsqu’on est en mode DEDICATED SERVER ou dans la SGA lorsqu’on est en mode SHARED SERVER. Ce curseur ouvert côté session correspond à un pointeur vers le curseur de la shared pool contenant toutes les informations nécessaires à l’exécution de la requête et notamment le plan d’exécution.
A la fin de l’exécution de la requête, le curseur ouvert peut être mis dans le cache des curseurs fermés (toujours côté UGA). Quand la requête est réexecutée une recherche est effectuée dans le cache des curseurs fermés. Si le curseur existe il est remis dans le cache des curseurs ouvert et le pointeur vers le curseur de la shared pool est utilisé pour exécuter la requête. On évite le Hard Parse mais on évite aussi la vérification syntaxique et sémantique du SOFT PARSE. Oracle comptabilise cette méthode comme un soft parse même s’il est plus efficace que le SOFT PARSE classique. Tom KYTE appelle ce type de parsing le « Softer Soft Parse ».

Le nombre de curseurs cachables pour chaque session dans l’UGA est défini par le paramètre d’instance SESSION_CACHED_CURSORS qui est par défaut setté à 50 en 11g.

La statistique « session cursor cache count » dans V$SESSTAT nous donne le nombre de curseurs cachés dans l’UGA pour une session donnée.

La statistique « session cursor cache hits » indique le nombre de fois où un curseur a été trouvé dans le cache de session.

La statistique « parse count (total) » indique le nombre de « parse call » (HARD PARSE, SOFT PARSE ou SOFTER SOFT PARSE).

Il faut savoir que pour qu'un curseur soit placé dans le cache des curseurs fermés, il doit avoir été exécuté 3 fois. Le but étant d'éviter de mettre en cache des curseurs qui ne seront exécutés qu'une seule fois.

En se basant sur ces statistiques il est possible de construire un petit test case permettant de comprendre le fonctionnement des curseurs en cache.

Test case :
Dans Session 1:
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       136


Depuis une autre session je regarde les stats existantes pour cette session :
SQL> SELECT sn.name, ss.value
  2   FROM v$statname sn, v$sesstat ss
  3   WHERE sn.statistic# = ss.statistic#
  4   AND sn.name IN ('session cursor cache hits',
  5   'session cursor cache count',
  6   'parse count (total)')
  7   AND ss.sid =136;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      29

Le nombre de curseurs dans le cache de ma session est de 18 et aucun parse call n'a pour l'instant profité du cache des curseurs (session cursor cache hits ).

J'exécute une simple requête dans ma 1ère session:
SQL> select count(1) from t1;

  COUNT(1)
----------
       100

Je regarde les stats:
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      31

Seul le nombre de « parse call » a bougé. J'ai toujours le même nombre de curseur dans le cache.

J'exécute une 2è fois ma requête:
SQL> select count(1) from t1;

  COUNT(1)
----------
       100

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      32

Idem que pour la 1ère exécution.
J'exécute une 3ème fois la requête dans ma 1ère session puis je regarde les stats:
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      33

Idem que précedemment.

J'exécute une 4ème fois la même requête:
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 1
session cursor cache count                                               24
parse count (total)                                                      34
Cette fois le nombre de curseurs dans le cache a bougé et surtout mon "session cursor cache hits" est passé à 1. Donc c'est uniquement à la 4ème exécution que le SOFT PARSE est évité au profit d'un « softer soft parse ».

Après la 5ème exécution:
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 2
session cursor cache count                                               24
parse count (total)                                                      35

Le "session cursor cache hits" est passé à 2.
En faisant le test pour 10 exécutions j'obtiens un "session cursor cache hits" de 7.
(10-7 = 3) donc 3 est bien le nombre d'exécutions nécessaires pour que le curseur soit caché dans l’UGA.

Quelle est la valeur optimale pour SESSION_CACHED_CURSORS ?
La réponse à cette question dépend de votre application.
Par défaut en 11g le paramètre est setté à 50. Si vous avez une base de donnée transactionnelle où les mêmes requêtes sont exécutées plusieurs fois, il est possible que 50 ne soit pas suffisant.
Pour en être sûr vous pouvez vérifier la valeur de la statistique « session cursor cache count » dans V$SESSTAT. Si pour certaines sessions la valeur de cette stat est de 50, c’est que surement ces sessions pourraient bénéficier d’un cache de curseur plus important.

Dans l’exemple ci-dessous je récupère pour une base (dont le paramètre SESSION_CACHED_CURSORS est à 100) les sessions dont le cache des curseurs a atteint le max :
SQL> SELECT ss.sid,ss.value
  2   FROM v$statname sn, v$sesstat ss
  3   WHERE sn.statistic# = ss.statistic#
  4   AND sn.name = 'session cursor cache count'
  5   and ss.value=(select value from v$parameter where name='session_cached_cursors');

       SID      VALUE
---------- ----------
       444        100
       451        100
       458        100
       460        100
       465        100
       466        100
       468        100
       471        100
       475        100
       493        100

10 rows selected.

SQL> select count(distinct sid) from v$sesstat;

COUNT(DISTINCTSID)
------------------
                45 

10 sessions sur 45 n’ont pu bénéficier d’un cache de curseur plus important.
On peut alors se demander si ça ne vaudrait pas le coup d’augmenter le paramètre SESSION_CACHED_CURSORS.
Le frein à tout ça c’est qu’en augmentant ce paramètre on aura besoin de plus de place dans l’UGA. Donc tout dépend des capacités en mémoire dont on dispose. Un juste équilibre doit être trouvé.


CONCLUSION :

Il est clair que le cache des curseurs permet de réduire le temps lié au parsing et donc de réduire le temps global d’exécution des requêtes SQL. De plus comme le fait de cacher les curseurs évite d’avoir à rechercher un curseur partageable dans la shared pool, cela réduit par le même effet les latchs posés au niveau de la library cache. Ca peut être très bénéfique pour les applications hyper transactionnelles en les rendant plus scalables.

Il est possible applicativement d’éviter les « parse call » et donc d’éliminer à la fois SOFT PARSE et HARD PARSE en utilisant par exemple les « prepared statements ». Mais il s’agit là d’un autre sujet.

mardi 7 juin 2011

ANTI-JOINS : NOT IN vs NOT EXISTS

Une anti-jointure est une jointure entre 2 tables où les lignes de la première table sont retournées s’il n’existe pas de correspondance dans la 2ème table.

Les anti-jointures s’écrivent essentiellement en utilisant soit la clause NOT IN soit la clause NOT EXISTS.

Voici le script de création des 2 tables T1 et T2 que je vais utiliser pour illustrer les anti-jointures:

create table t1 (c1 number, c2 varchar2(50));

create table t2 (c1 number, c2 number);

-- t1
insert into t1 values (1,'TEST1');
insert into t1 values (2,'TEST2');
insert into t1 values (3,'TEST3');

-- t2
insert into t2 values (1,1);
insert into t2 values (2,2);
insert into t2 values (3,NULL);

commit;

create index idx_t1 on T1(c1);
create index idx_t2 on T2(c2);


Si on décide de joindre les tables T1 et T2 en utilisant T1.C1 et T2.C2 seules 2 lignes matchent :
SQL> select * from t1,t2 where t1.c1=t2.c2;

        C1 C2                 C1         C2
---------- ---------- ---------- ----------
         1 TEST1               1          1
         2 TEST2               2          2

2 rows selected. 

L’anti-jointure entre T1 et T2 doit retourner à priori la ligne TEST3 puisque cette ligne est la seule qui n’a pas de correspondance dans T2.
Voyons ce que donne l’anti-join en utilisant la clause NOT EXISTS:
SQL> select /* NOT_EXISTS */ * from t1 where not exists (select 1 from t2 where t1.c1=t2.c2);

        C1 C2
---------- ----------
         3 TEST3

1 row selected. 

On a bien la ligne TEST3 qui est retourné.

Voyons maintenant ce que donne l’anti-join avec la clause NOT IN :
SQL> select /* NOT_IN*/ * from t1 where t1.c1 not in (select t2.c2 from t2);

no rows selected

Cette fois ci le résultat est différent du NOT EXISTS.
Surpris ????

La première conclusion de ce test c’est que le NOT EXISTS et le NOT IN ne sont pas équivalent fonctionnellement. Dans un article précédent sur les semi-joins j’avais montré que les clauses IN et EXISTS étaient équivalent aussi bien fonctionnellement que d’un point de vue performances.
Pour les anti-joins ça peut être le cas mais pas toujours (comme le montre l’exemple ci-dessus).

Voyons pourquoi le NOT IN et le NOT EXISTS ne retournent pas le même résultat dans notre exemple.
L’explication réside dans la manière dont Oracle traite les valeurs NULL avec la clause NOT IN.
En effet, avec la clause NOT IN Oracle compare chaque valeur de T1.C1 avec T2.C2. Si T1.C1 a une correspondance dans T2 alors la ligne de T1 n’est pas retournée. Par contre si T1.C1 est comparé avec NULL, Oracle ne sait pas faire et retourne toujours FALSE pour ce test. Il faut rappeler que NULL dans le monde d’Oracle n’est pas une vraie valeur et veut juste dire : « je ne sais pas ».
Regardez le test PL/SQL ci-dessous.
SQL> set serveroutput on
SQL> declare
  2  v1 varchar2(10);
  3  v2 varchar2(10):='toto';
  4  begin
  5     IF v1<>v2 THEN
  6        dbms_output.put_line('DIFFERENT');
  7     ELSE
  8        dbms_output.put_line('EGAL');
  9     END IF;
 10  end;
 11  /
EGAL

PL/SQL procedure successfully completed.

On a une variable V1 qui est non settée donc NULL, et une variable V2 qui vaut ‘TOTO’. Quand on fait le test « IF V1<>V2 » on ne rentre pas dans le test alors que pour nous V1 est vraiment différent de V2. C’est pour ça qu’il faut toujours faire attention aux valeurs NULL dans Oracle. Que ce soit au niveau des colonnes ou bien au niveau des variables.

La clause NOT EXISTS, elle, ne tient pas compte des NULL et retourne donc un résultat plus logique pour nous.

Lorsqu’on utilise la clause NOT IN il faut donc prendre en compte la possibilité que la sous-requête puisse retourner des valeurs NULL.
Pour ce faire vous pouvez :
- soit ajouter une clause « WHERE NOM_COLONNE IS NOT NULL » dans la sous-requête
- soit ajouter une contrainte NOT NULL au niveau de la colonne
- soit appliquer la fonction NVL à la colonne

Exemple :
SQL> select /* NOT_IN_NOT_NULL*/ * from t1 where t1.c1 not in (select t2.c2 from t2 where t2.c2 is not null);

        C1 C2
---------- ----------
         3 TEST3

1 row selected.

SQL> select /* NOT_IN_NVL*/ * from t1 where t1.c1 not in (select NVL(t2.c2,-1) from t2);

        C1 C2
---------- ----------
         3 TEST3

1 row selected.

Maintenant que nous avons vu la subtilité fonctionnelle entre NOT IN et NOT EXISTS, voyons ce que ça donne d’un point de vue performances en analysant de plus près les plans d’exécution.

Les plans ci-dessous sont les plans pour la version 11g :
SQL> explain plan for
  2  select /* NOT_EXISTS */ * from t1 where not exists (select 1 from t2 where t1.c1=t2.c2);

Explained.

SQL> select * from table(dbms_xplan.display);

Plan hash value: 1534930707

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     2 |    24 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |        |     2 |    24 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1     |     3 |    27 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IDX_T2 |     1 |     3 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - access("T1"."C1"="T2"."C2")
       filter("T2"."C2" IS NOT NULL)

16 rows selected.

SQL> explain plan for
  2  select /* NOT_IN*/ * from t1 where t1.c1 not in (select t2.c2 from t2);

Explained.

SQL> select * from table(dbms_xplan.display);

Plan hash value: 1275484728

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    24 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     2 |    24 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    27 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     3 |     9 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T1"."C1"="T2"."C2")

15 rows selected.

Comme nous l’avions vu pour les semi-jointures, l’anti-jointure est une forme d’optimisation appliquée aux méthodes de jointures classiques telles que NESTED LOOP ou HASH JOIN. L’avantage par exemple du NESTED LOOP ANTI par rapport au NESTED LOOP classique c’est que la recherche dans la sous-requête s’arrête aussitôt qu’une correspondance a été trouvée dans cette table. C’est comme si on avait une clause EXIT dans le code d'une boucle imbriquée.
La nouveauté en 11G est pour le plan du NOT IN avec l’opération « ANTI NA ». NA signifiant NULL AWARE. Cette nouveauté permet d’appliquer la méthode ANTI-JOIN même lorsque la sous-requête retourne des valeurs NULL. Comme on peut le voir ci-dessous, en 10g l’optimiseur ne pouvait pas utiliser d’anti-jointures pour les clauses NOT IN (à moins bien sûr qu’une contrainte NOT NULL soit spécifié pour la colonne récupérée) :

SQL> alter session set optimizer_features_enable='10.2.0.4';

Session altered.

SQL> explain plan for
  2  select /* NOT_IN*/ * from t1 where t1.c1 not in (select t2.c2 from t2);

Explained.

SQL> @plan

Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    20 |     5   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    60 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     3 |    39 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE
              LNNVL("T2"."C2"<>:B1)))
   3 - filter(LNNVL("T2"."C2"<>:B1))

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

Ici c’est la clause FILTER qui est utilisée. Ce plan est beaucoup moins performant que celui avec l’anti-join.

Voyons maintenant ce que donne les plans pour les 2 clauses NOT IN et NOT EXISTS lorsqu’on garantit via une contrainte NOT NULL que la sous-requête ne peut pas retourner de valeurs NULL :

SQL> update t2 set c2=5 where c1=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter table t2 modify c2 not null;

Table altered.

SQL> alter table t1 modify c1 not null;

Table altered.

SQL> explain plan for
  2  select /* NOT_IN */ * from t1 where t1.c1 not in (select t2.c2 from t2);

Explained.

SQL> select * from table(dbms_xplan.display);

Plan hash value: 1534930707

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     2 |    24 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |        |     2 |    24 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1     |     3 |    27 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IDX_T2 |     2 |     6 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - access("T1"."C1"="T2"."C2")

15 rows selected.

SQL> explain plan for
  2  select /* NOT_EXISTS */ * from t1 where not exists (select 1 from t2 where t1.c1=t2.c2);

Explained.

SQL> select * from table(dbms_xplan.display);

Plan hash value: 1534930707

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     2 |    24 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |        |     2 |    24 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1     |     3 |    27 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IDX_T2 |     2 |     6 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - access("T1"."C1"="T2"."C2")


En plus du même résultat on obtient désormais le même plan pour les 2 requêtes.

Cas pratique :

J’ai récemment eu à traiter un pb de performance sur une requête impliquant une clause NOT IN.
Voici le corps de cette requête avec son plan non-optimal (il s’agissait d’une base 10g):
SELECT  ti.ticker_symbol FROM TICKER_OPTION tf, ticker ti WHERE tf.ticker_id =
ti.ticker_id AND ti.provider_id = 2 AND tf.expiration_dt > SYSDATE AND tf.underlying_ticker_id = 2140
AND tf.ticker_id NOT IN (SELECT inv.ticker_id FROM inventory inv, to_do2 td WHERE  inv.inventory_id =
td.inventory_id AND inv.frequency = 1 AND td.how = 'INTRADAY_BIDASK_NIGHTLY')

Plan hash value: 1691322504

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER                       |                       |      1 |        |   1272 |00:01:56.13 |    7469K|
|   2 |   NESTED LOOPS                |                       |      1 |    552 |   1810 |00:00:00.13 |    6360 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TICKER_OPTION         |      1 |    552 |   1810 |00:00:00.07 |     843 |
|*  4 |     INDEX RANGE SCAN          | TICKOPT_TICKER_ID_IDX |      1 |  17464 |  17832 |00:00:00.02 |     134 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| TICKER                |   1810 |      1 |   1810 |00:00:00.06 |    5517 |
|*  6 |     INDEX UNIQUE SCAN         | TICKER_PK             |   1810 |      1 |   1810 |00:00:00.04 |    3707 |
|*  7 |   TABLE ACCESS BY INDEX ROWID | TO_DO2                |   1810 |      1 |    538 |00:01:55.96 |    7463K|
|   8 |    NESTED LOOPS               |                       |   1810 |      1 |   4984 |00:01:55.93 |    7462K|
|*  9 |     TABLE ACCESS FULL         | INVENTORY             |   1810 |      2 |   2761 |00:01:55.84 |    7458K|
|* 10 |     INDEX RANGE SCAN          | TO_DO2_INVID          |   2761 |      1 |    951 |00:00:00.06 |    4572 |
-----------------------------------------------------------------------------------------------------------------

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

   1 - filter( IS NULL)
   3 - filter("TF"."EXPIRATION_DT">SYSDATE@!)
   4 - access("TF"."UNDERLYING_TICKER_ID"=2140)
   5 - filter("TI"."PROVIDER_ID"=2)
   6 - access("TF"."TICKER_ID"="TI"."TICKER_ID")
   7 - filter("TD"."HOW"='INTRADAY_BIDASK_NIGHTLY')
   9 - filter(("INV"."FREQUENCY"=1 AND LNNVL("INV"."TICKER_ID"<>:B1)))
  10 - access("INV"."INVENTORY_ID"="TD"."INVENTORY_ID")

Cette requête générait plus de 7 millions de logical reads pour retourner 1272 lignes.
Le plan nous montre que la clause FILTER était utilisée et donc que l’optimisation ANTI-JOIN n’était pas effectuée. En jetant un œil sur la définition de la table INVENTORY je me suis aperçu que la colonne TICKER_ID était NULLABLE. On a vu précédemment qu’en 10g et lorsqu’on n’avait pas de preuve que la sous-requête ne pouvait pas retourner de valeur NULL, le CBO ne pouvait pas utiliser l’optimisation ANTI-JOIN via un la méthode NESTED LOOP ANTI ou HASH JOIN ANTI. En 11g il aurait pu utiliser la méthode ANTI NA.

Pour améliorer les perfs de cette requête j’ai ajouté la fonction NVL à la colonne TICKER_ID pour traiter explicitement les valeurs NULL. On peut voir ci-dessous que l’optimiseur s’en sort avec un meilleur plan utilisant notamment la méthode HASH JOIN ANTI. Le nombre de logical read passe alors de 7 millions à 9742 :
SELECT ti.ticker_symbol FROM TICKER_OPTION tf, ticker ti WHERE tf.ticker_id = ti.ticker_id AND ti.provider_id = 2
AND tf.expiration_dt > SYSDATE AND tf.underlying_ticker_id = 2140 AND tf.ticker_id NOT IN (SELECT nvl(inv.ticker_id,-10) FROM
inventory inv, to_do2 td WHERE  inv.inventory_id = td.inventory_id AND inv.frequency = 1 AND td.how = 'INTRADAY_BIDASK_NIGHTLY')

Plan hash value: 398754701

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                 |                       |      1 |      1 |   1272 |00:00:00.65 |    9742 |       |       |          |
|*  2 |   HASH JOIN ANTI              |                       |      1 |      1 |   1272 |00:00:00.64 |    5840 |  1452K|  1452K| 1288K (0)|
|*  3 |    TABLE ACCESS BY INDEX ROWID| TICKER_OPTION         |      1 |    552 |   1810 |00:00:00.08 |     676 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | TICKOPT_TICKER_ID_IDX |      1 |  17464 |  17832 |00:00:00.03 |      49 |       |       |          |
|   5 |    VIEW                       | VW_NSO_1              |      1 |  17690 |  17799 |00:00:00.54 |    5164 |       |       |          |
|*  6 |     HASH JOIN                 |                       |      1 |  17690 |  17799 |00:00:00.52 |    5164 |  1573K|  1573K| 1713K (0)|
|*  7 |      TABLE ACCESS FULL        | TO_DO2                |      1 |  17690 |  17800 |00:00:00.02 |     983 |       |       |          |
|*  8 |      TABLE ACCESS FULL        | INVENTORY             |      1 |    501K|    502K|00:00:00.01 |    4181 |       |       |          |
|*  9 |   TABLE ACCESS BY INDEX ROWID | TICKER                |   1272 |      1 |   1272 |00:00:00.01 |    3902 |       |       |          |
|* 10 |    INDEX UNIQUE SCAN          | TICKER_PK             |   1272 |      1 |   1272 |00:00:00.01 |    2630 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("TF"."TICKER_ID"="$nso_col_1")
   3 - filter("TF"."EXPIRATION_DT">SYSDATE@!)
   4 - access("TF"."UNDERLYING_TICKER_ID"=2140)
   6 - access("INV"."INVENTORY_ID"="TD"."INVENTORY_ID")
   7 - filter("TD"."HOW"='INTRADAY_BIDASK_NIGHTLY')
   8 - filter("INV"."FREQUENCY"=1)
   9 - filter("TI"."PROVIDER_ID"=2)
  10 - access("TF"."TICKER_ID"="TI"."TICKER_ID")


CONCLUSION :

Ce qu'il faut retenir de cet article c'est que lorsque les contraintes NOT NULL sont bien en place alors le choix entre NOT IN et NOT EXISTS n’a pas d’importance. L’optimiseur saura utiliser l’anti-jointure.
Par contre, si la sous-requête peut retourner des valeurs NULL alors il vaut mieux privilégier le NOT EXISTS pour éviter des erreurs fonctionnels (quel que soit la version d'Oracle) et des problèmes de performance (pour les versions antérieures à la 11g ne prenant pas en compte la méthode ANTI NA).

A lire aussi:
SEMI-JOINS: IN vs EXISTS
Sur le blog d'Arkzoyd: Pourquoi NOT IN est sémantiquement différent de NOT EXISTS ?