select * d'une table énorme

**sam01** · 18/08/2011, 14h40

Bonjour,

nous venons de nous apercevoir qu'un job dure anormalement longtemps.

Et quand on regarde le programme, on s'aperçoit qu'il y a un select * qui balaye donc toute la table :

Code :

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

1
2
3
4
5
6
7
 
 
     SELECT *                                                 
     FROM CFVTP                                               
     ORDER BY XAX,BTP,JKP,BDF,             
              NHY,LPM,HDT,NHP             
     FOR FETCH ONLY ;

La table en question fait plus de 500 Millions de lignes.

Mes question sont les suivantes :

Est-il normal en production de balayer la totalité d'une table qui à cette taille ?

La requête dure 4 heures. Y a-t'il un moyen de l'optimiser ?

Merci pour votre aide.

Existe t'il un moyen efficace de détecter les requêtes qui consomment trop ?

Merci pour votre aide.

**pdz74** · 18/08/2011, 15h23

Bonjour,

Pour répondre à tes différentes questions.

Est-il normal de scanner une table de cette grosseur -> Ca, seul le responsable d'appli peut te répondre. Ca peut malheureusement être normal, ça peut aussi être un moyen simple de ne pas chercher à analyser le besoin réel et d'éliminer les lignes inutiles de manière applicative plutôt que par une clause Where.

Y'a t-il un moyen de l'optimiser. Ca vaut le coup de se poser la question de l'ORDER BY. Est-il utile ? Si la table est vraiment énorme, ça peut même faire exploser la DSNDB07. A moins qu'il n'existe un index sur les colonnes de tri et que DB2 se serve de cet index pour réaliser son tri. Mais dans ce cas, cela signifie double scan de la table et de l'index. Dans tous les cas, ton tri est consommateur. Pour info, le tri MVS est plus rapide que le tri DB2.

Autre axe d'optimisation : tu remplaces le programme de mise à plat par l'utilisation de l'utilitaire UNLOAD qui est beaucoup plus rapide qu'un programme. Si en plus, tu as des zones en VARCHAR, tu ajoutes le paramètre NOPAD, histoire d'optimiser l'espace disque nécessaire et le temps d'écriture disque.

Quant à analyser les requêtes consommatrices, il y a de nombreux outils sur le marché, certains se contentant de tracer les programmes et de sortir toutes statistiques utiles, d'autres te donnant des conseils et axes d'optimisation, ... La liste est longue, il faudrait donc cibler ton besoin.

A ta disposition et n'hésite pas à nous donner des infos complémentaires.

**bernard59139** · 18/08/2011, 15h52

chez nous, un programme qui fait "SELECT *" sur 500M de lignes avec un order by, c'est retour à l'envoyeur (au développeur).

De plus, avec un order by de ce type, db2 peut choisir de passer par un index pas du tout optimal pour ce genre de requete.

a+

**sam01** · 18/08/2011, 17h23

Merci beaucoup pour vos réponses.

Je vas peut-être plus me tourner vers un unload de la table comme le préconise pdz74 (merci au passage pour tes remarques pertinentes, notament le coup de NOPAD que je ne connaissais pas ).

Je suis de ton avis bernard59139 pour faire un retour à l'envoyeur, le souci c'est que l'on pas de garde fous à ce sujet.

Je pense que les programmes sont testés (si on peut appeler ça tester..) dans des petits environnements de test avec des petites volumétries, et quand ça passe en prod ça fait des catastrophes.

Je ne sais comment vous faîtes chez vous (dans votre société) pour vous prémunir de ce genre de désagréments.. je pense que c'est impossible de scanner tous les programmes qui arrivent en production...

**bernard59139** · 18/08/2011, 17h56

chez nous, il y a quelques garde-fou, pas toujours efficaces.
ces garde-fou ne sont que des programmes maison, avec des bugs, et tous les cas possibles ne sont pas pris en compte.

ensuite, nous avons des "recommandations". Certaines doivent impérativement être suivies (pas de SELECT *), d'autres ne sont que de simples conseils (pas de ORDER BY). Toutes les recommandation peuvent conduire à une réécriture obligatoire du programme.
Le tout fait partie des "normes de developpements" consignées dans des doc officielles.

Et comme toi, on s'appercoit de certains problèmes que quand le prog arrive en PROD.

**punkoff** · 19/08/2011, 00h52

Envoyé par sam01

Je suis de ton avis bernard59139 pour faire un retour à l'envoyeur, le souci c'est que l'on pas de garde fous à ce sujet.

Je pense que les programmes sont testés (si on peut appeler ça tester..) dans des petits environnements de test avec des petites volumétries, et quand ça passe en prod ça fait des catastrophes.

