jeudi 28 juillet 2011

Un DELETE si long sur une petite table

On m’a remonté cette semaine un problème sur un DELETE qui mettait 2h20 pour supprimer 10 148 lignes dans une table en contenant 22 558.
SQL> delete from varvolcurvevaluehs where var_id= 101; 

10148 rows deleted. 

Elapsed: 02:19:52.86 
 

Comment une simple requête supprimant juste 10 148 lignes peut-elle mettre autant de temps à s’exécuter?
Jetons un œil sur l’évènement d’attente qui a le plus contribué à l’exécution de cette requête.
Pour obtenir cette information on peut par exemple interroger les données ASH (historique des sessions actives) pour cette requête :
SQL> select event, count(1) 
  2    from dba_hist_active_sess_history 
  3   where sql_id = '48cu5tz204jad' 
  4   group by event; 

EVENT                            COUNT(1) 
------------------------------ ---------- 
                                                     418 
db file sequential read                  1 
db file scattered read                418 
log buffer space                            1 

On voit que la requête a généré essentiellement une attente sur l’évènement « db file scattered read ». Cet évènement signifie que la requête pendant 2h20 a effectué essentiellement des Full Table Scan.

La table à deleter ne contenait que 22000 lignes et elle n’était pas fragmentée du tout ce qui signifie que tous les blocks alloués au segment contenaient des lignes. Le nombre de blocks pour cette table s’élevait à 226. De plus il n’existait pas de trigger ON DELETE sur cette table. Le Full Table Scan ne peut donc pas être lié à cette table. Mais alors quelle est la table concernée par ce Full Table Scan ?

Si j’exécute de nouveau la requête et que je regarde les paramètres P1 et P2 associés à cet évènement d’attente je peux savoir quelle est la table réellement incriminée.
SQL> select event,p1text,p1,p2text,p2,p3text,p3 
  2  from v$session where sid=441; 

EVENT                          P1TEXT                  P1 P2TEXT                  P2 P3TEXT               P3 
------------------------------ --------------- ---------- --------------- ---------- --------------- ---------- 
db file scattered read         file#                  117 block#               99305 blocks               32 

A noter que ces informations sont aussi disponibles dans les données ASH (V$ACTIVE_SESSION_HISTORY et/ou DBA_HIST_ACTIVE_SESS_HISTORY).

Avec le numéro du datafile et le numéro du block ci-dessus il est possible d’interroger la vue DBA_EXTENTS pour retrouver le nom de l’objet concerné par le Full Table Scan :
SQL> select segment_name 
  2  from   dba_extents 
  3  where  73385 between block_id and (block_id + blocks - 1) 
  4  and    file_id = 117; 

SEGMENT_NAME 
--------------------------------------------------------------------------------- 
VARVOLCURVEPOINTVALUEHS 

Il ne s’agit donc pas de la table VARVOLCURVEVALUEHS mais d’une autre table (même si les 2 tables ont un nom très similaire).
Cette table doit surement être une table enfant de la table VARVOLCURVEVALUEHS référençant la Primary Key via une clé étrangère.

La requête ci-dessous nous permet de récupérer les Foreign Key référençant notre table VARVOLCURVEVALUEHS :
SQL> select table_name, CONSTRAINT_NAME 
  2    from user_constraints 
  3   where R_CONSTRAINT_NAME in 
  4         (select CONSTRAINT_NAME 
  5            from user_constraints 
  6           where table_name = 'VARVOLCURVEVALUEHS') 
  7     and CONSTRAINT_TYPE = 'R'; 

TABLE_NAME                     CONSTRAINT_NAME 
------------------------------ ------------------------------ 
VARVOLCURVEPOINTVALUEHS        FK1_VVCPVH_VVCVH 

Ce qu’il se passe en fait au moment du DELETE c’est qu’Oracle va vérifier pour chaque ligne supprimée dans VARVOLCURVEVALUEHS s’il n’existe pas de lignes dans la table enfant VARVOLCURVEPOINTVALUEHS qui référencent la ligne à supprimer. Pour effectuer cette vérification Oracle va exécuter une requête dite récursive sur la table VARVOLCURVEPOINTVALUEHS. Comme il n’existe pas d’index sur la FK, un Full Scan est effectué pour chaque ligne à supprimer (environ 10 000 fois). Comme le nombre de blocks dans la table VARVOLCURVEPOINTVALUEHS est très important (environ 50 000 blocks), ces 10 000 full table scans sont extrêmement couteux. De plus comme le buffer cache est assez petit sur la base concernée, ces blocks ne sont pas gardés en cache et des I/O physiques sont effectués à chaque Full Scan pour remonter les blocks du disque. 

 
J'avais écrit il y' a quelques semaines un article sur l'intérêt d’indexer les FK. Ce problème de performance en est une bonne illustration.

