Compression SQL*Net, par Jonathan Lewis

Publié le par

Cet article est la traduction d’un article de Jonathan Lewis publié sur son blog. L’article original en anglais se trouve ici.

Il montre un fonctionnalité peu connue: la compression des données transférées par SQL*Net lorsque il y a répétition de valeurs d’une ligne à l’autre. Et l’avantage de récupérer ces données triées sur ces colonnes répétitives.

Voici une petite démonstration que j’avais l’intention d’écrire depuis ces dernières années.
C’est très simple: je crée une table, et je l’interroge plusieurs fois.

execute dbms_random.seed(0)
 
create table t1
as
with generator as (
  select  --+ materialize
    rownum   id
  from  all_objects
  where  rownum <= 3000
)
select
  lpad(dbms_random.string('U',2),40,'X')  v1
from
  generator  v1,
  generator  v2
where
  rownum <= 10000
;
 
break on report
compute sum of number_of_groups on report
 
select
  group_size, count(*)  number_of_groups
from
  (
  select
    v1, count(*) group_size
  from
    t1
  group by
    v1
  )
group by
  group_size
order by
  group_size
;

Les données sont dans une seule colonne: une chaîne de 2 caractères aléatoires, complétée à 40 caractères avec des ‘X’.
La première requête montre comment les données sont distribuées:

GROUP_SIZE NUMBER_OF_GROUPS
---------- ----------------
         5                2
         6                5
         7                6
         8               15
         9               27
        10               30
        11               51
        12               55
        13               63
        14               59
        15               78
        16               65
        17               63
        18               54
        19               21
        20               35
        21               19
        22               14
        23                8
        24                5
        26                1
           ----------------
sum                     676

Avec la manière dont j’ai écrit cette requête, vous pouvez voir qu’il y a deux paires de caractères qui apparaissent cinq fois chacune, une paire de caractères qui apparaît 26 fois, et 78 paires de caractères qui apparaissent 15 fois chacune. cela veut dire qu’il y a une certaine répétition, et un total de 676 paires de caractères différentes (ce qui n’est pas trop surprenant vu que j’ai pris 10000 fois de manière aléatoire une paire de lettres de l’alphabet majuscule , et que 26 * 26 = 676).

Donc faisons une requête sur toutes ces données à partir de SQL*Plus:

set arraysize 1000
set autotrace traceonly statistics
select  v1 from  t1 ;
select  v1 from  t1 order by v1;
set autotrace off

La sortie n’est pas trop grande vu que j’ai défini autotrace pour n’afficher que la trace, et seulement les statistiques. Voici les résultats de la première requête:

10000 rows selected.

Elapsed: 00:00:00.07

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        143  consistent gets
          0  physical reads
          0  redo size
     425479  bytes sent via SQL*Net to client
        494  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

Nous avons récupéré 10000 lignes (rows processed), fait 11 allers-retours réseau (SQL*Net roundtrips), et le volume reçu par le client via SQL*Net est de 420Ko. Avec 10000 lignes qui font environ 40 caractères par ligne, ce chiffre de 420Ko n’est pas surprenant.

Mais à quoi vont ressembler les statistiques lors de la seconde requête (celle où j’ai ajouté une clause order by):

10000 rows selected.

Elapsed: 00:00:00.04

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        133  consistent gets
          0  physical reads
          0  redo size
      79287  bytes sent via SQL*Net to client
        494  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

On peut noter noter que la requête s’est terminée plus vite (mais comparer 0,07 secondes à 0,03 seconde n’est pas très significatifs). Par contre ce qui est vraiment intéressant, c’est que le volume de données transférées sur le réseau est seulement 75Ko environ – soit pas loin de 6 fois moins de ce qu’on pourrait attendre.

Il y a deux questions qui se posent ici:

  • comment avons-nous reçu 10000 lignes d’environ 40 octets chacunes, dans seulement 79Ko de données transmises ?
  • Et, pour pour celui qui observe bien, comment se fait-il que le nombre d’aller-retours (SQL*Net roundtrips) n’ait pas diminué ? (Je laisse la deuxième question pour un prochain article – il y a trop de choses à dire là dessus.)

Depuis des années, au moins depuis la version 8.0, SQL*Net a été en mesure de faire de la compression (ou plutôt de la « dé-duplication »). Si un paquet SQL*Net (appelé ‘SDU’ – single session data unit ) contient une répétition de donnée et qui suit le bon pattern, alors SQL*Net peut supprimer les doublons avant la transmission (et bien sûr les remettre lors de la réception).

Le pattern en question est (ou semble être) décrit par: « Est-ce la ligne a la même valeur que la ligne précédente pour la colonne X ? Si oui alors je n’ai pas besoin d’envoyer à nouveau le contenu de la colonne X. » Donc, la dé-duplication se fait d’une ligne à l’autre: ce n’est pas une comparaison entre colonnes, ni une comparaison sur l’ensemble du paquet. Cela signifie que si nous trions nos lignes de manière appropriée, nous pouvons maximiser le volume de données pour lequel on aura souvent « les colonnes de cette ligne » qui sont souvent identiques aux « colonnes correspondantes de la ligne précédente ».

Dans mon exemple, evidemment, l’ order by assure que qu’au moins 5 lignes consécutives sont identiques, et celà peut aller jusqu’à 26 lignes, ce qui permet d’éliminer un nombre important de chaines de 40 caractères.

Note: Je peux m’avancer à dire que cette dé-duplication fonctionne au moins depuis la version 8 d’Oracle (ou plutôt, de SQL*Net) parce que j’avais eu une discussion sur les fonctionnalités ‘objet-relationnel’ juste après la parution de la 8.0
Quelqu’un avait essayé de me persuader qu’il valait mieux envoyer sur le réseau des « objets parents incluant plusieurs enregistrements fils » plutôt que d’envoyer « la jointure du parent avec les fils ». Leur argument était que l’approche objet réduisait le trafic réseau car la jointure répétait les colonnes du parent avec chaque fils contrairement à la méthode objet. Mais, comme j’ai pu le souligner, le SQL peut répéter les colonnes, mais SQL*Net élimine cette redondance. (En fait, dans certains cas, l’approche objet augmentait le trafic réseau à cause des messages envoyés par le client pour obtenir la définition du type objet – mais je ne sais pas si c’est toujours le cas.)

En réponse à un commentaire de Alberto Dell’Era, Jonathan Lewis précise que cette dé-duplication se fait dans les deux sens. Il cite un cas particulier d’un chargement Informatica où le mode ‘array insert‘ était plus rapide, grâce au moindre volume réseau, que le mode ‘direct-path‘ qui envoit des blocs complets.

Laisser un commentaire