Les plans abstraits avec Sybase Adaptive Server Enterprise et les migrations

Introduction

Les migrations de Sybase Adaptive Server Enterprise apportent bien souvent de très bonnes surprises dans les plans d’exécution des requêtes, cependant parfois certaines requêtes dérapent dramatiquement.

Dans cet article, il est proposé une mise en œuvre rapide d’un plan abstrait (AP ou Abstract Plan) pour une requête qui subit une régression très problèmatique à la suite d’une migration Sybase 12.5.3 vers Sybase 15.0.2. Tous les outils de diagnostic propres à Sybase 15.0.2 ont été mis en œuvre pour tenter de résoudre la régression dans le plan d’exécution sans apporter de solution (paramètres optimization_goal, nl_join, hash_join, statistiques et densité, etc.), la seule solution qui a permis de retrouver le plan d’exécution optimal a consisté à monter le plan abstrait de la version 12.5.3 au sein du serveur 15.0.2.

Le cas a été ouvert au support Sybase et une solution sera probablement apportée avec la version 15.0.3 ESD#1 prévue pour avril 2009.

Contexte de la requête

La requête qui pose problème est une insertion au sein d’une procédure stockée qui prend 3 paramètres en entrée, la syntaxe de l’insertion est donnée partiellement ci-dessous :

CREATE PROC SP_AP_ADM_ALL_MIRT_MONTHTR1
( @TRD_TRANSAC_VALIDITY numeric(1)= NULL,
  @TRD_TRANSAC_CREATION_DATE datetime= null,
  @TRD_TRANSAC_ID numeric(18) =NULL
)
AS

  INSERT INTO dbo.CK_OUT_ADM_ALL_MIRT_MONTHTRANS
     (SYSTEM_ID,
      EXEC_INST_ID_IN,
      ....
      )
  SELECT  G_T0.SYSTEM_ID ,
          G_T0.EXEC_INST_ID_IN ,
          G_T0.EXEC_INST_NUMBER_IN ,
          ....
  FROM  dbo.LD_OUT_ADM_ALL_MIRT_TRD_TRANS G_T0,
        dbo.V_LD_OUT_ADM_ALL_MIRT_ACC_CLI G_T1,
        dbo.V_LD_OUT_ADM_ALL_MIRT_ACC_INFO G_T2,
        dbo.LD_OUT_ADM_ALL_MIRT_PRD_PROD G_T3
  WHERE ( G_T3.PRD_ID = G_T0.TRD_PRD_ID
          AND G_T3.EXEC_INST_ID_IN = G_T0.EXEC_INST_ID_IN
          AND G_T3.EXEC_INST_NUMBER_IN = G_T0.EXEC_INST_NUMBER_IN  )
  AND   ( G_T0.EXEC_INST_ID_IN = G_T1.EXEC_INST_ID_IN
          AND G_T0.EXEC_INST_NUMBER_IN = G_T1.EXEC_INST_NUMBER_IN
          AND G_T0.TRD_ACC_ID = G_T1.ACC_ID  )
  AND   ( G_T0.EXEC_INST_ID_IN = G_T2.EXEC_INST_ID_IN
          AND G_T0.EXEC_INST_NUMBER_IN = G_T2.EXEC_INST_NUMBER_IN
          AND G_T0.TRD_ACC_ID = G_T2.ACC_ID  )
  AND   ( TRD_TRANSAC_VALIDITY = @TRD_TRANSAC_VALIDITY
          AND TRD_TRANSAC_CREATION_DATE >= @TRD_TRANSAC_CREATION_DATE
          AND TRD_TRANSAC_ID <> @TRD_TRANSAC_ID )

Récupération du plan abstrait dans la version 12.5.3

Les groupes de plans d’exécution ap_stdin et ap_stdout (sp_help_qpgroup)

Par défaut, un serveur ASE contient deux groupes de plans d’exécution par défaut : ap_stdin et ap_stdout.

le groupe ap_stdout est le groupe de plans d’exécution par défaut lorsque les plans sont capturés (paramètre "abstract plan dump" à 1 au niveau serveur ou "set plan dump on" au niveau session).
le groupe ap_stdin est le groupe de plans d’exécution par défaut pour l’association des plans avec les requêtes (paramètre "abstract plan load" à 1 au niveau serveur ou "set plan load on" au niveau session).

La procédure système sp_help_qpgroup exécutée dans une base de données permet de retrouver les groupes de plan d’exécution existants dans cette base de données (qpgroup pour Query Plan group).

execute sp_help_qpgroup
go
          Query plan groups in database 'GDH_DB'
Group                          GID         Plans
------------------------------ ----------- -----------
ap_stdin                                 1           0
ap_stdout                                2           0

Création du nouveau groupe de plans d’exécution (sp_add_qpgroup)

La procédure sp_add_qpgroup permet de créér un groupe de plans d’exécution sans perturber les groupes de plans d’exécution par défaut.

execute sp_add_qpgroup <qp group name>

Le nouveau groupe ap_gdh créé ici sert uniquement à sauvegarder le planabstrait 12.5.3 de l’insertion qui pose problème dans la version 15.0.2.

