> Tech > Se préparer pour le sql query engine V5R2

Se préparer pour le sql query engine V5R2

Tech - Par iTPro.fr - Publié le 24 juin 2010
email

Par Mike Cain & Kent Milligan

Des améliorations de performances franches pour des instructions SQL longues et complexesVous avez probablement entendu parler d'IBM à  deux propos : grande amélioration des performances de SQL ou un nouveau moteur de base de données en V5R2. Malheureusement, il y a beaucoup de malentendus sur ce sujet du fait qu'une partie seulement de ces changements liés aux performances SQL a été livrée avec la V5R2 en disponibilité générale. Cet article explique ce qu'il faut savoir sur le SQE (SQL Query Engine) V5R2 et comment obtenir la dernière version de ces améliorations DB2 UDB.

IBM et DB2 UDB continuent à  mener la danse en SQL et en optimisation des requêtes. Afin d'incorporer plus rapidement ces technologies de pointe dans DB2 UDB for iSeries, IBM a décidé de refondre l'optimiseur de requêtes et certaines parties du moteur runtime en V5R2. Le moteur revu et corrigé s'appelle SQL Query Engine et il a été conçu principalement pour des interfaces SQL comme embedded SQL, ODBC et JDBC. Le SQE bénéficie aussi des toutes dernières techniques de programmation orientées objet (OO). Grâce à  cette base de code OO, il sera beaucoup plus facile de s'adapter à  la future optimisation SQL. En fait, l'optimiseur SQE V5R2 contient de nombreuses techniques d'optimisation de pointe qui ont conduit à  des améliorations de performances sensibles pour des instructions SQL longues et complexes. Les instructions SQL courtes qui dominent dans les requêtes OLTP (online transaction processing) bénéficieront moins des améliorations SQE. Comme l'indique le nom SQE, seules les requêtes en lecture seule exécutées à  partir d'une interface de type SQL bénéficieront de SQE. Et donc, Query, QQQQRY API et OPNQRYF ne bénéficieront pas du nouveau moteur en V5R2. Ces interfaces utilisent encore ce que nous appelons le CQE (Classic Query Engine). Nous verrons plus loin quels types d'instructions SQL bénéficient de SQE.
La figure 1 montre l'amélioration de performances obtenue en incorporant de nouvelles techniques d'optimisation et de performances dans le moteur. D'après des tests effectués par IBM, ces résultats de performances représentent fidèlement l'impact potentiel de SQE sur une charge de travail SQL. Certaines requêtes seront nettement plus rapides, d'autres resteront inchangées et une poignée de requêtes SQL pourra même subir une dégradation. Comme dans toute mesure de performances, les résultats individuels varieront selon les circonstances.
La refonte du moteur de base de données visait surtout à  améliorer la performance. Mais il y avait aussi un autre objectif : faciliter l'ajout de nouvelles fonctionnalités à  DB2 UDB for iSeries. On en trouve un bon exemple dans le fait que la V5R2 est mieux à  même de traiter rapidement les requêtes « absurdes » (avec une condition de recherche telle que WHERE 1=2, par exemple).
Croyez-le ou non, certaines solutions logicielles exécutent des requêtes qui incluent des critères de sélection absurdes du genre WHERE 1=2. On fait cela pour s'assurer que la requête renvoie un jeu de résultats vide. Avant la V5R2, CQE aurait renvoyé le jeu de résultats vide sur ces requêtes, mais sans grande efficacité. Si une requête absurde était appliquée à  une table de 1000 lignes, CQE aurait visité les 1000 lignes et vérifié si 1=2 sur chacune d'elles. L'inefficacité du CQE était bien connue, mais la base du code procédural était telle qu'il était très difficile d'améliorer le moteur afin que les requêtes contenant des critères de sélection absurdes puissent être traitées sans balayer les données. En revanche, la conception OO de SQE permet très facilement d'améliorer le moteur afin que, pour les requêtes contenant des critères de sélection absurdes, DB2 renvoie simplement un jeu de résultats vide immédiatement.
Comme nous l'avons laissé entendre plus haut, IBM délivre les améliorations SQE par étapes sur la V5R2. On sait bien que le moteur DB2 UDB intégré joue un rôle central sur les serveurs iSeries et AS/400e ; c'est pourquoi IBM avance par étapes pour atténuer l'impact sur vos applications et