Je ne sais comment vous faîtes chez vous (dans votre société) pour vous prémunir de ce genre de désagréments.. je pense que c'est impossible de scanner tous les programmes qui arrivent en production...

Salut,

Perso, on avait une image de la prod (pré-prod) et on faisait tourner au moins une fois tous les programmes livrés. Ca ne permet pas de se prémunir de tous les problèmes mais au moins des problèmes foireux du genre de ta requête ...

Ensuite il existe toujours une période de garantie, suite à la livraison de ton prog par la SSII, donc la faut checker les stats de l'AS suite à une mise en prod... y a pas de secret.

**Mat.M** · 19/08/2011, 01h35

Salut Sam01 lorsqu'on apprend le langage SQL on déconseille clairement de faire un "select *"
Avec une clause where ce serait peut-être plus rapide...
c'est parfaitement logique que si tu veux sélectionner toutes les 500 millions de lignes de ta base de donnée ça va prendre du temps

Envoyé par sam01

Est-il normal en production de balayer la totalité d'une table qui à cette taille ?

pas du tout c'est presque illogique.
Je ne comprends pas pourquoi on balaie comme ça les 500millions de lignes.
Le langage SQL c'est spécialement conçu pour sélectionner les enregistrements voluus
Dans une application on ne fait quasiment jamais de select *

**sam01** · 19/08/2011, 10h23

Merci beaucoup pour vos retours d'expériences, je me disais que ce n'était pas normal...

C'est vrai qu'un unload est beaucoup plus simple et moins consommateur, le souci c'est qu'il y pas mal de développeur qui ne connaissent pas les outils de production (unload PAR EXEMPLE), et donc ils restent dans bulle et font des select à gogo !

**bernard59139** · 19/08/2011, 11h40

ils restent dans bulle

là, je ne suis pas d'accord.
conclusion trop rapide.

**sam01** · 19/08/2011, 13h52

je suis d'accord, ne généralisons pas, mais c'est du vécu...

**Julien Del** · 22/08/2011, 09h25

Je confirme: en tant que développeur, on est balancé sans préparation et pratiquement aucune formation.
Ensuite on se forme sur le tas en suivant ce que font les collègues qui eux aussi ont appris sur le tas...

**Sankasssss** · 26/08/2011, 23h16

Bah je pense que c'est ainsi dans quasiment toutes les branches.
Pas le temps de former convenablement donc on laisse faire des conneries au jeunes débutant...
Et c'est pas le pire, y'a aussi les anciens qui vont partir et dont on ne prépare pas le départ en faisant le transfère de connaissance et après on se plaint que personne ne sais comment faire ce que notre bon ancien faisait si bien sans que l'on s'en rende compte...

**sam01** · 16/09/2011, 14h05

Envoyé par bernard59139

chez nous, un programme qui fait "SELECT *" sur 500M de lignes avec un order by, c'est retour à l'envoyeur (au développeur).

De plus, avec un order by de ce type, db2 peut choisir de passer par un index pas du tout optimal pour ce genre de requête.

a+

Bonjour,

désolé de revenir dessus mais voici ce que répond l'équipe qui analyse les performances :

je cite

Code :

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

1
2
3
4
5
6
7
 
on fait un SELECT * sans clause WHERE, on parcours toute la table.
Ce qui aurait pu expliquer une durée excessive, c'est le ORDER BY ==> TRI
 
Mais comme cette table contient 1 index qui contient toutes les colonnes de l'order by et dans le bon ordre,
 
Pas d'amélioration possible côté DB2. L'étape traite 570 millions de lignes.

Voilà .

Je ne sais ce que vous en pensez, mais je trouve que l'explication n'est pas complète.
Je ne comprends pas comment on peut dire qu'un select d'une table de
570 Millions de lignes est normal...et en production en plus.

J'ai besoin de vos avis, je présente peut-être mal la chose. Merci pour votre aide.

**bernard59139** · 16/09/2011, 15h58

Sauf si l'index est très bien organisé, LEAFFAR = 0 et leafnear=0, et réellement, pas seulement avec des stat qui peuvent dater, le balayage de l'index peut prendre énormement de temps.

Ensuite, DB2 est obligé d'accéder aux données. En passant par l'index et pour chaque ligne lue, il y a 1 accès index en sequence + 1 accès data en direct depuis l'index. ET là, attention au cluster ratio.

Avec une requête similaire à ton exemple, dans certains de mes batchs, un TableScan d'une table de 300millions de lignes me prend 10m alors qu'une lecture par l'index me prend plus d'une heure. En pleine charge batch.

**Luc Orient** · 16/09/2011, 22h15

Déjà, est-ce que l'analyse du chemin d'accès choisi par DB2 a été faite (fonction EXPLAIN) ?

Ensuite, est-ce que tu es sûr que la majorité du temps d'exécution se situe bien dans cette requête et pas ailleurs ?
Un outil comme STROBE ou TriTune devrait te donner la réponse.

