Requêtes et performances


I/ 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 voyez s'il faut  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).

  • Mémoire et sous système

L'optimiseur prend des décisions en fonction de la mémoire disponible, tout en essayant de laisser de la mémoire aux autres travaux (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.



»Pour travailler dans un Pool dédié :


allouez de la mémoire à un Pool partageable : CHGSHRPOOL *SHRPOOLn SIZE(xxx)

créez un sous système utilisant ce pool :

CRTSBSD MONSBS POOLS((1 *SHRPOOL1)) TEXT('Sous système dédié')
puis
ADDRTGE MONSBS SEQNBR(10) CMPVAL(*ANY) PGM(QCMD) CLS(QBATCH)    

  • créez et ajoutez un JOBQ (ADDJOBQE) , si vous réalisez vos test en BATCH

  • ajoutez une entrée WorkStation (ADDWSE) si vos travaillez en 5250

  • Pour ODBC/JDBC (dont Iseries navigator), suivez la procédure suivante :
    • Ajoutez un travail à démarrage anticipé à votre sous système, par :
      ADDPJE SBSD(FORMATION/FORMATION) PGM(QSYS/QZDASOINIT) INLJOBS(1) THRESHOLD(1) JOBD(Qgpl/QDFTSVR) CLS(QSYS/QPWFSERVER)

    • modifiez les propriétés du serveur Database via Iseries Navigator


    • allez sur l'onglet sous système


    • Le bouton Ajout, permet d'indiquer votre sous système pour un ou plusieurs clients (adresse IP)
                             
                                                            


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

ATTENTION, Si vous faites des tests, le deuxième test ira toujours plus vite que le premier, du fait que les données sont (partiellement ?) restées en mémoire, ce qui peut fausser vos mesures.

Placez vous alors Obligatoirement dans un pool ou vous êtes seul, et lancez entre deux requêtes :
CLRPOOL POOL(*SHRPOOL1)

  • 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 et les index bitmap)


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

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

 


II/ Les outils de mesure :


D'abord un petit, "truc", 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.


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

vous verrez alors après chaque requête des messages CPI43xx ainsi que SQL79xx dans l'historique indiquant les choix de l'optimiseur lors de la création du plan d'accès


2/ pour un programme, vous pouvez voir le plan d'accès par la commande PRTSQLINF

vous verrez alors, dans un spool portant le nom du pgm, pour chaque requête des messages SQL40xx

 

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


dans une session 5250, lancez la commande STRDBMON

ou, mieux,

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

Les outils graphiques

1/ le gestionnaire de scripts SQL (CWBUNDBS.EXE)

Vous pouvez :

  • sauvegarder et relire un script SQL

  • lancer tout ou partie du script

  • demander l'inclusion des messages degug et voir l'historique du travail sur l'AS/400

  • modifier vos attributs de requête. (fichier QAQQINI)





2/ le moniteur de perf. Base de données.

cliquez sur moniteur de Base de données / nouveau ...

il s'agit en fait de la commande STRDBMON, il faut indiquer la bibliothèque où stocker les fichiers.

 

Puis indiquer les travaux à analyser (un , plusieurs , ou tous)

