jeudi 27 octobre 2016

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.



dimanche 9 octobre 2016

Exadata - Plan d'exécution et Smart scan

J'ai été amené ces derniers mois à travailler sur des environnements Exadata et je me suis rendu compte que beaucoup de développeurs et DBAs se trompaient dans leurs manières de s'assurer qu'une requête avait été offloadée ou pas, ce qui est fort dommage car ne pas bénéficier du OFFLOADING (aka SMART SCAN), c'est se priver sans le savoir de ce pour quoi Exadata a été conçu au départ.

Pour ceux qui ne le savent pas, le smart scan est la fonctionnalité propre à la plateforme Exadata permettant de déporter au niveau de la couche de stockage le traitement de la donnée (d'où le terme anglais de offloading). La selection des colonnes ainsi que les filtres des lignes au niveau de la table ne se font ainsi plus au niveau de la base de données mais au niveau des serveurs de stockages (aka cells) permettant ainsi de réduire le volume de données renvoyées à la base. Ce smart scan ne s'applique qu'aux full scans de segments (tables, index, vues materialisées etc.).

Voici un plan d'exécution impliquant un full scan d'une table dans un environnement Exadata:
 
SQL> explain plan for
 2 select object_name from T1 where owner='SYS';
 

Explained.
 

SQL> @plan
SQL> SET LINES 500
SQL> SET PAGES 500
SQL> select * from table(dbms_xplan.display);
 

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3617692013 

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |  2124 | 59472 |   182   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| T1   |  2124 | 59472 |   182   (5)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
--------------------------------------------------- 

   1 - storage("OWNER"='SYS')
       filter("OWNER"='SYS')
Cette requête est une requête qui est censée bénéficier du smart scan car elle ne selectionne qu'une seule colonne et possède une clause de filtrage. Le smart scan consisterait donc pour les cells à ne renvoyer à la couche database que la colonne OBJECT_NAME et les lignes correspondant à la clause OWNER='SYS'. Dans un environnement classique (non-exadata), un full scan irait chercher sur disque tous les blocs de la table T1, et le process server se chargerait de filtrer les lignes et les colonnes.

Si on regarde le plan d'exécution ci-dessus on constate que l'opération TABLE ACCESS FULL a été renommée en TABLE ACCESS STORAGE FULL. Ce qui change ici c'est le mot STORAGE qui indique qu'on est dans un environnement Exadata mais, l'erreur qui est souvent commise est de croire que cela indique la réalisation d'un smart scan.

Notez également la clause storage("OWNER"='SYS') dans la section "Predicate Information", celle-ci est ajouté à la clause filter("OWNER"='SYS'). La clause storage indique que ce prédicat pourrait être offloadé au niveau de la couche storage mais ne garantit pas que cela a réellement eu lieu. Pour le prouver, je vais exécuter la requête, et bien que le plan indique STORAGE dans la partie predicate je peux vous garantir que le SMART SCAN n'aura pas lieu:
SQL_ID  72v0k4fxst8cu, child number 0
-------------------------------------
select /* TEST_01 */ object_name from T1 where owner='SYS'
 

Plan hash value: 3617692013 

--------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |      1 |        |    206K|00:00:00.20 |   24136 |
|*  1 |  TABLE ACCESS STORAGE FULL| T1   |      1 |   2124 |    206K|00:00:00.20 |   24136 |
--------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 

   1 - storage("OWNER"='SYS')

       filter("OWNER"='SYS')

 

SQL> select IO_CELL_OFFLOAD_ELIGIBLE_BYTES from gv$sql where sql_id='72v0k4fxst8cu';
 

IO_CELL_OFFLOAD_ELIGIBLE_BYTES
------------------------------
                             0
Voici donc un moyen sûr de savoir si le SMART SCAN a eu lieu. Il suffit de regarder la colonne IO_CELL_OFFLOAD_ELIGIBLE_BYTES de V$SQL qui indique le nombre d'octects traités via un smart scan. Si cette colonne a une valeur supéreure à zéro alors cela indique qu'un OFFLOADING a été effectué pour la requête en question.

Après l'exécution de ma requête la colonne
IO_CELL_OFFLOAD_ELIGIBLE_BYTES est à zéro on en conclut donc que le offloading ne s'est pas produit et pourtant j'ai bien le mot STORAGE présent dans le plan et dans la section predicate. Cela prouve bien qu'il ne faut surtout pas se fier à cette information pour s'assurer que le smart scan a été effectué.

Vous vous interrogez alors surement sur la raison pour laquelle la requête n'a pas pu être offloadée? Je suis un petit cachotier, et ce que je ne vous ai pas dit c'est qu'avant d'exécuter ma requête j'ai setté le paramètre "_serial_direct_read" à NEVER ce qui empêche les lectures en direct path d'avoir lieu pour les requêtes exécutées en mode serial.

Ce qu'il faut absolument assimiler c'est que non seulement le SMART SCAN ne peut avoir lieu que si l'opération est un full scan de segment mais il faut également que ce full scan se fasse en mode DIRECT PATH READ (c'est à dire lorsque le process server bypass le buffer cache pour mettre les données dans la PGA).

