ARRAYSIZE: modification définitive de la valeur [Résolu]

[thisistheend]

Bonjour à tous,

Dans le cadre de l'optimisation de ma base de données où une de mes applications requiert la récupération de plusieurs dizaines de milliers de lignes, je souhaiterai modifier la valeur de ARRAYSIZE.
Je sais que l'on peut le modifier via:

Code :

SET ARRAYSIZE 500

Toutefois, je voudrais modifier cette valeur de façon définitive et non au cours d'une session. Par défaut, elle vaut 15, mais je voudrais qu'elle soit positionnée définitivement à 500.
Est-il possible de réaliser une telle chose?

Merci d'avance!

[mnitu]

Storing settings for SQL*PLUS (login.sql and glogin.sql)

[Pomalaix]

Envoyé par thisistheend

. Par défaut, elle vaut 15, mais je voudrais qu'elle soit positionnée définitivement à 500.

Ca serait intéressant que vous nous fassiez part de l'amélioration (chiffrée) des temps de traitement suite à ce changement.
Les quelques fois où j'ai voulu suivre cette piste, ça n'a jamais été très concluant.

[mnitu]

Envoyé par Pomalaix

Ca serait intéressant que vous nous fassiez part de l'amélioration (chiffrée) des temps de traitement suite à ce changement.
Les quelques fois où j'ai voulu suivre cette piste, ça n'a jamais été très concluant.

Cela est très simple

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
SQL> r
  1* SELECT * FROM all_objects
 
25119 ligne(s) sélectionnée(s).
 
Ecoulé : 00 :00 :01.15
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     120029  consistent gets
          0  physical reads
          0  redo size
    1494238  bytes sent via SQL*Net TO client
      18906  bytes received via SQL*Net FROM client
       1676  SQL*Net roundtrips TO/FROM client
        621  sorts (memory)
          0  sorts (disk)
      25119  rows processed
 
SQL> SET arraysize 1
SQL> r
  1* SELECT * FROM all_objects
 
25119 ligne(s) sélectionnée(s).
 
Ecoulé : 00 :00 :02.14
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     132748  consistent gets
          0  physical reads
          0  redo size
    3486146  bytes sent via SQL*Net TO client
     138641  bytes received via SQL*Net FROM client
      12561  SQL*Net roundtrips TO/FROM client
        621  sorts (memory)
          0  sorts (disk)
      25119  rows processed

[EDIT]
AskTom
[/EDIT]

[skuatamad]

Par contre SET ARRAYSIZE c'est pour sqlplus donc, à moins que l'application n'utilise sqlplus, il faut regarder dans la doc du driver utilisé.

[Pomalaix]

Envoyé par mnitu

SQL> set arraysize 1

Petit marrant ! Ca sert à quoi de tester avec 1 quand la valeur par défaut est 15 ??

Ma question ne porte pas sur le moyen de tester, mais sur les résultats réels.
Mon expérience est que 15 est une valeur assez bien adaptée, et que son augmentation n'apporte guère d'amélioration.
Vos résultats de la vraie vie m'intéressent.

[Pomalaix]

Tiens d'ailleurs, sans vouloir faire mon Burleson, faire ce genre de test avec un SET AUTOTRACE TRACE, ce qui semble simple et élégant, n'est pas réaliste quant à l'amélioration concrète qu'on peut tirer de la manoeuvre.

En effet, quand on ramène sur le client des centaines de milliers de lignes, voire des millions, soit on va les afficher, soit, plus probablement, on va les stocker sur disque. Affichage ou stockage, ceci prend un temps loin d'être négligeable.

Donc au mieux, le SET AUTOTRACE TRACE indiquera le gain théorique possible si on travaillait uniquement en mémoire.
Sur des tests réels (avec stockage des résultats), je ne gagne pas mieux que 20% en passant ARRAYSIZE de 15 à 100. Bien entendu, ça dépendra du matériel.

[mnitu]

Cher @Pom

1 (cas limite) c’est moins bien que 15 qui est moins bien que 100.

