Statement Cache - ASE 12.5.2

Introduction

Le cache de requêtes ou "statement cache" est une nouveauté de Sybase ASE 12.5.1 mais effectif seulement depuis Sybase ASE 12.5.2. Le cache de requêtes est utilisé pour sauvegarder des requêtes SQL en cache. Adaptive Server compare le code SQL entrant aux requêtes SQL en cache, si une correspondance est trouvée, le plan de la requête SQL en cache est exécuté. La cache de requêtes permet ainsi à l’application d’amortir les coûts de compilation pour des requêtes exécutées plusieurs fois.

Activation du cache de requêtes

Paramètre "statement cache size"

Le cache de requêtes permet de stocker le texte des commandes SQL Ad-hoc. Adaptive Server compare tout nouvel ordre SQL Ad-hoc reçu aux commandes SQL en cache et si une correspondance est trouvée utilise le plan d’exécution mis en cache lors de l’exécution initiale. Ainsi Adaptive Server n’a pas à recompiler les commandes SQL pour lesquelles un plan d’exécution existe.

Le cache de requêtes est une ressource serveur consommant de la mémoire au niveau du pool de mémoire du cache de procedures.

Le paramètre de configuration dynamique statement cache size dimensionne la taille mémoire du cache de requêtes :

exec sp_configure "statement cache size", size_of_cache

L’unité du paramètre size_of_cache est la page de 2K. Pour spécifier un cache de requêtes avec 5120 pages de 2K (soit une taille mémoire de 10Mb) :

exec sp_configure "statement cache size", 5120.

La quantité de mémoire allouée au cache de procédures est la somme des paramètres de configuration "statement cache size" et "procedure cache size". La mémoire du cache de requêtes est prise dans le pool de mémoire du cache de procédures.
Le paramètre "statement cache size" limite la quantité de mémoire utilisée dans le cache de procédures pour les textes SQL et plans correspondants mis en cache. Ainsi Adaptive Server ne peut utiliser plus de mémoire pour le cache de requêtes que celle configurée avec le paramètre "statement cache size".
Toute la mémoire du cache de procédures, incluant la mémoire allouée par le paramètre "statement cache size", est disponible pour les procédures stockées, lesquelles peuvent donc remplacer les commandes en cache avec la stratégie LRU.
Augmenter le paramètre de configuration "max memory" du même montant que celui configuré pour le cache de requêtes ("statement cache size").
Chaque commande SQL mise en cache consomme un descripteur d’objet, aussi il est nécessaire d’augmenter le nombre de descripteurs d’objets avec le paramètre de configuration "number of open objects".

Étapes de traitement des requêtes Ad-hoc

Adaptive Server réalise les étapes ci-dessous pour traiter les requêtes Ad-hoc en utilisant le cache de requêtes :

Étape 1.	Adaptive Server parse la commande. Si la commande peut être mise en cache, ASE calcule une hash value pour la commande (ex : `ssql_hashkey 0x530e4a67`), valeur qui permet au moteur Adaptive Server d’effectuer un critère de correspondance avec les commandes SQL déjà existantes en cache. Si une correspondance est trouvée au niveau du cache de requêtes grâce à cette hash value, Adaptive Server passe directement à l’étape 4. Si aucune correspondance n’est trouvée, Adaptive Server poursuit à l’étape 2.
Étape 2.	Adaptive Server met en cache la commande SQL.
Étape 3.	Adaptive Server package la commande SQL en une procédure stockée en convertissant toutes les variables locales en paramètres de procédures. A ce stade, la procédure n’est pas compilée et son plan d’exécution n’est pas en cache.
Étape 4.	Adaptive Server convertit la commande SQL en une commande `execute` de la procédure stockée correspondante. Si il n’existe pas de plan dans le cache pour la procédure stockée, Adaptive Server compile la procédure stockée et met le plan en cache. Adaptive Server compile le plan en utilisant les valeurs d’exécution pour les variables locales. Si le plan existe mais est déclaré comme invalide, Adaptive Server retourne à l’étape 3 en utilisant le texte de la commande SQL en cache.
Étape 5.	Adaptive Server exécute la procédure.

Critères de correspondance

Adaptive Server effectue la correspondance entre une commande SQL Ad-hoc et les commandes SQL en cache avec les critères suivants :

Texte SQL
Login
ID de l’utilisateur
ID de la base de données
paramètres de la session (forceplan, jtc, parallel_degree, prefetch, quoted_identifier, sort_merge, table count, transaction isolation level, chained transaction mode)

Conditions de mise en cache

