Requêtes et performances

Ce cours manipule des concepts comme les sous-systèmes et les pools mémoire qui doivent vous être familiers, si ce n'est pas le cas, voyez cette introduction

Préalable

• Il existe aujourd'hui 2 moteurs SQL

1. CQE, le plus ancien
2. SQE arrivé en 5.2, implémenté au niveau microcode
3. les deux cohabitent, il y a donc une couche logicielle, le query dispatcher qui décide, pour une requête, qui va faire le job
   (si SQE sait faire, c'est lui qui va être invoqué, sinon, à défaut CQE)
4. SQE évolue (et donc sait traiter de plus en plus de cas) au fur et à mesure des versions et des PTF.

I/ Les leviers à notre disposition

• Matériel

 les préconisations matérielles d'IBM pour les bases décisionnelles, sont les suivantes :

• au moins un processeur entier pour la partition
• 10 à 20 disques par CPU (à analyser dans le détail suivant la volumétrie)
• 16 à 32 Go de mémoire centrale par CPU
  
• IPL

• Une partie de la base elle-même (en fonction de l'expert cache, voir ci-dessous)
• le cache des plans d'accès SQL
• les index temporaires (MTI)

• Mémoire et sous système

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é :

• créez et ajoutez un JOBQ (ADDJOBQE) , si vous réalisez vos tests en BATCH
  
  
• ajoutez une entrée WorkStation (ADDWSE) si vous travaillez en 5250
  
  
• Pour ODBC/JDBC , suivez la procédure suivante :
  
• Ajoutez un travail à démarrage anticipé à votre sous système, par :
  ADDPJE SBSD(MONSBS) PGM(QSYS/QZDASOINIT) INLJOBS(1) THRESHOLD(1) JOBD(Qgpl/QDFTSVR) CLS(QSYS/QPWFSERVER)
  
  
• modifiez les propriétés du serveur Database via Navigator for i
  
  
  
• allez sur l'onglet sous système
  
  
  
• Le bouton Ajout, permet d'indiquer votre sous système pour un ou plusieurs clients (adresse IP)
                         

• Depuis SF99701 level 34 (7.1) ou SF99702 level 5 (7.2), vous pouvez aussi opérer par utilisateur  
  
  Il vous faut toujours un sous système configuré proprement et actif (comme vu ci-dessus)
  
  Appelez ensuite la procédure stockée SET_SERVER_SBS_ROUTING
                       
  
  • Indiquez le profil
  • le Job serveur
    • QRWTSRVR (DRDA/DDM)
    • QZDASOINIT (ODBC/JDBC)
    • QZRCSRVS (Serveur de commandes à distance, TR3/TR11)
    • QZSCSRVS (central, TR4)
    • QZHQSSRV (Data Queue, TR4)
    • QPWFSERVSO (partage de fichiers, TR4)
    • QNPSERVS (partage d'imprimante, TR4)
    • le sous-système
    • allow rollover ( TR4)
      • YES comme avant, si le sous-système ne peux pas prendre le JOB, il va dans QUSRWRK
      • NO, si le sous-système ne peux pas prendre le JOB, il ne démarre pas
      
      
• Select * from SERVER_SBS_ROUTING permet de voir les utilisateurs re-routés
  
  
  
  
• De fait, suite à une connexion avec le gestionnaire de scripts de System i Navigator
  
  
  
• S'il n'y a pas de sous système actif, par défaut, l'utilisateur ira "normalement" dans QUSRWRK
  
  
  
• Pour enlever cette configuration, appelez la même procédure en passant la valeur nulle
  
  
                                        

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

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)

-> Pour garder les données complètement en mémoire pour pouvez utiliser la commande SETOBJACC ou bien la nouvelle fonctionnalité de la version 7 :

• paramètre KEEPINMEM(*YES) des commandes CHGPF/CHGLF (objets créés par SDD ou SQL), uniquement honoré par SQE.

- Le pool mémoire utilisé est celui indiqué par MEMORY_POOL_PREFERENCE du fichier QAQQINI, qui doit être à *CALC :

• *BASE, le fichier est conservé dans le pool de base
• *JOB, le fichier est conservé dans le pool du job réalisant la requête SQL
• un chiffre, le fichier est conservé dans le pool mémoire dont vous donnez le n°

- Le fichier est monté en mémoire "à la demande" (lors de la première requête) en mode asynchrone et en parallèle
 (contrairement à SETOBJACC qui n'utilisait qu'un seul thread)

- La conservation complète du fichier n'est pas garantie, mais si vous avez de la mémoire elle est fort probable


Pour visualiser la mémoire consommée par SQL, Regardez SYSTMPSTG (Bucket *DATABASE...)

• Segement cache
  • Mémoire utilisée pour faire du cache sur les fichiers
• SQE heap
  • Mémoire utilisée par l'optimiseur
• SQE Mti
  • Mémoire utilisée par les index temporaires
• SQE LOB et SQQQ LOB
  • Mémoire temporaire pour manipuler des BLOB/CLOB

• 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 admise, elle n'a lieu que quand il est impossible de faire autrement

- *OPTIMIZE la copie des données est admise, 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 :

a/ Commandes système

WRKSYSSTS (gestion de la mémoire)

ATTENTION, tous ces écrans vous offrent des moyennes, il faut visualiser des données qui ont au moins 2 minutes .

la documentation V5R40 indique comme base de calcul rapide, par pool (ligne) :

c*p, où
  c est le pourcentage de CPU utilisé
  p est le nombre de processeurs de la partition

par exemple

1 processeur à 36 % = 36 de taux de pagination maxi
1,8 processeur (LPAR) utilisé à 80% = 144

C'est aussi cet écran (par F11) qui permet de fixer l'expert cache.

WRKDSKSTS (gestion des disques)

cet écran montre le taux d'occupation des disques (l'idéal est un taux de 75 % au maximum)
ainsi que l'activité des bras (% util).Une activité au delà de 50% indique des disques très occupés.

si la répartition n'est pas linéaire (achat de nouveaux disques, par exemple), passez la commande STRASPBAL TYPE(*CAPACITY)

b/ Base de données

D'abord un petit, "truc", mettez le paramètre QRYTIMLMT à 0 par la commande CHGQRYA :

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

rappel, utilisez les paramètres suivants

-  RUNTHLD : seuil en secondes, de temps d'exécution
-  STGTHLD : seuil en Mo de mémoire utilisée
-  FTRFILE : filtrage du moniteur sur un nom de fichier
-  FTRUSER : filtrage du moniteur sur un profil utilisateur
-  FTRINTNETA : filtrage du moniteur sur une adresse IP cliente
-  FTRQRYGOVR : filtrage du moniteur sur l'annulation de la demande pour dépassement de critère donné par CHGQRYA

• Nouveautés STRDBMON (SF99601 level 21, SF99701 level 11)
  
  
  • Profil de groupe admis sur le paramètre FTRUSER
    
    
    
  • FTRSQLCODE permet de filtrer sur le code SQL avec les valeurs suivantes :
    • *NONE - pas de filtre sur SQLCODE
    • *NONZERO - tout code SQL autre que 0
    • *ERROR - tout code SQL d'erreur (< à 0)
    • *WARN - tout code SQL de Warning (> à 0)
    • <un n° de SQLCODE>
      
      
      
  • les variables globales PROGRAMID, APPLNAME, USERID, WRKSTNNAME et ACCTNG sont maintenant renseignées par STRSQL et RUNSQLSTM
    
    
  • Ces mêmes variables globales peuvent être des filtres sur la commande STRDBMON en V7
    
    • FTRCLTPGM pour PROGRAMID
    • FTRCLTAPP pour APPLNAME
    • FTRCLTUSR pour USERID
    • FTRCLTWS pour WRKSTNNAME
    • FTRCLTACG pour ACCTNG
      
      
      
    • dans le paramètre COMMENT en V6, par exemple COMMENT('FTRCLTPGM(STRSQL)')
      
      
  • ces informations seront placées dans le résultat du STRDBMON quand QQRID = 1000
    • QVC3006 pour PROGRAMID
    • QVC3001 pour APPLNAME
    • QVC3002 pour USERID
    • QVC3003 pour WRKSTNNAME
    • QVC3005 pour ACCTNG
      
      
  • EN V7, la PTF SI46321 permet un support de QUERY/400
    
    Les registres clients sont maintenant renseignés :
    
    
    • CLIENT PROGRAMID = 'RUNQRY'
    • CLIENT APPLNAME = 'RUN QUERY'          
      
      Comme STRSQL et RUNSQLSTM
    • CLIENT USERID = le profil en cours                       
    • CLIENT WRKSTNNAME = le nom rdb en cours                
    • CLIENT ACCTNG = le code accouting en cours 
      
      
  • le PTF SF99701 (level 23) permet la même chose pour STRQMQRY
    • CLIENT PROGRAMID = 'STRQMQRY'
    • CLIENT APPLNAME = 'START QUERY MANAGEMENT QUERY'          
      
    • CLIENT USERID = le profil en cours                       
    • CLIENT WRKSTNNAME = le nom rdb en cours                
    • CLIENT ACCTNG = le code accouting en cours 

  et le paramètre FTRCLTPGM de STRDBMON admet le filtre RUNQRY et STRQMQRY

 

• SI46394 (7.1) place, en cas de "embeded SQL", dans :

  • QQC103 le nom du programme ou programme de service contenant l'ordre SQL
  • QQC104 le nom de la bibliothèque du programme
    
    
• SF99701 level 23, place, en cas d'erreur sur un FETCH :
  • QQI8     SQLCODE
  • QQC81 SQLSTATE

 

-> pour analyser ensuite le résultat, IBM propose la création de vues voir https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzajq/lofdds.htm


ou mieux, via ACS (voir ci-dessous) : Utilisez "Performance Center"

Les outils graphiques

1/ le gestionnaire de scripts SQL

Vous pouvez :

• sauvegarder et relire un script SQL
  

• lancer tout ou partie du script
  

• demander l'inclusion des messages Debug et voir l'historique du travail sur le serveur
  

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

une option ALLOW SAVE RESULT, permet la sauvegarde des enregistrements extraits:

ensuite, avec un clic droit sur les lignes affichées :

Les formats admis, sont :

• txt
• cvs
• Lotus 123
• tableur Excel

Plusieurs paramètrages de la connexion (JDBC) peuvent être mémorisés

Vous choisissez ensuite comme ceci :

Vous avez accès aux travaux et à la dernière requête SQL pour chacun d'eux (Affichage/SQL details for a Job)

Enfin, la(les) requêtes(s) peuvent être sauvegardée(s) (uniquement en local)

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

Depuis performance center, choisissez l'option Nouveau...

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

remarquez :

• la notion d'utilisateur en cours
• 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


   et récapitulatif final.

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

• Analyse, propose une vue globale :

Avec un classement par catégorie

Vous aurez (parfois) le choix entre liste détaillée ou récapitulative





Le menu option propose le choix entre
• Affichage des résultats
• et modification (pour personnalisation) de la requête
  
  
  
  

 

IBM fournit des requêtes d'exemple afin d'obtenir des statistiques globales à l'adresse https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzajq/rzajqmon4.htm


• des récapitulatifs sont aussi disponibles :

• L'option "Analysis Report" proposant un accès direct à toutes les requêtes (modification incluse)
  
  
  

• "Affichage des instructions", sur un moniteur, propose de nombreux critères de sélection avant l'affichage détaillé.

  
  
  Résultat
  
  
  Avec accès à Visual Explain et à l'instruction dans le gestionnaire de scripts
  
  
  

• Vous pourrez comparer deux moniteurs (en ayant réalisé certaines actions entre temps)
  
  
  Voire, descendre au niveau instructions (celles qui sont communes)
  
  
  
  

Vous pouvez aussi utiliser IBM Navigator for I (Performances/Etude des données) :

• Mémoire cache de plan SQL , permet de voir une image instantanée du cache SQL
• Moniteur de performances SQL permet aussi de voir le résultat d'un STRDBMON

Mais surtout, nous avons maintenant des statistiques d'Entrée/Sortie globales (sous PDI)

Choisissez la bibliothèque et la collecte (ou laissez "la plus récente")

Entrée/sortie base de donnée physiques, vue détaillée

Par travail :

Par sous-système

Par type de serveur

3/ Cache des Plans d'accès

• 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
  
  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, avant la 7.3)
  
  L'accès au cache des plans SQL, propose un affichage proche des moniteurs, mais en temps réel, sans rien démarrer..
  
  
  Propriétés:
  

 

