Performances Db2 for i – La règle des 7% du Plan Cache

Publié le 13/11/2019

Connaissez-vous la règle des 7% du Plan Cache de Db2 for i ?

Pour rappel, le Plan Cache est une fonction de Db2 for i permettant d’améliorer les performances des requêtes SQL utilisant le moteur SQE (SQL Query Engine). Il s’agit d’un cache qui stocke tous les plans d’accès utilisés, et donc optimisés, sur le système par les différentes requêtes. Il permet :

de favoriser la réutilisation d’un plan d’accès lorsque la même requête est exécutée à plusieurs reprises
de stocker les informations d’exécution pour optimiser les requêtes suivantes
de fournir des informations sur les performances des requêtes afin d’améliorer le tuning et de permettre les analyses précises.

Une fois qu’un plan d’accès est créé dans le Plan Cache, il est disponible pour les requêtes de tous les utilisateurs. Lorsqu’un plan d’accès est défini, toutes les requêtes peuvent en bénéficier. Ce plan d’accès élimine le besoin de ré-optimiser la requête, ce qui se traduit par une efficacité accrue.

Les requêtes s’appuyant sur le moteur CQE n’utilisent pas le Plan Cache.

Pour information, Db2 for i s’écrit désormais avec un « b » minuscule. 😉

Plus les requêtes utilisent les plans d’accès du Plan Cache, plus elles sont performantes. Lorsqu’une requête utilise un plan d’accès existant, on appelle cela un « hit » (ou taux d’accès).

Bien entendu, le but étant d’avoir un taux de hit le plus élevé possible. Pour cela, IBM a défini des règles relatives aux performances. Jusqu’en V7R1 le taux de hit devait être supérieur à 70%, mais à partir de la V7R2, les exigences d’IBM sur les performances ont été revues à la hausse.

Le taux de hit minimum à été positionné à … 90%.

Règle permettant l’accroissement automatique du Plan Cache

Plus la taille du Plan Cache sera importante, plus il pourra stocker de plans d’accès. La taille initiale du Plan Cache est de 512 MiB, et elle augmentera au fur et à mesure de l’activité.

Mais attention, c’est ici qu’entre en fonction la règle des 7% !

Cette règle est la suivante :

Si le taux de hit tombe en dessous des 90%, il y a agrandissement automatique de la taille du Plan Cache
L’accroissement automatique du Plan Cache ne peut se faire que si l’espace temporaire est inférieur à 7% de l’espace disque libre de l’ASP système.
Si l’espace temporaire est supérieur à 7%, le Plan Cache ne peut pas s’agrandir automatiquement.

La taille maximale autorisée pour l’auto-accroissement, qui était précédemment codée en dur dans l’Operating System, est désormais basée sur un calcul du pourcentage d’espace temporaire.

Et comme un schéma vaut mieux que des mots, voici la règle du calcul du pourcentage d’espace temporaire.

Il suffit d’aller sur l’écran de gestion de l’état du système, WRKSYSSTS, pour avoir toutes les informations nécessaires au calcul.

Méthode de calcul du pourcentage d’espace temporaire. Cliquer sur l’image pour agrandir.

Pour résumer :

Si le pourcentage d’espace temporaire est inférieur à 7%, alors la taille du Plan Cache pourra augmenter automatiquement en cas de besoin.
Si le pourcentage d’espace temporaire est supérieur à 7%, alors la taille du Plan Cache ne pourra pas augmenter automatiquement en cas de besoin. Il faudra le faire manuellement.

Exemple de calcul du % d’espace temporaire

Voici un exemple concret de calcul du % d’espace temporaire.

Exemple de calcul du pourcentage d’espace temporaire. Cliquer sur l’image pour agrandir.

L’espace temporaire, affiché en MB doit être divisé par 1000 car ce sont des GB qui sont utilisés dans le calcul (104260 / 1000 = 104,260).
L’espace total peut directement être intégré au calcul car il est déjà en GB (763,5).
Pour le pourcentage d’occupation de l’ASP 1, on indique sa valeur (25,0255 / 100 = 0,250255).

Bilan : Dans cet exemple, le % d’espace temporaire est de 18,21%. On est donc largement au-dessus des 7%. Cela signifie que si le nombre de hit est inférieur à 90%, alors le Plan Cache ne pourra pas s’agrandir automatiquement et qu’il faudra le faire manuellement sous peine de dégradation des performances.

Allons voir les paramètres du Plan Cache de cette partition.

Zoom sur les principales caractéristiques

A cet instant, les caractéristiques du Plan Cache sont les suivantes :

Taille du Plan Cache : 493 MiB
Pourcentage de hits : 96%
Taille maximale du Plan Cache par ajustement automatique : 9210 MiB (9 GiB)

Dans cet exemple, le taux de hits est de 96%, cela signifie que pour le moment, la taille du Plan Cache est encore satisfaisante. Toutefois, si le taux de hit venait à diminuer et descendre en dessous des 90%, le système ne pourrait pas accroître automatiquement la taille du Plan Cache, et les performances resteraient donc à un niveau insuffisant. Seule une action manuelle permettrait d’améliorer les performances, en accroissant la taille du Plan Cache.

La modification de la taille du Plan Cache peut se faire de différentes manières :

Par l’option « Modification de Configuration » dans le SQL Performance Center de ACS
Par une requête SQL

Méthode 1 (SQL Performance Center de ACS)

Interface SQL Performance Center de ACS.

Indiquer la valeur souhaitée.

Méthode 2 (Requête SQL)

Exemple : passer la taille à 1024 MiB (1 GiB)

CALL QSYS2.CHANGE_PLAN_CACHE_SIZE(1024);

La taille du Plan Cache est passée à 1 024 MiB.

Chaque environnement ayant un workload qui lui est propre, il n’y a pas de règle donnant la taille optimale du Plan Cache, l’idéal étant de laisser le système s’ajuster lui-même (*AUTO), mais pour cela, il faut respecter la règle des 7%. 🙂