Se préparer pour le sql query engine V5R2

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