• Paramètres concernant le Cache des plans d'accès
  
  
  • 7.1 + SF99701 Level 9 (level 20 en V6) la taille du cache est conservée lors des IPL
    (avant quand vous modifiez la taille, au prochaine IPL elle était réinitialisée à 512 Mo)
    
    
  • 7.1 + SF99701 level 14 , une taille à 0 indique un cache auto-extensible (pas disponible en V6)
    
    
    
    
• Affichage des instructions (du cache) :
  
  
  
  
  
  
  ici, Affichage d'un TOP 25
  
  
  
  Vous pouvez voir plusieurs affichages en parallèle
  
  
  Voici les options disponibles
  
  

  Longuest Run
  
  
  Job History

  
  
  User History

  
  
  Work with SQL Statement, place la requête dans le gestionnaire de scripts, vu plus haut.

  
  

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

•Depuis Performance Center







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

 

• Quand le cache est plein il est automatiquement épuré, il est possible de placer un moniteur sur cet événement afin de le sauvegarder en fichier avant (Event Monitor)
  
  
  Tout en gardant la possibilité de choisir les instructions sauvegardées (comme un moniteur)
  



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



il n'est pas possible de comparer un jeu d'instructions venant du cache et un moniteur

4/ Analyse des statistiques et des Index

Pour définir ou voir les statistiques, Utilisez la nouvelle (1.1.7) option Schémas