Examinons maintenant les composants
de SQE, à  savoir

• le Query Dispatcher
• le Query Optimizer
• Data Access Primitives
• le Plan Cache
• le Statistics Manager
Query Dispatcher.

Le Query
Dispatcher est le composant qui rend
SQE transparent aux yeux du programmeur
d’applications et de l’utilisateur
final. Le dispatcher examine la requête
et, d’après les critères énumérés en figure
2, détermine si elle doit être dirigée
vers SQE ou CQE. L’utilisateur et le
programmeur n’ont pas à  se préoccuper
de la présence de deux moteurs de
base de données différents sous le capot.
En réalité, le seul indice de la présence
de deux moteurs de base de
données est le champ QQC16 dans
l’enregistrement database monitor
3014. Cet indicateur est activé (set)
quand SQE est utilisé pour exécuter
une requête SQL. Au fur et à  mesure
qu’on améliorera SQE pour traiter davantage
de types de requêtes SQL, le
dispatcher sera modifié pour diriger
ces requêtes vers SQE.
Avec certains types de fichiers logiques
avec clés, une requête est initialement
dirigée vers SQE par le dispatcher,
mais restituée ensuite à  CQE. La
raison en est que SQE n’est pas encore capable d’utiliser tous les types de fichiers
logiques avec clés. Voici les attributs
de fichiers logiques avec clés qui
conduiront SQE à  transmettre une requête
à  CQE :

• Spécification Select/Omit
• Le fichier logique s’étend sur plusieurs
membres
• Clés dérivées (concaténation, renommage
de champs, omission de
champs, par exemple)
• Séquence de collation alternée
• Séquence de tri spécifiée pour fichier
logique avec clés ou index SQL

