Requêtes et performances:

 

I/ Différentes méthodes utilisées par l'optimiseur de requêtes

  • Table SCAN

Le principe est simple, il s'agit de balayer la totalité de la table

Ce balayage peut être réalisé en parallèle

1. Parallel pre-fetch

Le chargement du fichier est réalisé en parallèle

cette option est automatiquerment choisie par l'optimiseur si le temps prévu pour les I/O est supérieur au temps CPU prévu.Il faut autoriser cela par QQRYDEGREE et de préférence faire tourner le requête dans un pool avec expert cache actif (*CALC)

2. Parallel table scan

Idem au 1/ mais en plus le traitement de la table (une fois chargée en mémoire) est éclaté sur l'ensemble des processeurs.

Vous devez installer le dispositif facturable DB2/ SMP

3. Parallel table pre-load et parallel Index pre-load

Le chargement de la table et des index est réalisé en parallèle et en tache de fond, pendant le traitement de la requête (il faut beaucoup de mémoire, pour que cela soit efficace)

  • Accès par clefs

Utilisation d'un Index pour réaliser les tests contenus dans la requête.

  1. Index B-arbre

  2. Index EVI

ATTENTION, les Index EVI ne peuvent pas être utilisés pour les jointures.

Il vous faut donc encore quelques index b-tree sur les clefs primaires et étrangères, CE QUI EST FAIT en AUTOMATIQUE si vous mettez en place l'intégrité référentielle de DB2/400 (PRIMARY KEY et FOREIGN KEY)

  • JOINTURES et GROUPAGE

Le premier enregistrement de la table 1 est relié aux enregistrements de table2 suivant la valeur de clé (utilisation d'un index) , chaque enregistrement étant lui-même reliè à table3 etc…

Quand la jointure est complète, on passe à l'enregistrement suivant de table1 jusqu'à épuisement.

Cette technique est utilisée quand moins de 20 % des enregistrements sont retournés.

    • HASH JOIN (jointure par hachage)

On fabrique une nouvelle table (en mémoire)) dans laquelle on place les enregistrements selon la méthode du hash-coding. Il s'agit d'établir une correspondance entre la valeur d'une clé et la position physique de l'enregistrement par le jeu d'une fonction (souvent basée sur un nombre premier).la localisation d'une ligne est alors particulièrement rapide.


La table de hachage peut être construire en parallèle avec SMP


Cette technique n'est possible que si le paramètre ALWCPYDTA est fixé à *OPTIMIZE et si la jointure est INTERNE (pas de LEFT OUTER JOIN).

    • SORT/MERGE (tri / fusion)

Les deux tables sont triées suivant la valeur de la zone de jointure, puis l'on applique la jointure sur le résultat.

Un index n'est pas utile, cette technique est utilisée si plus de 20 % des lignes sont sélectées.



II/ Les leviers à notre disposition

  • Journalisation