exec sp_add_qpgroup ap_gdh
exec sp_help_qpgroup
go
          Query plan groups in database 'GDH_DB'
Group                          GID         Plans
------------------------------ ----------- -----------
ap_gdh                                   3           1
ap_stdin                                 1           0
ap_stdout                                2           0

Capture du plan abstrait de l’insertion au sein de la procédure stockée (set plan dump)

La capture des plans d’exécution peut être réalisée au niveau serveur (sp_configure 'abstract plan dump',1) : dans ce cas de figure, tous les plans abstraits sont capturés et stockés dans le groupe de plans d’exécution par défaut ap_stdout.

Lorsque l’on souhaite simplement capturer un plan abstrait pour une requête en particulier, une activation au niveau de la session est suffisante avec la commande "set plan dump [group_name] on". Lorsque le nom du groupe de plan d’exécutions n’est pas donné, les plans sont stockés dans le groupe par défaut ap_stdout :

set plan dump [group_name] on
go
… query …
go

Pour capturer des plans de requêtes au sein d’une procédure stockée, la commande de création de la procédure stockée CREATE PROCEDURE doit être lancée au moment de la capture. L’option set fmtonly permet quant à elle d’éviter l’exécution effective de la procédure stockée.

set plan dump [group_name] on
go
create procedure myproc …
go
set fmtonly on
go
exec myproc …
go

Dans le cas pratique de cet article :

set plan dump ap_gdh on
go
create procedure SP_AP_ADM_ALL_MIRT_MONTHTR1 …
go
set fmtonly on
go
exec SP_AP_ADM_ALL_MIRT_MONTHTR1 1,'2009 JAN 16',-1
go

La commande "set plan dump off" au niveau de la session arrête la capture des plans abstraits au niveau de la session. La commande "sp_configure 'abstract plan dump',0" arrête la capture au niveau du serveur.

Visualiser le détail du plan abstrait (sp_help_qplan)

La procédure sp_help_qpgroup fournit également des informations plus détaillées sur les plans capturés lorsque les paramètres <group_name> et 'counts' sont donnés :

exec sp_help_qpgroup <qp group_name>, 'counts'

exec sp_help_qpgroup ap_gdh, 'counts'
          Query plans group 'ap_gdh', GID 3
Total Rows  Total QueryPlans
----------- ----------------
         43                1
         
Query plans in this group
 Rows        Chars       hashkey     id          query
 ----------- ----------- ----------- ----------- ----------------------------
          43       10796  1598970416  1282467993 INSERT INTO dbo.CK_OUT_AD...

Les informations importantes données sont le nombre de plans abstraits sauvegardés dans le groupe (Total QueryPlans), la clé de hachage pour chaque plan d’exécution (hashkey) et l’identifiant de chaque plan d’exécution (id). L’identifiant des plans d’exécution est toujours unique, identifiant indispensable pour obtenir plus d’informations sur le plan abstrait grâce à la procédure système sp_help_qplan.

exec sp_help_qplan <id_plan>, 'brief|list|full'

Voici un exemple de plan abstrait (partiel), l’objectif de cet article n’étant pas de décortiquer la grammaire des plans abstraits :

exec sp_help_qplan 1282467993,full
go
           gid         hashkey     id
----------- ----------- -----------
          3  1598970416  1282467993
          
