lundi 4 novembre 2019

Row Prefetching et PL/SQL

J'avais écrit il y'a plusieurs années déjà un article qui présentait le row prefetching avec en illustration un problème de performance d'une requête SQL observé chez mon client de l'époque.

Récemment j'ai eu affaire à un problème de performance sur la partie fetching d'une requête exécutée via un curseur PL/SQL. Le rapport SQL Monitor de cette requête indiquait un temps d'exécution global de presque une heure alors que le temps base de données consommé n'était que d'à peine quelques secondes.
De plus, le nombre de fetch calls correspondait quasiment au nombre de lignes retournées par la requête ce qui me faisait clairement comprendre qu'on était face à un problème de mauvaise configuration du row prefetching.

Le développeur avait connaissance du row prefeftching et de son intérêt puisqu'il avait défini l'ARRAYSIZE à 5000 dans son code SQL PLUS.
L'ennui c'est que ce qu'il executait n'était pas une requête SQL mais un code PL/SQL et dans ce cas là le row prefetching n'est plus défini par le paramètre ARRAYSIZE.

Le but de cet article est justement de voir comment fonctionne le row prefetching dans le code PL/SQL.

Commençons par créer une table de travail:
create /*+ parallel(8) */ table T1 as
select * from dba_objects;

select count(*) from T1;


  COUNT(*)
----------
   1378527


1er test: Avec un curseur explicite
Nous allons dans un premier temps utiliser un code PL/SQL qui va récupérer et afficher quelques champs d'1million de lignes de la table en utilisant un curseur explicite :
set serveroutput on
DECLARE

    CURSOR cur IS SELECT /*+ parallel(4) */   object_name, LAST_DDL_TIME
                FROM  t1 where rownum<=1000000;

       v_obj_name varchar2(255);
       v_last_ddl_time DATE;     

BEGIN

    OPEN cur;
    LOOP
        FETCH cur into v_obj_name,v_last_ddl_time  ;
        EXIT WHEN cur%notfound;
                     dbms_output.put_line(v_obj_name);
    END LOOP;
    CLOSE cur;
END;
/


Si l'on jette un oeil au Real-Time SQL Monitor report  ci-dessous on s'aperçoit que la requête s'est exécutée en 7s (DURATION) mais que seulement 2,8s de temps base de données ont été consommé pour executer la requête. Le reste du temps correspondant au fetching. D'ailleurs le nombre de fetch calls indiqué est de 1000001 ce qui correspond à une unité près au nombre de lignes retournées:

Global Information
------------------------------
Status              :  DONE (ALL ROWS)
Instance ID         :  4
Session             :  AHMED (1323:34963)
SQL ID              :  7npr6bxbj8b8k
SQL Execution ID    :  67108865
Execution Started   :  09/13/2019 16:28:31
First Refresh Time  :  09/13/2019 16:28:31
Last Refresh Time   :  09/13/2019 16:28:38
Duration            :  7s
Module/Action       :  SQL*Plus/-
Service             :  #####
Program             :  sqlplus.exe
Fetch Calls         :  1000001

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    2.80 |    2.78 |     0.01 |    1M |  19924 |
================================================= 


Le problème avec le test ci-dessus est qu'il s'agit d'un curseur explicite, or avec ce type de curseur il est necessaire d'activer le row prefetching en activant la commande BULK COLLECT:
set serveroutput on
DECLARE
    CURSOR cur IS SELECT /*+ parallel(4) */  *
                FROM  t1 where rownum<=1000000;

    TYPE t_t1 IS   TABLE OF t1%rowtype;
    l_t1  t_t1;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur BULK COLLECT INTO l_t1 LIMIT 1000;
        EXIT WHEN l_t1.count = 0;
        FOR i IN l_t1.first..l_t1.last LOOP
                     dbms_output.put_line(l_t1(i).OBJECT_NAME);
        END LOOP;
    END LOOP;
    CLOSE cur;
END;
/


 Global Information
------------------------------
Status              :  DONE (ALL ROWS)
Instance ID         :  4
Session             :  AHMED (1323:35631)
SQL ID              :  gpn4z88bqcqb2
SQL Execution ID    :  67108865
Execution Started   :  09/13/2019 16:32:37
First Refresh Time  :  09/13/2019 16:32:37
Last Refresh Time   :  09/13/2019 16:32:39
Duration            :  2s
Module/Action       :  SQL*Plus/-
Service             :  ####
Program             :  sqlplus.exe
Fetch Calls         :  1001