Adaptive Server met en cache les commandes SELECT, UPDATE, DELETE et INSERT.
Les commandes ne sont pas mises en cache si les paramètres "abstract plan dump" ou "abstract plan load" sont activés.
Adaptive Server ne met pas en cache les commandes SELECT INTO, les commandes liées aux curseurs, les commandes dynamiques et les commandes internes aux procédures stockées, vues et triggers.
Les commandes liées à des tables temporaires ne sont pas mises en cache ainsi que les commandes ayant pour paramètres des types de donées BLOB.
Les commandes SQL très volumineuses et toutes les commandes encapsulées dans des conditions if exists et if not exists ne sont pas mises en cache.

Dimensionnement du cache de requêtes

Chaque commande SQL requiert en moyenne 1K de mémoire dans le cache de requêtes selon la taille du texte SQL. Chaque plan en cache requiert environ 2K de mémoire dans le cache de procédures. Pour estimer la mémoire nécessaire au cache de requêtes, il faut prendre en compte :

la taille du texte SQL arrondie au multiple de 256 le plus proche.
Approximativement 100 bytes d’entête.
La taille du plan dans le cache de procédures. Cette taille est équivalente à la taille du plan d’une procédure stockée contenant la commande SQL. Attention, il peut y avoir des plans dupliqués pour une même commande SQL en cache mais exécutée en concurrence par deux ou plusieurs utilisateurs.

Monitoring du cache de requêtes

Ma procédure sp_sysmon affiche le rapport sur l’activité du cache de requêtes 'SQLStatement Cache' au niveau de la rubrique 'Procedure Cache Management'.

Procedure Cache Management        per sec        per xact        count        % of total
--------------------------        -------        --------        -----        ----------
...
SQLStatement Cache:
Statements Cached                 0.0            0.0             0            n/a
Statements Found in Cache         0.7            0.0             2            n/a
Statements Not Found              0.0            0.0             0.0          n/a
Statements Dropped                0.0            0.0             0            n/a
Statements Recompiled             0.3            0.0             1            n/a
Statements Not Cached             1.3            0.0             4            n/a

Description des compteurs

Section	Description
`Statements Found in Cache`	Nombre de fois qu’un plan d’exécution a été réutilisé. Une faible valeur peut indiquer que le cache de requêtes est sous dimensionné.
`Statements Not Found`	Nombre de commandes SQL non trouvées en cache et implémentées dans ce dernier. La somme des compteurs "`Statements Found in Cache`" et "`Statements Not Found`" représente le nombre total de commandes SQL éligibles et soumises au cache de requêtes.
`Statements Cached`	Nombre de requêtes SQL mis en cache. C’est typiquement la même valeur que le compteur "`Statements Not Found`". Des valeurs faibles de ce compteur indique que le cache de requêtes est rempli de commandes SQL actives.
`Statements Dropped`	Nombre de requêtes qui ont été supprimées du cache. Une valeur importante peut indiquer que la quantité de mémoire pour le cache de procédures est trop petite ou bien que la taille du cache de requêtes est trop petite.
`Statements Restored`	Nombre de plans d’exécution régénérés à partir du texte SQL. Des valeurs importantes indique une valeur insuffisante pour le paramètre "`Procedure Cache Size`".
`Statements Not Cached`	Nombres de commandes SQL qui sont éligibles au cache de requêtes si ce dernier est activé

Commandes liées au cache de requêtes

Commandes dbcc : dbcc prsqlcache et dbcc purgesqlcache

Les commandes dbcc comprennent à présent les paramètres prsqlcache et purgesqlcache et sont directement liées au cache de requêtes.

dbcc prsqlcache
dbcc prsqlcache ([sqlid [ , printopt ]])

dbcc purgesqlcache
dbcc purgesqlcache (sqlid)   ! Nouveauté ASE 15.0.2

Affichage des données dans le cache de requêtes : dbcc prsqlcache

La commande dbcc prsqlcache [sqlid, printopt] affiche un résumé plus ou moins détaillé à la demande des commandes SQL en cache. Pour cette commande :

dbcc prsqlcache
dbcc prsqlcache ([sqlid [ , printopt ]])

La trace 3604 doit être activée pour pouvoir faire appel à la commande dbcc prsqlcache.

Le rôle sa_role est nécessaire pour l’exécution de la commande dbcc prsqlcache. Si aucune valeur n’est spécifiée pour les paramètres sqlid, printopt, tout le contenu du cache de requêtes est affiché.

