V5R20, Visual Explain

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.

V5R20


Le moteur SQL (query engine) a été revu, mais cohabite avec l'ancien.


 le nouvel optimiseur de requête se nomme SQE (SQL query Engine)
 l'ancien CQE (Core query Engine)


 De nombreuses fonctions SQL ont étées descendues en dessous du microcode,
  DONT SQE, qui est écrit en programmation Objet (C++) au niveau SLIC.


ATTENTION, seul SQL peut utiliser SQE :


  - SQL interactif ,dans les programmes ou des scripts (RUNSQLSTM), QM.
  - CLI, NetDATA et autre produits utilisant CLI
  - JDBC/ ODBC


 n'en profitent pas :


  - les E/S natives
  - OPNQRYF
  - QUERY/400

 Ceci dit, pour des problèmes de transition et de temps, toutes les
  requêtes ne profiterons pas immédiatement de SQE.


 il donc été mis en place un dispatcher qui va diriger (router) chaque
  requête soit vers SQE, soit vers CQE.


 Plus SQE saura traiter de cas différents, (au fur et à mesure des versions
  et des PTF, voyez l'APAR II13486) plus le dispatcher dirigera vers SQE.


 En début de V5R20 (début 2003), le dispatcher route la requête vers CQE si:


 - le resultat de la requête est modifiable (FOR UPDATE OF...)
 - la requête reférence :
   -> plus d'une table (jointure ou UNION)
   -> une table avec des BLOB ou des CLOB
   -> un fichier logique ou une vue SQL
   -> des tables distribuées (DB2 Multisystem)


 - la requête contient :
   -> un autre ordre SELECT( sous sélection, table dérivée, ...)
   -> une clause OR, LIKE , IN
   -> des comparaisons de colonnes ayant un CCSID différent



 - vous utilisez DB2 SMP,dispo facturable pour utilisation multi-processeurs


   Toutes les autres requêtes doivent être dirigées vers SQE.
   (suite à STRDBMON, QQC16 d'une ligne QQRID à 3014, l'indique)


 Au 14 Mai 2003, avec le gernier GROUPE de PTF database, (SF99502)
  ainsi que les PTF :


   - SI07650  et -SI08876


 SQE traite les requêtes avec :


   JOIN ainsi que les jointures en règle générale.
   les prédicats IN et OR


   Ainsi que les INSERT avec un SELECT imbriqué


 (voyez l'APAR II13486 et le site
 http://www-1.ibm.com/servers/eserver/iseries/db2/sqe.html )


 l'utilisation de DB2 SMP devrait être aussi intégrée rapidement.



 toutefois, il peut arriver que SQE soit invoqué à tort, auquel cas il
  redirige lui-même la requête vers CQE (légère perte de temps),
  particulièrement lors de l'utilisation d'index particuliers:


    - avec des sélections/omissions
    - pointant sur plusieurs membres
    - avec des clé dérivées (zones résulat de traitement: CONCAT, TRANSLATE)
    - utilisant le paramètre SRTSEQ et/ou le mot-clé ALTSEQ




 Data Access Primitives 


 SQE, contrairement à CQE ne fait pas de statistiques, de sondages sur les
  différentes valeurs et leur nombre, ces fonctions sont réalisées par l'OS.


 SQE ne créé pas d'index temporaires, mais il réalise mieux les balayages
  de table et les traitement de la clause DISTINCT.


 Il ne créé plus de tables temporaires pour les requêtes complexes, mais
  un objet interne (niveau microcode), plus léger.



 Nouveautés .


 Plan Cache 


   les plans d'accès SQE sont désormais conservés (dans un cache)


     - jusqu'à IPL ou jusqu'à une certaine taille (non indiquée)
         les plus anciens et les moins utilisés sont alors détruit.


     les SQL Package existent toujours (ainsi que le cache niveau JOB),
      ils stockent toujours les paramètres systèmes et les requêtes, mais
      ils utilisent les plans d'accès du cache système.


     - on mémorise jusqu'à 5 plans par requêtes
        (avec ou sans index, avec ALWCPYDTA(*YES ou *OPTIMZE), etc..)


     - on peut espérer, lors d'une requête,(sauf suite à IPL) rencontrer
        un plan d'accès déja validé et ainsi gagner du temps.


     - il n'existe pas de possibilité de visualisation de ce cache.



 Statistic Manager 


  nouvelle fonction (au niveau SLIC) chargée de collecter des informations
   liées aux performances.


  Avant, le seul moyen de prendre de bonnes décisions, était pour
  l'optimiseur d'utiliser les index (estimations), maintenant
  la collecte de statistiques permet au statistic manager de founir des
  informations plus pertinantes.


  c'est lui qui indique l'index à utiliser (et non plus l'optimiseur)


  Si aucune statistique n'existe pour une table, l'information est mémorisée
   et les statistiques sont collectées lors des temps morts.

 les statistiques contiennent les informations suivantes :


   - Cardinalité :  nombre de valeur différentes
   - Fréquence   :  liste des valeurs les plus fréquentes + nbr de lignes
   - Histogramme :  divise les différentes valeurs en tranches égales
                     et indique pour chaque tranche 1er, dernier et nombre.


 Elle sont collectées pour une colonne à la fois, et éventuellement à partir
  d'un index existant



  Vous pouvez aussi demander explicitement la collecte de certaines
    satistiques via iseries navigator ou API :



 La collecte de statistiques est effectuée par le JOB QDBFSTCCOL
   .................................................................
   : QALERT        QSYS        SYS    0,0                  DEQW    :
   : QCMNARB01     QSYS        SYS    0,0                  EVTW    :
   : QCMNARB02     QSYS        SYS    0,0                  EVTW    :
   : QCMNARB03     QSYS        SYS    0,0                  EVTW    :
   : QDBFSTCCOL    QSYS        SYS    0,0                  EVTW    :
   : QDBSRVXR      QSYS        SYS    0,0                  DEQW    :
   :...............................................................:
  Ce job est multi-thread, faire DSPJOB et option 20 pour les voir.
   .......................................................................
   :     Unité                       Total                    Priorité   :
   :   exécution       Etat            UC            E-S     exécution   :
   :   00000001        EVTW          0,132           458          50     :
   :   00000007        DEQW          0,000             1          99     :
   :   00000006        DEQW          0,000             0          99     :
   :   00000005        DEQW          0,016            65          96     :
   :   00000004        DEQW          0,013           104          96     :
   :   00000003        DEQW          0,000             0          93     :
   :   00000002        DEQW          0,000             1          90     :
   :.....................................................................:



 les requêtes utilisateur courtes sont traitées à une priorité de 90
 les requêtes utilisateur longues sont traitées à une priorité de 93


 les requêtes système courtes sont traitées à une priorité de 96
 les requêtes système longues sont traitées à une priorité de 99


 la valeur système QDBFSTCCOL permet de gérer les collectes


  -  *ALL   : autorise la collecte de toute statistique (val. par défaut)


  -  *NONE  : interdit toute collecte automatique (les collectes immédiates
               demandées par l'utilisateur sont toujours effectuées)


  -  *USER  : autorise les collectes utilisateur en tâche de fond.


  -  *SYSTEM:  autorise les collectes système en tâche de fond.


 les collectes étant réalisées par défaut en tâche de fond, cela devrait
   avoir peu d'impact sur les temps de réponse, mais peut en avoir sur
   l'utilisation globale de la CPU (moins de périodes d'inactivité).

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, qui plus est, 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 pour chaque job (fair share)

CQE 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)

SQE, lui divise la taille du pool par le nombre moyen de travaux (expert cache obligatoire, pour faire la moyenne)

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) et utilisation de la mémoire maxi (pas de fair share)
*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 le 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)