La requête a cette fois duré 2s et on voit que le nombre de fetch calls est d'environ 1000. Ce nombre s'explique par la commande LIMIT 1000 que j'ai ajouté dans mon code PL/SQL juste après le BULK COLLECT afin d'indiquer que je souhaite prefeecher 1000 lignes par fetch call. Comme j'ai 1M de lignes à récupérer, 1000000/1000=1000. Si j'avais mis une limite à 10k j'aurais eu besoin que de 100 fectch calls pour rapatrier mes 1M de rows.

Attention toutefois à ne pas mettre une valeur de prefetching trop élévé car les lignes prefetchées sont stockées dans la mémoire du client, et donc un nombre trop important pourrait saturer cette dernière.


2ème test: Avec un curseur implicite
Maintenant nous allons voir comment fonctionne le fecthing lorsqu'on utilise des curseurs implicites. Pour rappel, on appelle curseurs implicites les curseurs utilisés dans le cadre d'une boucle FOR, sans qu'il y'ait ouverture explicite du curseur via la commande OPEN.

set serveroutput on
BEGIN
    for cur in (SELECT /*+ parallel(4) */  *  FROM  t1 where rownum<=1000000)
       LOOP
                     dbms_output.put_line(cur.OBJECT_NAME);
    END LOOP;
END;
/

Global Information
------------------------------
Status              :  DONE (ALL ROWS)
Instance ID         :  4
Session             :  AHMED (1323:35631)
SQL ID              :  gpn4z88bqcqb2
SQL Execution ID    :  67108866
Execution Started   :  09/13/2019 16:37:57
First Refresh Time  :  09/13/2019 16:37:57
Last Refresh Time   :  09/13/2019 16:38:00
Duration            :  3s
Module/Action       :  SQL*Plus/-
Service             :  ####
Program             :  sqlplus.exe
Fetch Calls         :  10001


Cette fois, sans qu'on ait eu à spécifier une valeur de row prefetching, on s'aperçoit qu'Oracle a automatiquement utilisé un prefetching de 100 puisque le nombre de fetch calls constatés est de 10K (1M/100=10K).

CONCLUSION:
A chaque fois que vous avez à ramener des lignes côté client vous devez avoir en tête la notion de row prefetching. En fonction du client utilisé (SQL PLUS, JDBC, .NET etc.) le row prefetching va se configurer différemment.
Pour le PL/SQL le row prefetching est activé automatiquement pour tous les curseurs de type implicite (FOR loops cursors), pour les curseurs explicites il faut utiliser la clause BULK COLLECT et donc adapter son code pour utiliser des collections.







lundi 2 septembre 2019

Updates et Compression HCC (1)



Dans cet article je souhaiterais vous montrer ce qu'il se passe concrètement quand un update est effectué sur des lignes compressées en HCC (compression disponible sur Exadata uniquement) grâce à l'utilisation du package DBMS_COMPRESSION.

Je vais commencer par créer une table en mode compression HCC et y insérer des lignes en mode conventionnel (sans utiliser le hint APPEND):
DROP TABLE T1 PURGE;
create table T1 as select * from all_objects where 1=2;
alter table T1 compress for query high;
-- insert without using DIRECT PATH WRITE (data non compressed)
insert into T1 select * from all_objects;
commit;


Imaginons que je ne sache pas comment ces lignes aient été insérées. En regardant les propriétés de la table indiquant qu'il s'agit d'une table compressée en HCC je pourrais être amené à croire que cette table contient des lignes compressées alors que non puisque pour avoir des lignes compressées en HCC dans Exadata il ne suffit pas de créer une table en mode HCC, il faut également que les lignes soient insérées en mode direct path write (en mode APPEND). Faut vraiment bien garder cela à l'esprit.