En créant un index sur la FK la requête s’exécute en moins d’une seconde :
SQL> create index IDX_CURVE_ID on VARVOLCURVEPOINTVALUEHS(CURVE_ID); 

Index created. 

SQL> set timin on 

SQL> delete from varvolcurvevaluehs where var_id= 101; 

10148 rows deleted. 

Elapsed: 00:00:00.89 

lundi 25 juillet 2011

Forcer un hard parse

Une des bonnes pratiques que tout le monde connait en matière de performances c’est de limiter le nombre de Hard Parses. L’idéal étant d’avoir juste un hard parse lors de la première exécution puis uniquement des soft parses (voire pas de parse call du tout) pour les autres exécutions.

Toutefois, il peut arriver qu’on souhaite forcer le hard parse à chaque exécution pour une requête en particulier.
Imaginons que vous ayez une requête qui se base notamment sur une table temporaire alimentée préalablement par la session. Le contenu d’une table temporaire étant propre à chaque session, vous pouvez avoir une première session exécutant cette requête avec une table temporaire contenant 100 lignes. Comme c’est la première fois que la requête est exécutée celle-ci sera hard parsée et un plan tenant compte des 100 lignes dans la table temporaire sera calculé par le CBO (dynamic sampling effectué sur la table temporaire pour estimer le nombre de lignes retournées). Maintenant une autre session va exécuter la même requête mais cette fois la table temporaire contiendra 1 million de lignes. Comme la même requête a déjà été exécutée auparavant par l’autre session (au niveau du texte c’est exactement la même requête) et qu’un curseur partageable est déjà présent dans la library cache, la requête sera seulement soft parsée et donc le plan qui avait été calculé pour la première session sera réutilisé. Ce plan qui avait été calculé en partant du principe que la table temporaire ne contenait que 100 lignes risque fortement de ne pas être approprié pour cette exécution. Il serait en effet moins couteux ici d’avoir un hard parse supplémentaire avec un plan optimal, plutôt qu’un soft parse mais avec un plan désastreux.

Voici un petit cas simple pour illustrer ce genre de situation que j’ai personnellement déjà rencontré par le passé.
Je crée d’abord une table temporaire T1_TEMP et une table T1 contenant 100 lignes :
SQL> CREATE GLOBAL TEMPORARY TABLE T1_TEMP
  2  ON COMMIT PRESERVE ROWS
  3  AS SELECT * FROM DBA_OBJECTS WHERE 1=2;

Table created.

SQL> create index IDX_OBJ_TYP on T1_TEMP(OBJECT_TYPE);

Index created.

SQL> create table t1
  2  as select * from dba_objects
  3  where rownum<=100;

Table created.
Dans une session 1 j’insère dans la table temporaire 100 lignes et j’exécute une requête basée sur cette table temporaire :
SQL> -- session1
SQL> insert into T1_TEMP
  2  SELECT * FROM T1;

100 rows created.

SQL> insert into t1
  2  select * from T1_TEMP
  3  where OBJECT_TYPE='CLUSTER';

5 rows created.

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  7df6acdvufgud, child number 0
-------------------------------------
insert into t1 select * from T1_TEMP where OBJECT_TYPE='CLUSTER'

Plan hash value: 3185690956

-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |      1 |        |      0 |00:00:00.02 |      28 |      2 |
|   1 |  LOAD TABLE CONVENTIONAL |         |      1 |        |      0 |00:00:00.02 |      28 |      2 |
|*  2 |   TABLE ACCESS FULL      | T1_TEMP |      1 |      5 |      5 |00:00:00.01 |       4 |      0 |
-------------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
La partie SELECT de la requête retourne 5 lignes mais comme la table temporaire contient peu de lignes, le CBO a judicieusement opté pour un Full Table Scan. D’ailleurs le nombre de lignes estimées par le CBO ( E-ROWS) correspond bien au nombre de lignes réellement traitées par le moteur SQL (A-ROWS). Maintenant j’ouvre une autre session et je vais exécuter la même requête sauf que cette fois je vais insérer 1 million de lignes dans la table temporaire :
SQL> -- session2
SQL> insert into t1_temp
  2  select t1.* from t1,t1 t2,t1 t3;

1000000 rows created.

SQL> insert into t1
  2  select * from T1_TEMP
  3  where OBJECT_TYPE='CLUSTER';

