J’avais en tête depuis un moment d’écrire un post sur le
row prefetching et un problème de performance rencontré récemment m’en donne l’occasion aujourd’hui.
Le concept du
row prefetching concerne l’étape du FETCH de l’exécution d’une requête c'est-à-dire l’étape du SELECT qui ramène les lignes de la base à l’application cliente. Chaque
fetch call retournera tout ou une partie des lignes demandées par la requête. Le
row prefetching correspond justement au nombre de lignes retournées par un
fetch call. En augmentant la valeur du
row Prefetching on peut réduire l’étape de FETCH d’une requête SQL.
Le problème de performance que j’ai eu à traiter récemment permet justement d’illustrer ce concept.
La requête incriminée était la suivante :
SELECT Timestamptz, OPEN, High, Low, CLOSE, Volume, Timerange, Gmt_Offset
FROM V_Ticker_Intraday_Trade
WHERE Internal_Code = 'IND_EUROSTOXX_50'
AND TIMERANGE = 1
AND (PROVIDER_ID = 1 OR PROVIDER_ID = 2 OR PROVIDER_ID = 130)
AND DAYS BETWEEN to_date('1980/01/01','yyyy/mm/dd') AND to_date('2011/01/01','yyyy/mm/dd')
ORDER BY PROVIDER_ID,TIMESTAMPTZ;
L’exécution de cette requête sous SQLPLUS met plus de 5 minutes pour retourner plus de 2 millions de lignes :
SQL> set autot trace
SQL> SELECT
2 Timestamptz,
3 OPEN,
4 High,
5 Low,
6 CLOSE,
7 Volume,
8 Timerange,
9 Gmt_Offset
10 FROM V_Ticker_Intraday_Trade
11 WHERE Internal_Code = 'IND_EUROSTOXX_50'
12 AND TIMERANGE = 1
13 AND (PROVIDER_ID = 1 OR PROVIDER_ID = 2 OR PROVIDER_ID = 130)
14 AND DAYS BETWEEN to_date('1980/01/01','yyyy/mm/dd') AND to_date('2011/01/01','yyyy/mm/dd')
15 ORDER BY PROVIDER_ID,TIMESTAMPTZ;
2298385 rows selected.
Elapsed: 00:05:19.70
J’avais un fort doute que les 5 minutes étaient essentiellement causées par la récupération des 2 millions de lignes. Pour en être sûr j’ai remplacé la requête par un CTAS :
SQL> create table aha_t1 as
2 SELECT
3 Timestamptz,
4 OPEN,
5 High,
6 Low,
7 CLOSE,
8 Volume,
9 Timerange,
10 Gmt_Offset
11 FROM V_Ticker_Intraday_Trade
12 WHERE Internal_Code = 'IND_EUROSTOXX_50'
13 AND TIMERANGE = 1
14 AND (PROVIDER_ID = 1 OR PROVIDER_ID = 2 OR PROVIDER_ID = 130)
15 AND DAYS BETWEEN to_date('1980/01/01','yyyy/mm/dd') AND to_date('2011/01/01','yyyy/mm/dd')
16 ORDER BY PROVIDER_ID,TIMESTAMPTZ;
Table created.
Elapsed: 00:00:22.95
En exécutant la requête ci-dessus consistant à ne pas ramener les lignes côté client, j’obtiens un temps d’exécution de presque 23 secondes. On est donc bien loin des 5 minutes.
Le plan d’exécution suivant indique comment sont réparties les 22 secondes dans les différentes opérations du plan :
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID 5zzpky782tr91, child number 0
-------------------------------------
create table aha_t1 as SELECT Timestamptz, OPEN, High, Low, CLOSE, Volume, Timerange, Gmt_Offset FROM V_Ticker_Intraday_Trade WHERE Internal_Code
= 'IND_EUROSTOXX_50' AND TIMERANGE = 1 AND (PROVIDER_ID = 1 OR PROVIDER_ID = 2 OR PROVIDER_ID = 130) AND DAYS BETWEEN to_date('1980/01/01','yyyy/mm/dd') AND
to_date('2011/01/01','yyyy/mm/dd') ORDER BY PROVIDER_ID,TIMESTAMPTZ
Plan hash value: 948258494
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | LOAD AS SELECT | | 1 | | 1 |00:00:22.20 | 96511 | 36895 | 34284 | 519K| 519K| 519K (0)| |
| 2 | SORT ORDER BY | | 1 | 435 | 2298K|00:00:14.15 | 76367 | 36880 | 18098 | 159M| 4250K| 97M (0)| 142K|
| 3 | NESTED LOOPS | | 1 | 435 | 2298K|00:00:06.95 | 76365 | 18782 | 0 | | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 2 |00:00:00.01 | 11 | 9 | 0 | | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 2 |00:00:00.01 | 9 | 9 | 0 | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | INSTRUMENT | 1 | 1 | 1 |00:00:00.01 | 4 | 4 | 0 | | | | |
|* 7 | INDEX UNIQUE SCAN | INSTRUMENT_CODE_IX | 1 | 1 | 1 |00:00:00.01 | 3 | 3 | 0 | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | TICKER | 1 | 1 | 2 |00:00:00.01 | 5 | 5 | 0 | | | | |
|* 9 | INDEX RANGE SCAN | TICKER_AK | 1 | 1 | 2 |00:00:00.01 | 3 | 3 | 0 | | | | |
|* 10 | INDEX UNIQUE SCAN | PROVIDER_PK | 2 | 1 | 2 |00:00:00.01 | 2 | 0 | 0 | | | | |
| 11 | PARTITION HASH ITERATOR | | 2 | 1823 | 2298K|00:00:06.95 | 76354 | 18773 | 0 | | | | |
|* 12 | TABLE ACCESS BY LOCAL INDEX ROWID| INTRADAY_TRADE | 2 | 1823 | 2298K|00:00:04.65 | 76354 | 18773 | 0 | | | | |
|* 13 | INDEX RANGE SCAN | INTRADAY_TRADE_IX5 | 2 | 184K| 2314K|00:00:00.01 | 11594 | 11054 | 0 | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
On voit par exemple que sur les 22 secondes, 14 secondes sont liées au tri du result set.
Le problème initial c'est qu’on souhaite quand même ramener les 2 millions de lignes du côté de l’application cliente. L’idée lorsqu’on doit fetcher autant de lignes c’est d’optimiser le nombre de lignes envoyées de la base au client par aller-retour réseau. Cette optimisation peut se faire en jouant sur le paramètre de
row prefetching. Sous SQLPLUS ce paramètre s’appelle ARRAYSIZE et vaut 15 par défaut.
Voyons le nombre d’aller-retour réseau effectués pour récupérer les 2 millions de lignes lorsque l’ARRAYSIZE est à sa valeur par défaut :
SQL> set autot trace
SQL> select * from aha_t1;
2298385 rows selected.
Statistics
----------------------------------------------------------
188 recursive calls
0 db block gets
168444 consistent gets
16186 physical reads
0 redo size
101490001 bytes sent via SQL*Net to client
1685825 bytes received via SQL*Net from client
153227 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2298385 rows processed
Elapsed: 00:05:16.19
On voit que le nombre de SQL*Net roundtrips est de 153 227 pour récupérer exactement 2 298 385 lignes. Si on divise ces 2298385 lignes par la taille de l’ARRAYSIZE c-a-d 15 on obtient 153225 ce qui correspond quasiment au nombre de SQL*Net roundtrips constatés dans la partie statistiques de l'autotrace.
Voyons ce que donne la requête lorsqu’on passe le
row prefetching à 100 :
SQL> set arraysize 100
SQL> select * from aha_t1;
2298385 rows selected.
Elapsed: 00:01:05.09
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
39117 consistent gets
0 physical reads
0 redo size
90289189 bytes sent via SQL*Net to client
253163 bytes received via SQL*Net from client
22985 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2298385 rows processed
La requête s’exécute désormais en 1 minute et le nombre d’aller-retour réseau est passé de 153 227 à 22 985 (2298385/100).
En mettant l’ARRAYSIZE à sa valeur maximale c'est-à-dire 5000 j’arrive à un temps d’exécution de 12 secondes :
SQL> set arraysize 5000
SQL> select * from aha_t1;
2298385 rows selected.
Elapsed: 00:00:12.74
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16659 consistent gets
0 physical reads
0 redo size
88352125 bytes sent via SQL*Net to client
5399 bytes received via SQL*Net from client
461 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2298385 rows processed
Le nombre de SQL*Net roundtrips n’est plus que de 461.
On voit bien avec cet exemple combien en jouant sur la taille du
row prefetching on arrive à obtenir de meilleures performances sur les requêtes ramenant beaucoup de lignes.
Ce qu’il faut bien avoir à l’esprit c’est que le
row prefetching est quelque chose qui se paramètre du côté client et non pas du côté serveur. La manière de positionner ce paramètre dépend de l’application cliente utilisée. Sous SQLPLUS on a vu qu’il fallait toucher au paramètre ARRAYSIZE. Pour les application JAVA utilisant le driver JDBC, le
row prefetching peut être modifié en changeant la valeur de la propriété
defaultRowPrefetch des classes
OracleDataSource ou
OracleDriver. Par défaut, le Row Prefetching du JDBC vaut 10.
Pour les application utilisant ODP.NET la taille du fecth n’est pas définie en nombre de lignes mais en octects ce qui fait que le
row prefetching va dépendre de la taille des lignes récupérées. Par défaut la taille de la propriété
FetchSize de la classe
OracleCommand ou
OracleDataReader est de 65 536 octects. Si ma table a des lignes d’une taille de 100 octects alors par defaut ma requête sur cette table me ramènera les lignes avec une taille de fetch de 655 lignes environ (là où une application JAVA par défaut ramènerait les lignes avec une taille de fetch de 10 lignes seulement).
CONCLUSION:
A chaque fois que vous avez à ramener beaucoup de lignes du côté de l'application pensez bien à positionner de manière appropriée le
row prefetching. L'optimisation de ce paramètre dépendra du type d'application client utilisé (SQLPLUS,ADO.NET, JDBC, OCI etc.).