`sqlid`	Identifiant de l’objet dans le cache de requête pour lequel on souhaite un résumé. L’affichage d’informations supplémentaires sur l’entrée est contrôlé par le paramètre `printopt` de la commande. La valeur de `sqlid` peut valoir 0 et dans ce cas un bref résumé est affiché pour chaque commande SQL en cache, la valeur pour `printopt` est alors ignorée dans ce cas.
`printopt`	Paramètre permettant de contrôler l’affichage lorsque `sqlid` correspond à un objet valide. La variable `printopt` peut valoir 0 ou 1. Lorsque `printopt` vaut 1, le plan d’exécution (`showplan`) est affiché.

Exemples d’affichage :

dbcc traceon(3604)
go
dbcc prsqlcache
go
Start of SSQL Hash Table at 0xfc67d830
Memory configured: 1000 2k pages         Memory used: 18 2k pages

Bucket# 625 address 0xfc67ebb8
SSQL_DESC 0xfc67f9c0
ssql_name *ss1248998166_0290284638ss*
ssql_hashkey 0x114d645e                  ssql_id 1248998166
ssql_suid 1                ssql_uid 1                 ssql_dbid 1
ssql_status 0x28           ssql_parallel_deg 1
ssql_tab_count 0           ssql_isolate 1             ssql_tranmode 0
ssql_keep 0                ssql_usecnt 1              ssql_pgcount 8
SQL TEXT: select * from sysobjects where name like "sp%"

Bucket# 852 address 0xfc67f2d0
SSQL_DESC 0xfc67f840
ssql_name *ss1232998109_1393445479ss*
ssql_hashkey 0x530e4a67                  ssql_id 1232998109
ssql_suid 1                ssql_uid 1                 ssql_dbid 1
ssql_status 0x28           ssql_parallel_deg 1
ssql_tab_count 0           ssql_isolate 1             ssql_tranmode 0
ssql_keep 0                ssql_usecnt 1              ssql_pgcount 3
SQL TEXT: select name from systypes where allownulls = 0

End of SSQL Hash Table

Dans le résumé ci-dessus, deux requêtes sont en cache, ces dernières étant identifiées par ssql_id.

Pour afficher un résumé pour une unique requête en cache, c’est la valeur ssql_id qui doit être précisée pour le paramètre sqlid dans la commande dbcc prsqlcache.

dbcc traceon(3604)
go
dbcc prsqlcache (1232998109, 0)
go

SSQL_DESC 0xfc67f840
ssql_name *ss1232998109_1393445479ss*
ssql_hashkey 0x530e4a67                  ssql_id 1232998109
ssql_suid 1                ssql_uid 1                 ssql_dbid 1
ssql_status 0x28           ssql_parallel_deg 1
ssql_tab_count 0           ssql_isolate 1             ssql_tranmode 0
ssql_keep 0                ssql_usecnt 1              ssql_pgcount 3
SQL TEXT: select name from systypes where allownulls = 0

Pour cette même requête, afin d’afficher le plan d’exécution d’une requête en cache, spécifier 1 pour le paramètre printopt.

dbcc traceon(3604)
go
dbcc prsqlcache (1232998109, 1)
go

SSQL_DESC 0xfc67f840
ssql_name *ss1232998109_1393445479ss*
ssql_hashkey 0x530e4a67                  ssql_id 1232998109
ssql_suid 1                ssql_uid 1                 ssql_dbid 1
ssql_status 0x28           ssql_parallel_deg 1
ssql_tab_count 0           ssql_isolate 1             ssql_tranmode 0
ssql_keep 0                ssql_usecnt 1              ssql_pgcount 3
SQL TEXT: select name from systypes where allownulls = 0

QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
        The type of query is SELECT.
        FROM TABLE
        systypes
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

Purge du cache de requêtes : dbcc purgesqlcache

dbcc purgesqlcache
dbcc purgesqlcache (sqlid)   ! Nouveauté ASE 15.0.2

La commande dbcc purgesqlcache détruit une ou toutes les commandes SQL dans le cache de requêtes exceptées les commandes en cours d’exécution. Le rôle sa_role est nécessaire pour l’exécution de la commande de purge.

La possibilité de purger une requête uniquement identifiée par ssql_id a été introduite à partir de la version 15.0.2.

Commandes set (niveau session)

Le cache de requêtes implémente le paramètre set statement_cache au niveau de la session

set statement_cache [on | off]

Au niveau d’une session, cette commande permet d’activer ou non le cache de requêtes.

La désactivation du cache de requêtes au niveau session est très importante lors du tuning des requêtes.

Dès lors que le paramètre "statement cache size" a une valeur différente de 0, une session peut activer ou désactiver le cache de requêtes. Si le cache de requêtes est configuré au niveau du serveur, par défaut au niveau de la session ce dernier est également activé.

Le cache de requêtes (statement cache) - ASE 12.5.2