50000 rows created.

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  7df6acdvufgud, child number 0
-------------------------------------
insert into t1 select * from T1_TEMP where OBJECT_TYPE='CLUSTER'

Plan hash value: 3185690956

-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |      1 |        |      0 |00:00:02.94 |   18507 |  10198 |
|   1 |  LOAD TABLE CONVENTIONAL |         |      1 |        |      0 |00:00:02.94 |   18507 |  10198 |
|*  2 |   TABLE ACCESS FULL      | T1_TEMP |      1 |      5 |  50000 |00:00:02.83 |   11314 |  10196 |
-------------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
Comme la même requête avait déjà été exécutée par une autre session, cette requête n’a pas été hard parsée et c’est donc le plan déjà existant dans la shared pool qui a été utilisé. Le Full San n’est pas approprié et on voit que le nombre de lignes estimées correspond aux lignes retournées par la requête de la première session. Si cette requête avait été hard parsée on aurait surement eu un plan avec un accès indexé. Pour s’en assurer on peut flusher la shared pool et relancer la même requête :
SQL> rollback;

Rollback complete.

Elapsed: 00:00:36.39   

SQL> alter system flush shared_pool;

System altered.

SQL> insert into t1
  2  select * from T1_TEMP
  3  where OBJECT_TYPE='CLUSTER';

50000 rows created.

Elapsed: 00:00:01.92

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  7df6acdvufgud, child number 0
-------------------------------------
insert into t1 select * from T1_TEMP where OBJECT_TYPE='CLUSTER'

Plan hash value: 1045437032

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |             |      1 |        |      0 |00:00:01.68 |    8285 |    747 |
|   1 |  LOAD TABLE CONVENTIONAL     |             |      1 |        |      0 |00:00:01.68 |    8285 |    747 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1_TEMP     |      1 |  29962 |  50000 |00:00:01.53 |    1027 |    744 |
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYP |      1 |  29962 |  50000 |00:00:00.62 |     441 |    195 |
---------------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
Un hard parse a été effectué et le CBO a estimé une cardinalité plus proche de la réalité. Le plan contient un accès indexé et est bien meilleur puisqu’on passe de 18507 logical reads à 8285. Cet exemple est assez basique mais il montre bien que dans certains cas il peut être intéressant d’avoir un hard parse à chaque exécution plutôt que de réutiliser toujours le même plan.  

Comment donc forcer un hard parse ?

Il n’existe pas dans Oracle de moyen explicite de le faire : pas de hint /*+ FORCE_HARD_PARSE */ ni de paramètre caché « _HARD_PARSE » qu’on pourrait mettre à TRUE au niveau de la session. Il faut donc réfléchir un peu et se poser la question suivante: Quand est-ce qu’un Hard parse est effectué ?
Réponses :
1) Lorsqu’il n’existe pas de curseur associé à la requête dans la library cache c'est-à-dire lorsque la requête n’a jamais été exécutée ou bien que le curseur a été flushé de la shared pool pour faire de la place.
2) Lorsque le curseur a été invalidé à cause par exemple d’un calcul de stats sur une table référencée dans la requête
3) Lorsque l’environnement d’exécution est différent (ex : modification d’un paramètre de l’optimiseur au niveau de la session)

Dans mon exemple précédent j’ai utilisé la commande « ALTER SYSTEM FLUSH SHARED_POOL », mais il s‘agit là d’une solution un peu « bourrin » qu’il ne faut surtout pas utiliser dans une application puisqu’il vide tous les curseurs partagés dans la library cache. Avec cette solution on forcerait le hard parse pour toutes les requêtes ce qui dégraderait fortement les performances générales (latches, CPU….).

Solution 1 : Générer un SQL_ID différent
L’idéal pour forcer le hard parse c’est de faire en sorte que chaque exécution de la même requête devienne aux yeux d’Oracle une exécution différente. On pourrait par exemple utiliser une requête SQL dynamique avec une clause WHERE qui serait toujours vraie mais serait différente pour chaque exécution. Par exemple on pourrait ajouter une clause « WHERE :random_value = :random_value » où la variable random_value contiendrait un nombre aléatoire. Cette clause étant toujours vraie, elle n’aurait pas d’incidence sur le résultat de la requête et permettrait de générer un SQL_ID différent à chaque exécution. Imaginons que la requête soit exécutée dans du code PL/SQL, la partie du code qui exécute la requête pourrait ressembler à ceci :
DECLARE
 random_value number;
 BEGIN
 random_value := dbms_random.value;
 execute immediate 'insert /*test_PL */ into t1_temp select * from T1_TEMP where OBJECT_TYPE=''CLUSTER'' and ' || random_value ||  '=' || random_value;
 END;
 /