La fonction GET_COMPRESSION_TYPE du package DBMS_COMPRESSION permet de savoir quel type de compression est appliqué pour chacune des lignes d'une table:
select  decode (dbms_compression.get_compression_type(user,'T1',rowid),
1, 'NOCOMPRESS',
2, 'COMP_FOR_OLTP',
4, 'COMP_FOR_QUERY_HIGH',
8, 'COMP_FOR_QUERY_LOW',
16,'COMP_FOR_ARCHIVE_HIGH',
32,'COMP_FOR_ARCHIVE_LOW',
64,'COMP_BLOCK',
'OTHER') comp_type, count(*)
from T1
where rownum < 100
group by decode (dbms_compression.get_compression_type(user,'T1',rowid),
1, 'NOCOMPRESS',
2, 'COMP_FOR_OLTP',
4, 'COMP_FOR_QUERY_HIGH',
8, 'COMP_FOR_QUERY_LOW',
16,'COMP_FOR_ARCHIVE_HIGH',
32,'COMP_FOR_ARCHIVE_LOW',
64,'COMP_BLOCK',
'OTHER');

COMP_TYPE          COUNT(*)
---------------- ----------
NOCOMPRESS               99

select trunc(bytes/power(1024,2)) m
from user_segments where segment_name ='T1'; 

         M
----------
       258


La fonction GET_COMPRESSION_TYPE appliquée sur les lignes de ma table indiquent un type de compression à 1 c'est à dire non compressées.

Je vais maintenant supprimer les lignes de la table et y insérer les mêmes lignes en utilisant cette fois le mode DIRECT PATH WRITE:
truncate table T1;
insert /*+ APPEND */ into T1 select * from all_objects;
commit;

select  decode (dbms_compression.get_compression_type(user,'T1',rowid),
1, 'NOCOMPRESS',
2, 'COMP_FOR_OLTP',
4, 'COMP_FOR_QUERY_HIGH',
8, 'COMP_FOR_QUERY_LOW',
16,'COMP_FOR_ARCHIVE_HIGH',
32,'COMP_FOR_ARCHIVE_LOW',
64,'COMP_BLOCK',
'OTHER') comp_type, count(*)
from T1
where rownum < 100
group by decode (dbms_compression.get_compression_type(user,'T1',rowid),
1, 'NOCOMPRESS',
2, 'COMP_FOR_OLTP',
4, 'COMP_FOR_QUERY_HIGH',
8, 'COMP_FOR_QUERY_LOW',
16,'COMP_FOR_ARCHIVE_HIGH',
32,'COMP_FOR_ARCHIVE_LOW',
64,'COMP_BLOCK',
'OTHER');

COMP_TYPE          COUNT(*)
---------------- ----------
FOR_QUERY_HIGH           99

select trunc(bytes/power(1024,2)) m
from user_segments where segment_name ='T1';

         M
----------
        11


Le package DBMS_COMPRESSION nous indique que la compression appliquée aux lignes de la table est bien de type HCC FOR QUERY HIGH, qui est le type de compression qu'on a définit pour notre table lors de sa création.

Notez comment la taille de la table est passée de 258MB à 11MB seulement. C'est assez impressionnant.
Maintenant, je vais exécuter un update sur toutes les lignes de la table:
update T1 set CREATED=SYSDATE;
commit;

select  decode (dbms_compression.get_compression_type(user,'T1',rowid),
1, 'NOCOMPRESS',
2, 'COMP_FOR_OLTP',
4, 'COMP_FOR_QUERY_HIGH',
8, 'COMP_FOR_QUERY_LOW',
16,'COMP_FOR_ARCHIVE_HIGH',
32,'COMP_FOR_ARCHIVE_LOW',
64,'COMP_BLOCK',
'OTHER') comp_type, count(*)
from T1
where rownum <100 br="">group by decode (dbms_compression.get_compression_type(user,'T1',rowid),
1, 'NOCOMPRESS',
2, 'COMP_FOR_OLTP',
4, 'COMP_FOR_QUERY_HIGH',
8, 'COMP_FOR_QUERY_LOW',
16,'COMP_FOR_ARCHIVE_HIGH',
32,'COMP_FOR_ARCHIVE_LOW',
64,'COMP_BLOCK',
'OTHER');

COMP_TYPE               COUNT(*)
--------------------- ----------
COMP_BLOCK                    99

select trunc(bytes/power(1024,2)) m
from user_segments where segment_name ='T1';

         M
----------
        96