A noter que la retransmission
d’une requête à  CQE à  partir de SQE
peut alourdir de 10 à  15 % la requête
SQL.
Query Optimizer. Après refonte,
l’optimiseur de requêtes SQE fait
moins de travail que l’optimiseur CQE.
Le rôle principal des deux optimiseurs
est de déterminer les stratégies et algorithmes
idoines pour exécuter le
plus rapidement possible une requête
SQL donnée. L’optimiseur CQE a une
responsabilité supplémentaire : tirer
des statistiques des index et des tables,
ainsi que déterminer le profil du matériel
concerné (vitesse de CPU, par
exemple). Ces tâches supplémentaires
concernant les statistiques et le matériel,
venant s’ajouter à  la mission principale
de l’optimiseur qui est de calculer
le plan d’exécution le plus rapide,
sont l’un des exemples expliquant
pourquoi il est devenu difficile d’améliorer
la base du code CQE. IBM a beaucoup
simplifié le modèle SQE en
confiant les tâches de profilage statistique
et matériel dans leurs propres
composants (nous reviendrons sur ce
point plus loin) et en laissant l’optimiseur
de requêtes SQE optimiser uniquement.
Quand une information statistique
ou matérielle est nécessaire,
l’optimiseur SQE la sollicite auprès des
autres composants.
Les nouvelles stratégies et nouveaux
algorithmes employés par l’optimiseur
de requêtes SQE sont difficiles à  appréhender et à  apprécier, mais il y
a une poignée de changements fondamentaux
dans l’optimisation de requêtes
iSeries que presque tout le
monde comprendra. La plupart des
utilisateurs iSeries connaissent le phénomène
d’une requête attendant de
s’exécuter pendant que DB2 UDB crée
un index temporaire. Pourtant, l’optimiseur
SQE n’utilise pas la création
d’index temporaires dans les implémentations
qu’il construit. Tout simplement
parce que l’optimiseur SQE a
à  sa disposition une panoplie bien plus
grande de stratégies et d’algorithmes
alternatifs. Un autre changement fondamental
est le fait que les balayages
de tables peuvent être choisis plus souvent
par l’optimiseur SQE parce que
leur mise en oeuvre dans le moteur
runtime est beaucoup plus efficace avec les changements apportés en
V5R2.
Data Access Primitives. Vous
serez peut-être étonnés d’apprendre
qu’un optimiseur de requêtes n’exécute
pas la requête. Son rôle se limite à 
trouver le plan de mise en oeuvre le
plus rapide (un peu comme une recette
de cuisine) et à  le stocker dans le
plan d’accès. Le moteur runtime DB2
se charge de la cuisson (c’est-à -dire,
exécuter la requête). Le moteur runtime
SQE avec V5R2 est connu sous le
nom de Data Access Primitives.
Comme avec l’optimiseur, IBM a inclus
les meilleurs algorithmes d’accès aux
données du marché dans la refonte du
moteur runtime SQE.
Par rapport aux primitives SQE, les
balayages de tables sont plus efficaces
(c’est-à -dire, séquence d’arrivée). Les performances supérieures des balayages
de tables SQE sont le résultat
d’algorithmes qui (1) sont plus en harmonie
avec le stockage à  niveau
unique de l’iSeries et (2) utilisent plus
efficacement les ressources mémoire
et disque. Le mot-clé DISTINCT est
souvent présent dans des requêtes
SQL pour compter ou résumer des éléments
distincts. De nouvelles primitives
ont été ajoutées à  SQE dans le
seul but d’améliorer ce traitement SQL
DISTINCT. Dans certains tests du lab,
certaines requêtes SQL avec traitement
distinct se sont exécutées jusqu’à  huit
fois plus vite.
Une autre primitive SQE qui a amélioré
considérablement les requêtes
SQL est le traitement des résultats temporaires.
Les requêtes SQL complexes
combinent souvent des tables multiples,
qui demandaient à  DB2 UDB de
combiner toutes les tables dans une
table de résultats temporaires avant
que le groupage ou le classement spécifiés
puissent être effectués. Avant la
V5R2, DB2 UDB créait et peuplait cette
table de résultats temporaires en utilisant
essentiellement les mêmes interfaces
et étapes qu’un programme utilisateur.
Les primitives SQE V5R2 ont
amélioré ce traitement de résultats
temporaires inefficace en apportant
deux changements fondamentaux.
Premièrement, SQE place les données
combinées dans un objet interne
au lieu de s’infliger le fardeau de créer
un objet base de données OS/400 réel.
Deuxièmement, l’objet temporaire est peuplé sous la couche TIMI (technology-
independent machine interface)
OS/400. Avec SQE, les données que
l’on avait jointes étaient d’abord lues à 
partir de la couche TIMI, puis retransmises
plus bas dans la couche TIMI
pour peupler la table de résultats temporaires
avec les données jointes.
Plan Cache. SQE utilise un plan
cache autogéré pour réutiliser au maximum
les plans d’accès associés aux requêtes
SQL les plus courantes. Mieux
réutiliser les plans existants signifie
moins de constructions et de reconstructions
des plans d’accès, et donc
des performances système améliorées.
Le SQE Plan Cache s’applique à 
toute requête SQL qu’il exécute sur un
serveur, indépendamment de l’interface.
A l’inverse, le cache de plan effectué
par CQE est limité à  un job ou à 
une interface SQL spécifique. Comme
le SQE Plan Cache est utilisé pour les
requêtes SQL provenant de n’importe
quelle interface, la composition est différente
en V5R2 pour les plans d’accès
stockés dans des objets mémoire, programme
et package. Avant la V5R2, un
plan d’accès était un objet autonome à 
part entière.
La figure 3 montre comment les
plans d’accès générés par SQE sont
maintenant divisés en deux. Le SQE
Plan Cache stocke la portion optimisée
(par exemple, la recette du balayage
d’index) du plan d’accès. Les composants
du plan d’accès nécessaires pour
valider une requête SQL (comme l’information
texte et objet de l’instruction
SQL) sont laissés dans l’emplacement
du plan d’accès d’origine en
même temps qu’un lien virtuel vers le
plan dans le Plan Cache. Certaines instructions
de la figure 3 n’ont pas de relation
avec le Plan Cache ; ce sont les
instructions SQL qui sont traitées par
CQE.
Si vous avez des instructions SQL
communes fréquemment exécutées
par des programmes et des interfaces
différents, elles peuvent toutes désormais
partager la même portion optimisée
du plan d’accès, plutôt que de
créer chacune sa propre copie du plan
d’accès. Cette meilleure efficacité est
due à  la centralisation des plans d’implémentation
dans le SQE Plan Cache.
A l’instar de CQE, SQE est capable de
mettre à  jour les plans d’accès dans son
cache à  la demande pour utiliser les
éventuels nouveaux index ou nouvelles
statistiques qui ont pu être ajoutés
au système pour améliorer la performance.
L’avantage de la mise à  jour dynamique
des plans de SQE est que
chaque invoqueur de cette instruction
SQL bénéficiera automatiquement du
plan d’accès mis à  jour, au lieu que
chaque invoqueur mette à  jour sa
propre copie du plan d’implémentation.
Avant qu’une entrée pour une instruction
SQL soit placée dans le Plan
Cache, toutes les références de tables
non qualifiées sont résolues. Cela signifie
que si la même instruction SQL
est exécutée par trois programmes différents,
dont chacun a une liste de bibliothèques
ou chemins unique, il y
aura trois entrées différentes dans le
Plan Cache pour cette instruction SQL.
Le SQE Plan Cache est autogéré,
donc DB2 alloue automatiquement
l’espace et en surveille l’utilisation.
Lorsqu’on s’approchera de la limite
d’espace, DB2 UDB supprimera des
plans en se fondant sur le nombre de
fois qu’un plan d’accès a été utilisé et
quand il a été utilisé pour la dernière
fois. A noter que le Plan Cache est remis
à  zéro à  IPL et donc on constatera
un ralentissement temporaire des performances des programmes et des
objets package SQL après un IPL, pendant
que les plans d’accès sont reconstruits
et repeuplés dans le Plan Cache.
A l’heure actuelle, il n’existe pas d’outils
utilisateur permettant de visualiser
et de manipuler le contenu du SQE
Plan Cache.
Le SQE Plan Cache servira aussi de
fondation d’un optimiseur de requêtes
à  auto-apprentissage dans de futures
releases. Un tel optimiseur devrait être
capable de comparer le plan d’implémentation
stocké dans le cache par
rapport au temps d’exécution réel
pour la requête SQL Server associée. Si
le temps d’exécution réel est plus long
que prévu, l’optimiseur enregistrera ce
fait et ajustera ces stratégies d’implémentation
en vue d’améliorer la performance.
Statistics Manager. Le gestionnaire
statistique est le composant SQE
qui se charge de la collecte, du stockage
et de la consommation des statistiques
de bases de données.
Absolument: DB2 UDB for iSeries utilise
désormais et tire parti des statistiques
recueillies à  l’extérieur des index.
Avant de vous exciter et de
recruter un DBA (database administrator),
sachez que le support statistique
supplémentaire est entièrement automatisé.
Contrairement à  d’autres bases
de données pour lesquelles les statistiques
doivent être demandées manuellement,
DB2 UDB for iSeries reconnaîtra
automatiquement le besoin
d’une statistique de colonne et génèrera
une requête pour sa collecte. On
n’abandonne en rien la simplicité d’utilisation
et la facilité de gestion qui font
la réputation de l’iSeries.
Le rôle du gestionnaire statistique
consiste aussi à  répondre aux questions
que l’optimiseur se pose pendant
l’optimisation des requêtes. Ainsi, si un
utilisateur recherche toutes les chemises
violettes dans une table de catalogue
(par exemple, couleur=’violet’),
l’optimiseur doit savoir à  peu près
combien de lignes ont la valeur violet
pour la colonne couleur, afin de pouvoir
déterminer le moyen le plus rapide
d’extraire les lignes chemise violette
de la table. L’optimiseur SQE fait
cela en demandant au gestionnaire statistique
d’apporter une réponse à  sa
question. Le gestionnaire statistique a
pour mission de fournir la meilleure
réponse possible.
Pour répondre à  cette question
particulière, le gestionnaire statistique
se sert des données statistiques provenant
des index des collectes de statistiques
sur la colonne couleur. Si cette
donnée n’est pas disponible (c’est-à dire,
si aucun index ou statistique n’a
effectué de collecte sur la colonne
couleur), le gestionnaire statistique répondra
à  la question en utilisant des
valeurs par défaut. Celles-ci ne reflèteront
pas forcément la vraie distribution
des valeurs dans une colonne. Par
conséquent, le plan d’implémentation
le plus rapide possible choisi par l’optimiseur
ne sera pas forcément le
meilleur.
Mais alors, d’où viennent donc ces
données statistiques ? Chaque table et
index contient des informations sur sa
taille et son nombre de lignes. Outre
ces éléments, on peut extraire des index
(ou des fichiers logiques avec clés)
des informations sur la cardinalité et le
nombre moyen de valeurs en double
pour les champs clé.
Avec la V5R2, une nouvelle source
complémentaire de données statistiques
est offerte sous la forme de statistiques
de colonnes. Les statistiques
de colonnes ne bénéficieront que des
requêtes SQL exécutées par SQE.
Comme mentionné ci-dessus, ces statistiques
sont collectées automatiquement
et stockées avec la table par le
gestionnaire statistique. Si nécessaire,
les statistiques peuvent aussi être collectées
manuellement. Une collecte de
statistiques pour une colonne inclut
les types de statistiques suivants :