query
-----------------------------------------------------------------------------------------------
INSERT INTO dbo.CK_OUT_ADM_ALL_MIRT_MONTHTRANS (SYSTEM_ID, EXEC_INST_ID_IN,
EXEC_INST_NUMBER_IN, EXEC_INST_ID_OUT, ...

plan
-----------------------------------------------------------------------------------------------
( nl_g_join
   ( t_scan ( table ( IFRS_SEG LD_OUT_ADM_ALL_MIRT_IFRS_SEG ) ( in ( view G_T2 ) ) ) )
   ( t_scan ( table ( ACC LD_OUT_ADM_ALL_MIRT_ACC ) ( in ( view G_T2 ) ) ) )
   ( scan
      ( store
        ( t_scan ( table ( G_T0 dbo.LD_OUT_ADM_ALL_MIRT_TRD_TRANS) ) )
      )
    )

Export du plan abstrait 12.5.3 (sp_export_qpgroup)

La procédure sp_export_qpgroup exporte les plans abstraits d’un groupe pour un utilisateur donné dans une table, table qui permettra le transfert des plans vers un autre serveur.

exec sp_export_qpgroup <user>,<qp group_name>,<table>

exec sp_export_qpgroup dbo,ap_gdh,transfer

La table donnée dans la procédure sp_export_qpgroup est automatiquement créée par la procédure et la structure est la suivante :

uid       int                        NOT NULL,
gid       int                        NOT NULL,
hashkey   int                        NOT NULL,
id        int                        NOT NULL,
type      smallint                   NOT NULL,
sequence  smallint                   NOT NULL,
status    int                        NULL,
text      varchar(255)               NOT NULL

Une fois les plans exportés dans la table de transfert, il suffit d’exporter les données de cette table avec le binaire bcp dans un fichier en mode binaire :

bcp <dbname>..transfer out transfer.bcpn -Usa -P<password> -S<serveur 12.5.3> -n

Test du plan abstrait 12.5.3 dans le serveur 15.0.2

Dans le serveur cible 15.0.2, le groupe de plans d’exécutions ap_gdh est également créé avec la commande sp_add_qpgroup. Il est en effet déconseillé d’utiliser systèmatiquement le groupe par défaut ap_stdin pour tester des plans abstraits.

Import du plan abstrait 12.5.3 dans le serveur 15.0.2 (sp_import_qpgroup)

Pour importer le plan abstrait de la version 12.5.3 dans le serveur version 15.0.2, créér une table dans le serveur 15.0.2 ayant la structure évoquée lors de l’export :

CREATE TABLE transfer
(
   uid       int                        NOT NULL,
   gid       int                        NOT NULL,
   hashkey   int                        NOT NULL,
   id        int                        NOT NULL,
   type      smallint                   NOT NULL,
   sequence  smallint                   NOT NULL,
   status    int                        NULL,
   text      varchar(255)               NOT NULL
)

Puis importer les données exportées dans l’étape précédente avec le binaire bcp :

bcp <dbname>..transfer in transfer.bcpn -Usa -P<password> -S<serveur 15.0.2> -n

La procédure sp_import_qpgroup importe dans un groupe de plans d’exécution les plans abstraits contenus dans la table de transfert pour un utilisateur donné

exec sp_import_qpgroup <table>,<user>,<qp group_name>

exec sp_import_qpgroup transfer,dbo,ap_gdh

Test du plan abstrait, association via les clés de hâchage (hashkey) des requêtes

Pour finalement tester le plan abstrait importé, le groupe de plans abstraits ap_gdh est chargé pour les associations avec les requêtes au niveau de la session avec la commande "set plan load [group_name] on" :

set plan load [group_name] on
go
<requête>
go

La commande "set showplan on" activée au niveau de la même session permet de vérifier que le plan abstrait est effectivement utilisé avec le mot clé "Optimized using an Abstract Plan" apparaissant dans la sortie du plan d’exécution :

set plan load ap_gdh on
go
set showplan on
go
execute SP_AP_ADM_ALL_MIRT_MONTHTR1 1,'2009 JAN 16',-1
go
…
QUERY PLAN FOR STATEMENT 2 (at line 9).
Optimized using an Abstract Plan (ID : 1294992040)
…

Reste à vérifier à cette étape que le nouveau plan d’exécution forcé par le plan abstrait permet de retrouver les performances optimales.

Si le plan abstrait n’est pas utilisé, vérifier que les requêtes sont identiques entre la version 12.5.3 et la version 15.0.2. L’association est en effet réalisée à partir d’une clé de hâchage (hashkey), clé calculée à partir de la requête entrante et comparée aux clés de hâchage existantes dans les plans abstraits.

Lorsqu’un plan abstrait est sauvegardé, tout caractère spécial (tabulations, espaces multiples, retour chariot, etc.) est converti en un espace unique, puis une clé de hâchage est alors calculée sur cette requête parsée simplifiée. La requête simplifiée et la clé de hachage sont alors sauvegardées dans la table sysqueryplans (avec le plan abstrait, l’identifiant du plan unique, l’identifiant du user, et l’identifiant du groupe de plans).

Lorsque l’association des plans abstraits est activée, la clé de hâchage est calculée pour la requête SQL entrante qui est analysée et simplifiée, clé de hâchage qui permet d’effectuer les comparaisons et trouver l’éventuelle existence d’un plan abstrait pour cette requête.

Activation définitive au niveau du serveur 15.0.2

Copie des plans dans le groupe par défaut ap_stdin (sp_copy_all_qplans) et activation au niveau serveur

Lorsque les plans ont été testés, vérifiés et validés, il faut copier les plans abstraits dans le groupe ap_stdin avec la procédure sp_copy_all_qplans :

exec sp_copy_all_qplans <group_name>, ap_stdin
go

exec sp_copy_all_qplans <ap_gdh>, ap_stdin
go

Pour demander ensuite à ASE d’utiliser les plans stockés dans le groupe de plans par défaut ap_stdin pour les associations , l’option "abstract plan load" doit être activée au niveau serveur, ce paramètre est dynamique :

exec sp_configure 'abstract plan load',1
go

L’activation des paramètres "abstract plan load" et/ou "abstract plan dump" rend automatiquement inactif le cache de requêtes (statement cache).

Vérification de l’exécution avec le plan abstrait

La commande "set showplan on" permet de vérifier que le plan abstrait est bien utilisé au niveau serveur en observant les performances attendues :

set showplan on
go
set fmtonly on
go
exec SP_AP_ADM_ALL_MIRT_MONTHTR1 1,'2009 JAN 16',-1
go
…
QUERY PLAN FOR STATEMENT 2 (at line 9).
Optimized using an Abstract Plan (ID : 1310992097).
…

ASE 15.0 - Les plans abstraits et les migrations