Rejouons notre test précédent en utilisant ce bloc PL/SQL pour l’exécution de la requête qu’on souhaiterait voir hard parsée à chaque exécution :
-- session 1
SQL> insert into T1_TEMP
  2  SELECT * FROM T1;

100 rows created.

Elapsed: 00:00:00.04
SQL>  Declare
  2     random_value number;
  3   BEGIN
  4     random_value := dbms_random.value;
  5     execute immediate 'insert /*test_PL */ into t1_temp select * from T1_TEMP where OBJECT_TYPE=''CLUSTER'' and ' || random_value ||  '=' || random_value;
  6   END;
  7   /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> select sql_id from v$sql where sql_text like 'insert /*test_PL%';

SQL_ID
-------------
80y71zqh47kr4

1 row selected.

Elapsed: 00:00:00.01
SQL> @plan_curs
SQL> SET LINES 500
SQL> SET PAGES 500
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'));
Enter value for sql_id: 80y71zqh47kr4
Enter value for child_number:
old   1: select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'))
new   1: select * from table(dbms_xplan.display_cursor('80y71zqh47kr4','','allstats last'))

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  80y71zqh47kr4, child number 0
-------------------------------------
insert /*test_PL */ into t1_temp select * from T1_TEMP where
OBJECT_TYPE='CLUSTER' and .63062214722457742739970953577612375345=.63062
214722457742739970953577612375345

Plan hash value: 3185690956

----------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |      1 |        |      0 |00:00:00.01 |       8 |
|   1 |  LOAD TABLE CONVENTIONAL |         |      1 |        |      0 |00:00:00.01 |       8 |
|*  2 |   TABLE ACCESS FULL      | T1_TEMP |      1 |      5 |      5 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
On voit que pour la session où la table temporaire contient 100 lignes on a bien un plan avec le Full table Scan. Le SQL_ID correspondant à cette requête est le 80y71zqh47kr4. Maintenant exécutons la requête pour la session 2 (celle qui contient 1 millions de lignes) :
-- session 2
SQL> insert into t1_temp
  2  select t1.* from t1,t1 t2,t1 t3;

1000000 rows created.

Elapsed: 00:00:43.79
SQL> Declare
  2     random_value number;
  3   BEGIN
  4     random_value := dbms_random.value;
  5     execute immediate 'insert /*test_PL */ into t1_temp select * from T1_TEMP where OBJECT_TYPE=''CLUSTER'' and ' || random_value ||  '=' || random_value;
  6   END;
  7   /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.12
SQL> select sql_id from v$sql where sql_text like 'insert /*test_PL%';

SQL_ID
-------------
5a5mryn0ug6sg
80y71zqh47kr4

2 rows selected.