• Statistiques d'utilisation des index
  

  ACS, montre les index liés à une table (clic droit sur la table)
  
  
  Ce dernier vous affiche des informations, nouvelles en V5R40 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).
  
  Vous avez ici, 4 colonnes remarquables
  
  
  
  
  

  • 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.
    
    
  • Enfin, cette liste des index affiche les informations bien pratiques

    -> clé basée sur une expression

    -> type d'index
    
    

  • D'ailleurs, le filtre de la liste des index propose ces informations en tant que critères
    
    
    
  • LA PTF SF99701, level 13, apporte une nouvelle procédure cataloguée
    
    
    
    
    QSYS2.Reset_Table_Index_Statistics
    
    
    Paramètres
    
    1) Schema - varchar(128)
    Nom de la bibliothèque
    
    
    2) Table - varchar(128)
    Nom de la table ou du fichier physique
    
    Ces deux paramètres sont sensibles à la casse et acceptent les caractères '_' et '%' (comme un LIKE)
    
    La procédure remet à zéro Query use count et Query statistic use mais n'impacte pas Last query use, ni Last query statistic use.
    
    
• Nous avons ensuite accès aux recommandations faites par le cache des plans d'accès SQL 
  




• Avec la V5R40, lors de la création des plans d'accès, 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
  
  
  
  Depuis la V5R4, 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, toujours en demandant l'assistant de gestion d'index
  (sur la machine , sur un nom de schéma, sur un nom de table).
  
  
  

  la nouveauté se trouve tout à droite de cette fenêtre
  
  
  - Affichage de l'instruction SQL(Show SQL) montre l'ordre CREATE INDEX
  

   

  - ainsi que l'accès direct aux instructions qui ont provoqué cette suggestion (dans le cache, voir ci-dessous)

  

  - l'assistant affiche aussi le nombre de fois ou un index a été suggéré et, s'il a été créé automatiquement (MTI), le nombre de fois où il a été utilisé
  
  Ce compteur peut-être réinitialisé pour la table, par le menu contextuel suivant :
  

  voici donc, maintenant la structure de la table SYSIXADV (V7)

  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(128)	Schéma 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és 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.
  AVERAGE_QUERY_ESTIMATE _MICRO	QRYMICRO	BIGINT	temps moyen d'exécution de la requête à l'origine de cette suggestion
  EVI_DISTINCT_VALUES	EVIVALS	INTEGER	Nombre de valeurs distinctes pour création index EVI
  INCLUDE_COLUMNS	INCLCOL	CLOB(10000)	INCLUDE pour création index EVI
  FIRST_ADVISED	FIRSTADV	TIMESTAMP	Première suggestion
  SYSTEM_TABLE_SCHEMA	SYS_DNAME	CHAR(10)	Nom système de la bibliothèque
  MTI_USED_FOR_STATS +	MTISTATS	BIGINT	Nbr de fois, Index MTI utilisé pour des statistiques
  LAST_MTI_USED_FOR_STATS *	LASTMTISTA	TIMESTAMP	dernière utilisation Index MTI pour des statistiques
  DEPENDENT_ADVICE_COUNT **	DEPCNT	CHAR(10)	Nombre de dépendances d'index (traitement de OR)

  * SF99701, level 14 : les index MTI peuvent être utilisés pour collecter des performances
  
  ** SF99701, level 23 : les index proposés sont liés et utilisables par la même requête contenant des OR

  Exemple : select * from vins where vin_cepage1 = 'Cabernet' or vin_cepage2 = 'Cabernet' or vin_cepage3 = 'Cabernet' Contenu de SYSIXADV INDEX_TYPE DEPENDENT_ADVICE_COUNT KEY_COLUMNS_ADVISED ----------- --------------------- ------------------- RADIX 1 VIN_CEPAGE1 RADIX 1 VIN_CEPAGE3 RADIX 1 VIN_CEPAGE2

   

  LA PTF SF99701, level 12 (SF99601 level 21 en V6) propose deux procédures cataloguées

