Outil automatique d'analyse d'EXPLAIN [Résolu]

[battistuta]

Contexte : DB2 V8 sous Z.
Bonjour, je souhaite mettre en oeuvre un petit outil simple dans mon entreprise pour contrôler de manière automatique les chemins d'accés DB2 potentiellement consommateurs de CPU.
Pour cela j'envisage d'automatiser l'EXPLAIN lors des BIND PACKAGE et m'appuyer sur certaines données de la PLAN_TABLE pour "traquer" les ordres SQL non optimisés.

Mon idée première est de récupérer :
- tous les chemins d'accés qui n'utilisent pas d'index (TABLESPACE SCAN) : ACCESSTYPE = 'R'.
- les tris interne sur les tables réelles (non composites) pour effectuer des DISTINCT ou JOIN : METHOD = '3', SORTN_UNIQ = 'Y' ou SORTN_JOIN = 'Y'.

Mais je ne suis pas sur de la pertinence ou de l'exhaustivité de ces filtres. Ne faudrait il pas aussi traquer les tables à fortes volumétries ou avec des cardinalités faibles ? Avez vous d'autres idées ?

Merci pour vos réponses.

[alex.]

Vaste sujet qui peut devenir très complexe si on se laisse aller. Effectivement, tu peux :
- ajouter des scans d'index en matchcols 0, des matchcols de 1 sur une première colonne non discriminante.
- ne pas évaluer les I1 (one fetch index)
- utiliser la table des coûts DSN_STAqqch de l'explain pour faire une évaluation du coût de la requête.

Enfin, je te conseille que ton analyse prenne en compte que les stats sont bien présentes (pas de -1) ou quels ne sont pas à 0 ou en dessous d'un seuil à déterminer. Sur un site, j'ai vu aussi la chasse au multi-index scan mais bon perso j'ai rien contre.

D'une manière générale, cela présuppose que :
- ta plan_table soit bien géré (ménage régulier)
- les stats soient à jour
- tu saches différencier les pgms batch et tp.

Fais simple parce que tu ne pourras pas prendre en compte tous les cas de l'optimiseur. Tu peux aussi prévoir des exclusions car il y a des applis qui sont des cas désespérés et sur lesquels il n'y a plus ou trop peu de maintenance.

Bon courage.

[battistuta]

Ok, merci beaucoup. Effectivement je n'avais pas pensé aux stats non significatives. Du coup je vais les exclure de mon traitement et les recycler dans un fichier d'anomalie.
A mon avis, il faut mettre en place des règles de gestion assez compliqué pour arriver à un résultat ne comportant pas de "parasites".

Au boulot.

PS :

[pdz74]

Vaste sujet en effet que d'essayer d'automatiser des controles de chemins d'accès. Le problème est que tout n'est pas aussi simple que de rechercher tous les tablespaces scan par exemple. Différents exemples :
- un scan en TP, c'est souvent problématique, mais en batch c'est souvent un besoin.
- pour un batch, il est préférable de faire 1 fois le scan d'une table de 1 million de lignes, plutôt que 1 millions de fois un accès direct. Pourtant une analyse de la plan_table donnera des résultats sans souci pour le 2ème cas alors que le programme durera 10 fois plus longtemps que le premier.
- pour des petites tables, un scan n'est pas toujours un souci. En effet quand l'ensemble des lignes tiennent sur une page de 4k, le passage par un index coutera plus cher en i/o pages puisqu'il faut accéder à l'index et aux datas (sauf si indexonly).
- un double scan de tables pour réaliser une jointure, c'est mortel si méthod 1 mais parfait si method 2.
- ...

Bref une bonne analyse commence par une bonne connaissance du SI et des tables qui l'accompagnent.

Perso, j'ai en effet développé des chaines journalières qui recherchent tous les binds réalisés depuis le dernier passage (toujours en explain yes en production). Ensuite, comparaison anciens et nouveaux chemins d'accès et édition des différences (ou édition simple si premier bind). Cela permet une analyse simplifiée avec un état clair car "select * from plan_table", c'est pas génial à analyser... Si tu as la chance d'avoir easytrieve, c'est super simple à faire.

En plus de l'analyse des chemins d'accès, un autre point est très important en terme de perf, c'est le suivi de la volumétrie de tes tables. Il est en effet courant d'avoir des tables qui commencent à vide pour se remplir petit à petit. Si aucun runstats/rebind n'est jamais réalisé, ça se dégrade très vite. Il faut donc suivre la volumétrie des tables.

Si tu veux , je peux te passer des exemples de jcls qui font toutes ces manips. Me faudrait juste ton adresse mail (boulot ou perso).

Bonne mise en place !

[battistuta]

Je pense effectivement distinguer le TP du batch pour cibler un peu plus. Merci pour les réponses en tout les cas.