20% c’est 20% plus vite quand même.

Il n’est pas possible de gagner au niveau de traitement des données ramenées avec arraysize. Si t’écrit tes données sur disque comment arraysize pourrait t’aider ?

Donc, le test rapide montre ce que tu peux gagner au niveau de la lecture des données et non pas au niveau du traitement global.

[Pomalaix]

Envoyé par mnitu

Si t’écrit tes données sur disque comment arraysize pourrait t’aider ?

Demande à un informaticien !
Si je gagne 20% sur le temps constaté au niveau client, grâce à l'augmentation d'ARRAYSIZE (tout en continuant à stocker mes données), ça veut peut-être dire que les données n'étaient pas transmises assez vite au client initialement, et que maintenant, c'est la vitesse de stockage du disque qui est devenue le facteur limitant.

Ce n'est pas à toi que je vais apprendre que ce qui compte, ce sont les performances réelles à l'extrémité de la chaîne, du côté du client.
Si ces performances sont insuffisantes, ça peut provenir de ralentissements à divers niveaux, qu'il faut identifier et quantifier, au lieu de donner des coups de tournevis anarchiques à ARRAYSIZE en faisant des incantations (et des démonstrations de mauvaise foi, hein camarade !)

[mnitu]

Arraysize pour sqlplus c’est le même principe d’array processing qui est présent dans Pro*c, Pl/Sql (bulk fetch), Java, Vb, etc.
Je ne vois pas dans quoi ma démonstration est de mauvaise foi .

[Waldar]

Si je peux me permettre, ce que dit Pomalaix c'est que la valeur par défaut étant positionnée à 15, faire une comparaison entre 1 et 15 est inutile puisque le sujet concerne l'augmentation du paramètre, donc autant faire la comparaison entre 15 et 500.

Supposer que passer de 1 à 15 équivaut à passer de 15 à 500 est une sacrée extrapolation : comme chacun sait les performances ne sont jamais linéaires.

[Pomalaix]

Envoyé par mnitu

...
20% c’est 20% plus vite quand même.

Il n’est pas possible de gagner au niveau de traitement des données ramenées avec arraysize. Si t’écrit tes données sur disque comment arraysize pourrait t’aider ?

• D'une part tu dis que ces 20% d'amélioration, ce n'est pas négligeable (sous-entendu "tu vois que ça marche ARRAYSIZE"),

• d'autre part juste en dessous tu prétends qu'aucune amélioration n'est possible sur le temps de traitement par le simple effet d'ARRAYSIZE, notamment si le traitement commence par du stockage (sous-entendu "ça ne peut pas marcher dans ton cas, abruti")

Si on fait la synthèse, ces 20% sont magnifiques, mais impossibles !