• SYSTOOLS.ACT_ON_INDEX_ADVICE, création des index recommandés en fonction de critères :
  • Bibliothèque
  • Table ou fichier physique
  • nombre de fois ou l'index a été recommandé
  • nombre de fois ou un index temporaire a été créé (MTI)
  • temps estimé de création de l'index
    
    CALL SYSTOOLS.ACT_ON_INDEX_ADVICE(‘BDVIN1’,NULL,NULL,500,NULL)
    
    Chercher les index recommandés 500 fois ou plus dans BDVIN1 et les créer !
    
    
• SYSTOOLS.REMOVE_INDEXES , suppression des index non utilisés
  • Bibliothèque
  • nombre de fois (au plus) ou l'index a été utilisé
  • Période d'analyse (syntaxe SQL pour un calcul)
    
    CALL SYSTOOLS.REMOVE_INDEXES( ‘BDVIN1’, 4, ' 7 days ')
    
    Détruire les index utilisés moins de 5 fois lors des 7 derniers jour.
    
    

Vous pouvez retrouver les sources de ces procédures via System i Navigator ou ACS (elles sont livrées "tel que") et les personnaliser.

4/ Visual Explain

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

• En utilisant l'outil de requête d'ACS

Visual Explain peut être lancé et réactualisé, pendant l'exécution, les informations ayant bougé sont surlignées.

