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