Faut-il utiliser la journalisation SUR DES BASES DECISIONELLES ?

    • Pendant la phase de chargement initiale, si vous pouvez relancer la totalité du traitement, la réponse du lab est clairement NON.
    • Pendant les phases de mise à jour, comment faire autrement ? (il faut bien revenir à une situation propre en cas de plantage, en plus l'intégrité référentielle utilise elle aussi le journal)


Par contre pensez à enlever SMAPP (fonction de l'OS, provoquant une journalisation temporaire des index afin de proposer une reprise plus rapide) par la commande CHGRCYAP SYSRCYTIME(*NONE).

  • Taille mémoire

L'optimiseur prend des décisions en fonction de la mémoire disponible.

Il effectue un calcul simple : "taille du pool/ niveau d'activité" = mémoire maxi par job.


Si votre requête s'exécute seule dans un pool (ce qui est conseillé) , descendez le niveau d'activité à 1.


En même temps, utilisez l'expert cache qui est toujours une bonne solution pour les accès base de données, dans des pools mémoire de plus de 100 Mo (on garde en mémoire les tables les plus utilisées).


Cela se paramètre par CHGSHRPOOL PAGING(*CALC)

  • Allow Copy Data (ALWCPYDTA)

Ce paramètre est renseignable sur les commandes STRSQL , RUNSQLSTM


- *NO la copie des données n'est pas admises (pour des besoins de temps réel)

- *YES la copie des données est admises, elle n'a lieu que quand il est impossible de faire autrement

- *OPTIMIZE la copie des données est admises, elle aura lieu à chaque fois que cela améliorera les temps de réponses
(particulièrement le hachage pour les jointures)


c'est cette dernière valeur qu'il faut privilégier (elle est par défaut via ODBC./JDBC).

  • Traitement parallèle (QQRYDEGREE)

Ce paramètre est renseignable par la valeur système QQRYDEGREE (pour tout le monde) ou via le paramètre DEGREE de la commande CHGQRYA (session en cours)

    • *NONE pas de parallélisme spécifique. La parallélisme du chargement des tables est assuré par l'Os et le fait que les fichiers soient AUTOMATIQUEMENT éclatés sur l'ensemble des disques.

      Si vous installez de nouveaux disques, utilisez STRASPBAL
    • *IO utilisation du parallélisme uniquement pour charger les données en mémoire (par anticipation)
      (pas de SMP)

    • *OPTIMIZE utilisation du parallélisme de façon modérée (pour optimiser les requêtes, tout en laissant de la puissance aux utilisateurs)

    • *MAX utilisation du parallélisme de façon agressive (pour optimiser les requêtes, même si cela pénalise les utilisateurs)

    • *NBRTASK indiquez le nombre de processeurs à utiliser (CHGQRYA uniquement)


le point du vue du lab est le suivant : ( http://www.as400.ibm.com/developer/bi/teraplex/lessons.html#bigdials)

In the Teraplex Center, we have found that *MAX is most useful during the warehouse creation and update processes. For warehouse environments with many users running complex queries, running with SMP turned off is often a beter choice because the system will automatically send each query to its own processor.

En résumé, à utiliser pendant la phase de chargement de la base décisionnelle (traitement s'exécutant seul), à enlever si des utilisateurs travaillent pendant les mises à jour, l'OS/400 répartissant de lui-même les tâches sur l'ensemble des processeurs.

Par contre, si cela est possible, lors des mises à jour lourdes, le parallélisme améliorera grandement le temps de maintenance des index (lors du chargement initial, le plus simple est de construire les index après).

  • OPTIMIZE for x ROWS

Vous pouvez influencer l'optimiseur de requêtes en indiquant le nombre de lignes à traiter en même temps :


Si vous indiquez une petite valeur, l'optimiseur cherche à rendre un résultat la plus rapide possible, vous le forcer à utiliser des index, même si le temps global doit en pâtir.
C'est une solution interactive (STRSQL considère, sans indication de votre part, une optimisation pour 3% des lignes du fichier)

Si vous indiquez une GRANDE valeur (FOR ALL ROWS est admis depuis V4R30) l'optimiseur privilégie le temps global de traitement. Vous favoriserez les copies temporaires, les tris, le hachage s'ils sont plus efficaces)

C'est une solution purement orientée batch et gros volumes.
(INSERT into …SELECT … FORM …, est toujours traité pour un nombre maxi de lignes)

 

III/ Les outils de mesure :

1/ passez la commande STRDBG avant de lancer SQL en mode 5250 ,

vous verrez alors après chaque requête des messages CPI43xx ansi que SQL79xx dans l'historique indiquant les choix de l'optimiseur.

ID message Texte du message 
CPI4321    Chemin d'accès créé pour le fichier &4. 
CPI4322    Chemin d'accès créé à partir du fichier avec clé &1. 
CPI4323    Le plan d'accès du processeur de requêtes de l'OS/40 
CPI4324    Fichier temporaire créé pour le fichier &1. 
CPI4325    Fichier résultat temporaire créé pour la requête. 
CPI4326    Fichier &1 traité en position de jointure &11. 
CPI4327    Fichier &1 traité en position de jointure 1. 
CPI4328    Chemin d'accès du fichier &4 utilisé par la requête. 
CPI4329    Séquence d'accès par ordre d'arrivée utilisée pour la requête 
CPI433A    Impossible d'extraire le fichier d'options de requêtes 
CPI433B    Impossible de mettre à jour le fichier d'options de requêtes
CPI433C    Bibliothèque &1 introuvable. 
CPI433D    Options de requête utilisées pour créer le plan d'accès
SQL7910    Les curseurs SQL ont été fermés.                          
SQL7911 0 ODP réutilisé. 
SQL7912 0 ODP créé. 
SQL7913 0 ODP supprimé. 
SQL7914 0 ODP non supprimé. 
SQL7915 0 Le plan d'accès de l'instruction SQL a été construit
SQL7916 0 Groupage utilisé pour la requête.                          
SQL7917 0 Plan d'accès non mis à jour.
SQL7918 10 ODP réutilisable supprimé. 
SQL7919 0 Conversion de données nécessaire pour FETCH ou SELECT
SQL7939 0 Conversion de données nécessaire pour INSERT ou UPDATE

 

2/ mettez le paramètre QRYTIMLMT à 0 par la commande CHGQRYA :

toutes vos requêtes seront alors refusées (CPA4259) , l'optimiseur vous indiquant le temps qu'il a prévu :

 

3/ Pour les Batch, utilisez le Moniteur base de données :


dans une session 5250, lancez la commande STRDBMON

via Operation navigator : choisissez base de données/moniteur de performances SQL


V4R5, Visual Explain

 

 

Vous pouvez maintenant obtenir une Explication graphique du détail d'une requête SQL avec Visual Explain :

 

 

 

Le bouton EDITION SQL, place cette requête dans la fenêtre d'exécution de scripts SQL

 
Le bouton que voici lance la requête avec QRYTIMLMT à 0
(la requête ne sera pas vraiment exécutée), mais l'optimiseur aura fait son travail)
ce qui permet une analyse

 

Visual Explain vous affiche alors le détail des différentes phases de la requête :

ICI, la phase de jointure

et pour terminer un Hachage pour produire le résultat.

La fenêtre droite vous donne le détail pour chaque étape (ici la jointure)







une fois le moniteur arrêté :

 

Copyright © 1995,2001 VOLUBIS