Prenons un ETL, outil chargé d’extraire bêtement des données d’un gros progiciel d’entreprise, SAP pour ne pas le nommer. SAP lui-même s’appuie sur une base de données Oracle. L’ETL pourrait en principe attaquer les données directement au niveau Oracle, mais SAP impose que l’on passe par lui[1].

De ces trois outils, chacun utilise une variante de la lingua franca des bases de données, le SQL : Oracle connaît le PL/SQL (langage efficace et sans chichis que j’ai appris à apprécier), SAP utilise l’ABAP (mélange de Cobol et de SQL limité par sa tendance à vouloir s’appuyer sur plusieurs bases de données différentes sans en exploiter une à fond), et l’ETL définit ses requêtes sous forme graphique avec des morceaux de pseudo-SQL[2].

Besoin

Au départ, le besoin était tout simple : une bête jointure de deux tables sur les « documents article », et récupération des lignes où au moins une de trois dates potentielles était dans une certaine fourchette.

Du côté de l’ETL, le développeur a exprimé cela ainsi :

Joli graphique joignant les tables MKPF (en-têtes) et MSEG (lignes)[3],
et clause de filtrage définie ainsi :

MKPF.MBLNR = MSEG.MBLNR (jointure)
AND
(
( MKPF.CUPDT >= $PARAM2 AND MKPF.CPUDT <= $PARAM1 )
OR ( MKPF.AEDAT >= $PARAM2 AND MKPF.AEDAT <= $PARAM1 )
OR ( MKPF./BEV2/ED_AEDAT >= $PARAM2 AND MKPF./BEV2/ED_AEDAT <= $PARAM1 )
)

Boum !

L’ETL s’adresse donc à SAP, et génère pour cela à la volée un programme ABAP ; le noyau SAP traduit cette requête dans le SQL d’Oracle, lequel renvoie les données au programme ABAP, dont la sortie est renvoyée à l’ETL.

Après quelques mois en production, ce programme a un soir littéralement explosé (le noyau de SAP refusa de lui allouer plus de mémoire, considérant qu’avec plusieurs gigaoctets il abusait déjà).

En fouillant on découvre déjà que la volumétrie remontant dans SAP se compte en millions de lignes (tables de mouvements de stocks d’une entreprise de belle taille sur plusieurs années), et qu’aucune des colonnes de filtrage n’est indexée dans la base de donnée. Le résultat final que recueille l’ETL ne compte cependant que peu d’enregistrements.

Dans le contexte présent d’un cluster de machines bien burnées, Oracle exécute ce double full scan en peu de temps — pas assez pour qu’on ait besoin de demander à un chef d’imposer à un administrateur système réticent de rajouter des index lourds sur des tables très sollicitées[4]. Ce n’est pas un simple classique problème de performances.

Le programme ABAP généré par l’ETL ressemble à ceci (je simplifie) :

SELECT
MSEG9~MBLNR (champs)
MSEG9~MJAHR
MSEG9~ZEILE
...
INTO (MSEG9MBLNR, (stockage de la ligne dans des variables temporaires)
MSEG9MJAHR,
MSEG9ZEILE...)
FROM MSEG AS MSEG9
INNER JOIN MKPF AS MKPF9 (jointure)
ON MKPF9MBLNR
.
...
IF ( ( ( ( MKPF9CPUDT <= $PARAM1 ) (clause de filtrage)
OR ( MKPF9AEDAT <= $PARAM1 ) )
OR ( MSEG9/BEV2/ED_AEDAT <= $PARAM1 ) )
AND ( ( ( ( MKPF9CPUDT >= $PARAM2 )
OR ( MKPF9AEDAT <= $PARAM1 ) )
OR ( MSEG9/BEV2/ED_AEDAT <= $PARAM1 ) )
AND ( ( ( ( MKPF9CPUDT <= $PARAM1 )
OR ( MKPF9AEDAT >= $PARAM2 ) )
OR ( MSEG9/BEV2/ED_AEDAT <= $PARAM1 ) )
AND ( ( ( ( MKPF9CPUDT >= $PARAM2 )
OR ( MKPF9AEDAT >= $PARAM2 ) )
OR ( MSEG9/BEV2/ED_AEDAT <= $PARAM1 ) )
AND ( ( ( ( MKPF9CPUDT <= $PARAM1 )
OR ( MKPF9AEDAT <= $PARAM1 ) )
OR ( MSEG9/BEV2/ED_AEDAT >= $PARAM2 ) )
AND ( ( ( ( MKPF9CPUDT >= $PARAM2 )
OR ( MKPF9AEDAT <= $PARAM1 ) )
OR ( MSEG9/BEV2/ED_AEDAT >= $PARAM2 ) )
AND ( ( ( ( MKPF9CPUDT <= $PARAM1 )
OR ( MKPF9AEDAT >= $PARAM2 ) )
OR ( MSEG9/BEV2/ED_AEDAT >= $PARAM2 ) )
AND ( ( ( MKPF9CPUDT >= $PARAM2 )
OR ( MKPF9AEDAT >= $PARAM2 ) )
OR ( MSEG9/BEV2/ED_AEDAT >= $PARAM2 ) ) ) ) ) ) ) ) ).