Vous remarquez qu'après l'update le type de compression est passé au type 64 qui est en fait un type de compression OLTP.
Que s'est-il passé exactement?
En fait, pour pouvoir modifier des lignes compressées en HCC, Oracle migre ces lignes dans un nouveau block défini en mode OLTP compression. Un pointeur a été laissé dans le block d'origine. On est là devant un cas de ligne migrée c'est à dire qu'il faut lire 2 blocks pour lire une ligne. La taille de la table est passé de 11MB à 96MB ce qui est toujours mieux que la taille en mode non compressée (258MB) mais moins bien que le taux de compression obtenu en HCC.

De plus, lorsqu'un full scan est effectué sur une table et que le moteur Oracle tombe sur une ligne migrée, il bascule automatiquement en mode Block Shipping inhibant ainsi le smart scan.

En résumé, en modifiant des lignes compressées en HCC on casse en quelque sorte cette compression (les lignes sont migrées dans des blocks en mode compression OLTP) et on empêche le offloading de pouvoir s'effectuer sur ces lignes lors d'un SELECT. En gros, les 2 fonctionnalités phares de l'Exadata sont mis à mal à cause de ces updates. Voilà pourquoi les lignes compressées en HCC ne devraient jamais être modifiées. Si un update est necessaire il vaut mieux supprimer et réinsérer ces lignes.

Il existe d'autres contraintes liées aux updates de lignes compressées en HCC (Lock au niveau compression unit, entrées ITL limitées à 1 etc. ) mais j'en parlerai dans un autre post.

samedi 12 novembre 2016

L'importance de la contrainte NOT NULL

Il y'a quelques semaines de cela j'expliquais à un collègue, qui me posait des questions sur le rôle des index dans Oracle, que les accès indexés pouvaient aussi être utilisés par le CBO pour autre chose qu'une récherche de lignes, comme par exemple pour éviter un tri ou un Full Table Scan.
Je décidai alors de lui faire une petite démo rapide:
SQL> create table test1 as select * from dba_objects; 

Table created. 

SQL> select count(*) from test1; 

  COUNT(*)
----------
     92320
     
SQL> create index idx_test1 on test1(object_type); 

Index created. 

J'ai juste crée une table TEST1 de 92320 lignes qui est en fait une copie de DBA_OBJECTS. J'ai également créé un index sur la colonne OBJECT_TYPE.
Ce que je voulais montrer à mon collègue c'était que le CBO était capable de se servir de cet index lors d'un COUNT(*) sur cette table ou bien pour une requête effectuant un tri sur la colonne OBJECT_TYPE.
Sûr de moi je commençai par lui montrer le cas du COUNT(*):
SQL> explain plan for
  2  select count(*) from test1; 

