Exadata: Pourquoi mon ratio "Cell Offload Efficiency" est-il négatif?

Avec la 11G R2 il est devenu très pratique d'analyser le plan d'exécution d'une requête en utilisant le RTSM (Real-Time SQL Monitoring) puisqu'il permet notamment d'avoir des statistiques d'exécution pour chaque opération du plan ainsi que quelques métriques et ratios utiles. Dans un environnement Exadata un des ratios que l'on peut voir dans un rapport RTSM est le ratio "Cell Offload" qui est censé indiquer le pourcentage d'octects que les cells ont évité d'envoyer aux DB nodes grâce au smart scan.
Voici un exemple :

alter session set "_serial_direct_read"=always;


select /*+ monitor */ * from T1
where last_used_date>to_date('01/01/2014','DD/MM/YYYY');


Global Stats
===================================================================================================
| Elapsed |   Cpu   |    IO    | Application |  Other   | Fetch | Buffer | Read | Read  |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes | Offload |
===================================================================================================
|    0.16 |    0.01 |     0.01 |        0.00 |     0.14 |  1844 |     53 |    2 |   1MB |  12.28% |
===================================================================================================
 

SQL Plan Monitoring Details (Plan Hash Value=1644978049)
===========================================================================================================================================================================
| Id |          Operation          |     Name      |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Cell   |  Mem  | Activity | Activity Detail |
|    |                             |               | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | Offload | (Max) |   (%)    |   (# samples)   |
===========================================================================================================================================================================
|  0 | SELECT STATEMENT            |               |         |      |        10 |     +0 |     1 |    27642 |      |       |         |       |          |                 |
|  1 |   TABLE ACCESS STORAGE FULL | T1            |   27642 |    4 |        10 |     +0 |     1 |    27642 |    2 |   1MB |  12.28% |    2M |          |                 |
===========================================================================================================================================================================

La requête que j'ai exécutée est une requête candidate au smart scan puisque le Direct Path Read a été forcé, et n'existant pas d'index sur T1, le CBO n'a pas d'autre choix que de choisir un full table scan. De plus j'ai appliqué un filtre sur un champ date qui peut être offloadé.
Le rapport RTSM m'indique que ma requête a mis 0.16 sec pour récupérer 27642 lignes. La dernière colonne intitulée Cell Offload dans la section "Global Status" m'informe également que ma requête a bien fait l'objet d'un smart scan et surtout que cet offloading a permis de réduire d'environ 12% le traffic entre les serveurs de stockage et les DB nodes.
Lors de mon précédent article j'avais mis en évidence le fait qu'on pouvait comparer le nombre d'octects lus par les cells sur disque et le nombre d'octects retournés par les cells aux DB nodes en s'appuyant sur les colonnes physical_read_bytes et io_interconnect_bytes de la vue v$sql:

 SQL> SELECT
  2  round(physical_read_bytes/1024) "KB_reads_from disk",
  3  round(io_cell_offload_eligible_bytes/1024) "KB_offloaded",
  4  round(io_interconnect_bytes/1024) "KB_returned_by_cells"
  5  FROM
  6  gv$sql
  7  WHERE sql_id = 'g6kavcjp0mua8' ;
 

KB_reads_from disk KB_offloaded KB_returned_by_cells
------------------ ------------ --------------------
              1312         1312                 1153

La requête a donc généré 1312KB de lectures sur disque mais seulement 1153KB ont été retournés à la couche base de données soit une économie d'environ 12% (1-(1153/1312) =~ 0.12 ), on retrouve le chiffre du ratio indiqué dans le rapport du RTSM.
Jusqu'ici rien d'extraordaire, mais maintenant regardez l'exécution de la table T2 qui est une exacte copie des données de T1:

select /*+ monitor */  * from T2
where last_used_date>to_date('01/01/2014','DD/MM/YYYY');


Global Stats
===============================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Cluster  |  Other   | Fetch | Buffer | Read | Read  |   Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes | Offload  |
===============================================================================================================
|    0.54 |    0.01 |     0.02 |        0.00 |     0.00 |     0.50 |  1844 |     11 |    2 | 128KB | -488.24% |
===============================================================================================================
 

SQL Plan Monitoring Details (Plan Hash Value=4114405226)
===============================================================================================================================================================================
| Id |          Operation          |    Name     |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |   Cell   |  Mem  | Activity |   Activity Detail    |
|    |                             |             | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | Offload  | (Max) |   (%)    |     (# samples)      |
===============================================================================================================================================================================
|  0 | SELECT STATEMENT            |             |         |      |         9 |     +1 |     1 |    27642 |      |       |          |       |          |                      |
|  1 |   TABLE ACCESS STORAGE FULL | T2          |   27642 |    2 |         9 |     +1 |     1 |    27642 |    2 | 128KB | -488.24% |    1M |   100.00 | reliable message (1) |
===============================================================================================================================================================================

Le RTSM nous indique que la requête a mis 0.54 sec au lieu de 0.16 avec T1 pour récupérer le même nombre de lignes. Mais le plus inquiétant est de voir que le ratio est tombé à -488%. Bizarre vous ne trouvez pas?
Jetons un oeil aux colonnes de v$sql pour voir le traffic entre les cells et les DB nodes:

SQL> SELECT
  2  round(physical_read_bytes/1024) "KB_reads_from disk",
  3  round(io_cell_offload_eligible_bytes/1024) "KB_offloaded",
  4  round(io_interconnect_bytes/1024) "KB_returned_by_cells"
 5  FROM
  6  gv$sql
  7  WHERE sql_id = '83vyvg2cvg1b7' ;
 
KB_reads_from disk KB_offloaded KB_returned_by_cells
------------------ ------------ --------------------
               128           96                  768

Non seulement le nombre d'octects lus sur disque pour T2 est inférieur  au nombre d'octects lus pour T1 (128KB vs 1312KB) mais en plus le nombre d'octects renvoyés par les cells aux DB nodes est 6 fois plus élevé que ce qui a été lu sur disque.
Qu'a donc cette table T2 de si particulier? Et bien ce que j'ai ommis de vous dire c'est que la table T2 contairement à la table T1 est une table compressée en ARCHIVE HIGH (un des modes de la compression HCC):

 SQL> select compression from dba_tables where table_name='T1';
 
COMPRESS
--------
DISABLED
 

SQL> select compress_for from dba_tables where table_name='T2';
 
COMPRESS_FOR
------------------------------
ARCHIVE HIGH
  

L' un des avantage de la compression HCC disponible dans Exadata c'est qu'elle réduit sensiblement la taille occupée par les objets sur disque ce qui explique pourquoi le nombre d'octects lus par les cells est bien inférieur pour la table T2. L'autre avantage est que, lorsque la requête fait l'objet d'un smart scan, la décompression, qui est une opération extrêmement coûteuse en CPU, peut se faire au niveau des serveurs de stockage évitant ainsi au DB nodes d'avoir à effectuer ce travail. Dans le cas de ma requête sur T2 c'est ce qui s'est produit: la requête a été offloadée ce qui a permis aux cells de décompresser les données, et ce sont les données décompressées qui ont été envoyées aux DB nodes générant ainsi un traffic plus important. C'est ce qui explique pourquoi ici mon ratio "Cell Offload" est négatif.

Pour prouver que la décompression a été effectuée au niveau des cells il suffit de s'intéresser à la statistique cell CUs sent uncompressed dans v$sesstat qui indique le nombre de Compression Units (CUs) envoyés aux DB nodes  après avoir été décompressés. En comparant la valeur de cette statistique avant et après avoir executé ma requête je peux m'assurer que ce sont des données décompressées qui ont été envoyées:

SQL> SELECT sn.name, ss.value
  2  FROM v$statname sn, v$sesstat ss
  3  WHERE sn.statistic# = ss.statistic#
  4  AND sn.name like ('cell CUs sent uncompressed')
  5  AND ss.sid =(select sid from v$mystat where rownum=1);
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell CUs sent uncompressed                                          2232010
 
select /*+ monitor */  * from T2
where last_used_date>to_date('01/01/2014','DD/MM/YYYY');
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell CUs sent uncompressed                                          2232011  -->incrémenté
 
select /*+ monitor */  * from T1
where last_used_date>to_date('01/01/2014','DD/MM/YYYY');
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell CUs sent uncompressed                                          2232011  --> non incrémenté

Le test ci-dessus nous montre qu'après avoir exécuté la requête sur T2 la statistique "cell CUs sent uncompressed" est incrémenté de 1 alors qu'après la requête sur T1 la stats n'est pas incrémentée.

Dans les environnements de type Data Warehouse il est très fréquent de voir les données compressées en mode HCC et donc des ratios dans vos rapports RTSM qui n'indiquent pas vraiment l'efficacité du smart scan.

La compression n'est pas la seule raison pouvant expliquer un ratio Cell Offload négatif. En effet, la metric physical_read_bytes indique non seulement les octects lus sur disque mais également les octects écrits sur disque. Par exemple, toutes les opérations qui induisent une écriture comme celles dans le tablespace temporaire suite à un HASH JOIN ou un tri vont avoir une incidence sur ce ratio d'autant plus que le mirroring ASM va doubler (voir tripler) le nombre d'octects mesurés.

C'est pour toutes ces raisons qu'il faut lire avec des pincettes les valeurs du ration Cell Offload que vous obtenez dans vos rapport RTSM. Elles peuvent ne pas refléter réellement l'efficacité de vos requêtes faisant l'objet d'un offloading.