Ma requête ne s'exécutant pas en parallèle et le paramètre "_serial_direct_read" étant setté à NEVER le full scan de ma table T1 ne peut se faire en mode DIRECT PATH READ et le offloading est donc d'office inhibé.

Gardez également à l'esprit que le DIRECT PATH READ n'est pas une décision de l'optimiseur mais un choix effectué par le moteur Oracle à l'exécution de la requête.


Voyons maintenant ce que donne l'exécution de la requête lorsqu'on autorise le DIRECT PATH READ:
SQL> alter session set "_serial_direct_read"=always;
 
Session altered. 

SQL_ID  1tuc7v88bu0fq, child number 0
-------------------------------------
select /* TEST_02 */ object_name from T1 where owner='SYS'
 

Plan hash value: 3617692013 

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |      1 |        |    206K|00:00:00.20 |   10412 |  10393 |       |       |          |
|*  1 |  TABLE ACCESS STORAGE FULL| T1   |      1 |   2124 |    206K|00:00:00.20 |   10412 |  10393 |  1025K|  1025K| 7199K (0)|
--------------------------------------------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 

   1 - storage("OWNER"='SYS')

       filter("OWNER"='SYS')
       

SQL> select IO_CELL_OFFLOAD_ELIGIBLE_BYTES from gv$sql where sql_id='1tuc7v88bu0fq';
 

IO_CELL_OFFLOAD_ELIGIBLE_BYTES
------------------------------
                     340525056
J'obtiens exactement le même plan d'exécution que precédemment mais cette fois la colonne IO_CELL_OFFLOAD_ELIGIBLE_BYTES de V$SQL est supérieure à zéro (340525056/1024/1024=324,7MB) ce qui m'indique que la requête a bien été offloadée et que donc le scan s'est fait en mode DIRECT PATH READ.

On a également d'autres colonnes dans V$SQL qui nous donnent plus d'information sur le smart scan opéré. Il s'agit des colonnes PHYSICAL_READ_BYTES et IO_INTERCONNECT_BYTES. La première indiquant le nombre d'octects lus sur disque et la deuxième le nombre d'octects retournés par les cells à la base. Donc en comparant la colonne PHYSICAL_READ_BYTES à la colonne IO_INTERCONNECT_BYTES on peut savoir le nombre d'octects envoyées réellement à la base grâce au smart scan:
SQL> SELECT
  2  round(physical_read_bytes/1024/1024),
  3  round(io_cell_offload_eligible_bytes/1024/1024),
  4  round(io_interconnect_bytes/1024/1024)
  5  FROM
  6  gv$sql
  7  WHERE sql_id = '1tuc7v88bu0fq' ;
 

ROUND(PHYSICAL_READ_BYTES/1024/1024) ROUND(IO_CELL_OFFLOAD_ELIGIBLE_BYTES/1024/1024) ROUND(IO_INTERCONNECT_BYTES/1024/1024)
------------------------------------ ----------------------------------------------- --------------------------------------
                                 325                                             325                              7 

Ainsi, on voit que sur les 325MB lus sur disque la totalité a été effectué en utilisant le smart scan et que grâce à cela seulement 7MB ont été retournés aux serveurs de données. Sans Exadata et sa capacité à offloader, la base aurait dû traiter les 325MB de données.

Il existe d'autres manières de s'assurer que le smart scan a bien été effectué (trace 10046, RealTime SQL Monitor) mais je ne les détaillerai pas ici.
Le but de cet article était surtout de mettre en évidence le fait de ne pas se fier aux opérations visibles dans le plan d'exécution d'une requête pour s'assurer que celle-ci avait été offloadée au niveau des cells. Le plan indique juste si la requête est candidate au smart scan ou pas.

Pour ceux d'entre vous qui découvrent Exadata à travers cet article vous vous êtes peut-être etonnés du fait que j'ai utilisé parfois le terme de OFFLOADING et parfois celui de SMART SCAN? Ces deux termes sont synonymes et désignent la même chose. Par contre, cette fonctionnalité d'Exadata renferment plus de fonctionnalités que celles que j'ai mentionnées dans cet article comme les storage indexes ou le join filtering mais j'aurai surement l'occasion d'en parler ultérieurement.