Je veux bien que tu trouves tous les arguments pour défendre ton avis (d'ailleurs le comble, c'est qu'on n'est même pas en désaccord sur ce qu'on "mesure" avec le SET AUTOTRACE TRACE), mais évite au moins que ces arguments soient grossièrement contradictoires.

Tiens, pour faire réellement avancer le schmilblick, pourquoi ARRAYSIZE n'est-il pas directement à sa valeur maximum sous SQL*Plus ?

[mnitu]

Comme cette discussion dégénère un peu il serait sage que chacun garde les sous-entendues pour soi.

Peut être que j'ai été un peu superficielle dans mes interventions, j'avais pensé que les choses sont «*évidentes*» surtout après avoir ajouté le link sur la discussion du site AskTom. Mon test est un test qualitatif pour dire la valeur d'arraysize a un impact et non pas quantitatif, c'est pour ça que je ne vois pas de problème pour comparer 1 avec 15. A partir de ce test quelqu'un pourrait comparer 15 avec 100, 1000 ou bien 5000. Le fait même qu'il existe un valeur limite implique que les gains ne seront pas linéaires. De plus comme je l'ai déjà dit augmenter arraysize c'est la même technique qu'utiliser bulk fetch dans PL/SQL, utiliser les host array en Pro*C, etc. Pour quoi ça marchera en PL, Pro*C et non pas en sqlplus ?

Oui il est possible d'optimiser avec arraysize. Pour comprendre pourquoi je vous propose le test suivant.
D'abord je vais créer une table et chauffer un peu le système

Code :

1
2
3
4
5
6
7
8
9
10
11
 
mni@DIANA> CREATE TABLE t AS
  2  SELECT object_id, object_name FROM all_objects
  3  /
 
TABLE crÚÚe.
 
mni@DIANA> SET autotrace traceonly statistics
mni@DIANA> SELECT * FROM t;
 
71489 ligne(s) sÚlectionnÚe(s).

Ensuite on exécute le script du test

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
ConnectÚ Ó :
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND Real Application Testing options
 
mni@DIANA> SET autotrace traceonly statistics
mni@DIANA> SET arraysize 15
mni@DIANA> SET timi ON
mni@DIANA> ALTER session SET sql_trace=true
  2  /
 
Session modifiÚe.
 
EcoulÚ : 00 :00 :00.01
mni@DIANA> SELECT * FROM t
  2  /
 
71489 ligne(s) sÚlectionnÚe(s).
 
EcoulÚ : 00 :00 :00.67
 
Statistiques
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       5152  consistent gets
          0  physical reads
          0  redo size
    2784592  bytes sent via SQL*Net TO client
      52830  bytes received via SQL*Net FROM client
       4767  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
      71489  rows processed
 
mni@DIANA> SET arraysize 100
mni@DIANA> SELECT * FROM t cherche_moi
  2  /
 
71489 ligne(s) sÚlectionnÚe(s).
 
EcoulÚ : 00 :00 :00.23
 
Statistiques
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       1119  consistent gets
          0  physical reads
          0  redo size
    2258302  bytes sent via SQL*Net TO client
       8269  bytes received via SQL*Net FROM client
        716  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
      71489  rows processed
 
mni@DIANA> ALTER session SET sql_trace = false
  2  /
 
Session modifiÚe.
 
EcoulÚ : 00 :00 :00.00

Regardons aussi le fichier de trace. On peut noter deux modèles différentes d'appels Oracle dans ce fichier
1)

…
FETCH #4:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1601196873,tim=3784878805
FETCH #4:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1601196873,tim=3784878928
FETCH #4:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1601196873,tim=3784879060
FETCH #4:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1601196873,tim=3784879269
FETCH #4:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1601196873,tim=3784879396
FETCH #4:c=0,e=19,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1601196873,tim=3784879517
FETCH #4:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1601196873,tim=3784879637
FETCH #4:c=0,e=21,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1601196873,tim=3784879761
…

Notez les valeurs pour e (elapsed), cr (consistent reads) et surtout r (rows): 15

2)

…
FETCH #4:c=0,e=55,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785597434
FETCH #4:c=0,e=56,p=0,cr=1,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785597719
FETCH #4:c=0,e=58,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785598004
FETCH #4:c=0,e=55,p=0,cr=1,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785598288
FETCH #4:c=0,e=53,p=0,cr=1,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785598568
FETCH #4:c=0,e=58,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785598862
FETCH #4:c=0,e=55,p=0,cr=1,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785599145
FETCH #4:c=0,e=55,p=0,cr=1,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785599429
FETCH #4:c=0,e=58,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785599718
FETCH #4:c=0,e=52,p=0,cr=1,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=3785600012
...

Dans le premier cas il faut environ 4766 (71489/15) appels Oracles (FETCH ) pour lire les données. Comparez ça avec 715 appels dans le deuxième cas. Notez les valeurs pour e (18, 18, …) et comparez avec (55, 56, …) dans le deuxième cas et vous avez la tendance: chaque appel lis plus des données (100 par rapport au 15) en visitant parfois 2 blocs des données par rapport à 1 mais, pour des valeurs elapsed plus grandes.