• En lançant vos requêtes sur une autre interface, puis en demandant au gestionnaire de scripts la dernière instruction d'un JOB.

• Work with SQL statement, place cette requête SQL dans le gestionnaire de scripts
• Visual Explain, lance directement ... Visual Explain

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

A partir de cette requête


•Scannage de table et/ou utilisation d'un index

• phase de jointure

 

ici en utilisant des index pour la jointure
 et avec agrégation (pour count(*) )




Le résultat étant stocké dans une liste temporaire

• pour terminer, lecture de la liste temporaire et production du résultat

La fenêtre droite vous donne le détail pour chaque étape

ici, le résultat final indiquant le moteur utilisé


ici la phase jointure


Les messages de l'optimiseur (précisant la jointure) 
étant affichés dans cet onglet

depuis la 6.1, SQE possède un module d'auto-apprentissage, qui agit aujourd'hui dans 2 cas :

• Cold I-O to Warm I-O
  quand un plan d'accès prévoit un accès par index et que le moteur se rend compte que la table est actuellement en mémoire
• First I-O to ALL I-O
  quand un plan d'accès est prévu pour une optimisation First I-O et que l'application change (par exemple un chargement complet des lignes)
  
  Cette modification est notée dans Visual Explain
  

• Vous pouvez aussi lancer Visual Explain sur le résultat d'un moniteur de performance base de données. 

  
  
  
  

