关于SYBASE ASE各个版本的语句监控实现

众所周知,系统调优很重要的一个方面是应用的调优. 而基于数据库的应用中,最重要的工作之一就是进行SQL语句的调优. SYBASE ASE以前的版本中,很难实现语句监控,从而进一步分析,优化相关的语句,也就很难对已经投入生产的应用进行进一步调优. ASE12.5.0.3开始,增加了很多的系统监控表,称为MDA table,实际上是一些proxy table. 通过这些表可以完整地进行了系统监控,包括数据缓存监控,存储过程缓存监控,索引使用监控等,其中非常重要的一个部分是SQL语句的监控,它弥补了以前ASE性能调优重要的一个方面.详细内容大家可以参照sybase的官方文档:http://manuals.sybase.com/onlinebooks/group-as/asg1251c/monitoringzh(ASE12.5.1性能调优之监控下载链接). 而对于最新的版本ASE15,SYBASE则提供了更为方便的工具.本方档就ASE三种版本就SQL语句监控方法做一个总结.

一: ASE12.5.0.3之前语句监控

ASE12.5.0.3之前没有MDA监控表,只能通过dbcc 命令逐个查看当前用户正在执行的语句,这种方法也适用于其它版本:
1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> dbcc sqltext(1029)
2> go
SQL Text: select
btypeid,cname,atypeid,bureauid,deptid,llevel,baseval,range,remark from
e_jh_log_btype where (deptid = '1100121' and llevel = 2) or (deptid = '0' and
bureauid = '1100' and llevel = 1) or (bureauid = '0' and llevel = 0)

DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

其中1029是用户连接的sid.这种监控的方法能找到某个用户正在执行的语句. 但这种方法对于希望找出系统中最消耗资源的SQL语句,怕是无能为力. 对于由于不良的SQL语句导致的系统性能低下,DBA简直无能无力.

二: ASE12.5.0.3及以后版本的监控

ASE12.5.0.3开始,提供了一整套用于系统监控的proxy table. 默认安装下,这些新的监控表数据库中是没有的,需要执行以下步骤安装,这里假设用户的server名为SYBASE:

1. 增加loopback服务
1>use master
2>go
1>sp_addserver loopback, null, SYBASE
2>go

2. 在CMD下,执行脚本installmontables,安装MDA表,这些表会安装在master数据库中.

isql -Usa -P -Ssa -i%SYBASE%ASE-12_5scriptsinstallmontables

3、给sa授权mon_role(监控用户)权限
1>grant role mon_role to sa
2>go

4, 配置相关参数

用sa登录,配置以下参数:

1>sp_configure 'max SQL text monitored',2000
2>go
1>sp_configure 'SQL batch capture',1
2>go
1>sp_configure 'sql text pipe max messages',30000
2>go
1>sp_configure 'sql text pipe active',1
2>go
1>sp_configure 'statement statistics active',1
2>go
1>sp_configure 'per object statistics active',1
2>go
1>sp_configure 'statement pipe max messages',30000
2>go
1>sp_configure 'statement pipe active',1
2>go
1>sp_configure 'enable monitoring',1
2>go
1>shutdown
2>go

上述参数中'max SQL text monitored'是静态的，配置完后，需要重新启动后才能生效。这些参数的具体含义大家看一下通过上面的链接下载的手册就知道了.其中sp_configure 'enable monitoring',1是启用监控,启用监控对系统的性能会有一定的影响,所以在不需要监控的时候,把它置为0,关闭监控:
sp_configure 'enable monitoring',0
go

5. 重新启动后,就可以监控了.
监控语句用会用到四个监控表, monProcessStatement, monProcessSQLText, monSysStatement, monSysSQLText:
monProcessStatement: 提供当前正在执行的语句的信息.
monProcessSQLText: 提供当前正在执行的 SQL 文本.
monSysStatement: 提供有关最近执行的语句的统计信息. 每次查询后都会被清空.
monSysSQLText: 提供已经执行的最新 SQL 文本或当前正在执行的 SQL 文本。每次查询后都会被清空.

监控SQL示例：

a. 查看当前正在执行的sql语句的情况,显示逻辑读的多的在前面.可以看到具体的语句及CPUtime, WaitTime,MemUsageKB,PhysicalReads,LogicalReads,PagesModified等信息,含义请查看上述手册:

select DBID, S.SPID, T.SequenceInLine, CpuTime, WaitTime, MemUsageKB, PhysicalReads, LogicalReads, PagesModified,
SQLText from master..monProcessStatement S, master..monProcessSQLText T where S.SPID = T.SPID order by LogicalReads desc

用户可以根据需要选择其它的列进行排序,找出当前正在运行的消耗资源的SQL语句.

b. 查看最近一段时间内的SQL语句执行情况.

为方便语句监控,我建立了四个存储过程, 这里假设用户库名中TEST:


use TEST
go

