ASE 15.0.1 - Le cache de requêtes et la paramétrisation littérale

Logo

Introduction

Le cache de requêtes (Statement Cache) a été introduit dans Sybase ASE 12.5.2. Cette nouveauté avait fait l’objet d’un article (Statement Cache - ASE 12.5.2).

Toutefois il existe un inconvénient majeur dans la première implémentation du cache de requêtes, en effet deux requêtes identiques et qui ne diffèrent que par des valeurs littérales ont deux plans d’exécution distincts dans le cache de requêtes. Par exemple, les plans d’exécution des requêtes ci-dessous sont stockées séparément dans le cache de requêtes alors qu’elles sont presque identiques :

select count(*) from titles where total_sales > 100
select count(*) from titles where total_sales > 200

C’est pour cette raison que le cache de requêtes n’a généralement pas été implémenté, sp_sysmon n’ayant pas prouvé l’efficacité du cache de requêtes pour ce type de requêtes atomiques et très variantes uniquement au niveau des valeurs littérales.

ASE 15.0.1 pallie ce problème avec l’implémentation de la 'paramétrisation littérale' rendant le cache de requêtes bien plus efficace, un seul plan est généré dans le cache de requêtes pour une requête générique.

La paramétrisation littérale 'literal parameterization'

ASE 15.0.1 permet de convertir automatiquement les valeurs littérales dans les requêtes en des paramètres (ou variables). Lorsque cette fonctionnalité est activée, elle s’applique :

  • au cache de requêtes.
  • au plans de requêtes abstraits (abstract query plans).
  • à la capture des métriques des requêtes (tables MDA, …).

Activation au niveau serveur et session (enable literal autoparam | set literal_autoparam )

Pour cette nouveauté, une nouvelle option serveur modifiable avec sp_configure a été introduite : « enable literal autoparam »

exec sp_configure 'enable literal autoparam' , [ 0 | 1 ]

Par défaut, la valeur de cette option est fixée à 1 (option activée). La valeur 0 désactive la paramétrisation littérale.

La paramétrisation littérale peut aussi être appliquée au niveau de la session :

set literal_autoparam [ off | on ]

Exemples

Lorsque la paramétrisation littérale est activée, la commande SELECT donnée en introduction est transformée ainsi :

select count(*) from titles where total_sales > @@@V0_INT

@@@V0_INT est générée en interne pour le paramètre qui représente la valeur littérale. Grâce à cette paramétrisation, un seul plan est généré et réutilisé pour cette requête dans le cache de requêtes.

Toutes les valeurs littérales d’une requête sont transformées par la paramétrisation littérale, ainsi la requête :

select substring(name, 3, 4)
from sysobjects where name in ("systypes", "syscolumns")

est transformée en 

select substring(name, @@@V0_INT, @@@V1_INT)
from sysobjects where name in (@@@V2_VARCHAR, @@@V3_VARCHAR)

Toute combinaison de valeurs qui remplacent 3, 4, systypes et syscolumns sont transformées en un même texte SQL avec la paramétrisation littérale, texte SQL qui aura un plan unique associé dans le cache de requêtes.

Restrictions

  • Adaptive Server paramétrise les valeurs littérales pour les commandes SELECT, DELETE, UPDATE et INSERT. Toutefois pour les commandes INSERT, Adaptive Server paramétrise seulement les commandes INSERT … SELECT et pas les commandes INSERT … VALUES.
  • Adaptive Server ne paramétrise pas les requêtes du type select id + 1 from sysobjects group by id + 1 ou select id + 1 from sysobjects order by id + 1 à cause des expressions 'id +1' dans les clauses group by et order by.
  • Adaptive Server ne met pas en cache les requêtes dont le texte excède 16384. La transformation littérale d’une requête (plus coûteuse en octets) peut dépasser cette limite et dès lors la requête n’est pas mise en cache.
  • Les types unichar et univarchar ne sont pas supportés.
  • Avec un jeu de caractère multi-bytes, la paramétrisation littérale est automatiquement désactivée.
  • Si deux requêtes sont identiques mais que leurs valeurs littérales sont de types différents, elles ne sont pas transformées en un même texte SQL paramétré ! Il faut être vigilant, car ASE détermine automatiquement le type de la valeur littérale. 
    select name from sysobjects where id = 1
select name from sysobjects where id = 1.0
    sont respectivement transformées en 
    select name from sysobjects where id = @@@V0_INT
select name from sysobjects where id = @@@V0_NUMERIC_2_1

Effets de bord potentiels sur les plans d’exécutions

La paramétrisation littérale peut avoir un effet de bord sur la génération des plans d’exécution, en effet en fonction des poids de valeurs dans les statistiques, un scan de table se révèle parfois moins coûteux que de passer par un index si la distribution de valeurs recherchée représente par exemple plus de 80% d’une table.

Or la paramétrisation, telle qu’elle apparaît sur le papier, force la génération d’un plan d’exécution avec passage par un index, et ceci quelque soit la valeur littérale, même si cette valeur littérale représente plus de 80% d’une table et qu’un plan d’exécution avec un « table scan » serait plus efficace. Donc attention aux I/Os supplémentaires dans ce contexte.