et les options à écrire dans le fichier trace

  •  ici, nous avons choisi une trace récapitulative (donnant des cumuls) STRDBMON va alors créér un fichier par option choisie sur cette fenêtre.
  • L'option détaillée elle, écrit dans un seul fichier, mais attention aux volumes !
    (difficile d'utilisation sans se limiter à UN seul travail!)
  • Enfin, si vous lancez STRDBMON hors de l'interface iSeries Navigator, il faut ensuite l'importer.

quand la trace est terminée (l'arrêt est à votre charge), choisissez une vue (les données à afficher)

il s'agit en fait d'un ordre SQL, lancé sur les fichiers stockés dans la bibliothèque choisie au début du paramétrage.

Vous pouvez modifier cet ordre ....... avec le gestionnaire de scripts :

Sur un moniteur détaillé, IBM fournit des requêtes d'exemple afin d'obtenir des statistiques globales à l'adresse http://www-03.ibm.com/servers/eserver/iseries/db2/dbmonqrys.htm


3/ 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 basée sur une estimation

 

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é (ou importé):






· les informations affichées par Visual Explain

chaque icône représente une action :
  • création
  • balayage (scan)
  • recherche, accès direct (probe)
les actions peuvent être enchaînées : accès direct à un index (SETLL), puis balayage (READE)

Ces actions vont porter sur un/des objet(s) :
  • permanent
    • table
    • index
Icône STRDBG PRTSQLINF Commentaires
Table Scan
Table Scan		 CPI4329 SQL4010 Utilisé pour retourner un grand nombre de lignes
Table Probe
Table Probe		  
Accès direct à une ligne par son n° de rang
(la connaissance du n° de rang peut venir d'une lecture d'index)
Index Scan
Index Scan		 CPI4328 SQL4008
 utilisé pour retourner un petit nombre de lignes, si l'index correspond au critère de tri ET à une sélection, par exemple.
Index Probe
Index Probe		 CPI4338 SQL4032
 utilisé pour retourner un petit nombre de lignes, l'index permettant de réaliser la sélection la plus importante
(primary key, par exemple)
Index Probe
EVI Probe		 CPI4328 SQL4008
SQL4011		 utilisé éventuellement avec d'autres pour créer un bitmap, entraîne ensuite un accès direct sur la table (table probe)
  • ou sur un objet temporaire (créé donc, ce qui implique ALWCPYDTA)
    • table de hachage
    • liste triée
    • liste simple
    • liste numérotée (basée sur les n° de rang)
    • index bitmap
    • index temporaire
    • buffer
Icône STRDBG PRTSQLINF Commentaires
Hash Scan
Hash Scan		 CPI4329 SQL4010
SQL4029		 Utilisé  principalement pour la gestion du GROUP BY
Table Probe
HASH Probe		 CPI4327 SQL4007
SQL4011		 Utilisé principalement pour la jointure
Temporary Sort List
Liste triée (Scan)		 CPI4328
CPI4325		 SQL4008
SQL4002		 Utilisé  pour Order BY et l'option DISTINCT
Temporaty List Probe
Liste triée (Probe)		 CPI4327 SQL4007
SQL4010		 Utilisé pour une jointure avec un autre critère que l'égalité
Temporary List
Liste simple (Scan)		 CPI4325
CPI4327		 SQL4007
SQL4010		 Utilisé pour préparer une utilisation parallèle (SMP)
Temporaty Row Scan
Liste numérotée (Scan)



Utilisé avec des index multiples pour favoriser ensuite le groupage des I/O disque.  (récupération physique des lignes par paquet)
Temporaty Row Probe
Liste numérotée
(Accès direct)
  CPI4338  SQL4032 technique bitmap pour combiner plusieurs index et limiter ensuite l'accès direct aux lignes de la table
Bitmap Scan
Bitmap Scan
  CPI4338  
SQL4010
SQL4032		 technique pour combiner plusieurs index en un index bitmap.

Un index bitmap est un nuage de point ou chaque position représente l'adresse, un liste numérotée contient les adresses (N° de rang)
Bitmap Probe
Accès direct Bitmap
  CPI4338 SQL4011
SQL4032		 technique bitmap pour combiner plusieurs index et limiter ensuite l'accès direct aux lignes de la table
Index Scan
Index Scan		 CPI4321 SQL4009
 création d'un index temporaire pour tri ou groupage
 (de plus en plus rare)
Index Probe
Index Probe		 CPI4321 SQL4009
 création d'un index temporaire probablement pour jointure
BUffer Scan CPI4330 SQL4030 Objet temporaire utilisé lors des opérations de parallèlisme (SMP)

Enfin, l'icone vous donnera des informations globales (temps, nombre de lignes résultat , ...)



· V5R20 & Collectes de statistiques


Pour définir ou voir les statistiques, utilisez le clic droit sur une table

Le bouton nouveau, permet de définir une nouvelle collecte
Une message vous est affiché (la première fois au moins)


puis :



si vous demandez la durée estimée, on vous affiche (ici sur une table de 500.000 lignes)


puis cliquez sur

  • Collecte immédiate, les statistiques sont collectées immédiatement (vous attendez)

  • Collecte en tâche de fond (on rend la main tout de suite)

On vous rappelle alors vos choix concernant la valeur système QDBFSTCCOL


et les collectes sont soumises.

Pour voir les collectes en attente, cliquez droit sur l'option Base de données

la fenêtre permettant de créer une nouvelle collecte, donne aussi un accès aux informations concernant une collecte existante :

  • Informations générales

  • Plages de valeurs (ou tranches)

  • Valeurs les plus utilisées

Visual Explain sait pleinement tirer profit de ces nouveautés :

Tout d'abord nous pouvons maintenant afficher l'historique du travail (contenant les messages DEBUG)

, ce qui s'affiche en bas

le Menu Option propose

  • Un accès direct à la gestion des statistiques

  • Un outils de conseil, suggérant certaines collectes

  • ou la création d'index

  • Le menu VUE, peut mettre en évidence

    • les index recommandés


      ce qui s'affiche :

    • les étapes les plus coûteuses en nombre de lignes


      Ce qui s'affiche :

Visual Explain et tables matérialisées



 Tables matérialisées (ou MQT, Nouveauté V5R30)


  Cela permet de définir une table en lui associant une requête initiale


   -la table peut etre réactualisée avec le résultat de la requête
     à tout moment.


   - En cours de version V5R30 (DataBase group niveau 4) et avec les PTF
     suivantes : SI17164, SI17609, SI17610, SI17611, SI17637
                et MF34848, I5/OS est capable d'utiliser une MQT
                (Materialized Query Table) dans un but d'optimisation




 syntaxe :


  CREATE SUMMARY TABLE -(nom)---AS (Select SQL complet [ORDER BY admis])->


      !--DATA INITIALLY IMMEDIATE---!
  >---!                             !--REFRESH DEFERRED-->
      !--DATA INITIALLY DEFERRED ---!


                           !---ENABLED QUERY OPTIMIZATION--!
  >--- MAINTAINED BY USER--!                               !---.
                           !---DISABLE QUERY OPTIMIZATION--!



 Attention, seul SQE sait utiliser les tables matérialisées, il ne faut donc
  pas que votre requête utilise des options traitées uniquement par CQE
  (comme LIKE, l'utilisation des BLOB, etc ...)


 il faut une requête - portant sur un seule table ou avec INNER JOIN
                     - sans UNION
                     - sans User Defined Function (UDF) ni UDT
                     - sans sous sélection
                     - sans certaines fonctions du langage comme
                       SOUNDEX, DIFFERENCE, DECRYPT_xx, INSERT, REPEAT,
                       REPLACE, DAYNAME, MONTHNAME.


 L'utilisation des tables matérialisée est une option, ce n'est pas le
  standart, il faut le demander en placant dans QAQQINI :


   MATERIALIZED_QUERY_TABLE_USAGE = *ALL
   MATERIALIZED_QUERY_TABLE_REFRESH_AGE = *ANY
   (ou une durée sous la forme d'un timestamp AAAAMMJJHHMMSS)


il faut d'autre part que la TABLE a été crée avec ENABLED QUERY OPTIMIZATION
 et que le paramètre ALWCPYDTA soit à *OPTIMIZE (par défaut sous ODBC/JDBC)


Le rafraichissement de la table matérialisée est à votre charge (ordre REFRESH)


Quelques restrictions

  •  Si vous demandez dans votre requête, une colonne ne se trouvant pas dans la
      MQT, cette dernière ne peut pas être utilisée. 
  •  une seule table MQT par requête !
  •  C'est a vous de faire le rapport en entre les avantages
    • gain de temps lors de la requête, optimisation transparente.
  •  et les inconvénients
    • temps de rafraichisement des données (la MQT n'est PAS maintenue par le système)
    • affichage de données obsolètes, suivant le dernier REFRESH.
           (sauf à utiliser MATERIALIZED_QUERY_TABLE_REFRESH_AGE)       

Si la table materialisée est utilisée, c'est elle qui sera montrée par
Visual Explain et vous verrez son nom dans la log dans le message CPI4329 (si debug)


· V5R30 & Statistiques d'utilisation des index

iSeries Navigator, montre les index liés à une table (clic droit sur la table)

Cette dernière option vous affiche des informations nouvelles en V5R30 concernant l'utilisation des index

En effet, la date de dernière utilisation de l'objet fichier logique, n'est pas significative dans le cas d'une requête SQL,
où c'est l'optimiseur qui décide de l'utilisation ou non de l'index (utilisation non explicite).

Les PTF suivantes : SI12938, SI15255, SI13432, SI13245 ET SI16620 (en France) apportent 4 nouvelles colonnes

  • Last query use
    Date de dernière utilisation de cet index pour accéder à la table
  • Last query statistic use
    Date de dernière utilisation de cet index pour collecter des statistiques

  • Query use count
    Nombre d'utilisations de cet index pour accéder à la table
  • Query statistic use
    Nombre d'utilisations de cet index pour collecter des statistiques

    les compteurs sont mis à jour aussi bien par SQE (nouvel optimiseur) , que par CQE (l'ancien) ,
    mais ne sont à jour que depuis l'installation des PTF vues plus haut.

    L'API QUSRMBRD a été modifiée pour fournir ces informations.




Copyright © 1995,2005 VOLUBIS