Elapsed: 00:00:00.00
SQL> @plan_curs
SQL> SET LINES 500
SQL> SET PAGES 500
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'));
Enter value for sql_id: 5a5mryn0ug6sg
Enter value for child_number:
old   1: select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'))
new   1: select * from table(dbms_xplan.display_cursor('5a5mryn0ug6sg','','allstats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  5a5mryn0ug6sg, child number 0
-------------------------------------
insert /*test_PL */ into t1_temp select * from T1_TEMP where
OBJECT_TYPE='CLUSTER' and .97829861972676033156035029701963270324=.97829
861972676033156035029701963270324

Plan hash value: 1045437032

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |             |      1 |        |      0 |00:00:02.75 |   13565 |    775 |
|   1 |  LOAD TABLE CONVENTIONAL     |             |      1 |        |      0 |00:00:02.75 |   13565 |    775 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1_TEMP     |      1 |    109K|  50000 |00:00:02.51 |    1047 |    775 |
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYP |      1 |    109K|  50000 |00:00:01.04 |     461 |    206 |
---------------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_TYPE"='CLUSTER')

Note
-----
   - dynamic sampling used for this statement (level=2)
La requête a bien été hard parsée puisqu’on obtient directement notre plan avec accès indexé. Le SQL_ID vaut 5a5mryn0ug6sg et est bien différent du premier SQL_ID. Au fur et à mesure des exécutions le SQL_ID générée sera différent et donc un hard parse sera effectué à chaque fois. Il est important dans le bloc PL/SQL de ne pas utiliser de clause USING et d’avoir un paramètre CURSOR_SHARING à EXACT, sinon la variable contenant la valeur aléatoire deviendrait une Bind Variable, et on aurait un seul curseur partagée réutilisée (et donc un même plan) à chaque exécution de la requête.

Solution 2 : Invalider le curseur
Au lieu de générer un nouveau SQL_ID à chaque exécution une autre solution consisterait à invalider le curseur déjà dans la shared pool. Pour ce faire vous pouvez utiliser la procédure SET_TABLE_STATS du package DBMS_STATS.
Voyons ça avec un exemple :
-- session 1
SQL> drop table t1;

Table dropped.

SQL>
SQL> create table t1 as select * from dba_objects;

Table created.

SQL>
SQL> create index t1_idx on t1(object_id);

Index created.

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

PL/SQL procedure successfully completed.

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       136
Dans une session 1 j’ai crée une table T1et j’ai récupéré l’identifiant de ma session (SID=136). Dans une 2ème session je regarde les statistiques « parse count (hard) » et « parse count (total) » pour ma première session :
-- session 2
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 (
  5   'parse count (hard)','parse count (total)')
  6   AND ss.sid =136;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                    1500
parse count (hard)                                                      269
Dans ma session 1 j’exécute une requête sur la table T1 :
-- session 1
SQL> select object_id from t1 where object_id= 20;

 OBJECT_ID
----------
        20
Je regarde maintenant les stats sur mon autre session et je constate qu’un Hard Parse a bien été effectué (la statistique « parse count (hard) » a été incrémenté de 1) :
-- session 2
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                    1501
parse count (hard)                                                      270
J‘exécute de nouveau ma requête dans ma 1ère session, puis je regarde les stats :
-- session 1
SQL> select object_id from t1 where object_id= 20;

 OBJECT_ID
----------
        20
  
-- Session 2
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                    1502
parse count (hard)                                                      270
Cette fois la statistique « parse count (total) » a été incrémenté mais pas la stats « parse count (hard) », il s’agit donc d’un soft parse. C’est normal puisque un curseur partageable existait déjà dans la library cache depuis que la requête a été exécutée la 1ère fois.
Maintenant nous allons invalider ce curseur grâce au package DBMS_STATS :
SQL> -- invalidation curseur
SQL>  exec dbms_stats.set_table_stats(USER, 'T1', numrows=>null, no_invalidate=>false);


-- Session 1
SQL> select object_id from t1 where object_id= 20;

 OBJECT_ID
----------
        20
  
-- Session 2
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 (
  5   'parse count (hard)','parse count (total)')
  6   AND ss.sid =136;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                    1503
parse count (hard)                                                      271
Après avoir invalidé le curseur et relancer la même requête sur T1 on constate que la statistique « parse count (hard) » a de nouveau été incrémentée de 1. C’est donc que le Hard Parse a bien eu lieu. Il est important de mettre le paramètre NO_INVALIDATE de la procédure à FALSE afin de forcer l’invalidation du curseur. Vous noterez comme moi qu’Oracle aime parfois compliquer les choses. Au lieu d’avoir un paramètre INVALIDATE à TRUE ou FALSE ils ont préféré un paramètre NO_INVALIDATE. C’est de la triple négation que j’ai du mal à comprendre…enfin bref. L’inconvénient de cette solution c’est que tous les curseurs correspondant aux requêtes qui référencent la table T1 seront invalidés. Alors qu’on souhaiterait uniquement agir sur une requête en particulier, avec cette solution on risque d’impacter beaucoup plus de requêtes.

3ème solution : Flusher le SQL_ID
La 3ème solution qui me vient à l’esprit serait de récupérer avant chaque exécution l’identifiant de la requête à hard parser et de flusher le curseur associé grâce à la procédure dbms_shared_pool.purge. J’avais déjà parlé de cette astuce en Décembre 2010 dans un article intitulé « Flusher un curseur de la shared pool avec DBMS_SHARED_POOL».


CONCLUSION :

Il existe différents moyens de forcer un hard parse pour une requête donnée :
- Faire en sorte de générer un SQL_ID différent à chaque exécution
- Invalider le curseur présent dans la shared pool
- Flusher le curseur de la shared ppol

Lorsque vous décidez de forcer un hard parse pour une requête donnée il faut bien avoir à l’esprit du risque encouru : contentions au niveau de la library cache, parse call plus long et consommation accrue de CPU.
Le hard parse forcé est justifié quand l’utilisation d’un unique plan pour une requête donnée conduit à des performances désastreuses. Il peut arriver aussi qu’on souhaite forcer un hard parse mais sans qu’on ait la possibilité de toucher au code. Randolf Geist a mis en évidence dans son blog comment il était possible de générer des clauses WHERE de manière dynamique en utilisant le VPD (Virtual Private Database). Cet article est disponible ici.

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.