Fonctions approximatives Oracle 12.2 (#3)

Avec Oracle 12.2, l’éventail des fonctions approximatives s’est élargi avec l’adjonction de fonctions de calcul de percentiles: APPROX_MEDIAN, APPROX_PERCENTILE, APPROX_PERCENTILE_DETAIL, APPROX_PERCENTILE_AGG, TO_APPROX_PERCENTILE

A l’instar des fonctions de comptage distinct présentées dans l’article précédent, l’idée est ici de disposer de fonctions capables de traiter de manière performante de très grands volumes avec des besoins limités de resource et cela au prix d’une approximation – maitrisée – des résultats. Le mécanisme est basé sur l’algorithme Count-min sketch.

En utilisant les mêmes données que dans les articles précédents, commençons par constituer une table listant pour chaque contributeur de la version anglaise de Wikipedia le nombre d’actions enregistrées:

SQL> CREATE TABLE T_NB_CONTRIBUTIONS
  2  AS
  3        SELECT uname, COUNT (*) nb
  4          FROM log_contribs
  5      GROUP BY uname;

Table created.

SQL>

La table contient environ 29 millions d’enregistrements pour un peu plus de 600MB:

SQL> SELECT COUNT (*) FROM T_NB_CONTRIBUTIONS;

  COUNT(*)
----------
  28839445

SQL>
SQL> SELECT bytes / POWER (1024, 2)
  2    FROM user_segments
  3   WHERE segment_name = 'T_NB_CONTRIBUTIONS';

BYTES/POWER(1024,2)
-------------------
                624

SQL>

A partir de cette table, on peut déterminer quel nombre de contributions correspond au 99eme percentile:

SQL> set timing on
SQL>
SQL> SELECT PERCENTILE_DISC (0.99) WITHIN GROUP (ORDER BY nb)
  2    FROM T_NB_CONTRIBUTIONS;

PERCENTILE_DISC(0.99)WITHINGROUP(ORDERBYNB)
-------------------------------------------
                                          3

Elapsed: 00:00:41.96
SQL>
SQL> SELECT a.name, b.VALUE
  2     FROM v$statname a, v$mystat b
  3    WHERE a.statistic# = b.statistic# AND a.name = 'session pga memory max';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory max                                            109971816

Elapsed: 00:00:00.03
SQL> 

42 secondes et environ 100MB de PGA ont été nécessaire pour ce calcul.

Utilisons maintenant une fonction approximative:

SQL> set timing on
SQL> 
SQL> SELECT APPROX_PERCENTILE (0.99 DETERMINISTIC) WITHIN GROUP (ORDER BY nb)
  2    FROM T_NB_CONTRIBUTIONS;

APPROX_PERCENTILE(0.99DETERMINISTIC)WITHINGROUP(ORDERBYNB)
----------------------------------------------------------
                                                         3

Elapsed: 00:00:12.85
SQL> 
SQL> SELECT a.name, b.VALUE
  2     FROM v$statname a, v$mystat b
  3    WHERE a.statistic# = b.statistic# AND a.name = 'session pga memory max';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory max                                              4262248

Elapsed: 00:00:00.03
SQL>

Le temps d’exécution a été divisé par 3.26 et l’impact de l’opération sur la PGA est marginal (4MB correspond à l’allocation initiale de la session).

Essayons maintenant de déterminer combien d’actions sont nécessaires pour faire parti des 1000 utilisateurs les plus actifs de la plateforme anglaise de Wikipedia:

SQL> SELECT 1 - (1000 / COUNT (*))
  2    FROM T_NB_CONTRIBUTIONS;

1-(1000/COUNT(*))
-----------------
       .999965325

SQL>

Cela correspond donc au percentile 99.9965325. Comparons le calcul exact et une approximation:

SQL> set timing on
SQL>
SQL> SELECT APPROX_PERCENTILE (0.999965325 DETERMINISTIC) WITHIN GROUP (ORDER BY nb)
  2    FROM T_NB_CONTRIBUTIONS;

APPROX_PERCENTILE(0.999965325DETERMINISTIC)WITHINGROUP(ORDERBYNB)
-----------------------------------------------------------------
                                                             6620

Elapsed: 00:00:12.80
SQL>
SQL> SELECT PERCENTILE_DISC (0.999965325) WITHIN GROUP (ORDER BY nb)
  2    FROM T_NB_CONTRIBUTIONS;

PERCENTILE_DISC(0.999965325)WITHINGROUP(ORDERBYNB)
--------------------------------------------------
                                              6591

Elapsed: 00:00:39.50
SQL>

On obtient à nouveau un facteur 3 dans le temps d’exécution des deux requêtes. Le calcul de la valeur approximative est 3 fois plus rapide pour une erreur de l’odre de 0.5% [100 * ABS (6620 – 6591) / 6591]

A l’instar des fonctions approximatives de comptage distinct, les fonctions approximatives de calcul de percentile autorisent la « réaggregation » via APPROX_PERCENTILE_DETAIL & APPROX_PERCENTILE_AGG. Cf le billet précédent pour un exemple similaire.