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.
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.
