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.

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.