Explained. 

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 3896847026 

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |   423   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1 | 89162 |   423   (1)| 00:00:01 |
-------------------------------------------------------------------- 

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)  
Aie!!!Le CBO a complètement ignoré mon index :-(
Moi qui voulait épater mon collègue j'avais plutôt l'air bête.
Au lieu de prendre le temps de réfléchir j'ai préféré utiliser un hint pour forcer l'utilisation de l'index mais là encore toujours le Full Scan.
Hum...très embarassé, je suis passé alors au test suivant consistant à trier mes lignes de la table sur la colonne indexée OBJECT_TYPE:
SQL> explain plan for select * from test1 order by object_type; 

Explained. 

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1692556001
------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 89162 |    31M|       | 22161   (1)| 00:00:01 |
|   1 |  SORT ORDER BY     |       | 89162 |    31M|    34M| 22161   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST1 | 89162 |    31M|       |   423   (1)| 00:00:01 |
------------------------------------------------------------------------------------ 

Note
-----
 - dynamic statistics used: dynamic sampling (level=2)
Mon index n'était toujours pas utilisé T_T
J'ai pourtant un index, trié par définition sur le champ OBJECT_TYPE, et le CBO préfère effectuer un full scan de la table suivi d'une opération de tri (SORT ORDER BY).
Pour info, l'opération SORT AGGREGATE du plan précédent n'effectue aucun tri contrairement à ce que son nom pourrait laisser entendre. Il effectue juste un comptage mais en aucun cas il ne trie quoi que ce soit.

Je vous avoue que je suis resté plusieurs minutes complètement incrédule à ce qu'il se passait. Et c'était pourtant d'une évidence absolue.
Un DESCRIBE sur ma table m'aida à refaire jaillir la lumière dans mon esprit:
SQL> desc test1

Name                                                                                                      Null?    Type
----------------------------------------------------------------------------------------------------------------- -------- ------------
OWNER                                                                                                              VARCHAR2(128)
OBJECT_NAME                                                                                                        VARCHAR2(128)
SUBOBJECT_NAME                                                                                                     VARCHAR2(128)
OBJECT_ID                                                                                                          NUMBER
DATA_OBJECT_ID                                                                                                     NUMBER
OBJECT_TYPE                                                                                                        VARCHAR2(23)
CREATED                                                                                                            DATE
LAST_DDL_TIME                                                                                                      DATE
TIMESTAMP                                                                                                          VARCHAR2(19)
STATUS                                                                                                             VARCHAR2(7)
TEMPORARY                                                                                                          VARCHAR2(1)
GENERATED                                                                                                          VARCHAR2(1)
SECONDARY                                                                                                          VARCHAR2(1)
NAMESPACE                                                                                                          NUMBER
EDITION_NAME                                                                                                       VARCHAR2(128)
SHARING                                                                                                            VARCHAR2(13)
EDITIONABLE                                                                                                        VARCHAR2(1)
ORACLE_MAINTAINED                                                                                                  VARCHAR2(1)

Toutes les colonnes sont NULLables.
Vous savez tous qu'un index ne stock pas de valeurs NULL et que donc pour qu'un index puisse être utilisé dans mes 2 requêtes il ne suffit pas qu'il y' ait réellement que des valeurs NULL, il faut que l'optimiseur en soit sûr et certain. C'est ce à quoi sert la contrainte NOT NULL pour le CBO. Elle lui indique qu'il ne peut y avoir de valeurs NULL pour la colonne en question et que donc l'utilisation de l'index est possible sur cette colonne car le CBO  a la certitude que l'index contiendra toutes les lignes de la table.
Ajoutons donc la contrainte NOT NULL à la colonne OBJECT_TYPE et regardons les plans des requêtes précédentes:
SQL> alter table test1 modify (object_type not null);

Table altered. 

SQL> explain plan for
  2  select count(*) from test1; 

Explained. 

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 2671621383 

---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    71   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TEST1 | 89162 |    71   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)



SQL> explain plan for select * from test1  order by object_type;
 
Explained.

SQL> @plan
SQL> SET LINES 500
SQL> SET PAGES 500

SQL> select * from table(dbms_xplan.display); 

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 2541586222

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 89162 |    31M|  4492   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1     | 89162 |    31M|  4492   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | IDX_TEST1 | 89162 |       |   258   (1)| 00:00:01 |
----------------------------------------------------------------------------------------- 

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Ha ha!!! mon honneur est sauf. Cette fois pour le count(*) le CBO a decidé de parcourir l'index pour compter les lignes au lieu de toute la table, ce qui est logique, le segment INDEX étant plus petit que la table il y'a moins de blocks à lire en faisant un INDEX FAST FULL SCAN. D'ailleurs on voit que le COST n'est que de 71 alors qu'il était de 423 avec le Full Table Scan.
Quant à la deuxième requête on voit que le CBO a opté pour un parcours séquentiel de l'index (INDEX FULL SCAN) évitant ainsi l'opération SORT ORDER BY effectuant le tri. On constate que grâce à cet index le COST du plan est passé de 22161 à 4492.

Pour un index composite il n'est pas necéssaire que toutes les colonnes soient NOT NULL pour qu'il soit pris en compte. Il suffit qu'au moins une des colonnes constituant l'index soit NOT NULL.

CONCLUSION:
Voilà donc comment malgré plusieurs années d'expérience sur Oracle on arrive à se faire avoir sur des choses évidentes qu'on sait depuis longtemps. Tom Kyte disait qu'il se forcait à lire au moins une fois par an la doc Database Concepts car on a tendance à les oublier et ils permettent de faire face à toutes les problématiques.

Cet article a également pour but de mettre en évidence à quel point il est important de donner un maximum d'informations au CBO pour que celui nous choisisse le plan optimal. Les statistiques objets sont certes indispensables mais l'optimiseur a également besoin de connaitre le type de contrainte (NOT NULL, FK, PK) qui lui permettent notamment d'estimer de meilleures cardinalités.

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.