PS.
@Pom
Dans ce que tu considère comme une contradiction j'ai essayé de faire tout simplement la différence entre la lecture des données ou arraysize compte et ce qu'on fait ensuite avec ces données, pour exemples les écrire dans un fichier, ou arraysize ne peut pas intervenir.

[Pomalaix]

Envoyé par mnitu

Oui il est possible d'optimiser avec arraysize.

Pour commencer, je te rassure, rien ne dégénère, on discute juste .
Par contre visiblement, on ne se comprend pas.

Pourtant les choses sont simples.
Quand une application a des performances insuffisantes, augmenter ARRAYSIZE ne peut être efficace que si l'application traite les données plus vite qu'elle ne les reçoit du serveur. (Et il faut bien sûr que le serveur soit capable de les extraire assez vite, pour en envoyer davantage d'un coup).
Sinon cette piste ne peut apporter aucune amélioration.

Traiter, c'est très général. Ca peut vouloir dire stocker aussi.
Si mon stockage est plus rapide que l'envoi des données, alors mon traitement peut bénéficier d'une augmentation de l'ARRAYSIZE.

Comme j'aime bien dire, un marteau c'est bien, mais pas pour couper du saucisson.
Avant de sortir ARRAYSIZE de sa poche, il faut juste s'assurer d'être face à un clou.

[Rams7s]

Envoyé par Pomalaix

Tiens, pour faire réellement avancer le schmilblick, pourquoi ARRAYSIZE n'est-il pas directement à sa valeur maximum sous SQL*Plus ?

Parce que ce n'est pas forcement malin.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE package tmp_pkg IS
   type t_num IS TABLE of date;
   FUNCTION my_func RETURN t_num pipelined;
end tmp_pkg;
/
SHOW errors
 
CREATE package body tmp_pkg IS
   FUNCTION my_func RETURN t_num pipelined IS
      answer number DEFAULT 42;
   begin
      FOR i IN 1..20 loop
         dbms_lock.sleep(1);
         pipe row(current_timestamp);
      end loop;
   end;
 
end tmp_pkg;
/
SHOW errors
SET arraysize 2
SELECT * FROM TABLE(tmp_pkg.my_func);
 
SET arraysize 15
SELECT * FROM TABLE(tmp_pkg.my_func);
 
DROP package tmp_pkg;

La si je peux traiter les résultats un par un, je vais pas m'amuser a mettre un arraysize de 500. Sinon mon appli perdrait du temps a rien faire avant d'avoir les resultats de la BdD.

Une remarque quand meme, avoir un petit arraysize oblige a augmenter le nombre de LIO (Cf examples de mnitu). Si le serveur est deja a la peine a cause de ca, ce n'est pas forcement l'aider que de mettre un arraysize tout petit.

Mais ca peut quand meme etre pratique d'augmenter le arraysize. Ca peut eventuellement pernettre de reduire le nombre de LIO cote serveurs, et le nombre de SQL*NET round trips.

[pacmann]

Bah, je pense que tout le monde (mnitu et pom entre autres) est d'accord que :
- Parfois arraysize peut aider
- Parfois arraysize n'aide pas plus que ça

Pom, je sais que ça n'aide pas beaucoup vu que ça ne s'appuie sur rien, mais dans une de mes missions, on réalisait un tas d'export par SQL*Plus, par fois sur des requêtes très simples (d'ailleurs on ressent plus la différence dans ces cas), et dans ces cas l'augmentation du arraysize nous à bien aidé

[thisistheend]

Bonjour à tous,

Je vois que cette question suscite beaucoup d'intérêt de votre part .
Dans tous les cas, je n'ai pas ressenti d'amélioration de performances sur mes requêtes ou incrémentant ou en décrémentant cette valeur.
Ma question était plus d'un ordre de culture générale, au cas je tomberais sur un cas où ARRAYSIZE pourrait m'aider. Ce qui n'est pas le cas ici malheureusement.

Merci en tout cas pour vos réponses, ça m'a un peu plus éclairé sur l'intérêt de cette variable .