Rappel :

• Il existe différents types d'objets pouvant améliorer les performances
  • Index
    • B-Tree (B-Arbre ou arbres binaires)
      
      C'est la technique la plus ancienne (celle des LF), ils permettent de retrouver très rapidement une aiguille dans une meule de foin
      
    • Index Bitmap
      
      On indique par un 1 si la ligne correspondante (ici la 1 ou la 2, par ex.) a cette clé là (oui 1001 et 1002 sont des femmes)
      
      Assez difficile à maintenir, il ne sont utilisés par DB2 for I que de manière temporaire
      
      
    • EVI
      les index EVI sont constitués de deux éléments
      • Une table des symboles contenant :
        • un code attribué à chaque valeur de clé
        • des informations statistiques (nbr, position du premier record, position du dernier record ayant cette clé)
  • Un vecteur
    • attribuant à chaque rang, un des codes de la table des symboles vue plus haut.
      

  

  • les index EVI se cumulent parfaitement, en technique bitmap :
     (quatre critères dans le WHERE, un index EVI par critère : SQL en fait un seul à partir des 4)
  • ils ne sont utilisables que par SQL (pas en RPG/COBOL)
  • hash Table (table de hachage)
    
    
    pas vraiment un index, il s'agit de stocker des données de manière temporaire
    • on stocke un couple clé/valeur
    • la clé est transformée par une fonction de hachage qui fournit une position dans la table
      • la même valeur produit toujours la même position
    • très utile pour :
      • faire des jointures/JOIN (entre deux tables de hachage les données sont à la même position)
      • faire des regroupement/GROUP BY (toutes les valeurs identiques sont adjacentes)
  • Materialize Query table
    • table contenant le résultat d'une requête
      
      
      Ce rafraîchit "à la demande" (par un ordre : Refresh Table MQT01)
      
    • Le fichier QAQQINI indique
      • MATERIALIZED_QUERY_TABLE_USAGE : *ALL / *USER : utiliser les MQT (*DEFAULT = non)
      • MATERIALIZED_QUERY_TABLE_REFRESH_AGE : ancienneté de la donnée admise
        • *ANY ou une durée sous forme AAAAMMJJHHMMSS
          
    • , doit utiliser la MQT
      
      
    
    

 

· 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	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 à *YES ou *OPTIMIZE)
  
• 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	Utilisation d'un index temporaire pour tri ou groupage  (MTI)
Index Probe	CPI4321	SQL4009	Utilisation d'un index temporaire, probablement pour jointure
	CPI4330	SQL4030	Objet temporaire utilisé lors des opérations de parallélisme (SMP)

• Icones, non liées aux stratégies d'utilisation des index :
  • représente l'utilisation d'une fonction Table (UDTF), l'optimisation doit se faire dans la fonction
    
    
  • indique un calcul, un traitement CPU
    
    
  • utilisation de la mémoire cache

 

 

 

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

Nouveaux en V5R40

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

Nouveaux en V6R10

temporary distinct Sorted list Scan	(pas de message)	(pas de message)	Liste triée de valeurs distinctes, utilisée pour GROUP BY ROLLUP
temporary value List Scan	CPI4329 Arrival sequence was used for file *VALUES	SQL4010	Liste de valeurs temporaires, utilisée avec VALUES( )