... (sauvegarde de la ligne dans une table interne qui sera envoyée à l’ETL)

ENDIF.

ENDSELECT. (fin du parcours des lignes ramenées)

J’ignore pourquoi la clause IF est si tourmentée, mais elle est mathématiquement équivalente à celle d’origine (une des trois dates doit être dans la fourchette demandée).

Le point à retenir est que ce IF est hors de la requête. Chacune des millions de lignes de la table passe dans ce IF !
On objectera naïvement que de toute manière, faute d’index, il n’y a pas d’autre moyen que ce fastidieux méga-test.

Mais l’important est l’endroit où ce test s’effectue : les millions de lignes sont récupérées par la base Oracle, en sortent, entrent dans le noyau SAP (potentiellement une autre machine), et c’est le processeur ABAP qui se charge des tests sur les dates.

Reformulation

Le changement du programme ABAP est assez basique : j’ai déplacé la clause de filtrage dans la requête ABAP, espérant ainsi que SAP traduira cela à Oracle en une seule requête avec la clause de filtrage. (Et pour être propre on réécrit lisiblement la clause, et on rajoute MJAHR dans la jointure, qui manquait sans que cela gêne en pratique.)

SELECT...
FROM MSEG AS MSEG9
INNER JOIN MKPF AS MKPF9
ON ( MKPF9MBLNR
AND MKPF9MJAHR )
WHERE ( MKPF9~CPUDT BETWEEN $PARAM2 AND $PARAM1 )
OR ( MKPF9~AEDAT BETWEEN $PARAM2 AND $PARAM1 )
OR ( MSEG9~/BEV2/ED_AEDAT BETWEEN $PARAM2 AND $PARAM1 )

Oracle fait toujours un double full scan, le temps d’exécution est très proche, mais les lignes filtrées ne sortent même pas de la couche SQL, Oracle les jette à peine délivrées par le disque dur. On a évité à des gigaoctets de données de traverser au moins deux couches du mille-feuilles et d’occuper de la précieuse mémoire.

Pour les curieux, le SQL généré par SAP après la modification est basique :

SELECT
T_00 . "MBLNR" , T_00 . "MJAHR" , T_00 . "ZEILE" , ...
FROM
"MSEG" T_00 , "MKPF" T_01
WHERE
(jointure)
( T_01 . "MANDT"[5] = :A0 AND T_01 . "MBLNR" = T_00 . "MBLNR"
AND T_01 . "MJAHR" = T_00 . "MJAHR" ) AND T_00 . "MANDT" = :A1
(filtre sur les dates)
AND ( T_01 . "CPUDT" BETWEEN :A2 AND :A3
OR T_01 . "AEDAT" BETWEEN :A4 AND :A5
OR T_00 . "/BEV2/ED_AEDAT" BETWEEN :A6 AND :A7
)

Moralité

