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.
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 )
Par défaut, un serveur ASE contient deux groupes de plans d'exécution par défaut : ap_stdin et ap_stdout.
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).
SQL> sp_help_qpgroup
SQL> go
Query plan groups in database 'GDH_DB'
Group GID Plans
------------------------------ ----------- -----------
ap_stdin 1 0
ap_stdout 2 0
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.
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
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
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.
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 :
sp_help_qpgroup <qp group name>, 'counts'
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.
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 :
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
) ) )
)
)
La procédure sp_export_qpgroup permet d'exporter les plans abstraits d'un groupe pour un utilisateur donné dans une table, table qui permettra le transfert des plans vers un autre serveur.
sp_export_qpgroup <user>,<qp group name>,<table>
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
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.
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 (paragraphe 3-5-) :
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 permet ensuite alors d'importer dans un groupe de plans d'exécution les plans abstraits contenus dans la table de transfert pour un utilisateur donné
sp_import_qpgroup <table>,<user>,<qp group name>
sp_import_qpgroup transfer,dbo,ap_gdh
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
query
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.
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.
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 :
sp_copy_all_qplans <group_name>, ap_stdin
go
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 :
sp_configure 'abstract plan load',1
go
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).
...
| Version | Date | Commentaires |
|---|---|---|
| 1.0 | 01/2009 | Version initiale |
| 1.1 | 01/2009 | Note importante sur la cache de requêtes et les paramètres "abstract
plan load" et "abstract plan dump". Description des associations des requêtes avec des plans abstraits via les clés de hâchage des requêtes. |
Sybase BOL :
Creating and Using abstract plans
Sybase BOL : set
fmtonly
Sybase BOL :
using fmtonly
Sybase BOL :
sp_help_qgroup