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.