Nouveaux en V7 (les index EVI peuvent maintenant être utilisés en lecture pour GROUP BY et DISTINCT)

EVI Table SCAN, preload	CPI4328	SQL4008	Lecture d'un Index EVI avec INCLUDE pour fonction d'agrégation (GROUP BY)
EVI Table Probe, Preload	CPI4328	SQL4008	Lecture d'un Index EVI avec INCLUDE pour fonction d'agrégation (GROUP BY et WHERE)
UNNEST ARRAY	CPI4329	SQL4010	Utilisation de la fonction UNNEST d'un tableau SQL (ARRAY) dans une procédure
EVI Only Access	CPI4338		Utilisation de deux index EVI, l'un pour la sélection, l'autre pour les colonnes à afficher (la table n'est pas lue, voir ce cours)

---

Visual Explain sait pleinement tirer profit des différentes évolutions, comme la collecte de statistiques.

 

le Menu Option propose

• Un certain nombre d'actions, dont un outil 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 :
    

  • 
    

    SI Visual Explain utilise une table matérialisée (MQT) vous verrez son nom (et CPI4329 si le debug est actif)
    
    
    
    

  • les étapes les plus coûteuses
    

• Vous retrouverez ces informations depuis un moniteur


de fait quand on lance Visual Explain

 

Quelques recommandations   Respectez bien les trois formes normales (voir Wikipedia s'il le faut) Ayez un dictionnaire de données afin qu'une information soit toujours codée de manière identique       ÉVITEZ ABSOLUMENT :          - le n° de client packé dans un fichier, étendu dans l'autre          - date dans un fichier et jour/mois/année dans l'autre n'indexez que l'essentiel, un index c'est aussi des cycles CPU consommées lors des écritures,mises à jour. (faites la chasse aux index non utilisés en filtrant, comme vu plus haut)   SÉLECTION    il faut bien voir que le but est de gagner du temps. Or ce qui prend le   de temps, ce sont le E/S (I/O) disque.       SQL va donc, TOUJOURS, chercher à optimiser la sélection (Pour Order By, c'est facile de trier en mémoire).  • En réutilisant des index, sauf quand le nombre d'enregistrement correspondant à la sélection est élévé (plus de qq %) • sinon, en utilisant des techniques comme la table de haschage, SI ALWCPYDTA(*OPTIMIZE) L'idéal étant de trouver un index portant sur la sélection ET le critère  de jointure ,  par exemple : SELECT * FROM CLIENTS JOIN CDES on CLIENTS.NOCLI = CDES.NOCLI             WHERE DEPCLI = 44 Ici, l'idéal serait index sur NOCLI ET DEPCLI L'index parfait est celui qui contient toutes les données, évitant d'aller chercher le reste dans la table. Si vous avez le choix , mettez la clef la plus restrictive en premier Mutualisez les index, un index avec DEPCLI puis NOCLI sera utilisé pour une recherche sur le département seul. (ne faites donc pas un index avec DEPCLI tout seul, il ne sert à rien !) Ne confondez pas nombre de logiques et nombre d'index : le système ne créé jamais 2 fois le même index (partage implicite de chemin d'accès) si vous créez un logique L1 avec comme clé K1 puis un logique L2 avec comme clé K1 -> L2 est une enveloppe vide pointant sur L1 si vous détruisez L1, l'index est déplacé dans L2 si vous créez un index (CREATE INDEX) puis un logique, ils partagent le C.A si vous créez un logique puis un index, ça dépent le logique ayant été crée avec PAGESIZE(64) : oui le logique ayant été créé avec une taille plus petite (le défaut) : non si vous définissez une PRIMARY KEY puis un logique, ils partagent le C.A Enfin, si vous faites des LF avec sélection d'enregistrement, ajoutez DYNSLT et un index existant sera certainement réutilisé (partage implicite de chemin d'accès, montré par DSPFD) Vérifiez tout cela avec Visual Explain ! voyez enfin notre cours sur la réutilisation des logiques en version 7.2

 

---

Copyright © 2019 VOLUBIS