La V5R40 propose de commencer par établir des critères de collecte :

remarquez :

• le critère : Adresse Internet (adresse IP) 
• l'élimination des requêtes liées à l'activité système (iSeries Navigator lui même, entre autre)

Choix des travaux (comme en V5R30)


et récapitulatif final.


une fois le moniteur arrêté (ou importé):

En V5R30, on vous affiche toutes les instructions, en V5R40 vous pouvez au préalable, choisir vos critères d'affichage :

· les informations affichées par Visual Explain

Chaque icône représente une action :

• création
• balayage (scan)
  
• recherche, accès direct (probe)

• permanent
  
• table
• index

Icône	STRDBG	PRTSQLINF	Commentaires
Table Scan	CPI4329	SQL4010	Utilisé pour retourner un grand nombre de lignes
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	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	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)
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	CPI4329	SQL4010 SQL4029	Utilisé  principalement pour la gestion du GROUP BY
HASH Probe	CPI4327	SQL4007 SQL4011	Utilisé principalement pour la jointure
Liste triée (Scan)	CPI4328 CPI4325	SQL4008 SQL4002	Utilisé  pour Order BY et l'option DISTINCT
Liste triée (Probe)	CPI4327	SQL4007 SQL4010	Utilisé pour une jointure avec un autre critère que l'égalité
Liste simple (Scan)	CPI4325 CPI4327	SQL4007 SQL4010	Utilisé pour préparer une utilisation parallèle (SMP)
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)
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	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)
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	CPI4321	SQL4009	création d'un index temporaire pour tri ou groupage  (de plus en plus rare)
Index Probe	CPI4321	SQL4009	création d'un index temporaire probablement pour jointure
	CPI4330	SQL4030	Objet temporaire utilisé lors des opérations de parallélisme (SMP)

EVi table Scan, preload	CPI4328	SQL4008	utiliser pour retrouver les entrées à partir uniquement de la table des symboles d'un index EVI
QUEUE/DEQUEUE	(pas de message)	(pas de message)	QUEUE, Objet temporaire pour mémoriser des données durant une requête récursive

· Collectes de statistiques (depuis la V5R20)

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 être 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
  standard, 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 rafraîchissement de la table matérialisée est à votre charge (ordre REFRESH)


Quelques restrictions

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)

· Statistiques d'utilisation des index (V5R30, plus correctifs)

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.

la version 5.40 nous amène une consultation du cache des plans d'accès SQL

(ce concept date de la V5R20, mais n'était pas consultable, c'est lui aujourd'hui qui suggère les index)


Un plan d'accès est le " plan, de bataille " du moteur SQL face une requête :
"comment faire pour réaliser une requête mieux ?" , pendant cette phase le système examine les index disponibles et en tire des conclusions.

ces "conclusions" sont mise en cache par SQE (depuis la version 5.20)

Attention, le cache des plans d'accès n'est fait que par SQE, vous ne verrez donc pas de suggestion concernant les requêtes encore effectuées par CQE
(ancien moteur utilisé par Query ou OPNQRYF, par exemple)

Avec la V5R40, au passage le système note les index qui lui paraissent manquant dans QSYS2/SYSIXADV.

Le contenu de ce fichier est affiché par cette option "Assistant de gestion des index" d'iSeries Navigator

Le jeu d'instructions actuellement en cache pouvant être sauvegardé sous forme d'image.
(sinon, il y a mise à blanc à l'IPL)

•Par iSeries Navigator, en cliquant, lors de l'affichage sur "création d'une image instantanée"

•Par appel à la procédure cataloguée QSYS2/DUMP_PLAN_CACHE(bibliothèque, nom_de_sauvegarde)

         dans ce dernier cas, il faut importer ce cache pour le voir apparaître dans iSeries navigator :
         pour cela, faites un clic droit sur "images instantanées de mémoire cache de plan SQL"

Cette sauvegarde peut ensuite, être réutilisé pour une comparaison :





il n'est pas possible de comparer un jeu d'instructions venant du cache et un moniteur
(ils n'ont pas le même type, voyez ci-dessous)

Enfin, depuis l'été 2006 avec la V5R40 et la SI24893 du coté client et la SF99540 (Groupe database,) niveau 4, le système créé lui même sous forme d'index temporaires,
les index qu'il juge nécessaires . (fonction MTI soit Maintained Temporary Indexes), ces index disparaissent à l'IPL.

vous pourrez le constater en demandant l'assistant de gestion d'index (sur la machine ou sur un nom de schéma).

la nouveauté se trouve tout à droite de cette fenêtre


voici donc, maintenant la structure de la table SYSIXADV

nom de la colonne (zone)	nom système	Type de donnée	Description
TABLE_NAME	TBNAME	VARCHAR(258)	Table sur laquelle l'index est suggéré
TABLE_SCHEMA	DBNAME	CHAR(10)	Bibliothèque de la table
SYSTEM_TABLE_NAME	SYS_TNAME	CHAR(10)	nom système (court) de la table
PARTITION_NAME	TBMEMBER	CHAR(10)	Partition
KEY_COLUMNS_ADVISED	KEYSADV	VARCHAR(16000)	nom des clé suggérées
LEADING_COLUMN_KEYS	LEADKEYS	VARCHAR(16000)	clé principale, dont le critère de tri (croissant/décroissant) n'importe pas.
INDEX_TYPE	INDEX_TYPE	CHAR(14)	type d'index (normal ou EVI)
LAST_ADVISED	LASTADV	TIMESTAMP	date/heure de suggestion
TIMES_ADVISED	TIMESADV	BIGINT	nombre de fois la suggestion a été faite
ESTIMATED_CREATION_TIME	ESTTIME	INT	nombre de secondes(estimées) pour la création
REASON_ADVISED	REASON	CHAR(2)	Code raison (Sélection / tri ou groupage / les deux)
LOGICAL_PAGE_SIZE	PAGESIZE	INT	taille des pages recommandée
MOST_EXPENSIVE_QUERY	QUERYCOST	INT	temps d'exécution le plus long
AVERAGE_QUERY_ESTIMATE	QUERYEST	INT	temps d'exécution moyen
TABLE_SIZE	TABLE_SIZE	BIGINT	nombre de lignes dans la table (lors de la suggestion)
NLSS_TABLE_NAME	NLSSNAME	CHAR(10)	Séquence de tri à utiliser
NLSS_TABLE_SCHEMA	NLSSDBNAME	CHAR(10)	Bibliothèque de la séquence de tri
MTI_USED	MTIUSED	BIGINT	Nombre de fois ou cet MTI a été utilisé (le système n'utilise plus un MTI, dès qu'un index permanent existe)
MTI_CREATED	MTICREATED	INT	Nombre de fois ou cet MTI a été créé (rappel un index MTI disparaît à l'IPL)
LAST_MTI_USED	LASTMTIUSE	TIMESTAMP	Date/heure de dernière utilisation de cet index MTI.