Cet exemple est inhabituel : le développeur comme l’administrateur d’une base cherchent en général à limiter les full scans sur des tables aussi volumineuses, et rajoutent des index (mais il faut contrebalancer avec le coût en disque et en maintenance). On a ici la conjonction de plusieurs problèmes :

  • pas d’index sur de grosses tables (quoique, reconnaissons-le, des stats rafraîchies ont mené à un parcours un peu différent en joignant les tables par leur clé commune ; dans le cas d’un parcours complet des tables, cela n’est pas forcément une bonne chose, et on ne change rien au problème de la volumétrie) ;
  • un ETL qui n’a pas le droit de s’adresser directement à Oracle, alors qu’il connaît très bien son langage (un bon ETL est polyglotte : Oracle dans toutes ses variantes, Sybase, MS SQL Server, DB2...) ;
  • cet ETL (ou plutôt son plug-in pour SAP) commet une erreur stupide en séparant requête et filtrage (bug d’optimisation)
    (Ajout de 2014 : Cet ETL, je l’utilise encore des années après, et même dans des incarnations plus modernes il reste le plus stupide et le moins agréable de tous ceux que j’ai pu manipuler — mais il est vendu par SAP) ;
  • le compilateur ABAP n’est pas assez fûté pour repérer le problème et demande à Oracle le contenu complet de MSEG et MKPF ;
  • le compilateur ABAP semble incapable de gérer efficacement des paquets de plusieurs gigaoctets, il semble tout vouloir traiter d’un bloc, alors qu’Oracle traite ses données par paquets maniables de quelques dizaines de lignes (j’aimerais des détails, cette limite de SAP m’étonne) : le même bug ne serait pas apparu en PL/SQL ;
  • ajoutons un effet pervers dû au dimensionnement imposant des machines concernées : le problème n’est apparu ni en développement ni en recette (de plus ces bases sont anciennes, très anciennes, et je le déplore tous les jours), mais en production après un certain temps, quand les tables ont dépassé une certaine taille.

Comme toutes les grandes catastrophes[6], ce problème naît d’une accumulation de plusieurs bugs, et non d’un seul.

Effet mille-feuilles et communication

Les problèmes de communication entre programmes et la génération automatique de code sont courants, mais le « mille-feuilles » est une bonne pratique en programmation : vive la séparation des tâches entre modules spécialisés ! Cf couches OSI, TCP/IP, le protocole X, etc.

Mais ce n’est pas le cas ici : trois éléments ont chacun leur langage pour exprimer ce qui est fondamentalement une requête SQL ; la traduction n’est pas parfaite ou limitée, et n’utilise en tout cas pas toutes les possibilités de l’outil sous-jacent (Oracle).

On n’a pas délégation du travail et encapsulation des détails propres à un niveau qui ne concernent pas les autres (comme entre les différences couches de TCP/IP ou X), mais réécriture de consignes avec réinterprétation au passage à chaque étape : à la syntaxe près, la requête de l’ETL est strictement la même que le SQL d’Oracle, les couches qui font le boulot sont en dessous (dans le noyau Oracle).

Analogie

Cette histoire me rappelle furieusement le fonctionnement d’un service informatique au sens large, où la définition d’un développement à effectuer passe de l’utilisateur au support au fonctionnel interne au fonctionnel externe à l’analyste présent chez le client au chef de projet externe au développeur sur un autre continent, avec spécifications différentes à chaque niveau suivant les consignes/niveau/langue/priorités/besoins/limites/obsessions/normes de chaque strate ; alors que le premier besoin exprimée par l’utilisateur pourrait souvent (pas toujours, loin de là !) suffire au développeur final pour travailler, sans rajouter un effet téléphone arabe et une lourdeur monstrueuse en gestion et « pilotage ».

Notes

[1] Je dis toujours que SAP est totalitaire car il veut tout contrôler.

[2] Ce qui devient vite illisible à mon avis.

[3] Si vous ne comprenez pas immédiatement la fonction des tables et colonnes par leur nom, c’est tout à fait normal habituel sur SAP.

[4] Au passage, je suis surpris du faible nombre d’index présents sur les tables de SAP par rapport à celles du concurrent Oracle Applications.

[5] Le mandant est une clé implicite de chaque table. Il correspond à une « vision du monde » et permet de séparer plusieurs jeux de données (test, paramétrage...) voire certains programmes.

[6] N’exagérons pas, c’est « juste » du décisionnel, ni la production ni la logistique n’ont été affectées.