/*用来在用户库中中建立两个用来存放monSysStatement,monSysSQLText的固定表 */
create procedure sp_monCreate
as
if exists (select 1 from sysobjects where name='temp_monSysStatement')
exec ('drop table temp_monSysStatement')

if exists (select 1 from sysobjects where name='temp_monSysSQLText')
exec ('drop table temp_monSysSQLText')
select * into temp_monSysStatement from master..monSysStatement where 1=2
select * into temp_monSysSQLText from master..monSysSQLText where 1=2
go

/*生成固定表 */
exec sp_monCreate
go

/*清除固定表的数据 */
create procedure sp_monPurge
as
truncate table temp_monSysStatement
truncate table temp_monSysSQLText
go


/*把monSysStatement,monSysSQLText表中的数据保存到两个固定表中 */
create procedure sp_monCollect
as
insert temp_monSysStatement select * from master..monSysStatement
insert temp_monSysSQLText select * from master..monSysSQLText
go

/* 生成语句监控结果,这里按LogicalReads排序,可以按自己的需要调整. 数据库版本在ASE12.5.3之上时,可以在语句前面加上top n, 得到前几条*/
create procedure sp_mon
as
select /* top 1000 */ DBID,S.SPID,T.SequenceInBatch,CpuTime,WaitTime,MemUsageKB,PhysicalReads,LogicalReads,PagesModified,
SQLText from TEST..temp_monSysStatement S,TEST..temp_monSysSQLText T where S.SPID=T.SPID and S.BatchID=T.BatchID order by LogicalReads desc
go

监控过程如下:

(1). 执行sp_monPurge,清除上次监控时留下的数据
(2). 启用监控 sp_configure 'enable monitoring',1
(3). 每隔一小段时间(这个时间内系统运行的SQL语句不超过前面设置的'statement pipe max messages'值,否则会有语句被清掉,不能监控到.)
(4). 停止监控 sp_configure 'enable monitoring',0
(5). 在系统相对空闲时,生成监控的结果.监控时间长时,两个表中的数据量会很大,生成结果会消耗很多资源,建议在不影响生产的情况下生成. 同时,监控的SQL语句很多时,建议先在 temp_monSysStatement,temp_monSysSQLText上分别建立索引(SPID,BatchID).

监控到的语句很长时,结果集中是分多行的,这很容易看出来.(CpuTime,WaitTime,MemUsageKB,PhysicalReads,LogicalReads等完全一样的).

从上面的过程中可以看出来,通过MDA来进行语句的监控还是有些不方便的,但至少我们有了一个可以进行SQL语句监控,进而进一步分析调优的手段.


三. ASE15的语句监控

ASE15的发布已经快有两年的时间了,目前已经在一些生产环境使用. 它在语句监控这方面,确实给用户提供了极大的方便.
ASE15中,每一个database中都有一个视图 sysquerymetrics. 直接通过查询这个视图就可以得到MDA语句监控的结果,而且它对于同样的语句,它自动进行了合并处理,生成执行次数,各项指标的最大值,最小值与平均值,比MDA更加的合理且灵活.
要使用这个视图,需要将参数'enable sysmetrics capture'设置为1:
1>sp_configure 'enable sysmetrics capture',1
2>go

同样,使用这个功能对性能会有一定的影响,建议只有需要监控的时候开启,其它时间把它禁止. 更为严重的是,长时间启用这个功能,这个视图使用到的另外一个表sysqueryplans的cluster索引经常会损坏,我多次在不同的环境中碰到这个问题,导致这个系统表的索引需要重建,并需要重新启动数据库服务.所以建议大家,在不需要SQL语句监控的时候,禁止这个功能. 如何重建sysqueryplans表的索引,我会另外写一个文档.

大家看看sysquerymetrics表的列就知道它能帮助我们做什么了,这里也就不再示例了:

列名 数据类型 说明
uid int 用户 ID
gid int 组 ID
hashkey int SQL 查询文本上的散列键
id int 唯一 ID
sequence smallint null 在 SQL 文本需要多行的情况下行的序列号
exec_min int null 最短执行时间
exec_max int null 最长执行时间
exec_avg int null 平均执行时间
elap_min int null 最短经历时间
elap_max int null 最长经历时间
elap_avg int null 平均经历时间
lio_min int null 最小逻辑 IO
lio_max int null 最大逻辑 IO
lio_avg int null 平均逻辑 IO
pio_min int null 最小物理 IO
pio_max int null 最大物理 IO
pio_avg int null 平均物理 IO
cnt int null 已经执行的查询次数
abort_cnt int null 查询由于超过资源限制而被资源管理器 (Resource Governor) 中止的次数
qtext varchar(255) null 查询文本

此外,ASE15提供了一个存储过程sp_metrics,用来清空,备份sysquerymetrics(实际上是sysqueryplans表).具体见SYBASE官方网站可下载的手册.

从语句监控的三种实现来看, ASE15无疑提供了极为方便的手段,这对SYBASE DBA们来说,无疑是个很好的消息.