**sam01** · 17/09/2011, 21h03

Envoyé par bernard59139

Sauf si l'index est très bien organisé, LEAFFAR = 0 et leafnear=0, et réellement, pas seulement avec des stat qui peuvent dater, le balayage de l'index peut prendre énormement de temps.

Ensuite, DB2 est obligé d'accéder aux données. En passant par l'index et pour chaque ligne lue, il y a 1 accès index en sequence + 1 accès data en direct depuis l'index. ET là, attention au cluster ratio.

Avec une requête similaire à ton exemple, dans certains de mes batchs, un TableScan d'une table de 300millions de lignes me prend 10m alors qu'une lecture par l'index me prend plus d'une heure. En pleine charge batch.

Bonsoir bernard59139 et merci pou ta réponse.

Alors si j'ai bien compris,
lors d'une telle requête, il y a 2 étapes :

1) Balayage de tout l'index (donc 570 millions de lignes)

2) Ensuite lecture ligne par ligne, d'abord l'index puis la ligne.

DB2 doit prendre un temps fou à faire tout ça.

Je ne sais si l'index est bien réorganiser...
Comment connaître les valeurs de LEAFFAR et leafnear. Ce sont des colonnes e l'index ? Je vais chercher de mon côté.
Il y a une chose qui m'interpelle dans ton message, c'est la notion de TableScan. Qu'est-ce que c'est exactement ? Puis remplacer le select * par un table scan en production ? (ou bien ce sont deux choses totalement différents ?)

Je vais aussi en profiter pour répondre à Luc Orient.
Un explain n' pas été fait. C'est compliqué à faire ? Aurais-tu un exemple de JCL pour effectuer un explain et comment l'interpréter ? (Là je prends de l'avance sur ma futur formation

)

Je ne suis pas du tout sûr que la majorité du temps est sur l'accès DB2 aucune trace n'a été faîte. On s'est juste apperçu que ce job prenait énormément de temps et en fouillant dans le programme, on est tombé sur ce fameux select *...

Je ne suis pas au système alors je n'ai pas les outils pour...
Je ne sais pas ce que l'on a la maison (STROBE ou TriTune ?) je vais me renseigner.

Je trouve juste que la réponse que l'on m'a faîte est un peu limite. Nous avons en plus solliciter l'équipe de développement qui se charge de ce programme mais elle essaie d'éviter le sujet...

Merci pour votre aide et vos précieux conseils.

**bernard59139** · 18/09/2011, 07h10

1) Balayage de tout l'index (donc 570 millions de lignes)

2) Ensuite lecture ligne par ligne, d'abord l'index puis la ligne.

DB2 doit prendre un temps fou à faire tout ça.

OUI, si tu passe par l'index, et si l'index est mal organisé (Near et Far leaf)

Pour connaitre les stats sur Far et Near LEAF, consulte le catalog, sysindexpart je crois. Après un runstats si possible.
Et pour comprendre leur importance, consulte la doc l'admin guide.

pour apprendre l'EXPLAIN, je te conseille fortement de te faire expliquer online par un collègue. On peut écrire des bouquins rien que sur cet ordre SQL.
le faire est une chose, interpreter le résultat en est une autre.
Une 1ere approche est dans la doc SQL de db2.

**bernard59139** · 19/09/2011, 06h55

Et, comme le fait remarque Luc Lorient, il faut être sur que la requête dure longtemps.

**sam01** · 19/09/2011, 10h53

Envoyé par bernard59139

OUI, si tu passe par l'index, et si l'index est mal organisé (Near et Far leaf)

Pour connaitre les stats sur Far et Near LEAF, consulte le catalog, sysindexpart je crois. Après un runstats si possible.
Et pour comprendre leur importance, consulte la doc l'admin guide.

Juste pour info,

voici les valeurs de FAR et NEAR LEAF

Code :

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

1
2
3
 
LEAFNEAR: 1,375                      LEAFFAR : 0
Le runstat date du 7 mais 2011.

**iberserk** · 19/09/2011, 13h47

L’intérêt ne serait'il pas d'analyser ce qui est fait de ce résultat?

L'équipe vous répond simplement que côté DB2 ils ne peuvent rien faire... et je les comprends...

Il est impensable de faire un SELECT * sur une table pareil...

Pour reprendre régulièrement beaucoup de ces BATCH de 'développeur', 9 fois sur 10 il s'agit d'un traitement ligne à ligne en code client (JAVA,CSHARP etc.) avec même parfois un filtre fait côté client (donc trop tard).

Je commencerais donc par analyser qui (quoi) fait cette requête et quoi sert son résultat...

select * d'une table énorme

DB2

Discussions similaires

Partager

Partager