Discussion :

[Pozzo]

Bonjour,

J'effectue en PLSQL 12.1.0.2 des fetch bulk limit dans des tableaux mémoire d'enregistrements en vue de traitement.
Le nombre d'enregistrements à traiter étant important je fetche par paquet de 100.000 (bulk limit 100.000).

Mais le temps d'allocation de la mémoire nécessaire semble long car un traçage par TKPROF montre un temps de fetch très long par rapport au parse et execute.
parse.elapsed=1, execute.elapsed=1, fetch.elapsed=2000.

Je cherche donc à pré-reserver de la mémoire.

Il me semble que le tableau est stocké dans la UGA / PGA de l'utilisateur.
La base est largement pourvue en PGA.

Mes deux questions sont:
1/ Si comme je le pense la UGA est bien le siège de mon tableau, est-il possible de pré-allouer de la mémoire à cette zone ? (uga_cache?)
2/ Quel statistique de session pourrais-je surveiller pour diagnostiquer la raison de la lenteur du fetch ? (Je pense par exemple au temps de lecture sur disque que je maîtrise mal)

En espérant me faire comprendre.
Pozzo

[mnitu]

100 000 est de loin trop. Les bénéfices du traitement par lot sont meilleurs pour des tailles des lots situées quelques part entre 1000 et 5000. Ensuite la gestion de la mémoire diminue ces bénéfices.
Commencez donc avec une taille de 2000.
Pour apprendre plus sur les temps écoulés il faut activer la trace étendue avec la trace des évènements et analyser le fichier de trace sur la nature et durée de ces évènements.

[Pomalaix]

Je rejoins Mnitu sur le fait que 100 000 est une limite excessivement haute.
Pour ma part, je constate qu'une limite de quelques centaines de lignes (500 par exemple) constitue souvent un bon dosage.

Quant au fetch qui prend du temps, je parierais que ce n'est pas l'écriture dans la collection qui prend du temps (c'est probablement négligeable), mais la lecture préalable des données avant leur stockage en mémoire: accès aux blocs, réalisation des jointures, application des WHERE éventuels, etc.

[Pozzo]

Merci pour vos deux réponses.

Pour le fetch ce n'était en effet pas un problème d'allocation de mémoire.
Une modification du plan d'exécution qui retourne le même nombre de ligne a résolu le problème.

Ma démarche vient d'une erreur d'interprétation de la documentation.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
EXECUTE: Actual execution of the statement by Oracle Database. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.
FETCH: Retrieves rows returned by a query. Fetches are only performed for SELECT statements.

J'en ai déduit que le fetch consistait principalement à charger les données en mémoire.

Pour la limite du bulk je vais effectuer de tests. Si l'allocation de mémoire lors d'un fetch de requête est négligeable je vois mal pourquoi une limite haute d'un bulk collect en poserait.

Pour la trace étendue j'imagine que par "activer la trace" vous songez à utiliser l'option waits=true lors de l'activation de la trace de la session ouis à utiliser tkprof avec le paramètre waits=yes ?

Merci en tout cas.
Pozzo

[Pomalaix]

...Si l'allocation de mémoire lors d'un fetch de requête est négligeable je vois mal pourquoi une limite haute d'un bulk collect en poserait...

L'objectif de la clause LIMIT du BULK COLLECT, c'est de profiter de l'amélioration des performances obtenue en extrayant les lignes de données par lot plutôt qu'unitairement, tout en limitant la mémoire consommée.
On constate à l'usage qu'au delà d'un certain seuil, le fait d'augmenter encore la limite n'apporte plus aucun gain de performances. Au delà, vous gaspillez juste de la mémoire.

Je n'ai pas dit que l'allocation mémoire était négligeable, mais que le temps passé à stocker les données en mémoire dans la collection était négligeable par rapport au temps passé par le moteur SQL pour les extraire préalablement.

[Pomalaix]

...Ma démarche vient d'une erreur d'interprétation de la documentation.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
EXECUTE: Actual execution of the statement by Oracle Database. For INSERT, UPDATE, and DELETE statements, this modifies the data. 
For SELECT statements, this identifies the selected rows.
FETCH: Retrieves rows returned by a query. Fetches are only performed for SELECT statements.

...

Difficile de ne pas comprendre les choses de travers quand la doc est aussi peu claire !

Tom Kyte explique ici que dans le cas d'un SELECT ordinaire (sans la clause FOR UPDATE), la phase EXECUTE ne fait rien de concret ("Most selects will do ZERO work during the execute"), et que c'est lors de la phase de FETCH que tout le travail se fait.
https://asktom.oracle.com/pls/asktom...00000346846274
En revanche, il essaye plus bas de faire des pirouettes sémantiques pour ne pas reconnaître que la doc est foireuse (eh oui, il a quand même l'étiquette de vice président d'Oracle, et la franchise a ses limites...)

[Pozzo]

Merci Pomalaix pour le lien sur Asktom. Intéressant.

Pour ce qui est de la taille du bulk fetch j'ai testé mon traitement avec une imite à 2.000 puis à 100.000
Le temps avec une limite à 2.000 est de 50% plus lourd (60mn vs 40mn).

Evidemment le contenu du traitement joue et je ne peux le détailler ici.
Disons cependant que "dans certains traitements" l'utilisation d'une limite importante pour le bulk fetch limit peut être envisagée.

Je clos le sujet.
Merci encore pour votre aide.

Pozzo

[mnitu]

En ce qui concerne la trace étenduée oui cela concerne les évènements d'attente (waits) lors de l'activation de la trace. TkProf est juste un programme de formatage de la trace brute d'Oracle et lui ne peut pas ajouter les waits si ils n'ont été pas activés avec la trace.

Concernant vos test avec des tailles de lots allant de 2000 à 100 000 vous devez prendre en compte les conditions d'exécution du traitement en question:

• traitement exécuté uniquement en batch pouvant utiliser toutes les ressources disponibles
• traitement exécuté par plusieurs utilisateurs simultanément en même temps que des autres transactions se poursuivent