les préconisations matérielles d'IBM pour
les bases décisionnelles, sont les suivantes :
Faut-il utiliser la journalisation
SUR DES BASES
de PRODUCTION, bien sûr !
SUR DES BASES DECISIONELLES ?
Par contre, toujours sur ces bases décisionnelles, pensez à :
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é :
» 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 :
- Le pool mémoire utilisé est celui indiqué par MEMORY_POOL_PREFERENCE du fichier QAQQINI, qui doit être à *CALC :
- 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...)
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).
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)
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 partitionpar 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)
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
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
et le paramètre FTRCLTPGM de STRDBMON admet le filtre RUNQRY et STRQMQRY
SI46394 place, en cas de "embeded SQL", dans :
-> 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 iSeries Navigator (voir ci-dessous) :
choisissez "base de données"/"moniteur de performances SQL"
Les
outils graphiques
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
l'AS/400
modifier vos attributs de requête. (fichier QAQQINI)
Il subit de nombreux changements en version 6
1/ 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 :
Les paramètres de connexion (JDBC) peuvent être modifiés temporairement ou définitivement
et proposent maintenant l'affichage des COLHDG
plutôt que les noms de zone en entête de colonne
La(les) requêtes(s) peuvent être
sauvegardée(s) sur le serveur (fichier physique ou IFS)
Ce qui accompagne très bien le nouveau paramètre SRCSTMF de la commande RUNSQLSTM
cliquez sur moniteur de Base de données / nouveau ...
remarquez :
Choix des travaux (comme en V5R30)
et récapitulatif final.
Quand la trace est terminée (l'arrêt est à votre charge), choisissez une vue (les données à afficher)
le menu Fichier/préférences, propose
l'affichage des boutons de modification de la requête
qui vous affichera (pour modification) la requête
correspondant à la ligne active
Vous pouvez modifier cet ordre ....... avec le gestionnaire de scripts :
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
, ci-dessous
par
utilisateur :
EN version 7 vous pouvez aussi utiliser IBM Navigator Director
Choisissez la bibliothèque et la collecte (ou laissez "la plus récente")
Mais surtout, nous avons maintenant (SF99701, level 18) des statistiques d'Entrée/Sortie globales (si la notion de collecte est démarrée)
Entrée/sortie base de donnée physiques, vue détaillée
Par travail :
Par sous-système
Par type de serveur
3/ Analyse des statistiques et des Index
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)
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
Statistiques
d'utilisation des index (V5R30 plus correctifs, ou suivantes)
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
-> clé basée sur une expression 
-> clause WHERE de sélection de lignes 
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
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, toujours 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
- Affichage de l'instruction SQL est nouveau en V6
- 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 ou 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
Vous pouvez retrouver le sources de ces procédures via System i Navigator (elles sont livrées "tel que") et les personnaliser.
Les possibilités d'affichage sur une instruction ont été étendues
en V6
- l'affichage des instructions les plus longues est limité aux 500 premières
- vous pouvez demander la liste des travaux utilisant actuellement cette instruction
- et la liste des utilisateurs ayant utilisé cette instruction (historique de l'utilisateur)
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" (V5) ou "Sauvegarde dans Nouveau" (V6)
•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 opération est inutile en version 6 et peut être automatisée
Quand le cache est plein il est automatiquement épuré, en V6 il est possible de placer un moniteur sur cet événement afin de le sauvegarder en fichier avant
Tout en gardant la possibilité de choisir les instructions sauvegardées (comme un moniteur)
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)
4/
Visual Explain
Vous pouvez maintenant obtenir une Explication graphique du détail d'une requête SQL avec Visual Explain :
Cet affichage a été revu en version 6 (plus de détails) :
Le bouton EDITION SQL, ou en V6 clic droit puis Gestion de l'instruction 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 |  |
en V6, Visual Explain 
peut être
lancé et réactualisé,
pendant l'exécution, les informations ayant
bougé sont surlignées.
Visual
Explain vous affiche alors le détail des
différentes phases de la requête :
•Scannage de table et/ou utilisation d'un index
• phase de jointure
en V6R10, le moteur possède un module d'auto-apprentissage, qui agit aujourd'hui dans 2 cas :
Vous pouvez aussi lancer Visual Explain sur le résultat ( détaillé en V5) d'un moniteur de performance base de données.
de nouvelles zones ont étés ajoutées
pour Visual
Explain
La V5R40 propose de commencer par
établir des critères de collecte :
remarquez :
Choix des travaux
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 :
| 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) |
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) |
vous
donnera des informations globales (temps,
nombre de lignes résultat , ...) 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 |
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 |
SQL4008 | Lecture d'un Index EVI avec INCLUDE pour fonction d'aggregation (GROUP BY) | |
 EVI Table Probe, Preload |
SQL4008 | Lecture d'un Index EVI avec INCLUDE pour fonction d'aggregation (GROUP BY et WHERE) | |
 UNNEST ARRAY |
SQL4010 | Utilisation de la fonction UNNEST d'un tableau SQL (ARRAY) dans une procédure | |
 EVI Only Access |
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 de des nouveautés, comme la collecte de statistiques
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 :
• EN V7, la PTF SF99701 level 18 propose deux nouvelles colonnes lors de l'affichage des instructions d'un moniteur de performance
Il faut cliquez sur "Colonnes"
et choisir ces deux colonnes
qui affichent
Si on lance Visual Explain, auquel on demande les suggestions proposées