• Column Cardinality – Nombre de valeurs
distinctes dans une colonne
• Histogram – Statistique de distribution
qui décrit la sélectivité et la distribution
des valeurs pour une colonne

• Frequent Values List – Table des valeurs
qui reviennent le plus souvent
dans une colonne et un comptage de
la fréquence

La particularité des statistiques de
colonnes de DB2 UDB for iSeries est la
faculté de détecter automatiquement
le besoin d’une statistique de colonne
pendant l’optimisation et de générer
cette statistique. Un peu comme des
« statistiques à  la demande ». Après
l’optimisation d’une requête pour laquelle
il n’existait pas de statistiques,
une requête statistique est soumise à 
un job system d’arrière plan (QDBFSTCCOL).
Ce job system va recueillir
les éventuelles requêtes statistiques
supplémentaires pour la même table
puis balayer la table avec une faible
priorité, en compilant l’information.
Une fois l’opération terminée, l’information
statistique est stockée avec la
table et mise à  disposition des optimisations
de requêtes suivantes qui feront
référence à  cette colonne. Pour
valider et contrôler cette collecte statistique
automatique, il existe une nouvelle
valeur système: QDBFSTCCOL.
La valeur système QDBFSTCCOL a
les valeurs suivantes :

*ALL – Permet les collectes statistiques
demandées par l’utilisateur
et le système (par défaut)
*NONE – Aucune collecte statistique
n’est permise
*USER – Seule la collecte statistique
demandée par l’utilisateur est permise
*SYSTEM – Seule la collecte statistique
demandée par le système est permise

Téléchargez gratuitement cette ressource

Guide des Services Managés pour se moderniser et se sécuriser

Guide des Services Managés pour se moderniser et se sécuriser

À l’heure où les talents du numérique sont rares et difficiles à séduire, comment bénéficier des meilleures compétences en infrastructure Cloud ou en cybersécurité pour gagner en agilité et en cyber-résilience ? Découvrez le Guide des Services managés dédiés aux PME.

Tech - Par iTPro.fr - Publié le 24 juin 2010