存档
Sybase 在ASE v15.0.2 中新增了“应用程序”跟踪这个特性。
您可以利用“应用程序”跟踪功能收集一个正在运行中的客户会话的有用诊断信息，包括：执行查询、IO 消耗等；并且ASE 会自动把收集到的跟踪信息写到一个文本文件中。可以很方便得用这个特性来诊断一些系统性能瓶颈。
您只需要知道想要跟踪的会话的SPID(Server Process ID) 就可以对那个会话进行跟踪监控。应用系统的用户只需要被授予“set tracing ”权限就可以利用“应用程序”跟踪对自己的会话信息进行跟踪。
执行“应用程序”跟踪需要具有 set tracing 的权限。主要有两类用户：第一：具有sa或者sso角色的用户（默认具有set tracing权限）；第二:被赋予了set tracing 执行权限的用户。下面会分别予以介绍。
（1） 具有管理员特权的用户（属于 sa 或者 sso 角色）
对其他用户连接会话启用“应用程序”跟踪，执行：
set tracefile "<file-path>" for <spid>
关闭刚才打开的针对其他用户连接会话的跟踪，需要执行：
set tracefile off for <spid>
备注：
(1) 针对某个用户会话启用跟踪时，需要知道它的SPID 并填写跟踪结果文件保存的路径。如果没有指定跟踪结果文件保存的路径，那么ASE 会自动保存到$SYBASE 目录下。
(2) 在一个会话中，只能有一个set tracefile 为启用状态；并且，某一个SPID 不能被多个会话同时跟踪。
(3) 当被跟踪的会话自己退出，或者跟踪被手动关闭时，ASE 会生成跟踪结果文件。
(4) 在“应用程序”跟踪的过程中，如果跟踪结果文件所在的磁盘空间被耗尽，那么ASE 会自动保存跟踪文件并结束本次“应用程序”跟踪。
(5) 用sp_helpapptrace 存储过程来查看当前正在执行中的所有应用程序跟踪信息。
（2） 被赋予“set tracing” 权限的应用程序用户
对自己的会话启用跟踪，执行：
set tracefile "<file-path>"
结束对自己的会话上的跟踪，使用：
set tracefile off
备注： 不能使用sp_helpapptrace过程来查看正在被跟踪的会话。
（3 ） 哪些信息可被用来跟踪？
在“应用程序”跟踪中可以追踪以下的set option 命令。
set show_sqltext <on/off>
set showplan <on/off>
set statistics io <on/off>
set statistics time <on/off>
set statistics plancost <on/off>
set option 的子命令也可被用来跟踪。
set option show_lop <normal/brief/long/on/off> ---显示使用的逻辑操作数量
set option show_managers <normal/brief/long/on/off> --- 显示使用的数据结构管理器的数量
set option show_log_props <normal/brief/long/on/off> --- 显示所用的逻辑属性
set option show_parallel <normal/brief/long/on/off> --- 显示并行查询优化器信息
set option show_histograms <normal/brief/long/on/off> --- 显示直方图统计信息处理过程
set option show_abstract_plan <normal/brief/long/on/off> --- 显示虚拟查询计划具体信息
set option show_search_engine <normal/brief/long/on/off> --- 显示搜索引擎的详细信息
set option show_counters <normal/brief/long/on/off> --- 显示优化器找到的计划的数量
set option show_best_plan <normal/brief/long/on/off> --- 显示最优计划信息
set option show_code_gen <normal/brief/long/on/off> --- 显示代码产生器的信息
set option show_pio_costing <normal/brief/long/on/off> --- 显示估计的物理IO数
set option show_lio_costing <normal/brief/long/on/off> --- 显示估计的逻辑IO数
set option show_elimination <normal/brief/long/on/off> --- 显示分区消除信息
（4） 例子 （使用具有sa 和sso 角色的用户）
在一个会话中执行如下的命令：
set tracefile "c:/tmp/trace-spid-16.txt" for 16
set show_sqltext on
set showplan on
go
sp_helpapptrace
go
traced_spid tracer_spid trace_file
----------- ----------- ----------------------------
16 19 c:/tmp/ase-trace-spid-16.txt
(1 row affected)
(return status = 0)
上面的输出结果表明该会话的spid为16。
在另外一个会话中，在被监控的用户会话spid=16中执行sp_helpdb过程。
set tracefile off for 16
go
sp_helpapptrace
go
traced_spid tracer_spid trace_file
----------- ----------- ----------
(0 rows affected)
(return status = 0)
跟踪结果信息如下：
================================================================================
Application Tracing report for spid -1 from application "isql"
run by login "sa" on host "TEST"
Tracing started on 2010/04/24 17:08:10.23
================================================================================
No useful sqltext available.
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is EXECUTE.
2010/04/24 17:08:10.23
No useful sqltext available.
Sproc: sp_helpdb, Line: 0
QUERY PLAN FOR STATEMENT 1 (at line 0).
STEP 1
The type of query is DECLARE.
Sproc: sp_helpdb, Line: 38
QUERY PLAN FOR STATEMENT 2 (at line 38).
STEP 1
The type of query is COND.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 40
QUERY PLAN FOR STATEMENT 3 (at line 40).
STEP 1
The type of query is SET OPTION OFF.
Sproc: sp_helpdb, Line: 43
QUERY PLAN FOR STATEMENT 4 (at line 43).
STEP 1
The type of query is SET OPTION ON.
Sproc: sp_helpdb, Line: 45
QUERY PLAN FOR STATEMENT 5 (at line 45).
STEP 1
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 47
QUERY PLAN FOR STATEMENT 6 (at line 47).
STEP 1
The type of query is COND.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 49
QUERY PLAN FOR STATEMENT 7 (at line 49).
STEP 1
The type of query is COND.
3 operator(s) under root
|ROOT:EMIT Operator (VA = 3)
|
| |RESTRICT Operator (VA = 2)(0)(0)(0)(4)(0)
| |
| | |SCALAR AGGREGATE Operator (VA = 1)
| | | Evaluate Ungrouped ANY AGGREGATE.
| | | Scanning only up to the first qualifying row.
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | master.dbo.sysmessages
| | | | Index : ncsysmessages
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Index contains all needed columns. Base table will not be read.
| | | | Keys are:
| | | | error ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
Sproc: sp_helpdb, Line: 53
QUERY PLAN FOR STATEMENT 8 (at line 53).
STEP 1
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 55
QUERY PLAN FOR STATEMENT 9 (at line 55).
STEP 1
The type of query is COND.
3 operator(s) under root
|ROOT:EMIT Operator (VA = 3)
|
| |RESTRICT Operator (VA = 2)(0)(0)(0)(4)(0)
| |
| | |SCALAR AGGREGATE Operator (VA = 1)
| | | Evaluate Ungrouped ANY AGGREGATE.
| | | Scanning only up to the first qualifying row.
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | master.dbo.sysmessages
| | | | Index : ncsysmessages
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Index contains all needed columns. Base table will not be read.
| | | | Keys are:
| | | | error ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
Sproc: sp_helpdb, Line: 59
QUERY PLAN FOR STATEMENT 10 (at line 59).
STEP 1
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 0
QUERY PLAN FOR STATEMENT 11 (at line 0).
STEP 1
The type of query is GOTO.
Sproc: sp_helpdb, Line: 0
QUERY PLAN FOR STATEMENT 12 (at line 0).
STEP 1
The type of query is GOTO.
Sproc: sp_helpdb, Line: 62
QUERY PLAN FOR STATEMENT 13 (at line 62).
STEP 1
The type of query is SET OPTION ON.
Sproc: sp_helpdb, Line: 69
QUERY PLAN FOR STATEMENT 14 (at line 69).
STEP 1
The type of query is COND.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 70
QUERY PLAN FOR STATEMENT 15 (at line 70).
STEP 1
The type of query is SELECT.
2 operator(s) under root
|ROOT:EMIT Operator (VA = 2)
|
| |SCALAR AGGREGATE Operator (VA = 1)
| | Evaluate Ungrouped COUNT AGGREGATE.
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | master.dbo.sysdatabases
| | | Index : ncsysdatabases
| | | Forward Scan.
| | | Positioning at index start.
| | | Index contains all needed columns. Base table will not be read.
| | | Using I/O Size 2 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf pages.
Sproc: sp_helpdb, Line: 73
QUERY PLAN FOR STATEMENT 16 (at line 73).
STEP 1
The type of query is SELECT.
3 operator(s) under root
|ROOT:EMIT Operator (VA = 3)
|
| |SCALAR AGGREGATE Operator (VA = 2)
| | Evaluate Ungrouped COUNT AGGREGATE.
| |
| | |RESTRICT Operator (VA = 1)(0)(0)(0)(6)(0)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | master.dbo.sysdatabases
| | | | Using Clustered Index.
| | | | Index : cdatabases
| | | | Forward Scan.
| | | | Positioning at index start.
| | | | Index contains all needed columns. Base table will not be read.
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
Sproc: sp_helpdb, Line: 80
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：tracefile showplan statistics 跟踪 会话
————————————————————————————————-
在ASE中为对象命名的时候，要避免使用系统保留关键字，以防止带来不必要的麻烦。如果是对象名称必须使用系统的自留字，请用 quoted_identifer将该名称引起来。在ASE15.x中可以使用中括号[]来强制使用自留字作为对象名称。（这点功能和sqlserver 相似！）
演示一下在ASE15.x中使用中括号引起来保留字作为列名来创建一张表。
1> select @@version
2> go
------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
Adaptive Server Enterprise/15.0.3/EBF 16738 ESD#2/P/NT (IX86)/Windows 2003/ase1503/2708/32-bit/OPT/Mon Jul 27 20:19:56 2009
(1 row affected)
1> create table testa ([create] int ,[alter] int)
2> go
1> sp_help testa
2> go
Name Owner Object_type Create_date
----- ----- ----------- -------------------
testa dbo user table Apr 24 2010 4:10PM
(1 row affected)
Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
----------- ---- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
create int 4 NULL NULL 0 NULL NULL NULL NULL 0
alter int 4 NULL NULL 0 NULL NULL NULL NULL 0
Object does not have any indexes.
No defined keys for this object.
name type partition_type partitions partition_keys
----- ---------- -------------- ---------- --------------
testa base table roundrobin 1 NULL
partition_name partition_id pages row_count segment create_date
--------------- ------------ ----- --------- ------- -------------------
testa_672718418 672718418 1 0 default Apr 24 2010 4:10PM
Partition_Conditions
--------------------
NULL
Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)
----------- ----------- ----------- --------------------------- ---------------------------
1 1 1 1.000000 1.000000
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme.
exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts
------------ -------------- ---------- ----------------- ------------ -----------
1 0 0 0 0 0
(1 row affected)
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
0 0 0
(return status = 0)
1>
在12.x中不支持使用中括号来限定的方法。
回到正题，ASE的所有保留关键字有哪些呢？
可以通过查询系统表来得到结果。 在spt_values表中type为W表示系统保留字。
执行select number,name from spt_values where type='W'这条语句可以得到所有的系统自留关键字。
1> select @@version
2> go-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------
Adaptive Server Enterprise/12.5/SWR 9616 GA/P/NT (IX86)/OS 4.0/main/1647/32-bit
/
OPT/Fri Jun 01 16:58:25 2001
(1 row affected)
1> select number,name from spt_values where type='W'
2> go
number name
----------- ----------------------------
1 add
2 all
3 alter
4 and
5 any
6 arith_overflow
7 as
8 asc
9 at
10 authorization
11 avg
12 begin
13 between
14 break
15 browse
16 bulk
17 by
18 cascade
19 case
20 char_convert
21 check
22 checkpoint
23 close
24 clustered
25 coalesce
26 commit
27 compute
28 confirm
29 connect
30 constraint
31 continue
32 controlrow
33 convert
34 count
35 create
36 current
37 cursor
38 proxy_table
39 database
40 dbcc
41 deallocate
42 declare
43 default
44 delete
45 desc
46 disk
47 distinct
48 double
49 drop
50 dummy
51 dump
52 else
53 end
54 endtran
55 errlvl
56 errordata
57 errorexit
58 escape
59 except
60 exclusive
61 exec
62 execute
63 exists
64 exit
65 exp_row_size
66 external
67 fetch
68 fillfactor
69 for
70 foreign
71 from
72 goto
73 grant
74 group
75 having
76 holdlock
77 identity
78 identity_insert
79 identity_start
80 if
81 in
82 index
83 insert
84 install
85 intersect
86 into
87 is
88 isolation
89 jar
90 join
91 key
92 kill
93 level
94 like
95 lineno
96 load
97 lock
98 max
99 max_rows_per_page
100 min
101 mirror
102 mirrorexit
103 national
104 identity_gap
105 noholdlock
106 nonclustered
107 not
108 null
109 nullif
110 numeric_truncation
111 of
112 off
113 offsets
114 on
115 once
116 online
117 only
118 open
119 option
120 or
121 order
122 over
123 partition
124 perm
125 permanent
126 plan
127 precision
128 prepare
129 primary
130 print
131 privileges
132 proc
133 procedure
134 processexit
135 public
136 raiserror
137 read
138 readpast
139 readtext
140 reconfigure
141 references
142 remove
143 reorg
144 replace
145 quiesce
146 reservepagegap
147 return
148 revoke
149 role
150 rollback
151 rowcount
152 rows
153 rule
154 save
155 schema
156 select
157 set
158 setuser
159 shared
160 shutdown
161 some
162 statistics
163 stripe
164 sum
165 syb_identity
166 syb_restree
167 syb_terminate
168 table
169 temp
170 temporary
171 textsize
172 to
173 tran
174 transaction
175 trigger
176 truncate
177 tsequal
178 union
179 unique
180 unpartition
181 update
182 use
183 replication
184 user
185 user_option
186 using
187 values
188 varying
189 view
190 waitfor
191 when
192 where
193 while
194 with
195 work
196 writetext
197 modify
198 deterministic
199 func
200 function
201 inout
202 new
203 out
204 output
205 returns
206 stringsize
(206 rows affected)
1>
同样在ASE15.x环境上执行同样的语句，发现ASE15.x比12.x版本多了一些保留字。
name number
---------------------------- -----------
xmltest 207
xmlextract 208
xmlparse 209
encrypt 210
decrypt 211
materialized 212
count_big 213
tracefile 214
scroll 215
decrypt_default 216
insensitive 217
semi_sensitive 218
xmltable 219
关于保留字，对应着有一个系统存储过程sp_checkreswords可以用来查看系统内所有使用保留字作为对象名称的对象。
sp_checkreswords语法为:
sp_checkreswords - Detects and displays identifiers that are Transact-SQL
reserved words. Checks server names, device names, database
names, segment names, user-defined datatypes, object names,
column names, user names, login names, and remote login names.
sp_checkreswords [user_name_param]
之前我用create table testa ([create] int ,[alter] int)创建了一张表。在这张表所在的数据库内部执行sp_checkreswords过程，
系统报告出来了create alter 这两个列名在用关键字。
1> sp_checkreswords
2> go
Reserved Words Used as Database Object Names for Database,testdb.
Upgrade renames sysobjects.schema to sysobjects.schemacnt.
Owner
------------------------------
dbo
Table
Reserved Word Column Names
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------- -----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
testa
alter
testa
create
-------------------------------------------------------------
-------------------------------------------------------------
Owner
------------------------------
escourt4
Table
Reserved Word Column Names
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------- -----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
syscolumn
check
syscolumn
default
-------------------------------------------------------------
-------------------------------------------------------------
Database-wide Objects
---------------------
Found no reserved words used as names for database-wide objects.
(return status = 4)
1>
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：12.0 15.0 spt_values 关键字 reserved words sp_checkreswords
————————————————————————————————-
Sybase ASE自12.5.0.3以来引入了一组监控表，称为：MonTables后者MDA tables。可以利用MDA表实现对sybase ASE的监控和诊断。监控表里面存储着对于ASE状态的统计、汇总信息的快照snapshot。我们可以像查询其他系统表（比如sysobjects、 sysindexes、syscolumns等）一样来查询这些监控表MonTables。
在12.x版本中MDA表默认是没有安装的，需要我们手动进行安装。在ASE15.x中新建数据库服务器的时候就默认装上了。
下面开始详细得介绍安装以及配置MDA的过程。
(1) 检查参数:enable cis是否启用？如果没有启用，打开该参数
sp_configure "enable cis"
go
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable cis 1 0 1 1 switch static
(1 row affected)
(2) 查看sysservers系统表中是否有loopback这一条记录，如果没有，手动添加一个指向自己的远程服务器
(注：在ASE12.5.4以及之后的版本中在创建数据库服务器的时候默认会添加一个loopback服务器。)
use master
go
sp_helpserver
go
name network_name class status id cost
----------- -------------- ------------ ------------------------------------------------------------------------ -- ----
SYB_BACKUP TEST_BS ASEnterprise timeouts, no net password encryption, writable , rpc security model A 1 NULL
SYB_EJB EJBServer ASEJB external engine auto start 2 NULL
SYB_JSAGENT TEST_JSAGENT ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 4 1000
SYB_JSTASK TEST ASEnterprise timeouts, no net password encryption, writable , rpc security model A 6 1000
TEST TEST local 0 0
TEST_XP TEST_XP RPCServer no timeouts, no net password encryption, writable , rpc security model A 3 1000
(return status = 0)
sp_addserver loopback,null,@@servername
go
-- Test this configuration: -- (NB: this step is no longer required in 15.0 ESD#2 or later) set cis_rpc_handling on go -- -- Alternatively, run: -- sp_configure 'cis rpc handling', 1 -- ...and disconnect/reconnect your session exec loopback...sp_who -- note: 3 dots! go
(3) 安装MDA系统表
在unix的shell下执行：isql -Usa -Pyourpassword -Syourservername -i$SYBASE/$SYBASE_ASE/scripts/installmontables -o$SYBASE/$SYBASE_ASE/scripts/instmontables_log.txt
在windows的命令行下执行：isql -Usa -Pyourpassword -Syourservername -i％SYBASE％\％SYBASE_ASE%\scripts\installmontables -o％SYBASE％\％SYBASE_ASE%\scripts\instmontables_log.txt
（注：ASE15.x中不需要再安装mda表）
(4) 给需要有监控权限的登录赋予mon_role角色
use master
go
grant role mon_role to sa
go
use master go grant role mon_role to sa go
(5) 检查测试基本的MDA配置信息
1> select * from master..monState
2> go
LockWaitThreshold LockWaits DaysRunning CheckPoints NumDeadlocks DiagnosticDumps Connections MaxRecovery StartDate CountersCleared
----------------- ----------- ----------- ----------- ------------ --------------- ----------- ----------- -------------------------- --------------------------
5 0 0 0 0 0 9 5 Apr 24 2010 1:34PM Apr 24 2010 1:34PM
(1 row affected)
1>
(6) 启用所有的监控配置参数
sp_configure "sql text pipe active",1
go
sp_configure "sql text pipe max messages",2000
go
sp_configure "plan text pipe active",1
go
sp_configure "plan text pipe max messages",1000
go
sp_configure "statement pipe active",1
go
sp_configure "statement pipe max messages",5000
go
sp_configure "errorlog pipe active",1
go
sp_configure "errorlog pipe max messages",1000
go
sp_configure "deadlock pipe active",1
go
sp_configure "deadlock pipe max messages",1000
go
sp_configure "wait event timing",1
go
sp_configure "process wait events",1
go
sp_configure "object lockwait timing",1
go
sp_configure "SQL batch capture",1
go
sp_configure "statement statistics active",1
go
sp_configure "per object statistics active",1
go
sp_configure "max SQL text monitored",256
go
其中参数：max SQL text monitored需要重启ASE服务器后才能生效。
(7) 重启ASE后，就可以通过查询monTables来了解ASE的监控信息了。比如：查看当前会话执行的sql语句。
1> sp_autoformat "monProcessSQLText"
2> go
SPID KPID ServerUserID BatchID LineNumber SequenceInLine SQLText
---- ------- ------------ ------- ---------- -------------- ----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------
31 2228258 1 35 1 1 SELECT SPID=right(space(80)+isnull(convert(varchar(80),SPID),'NULL'),4), KPID=right(space(80)+isnull
(convert(varchar(80),KPID),'NULL'),7), ServerUserID=right(space(80)+isnull(convert(varchar(80),ServerUserID),'NULL'),12), BatchID=right(space(80)+isnull
31 2228258 1 35 1 2 (convert(varchar(80),BatchID),'NULL'),7), LineNumber=right(space(80)+isnull(convert(varchar(80),Line
Number),'NULL'),10), SequenceInLine=right(space(80)+isnull(convert(varchar(80),SequenceInLine),'NULL'),14), SQLText=SUBSTRING(convert(varchar(255),SQLTe
31 2228258 1 35 1 3 xt),1,252) FROM monProcessSQLText
(3 rows affected)
(return status = 0)
备注：在ASE12.5.3及后续的版本中的某些罕见的情况下，配置参数"per object statistics active"会导致时间片的错误。这个bug已在ASE15.x中得到了修正。
另外，ASE的MDA监控功能对系统的整体性能是有不小的影响的，据说要损耗20%多的ASE系能。所以，不建议在生产环境上配置MDA监控表；如果启用了监控表，也一定要在不使用监控功能的时候及时关闭监控参数。执行：
sp_configure "enable monitoring",1
go
关于MDA监控表的使用，后面会有博文继续介绍...
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：监控表 启用 MDA MonTables
————————————————————————————————-
在Sybase ASE中查看或者修改配置参数有多种方法，比如：利用系统存储过程sp_configure；Sybase Central；Embarcadero DBArtisan等。
我比较喜欢用系统过程sp_configure来修改和查看参数。不管用那种方式，ASE的配置参数信息都是存储在两张系统表：sysconfigures和syscurconfigs上的。其中，表sysconfigures存储所有参数已生效 的详细信息；表syscurconfigs存储所有参数的当前 配置信息。
另外，Sybase ASE各个版本中都会备份曾经修改过的配置参数文件。在$SYBASE目录下servername .cfg是系统的配置参数文件，每次修改系统的参数内容时，ASE都会为servername .cfg做一次备份，备份文件名称用servername 加上依次递增的数字。servername .cfg中每个参数值后面的DEFAULT表示该参数没有被改变，仍然使用默认值。我们可以利用这些配置备份文件servername .001,servername .002 ... servername .040 等，来追踪参数的修改过程。如果某个参数修改后对性能反而没有提升，那么我们就可以利用之前备份的参数文件来恢复到修改之前的参数值。
参数文件比较重要的一点功能是：在master系统库崩溃的时候，可以利用cfg或者顺序参数备份文件来恢复之前的ASE的配置信息。
说了这么多，现在回到标题上的主题。如何知道ASE中哪些参数被修改过了呢？或者说：如何查看哪些参数使用了非缺省呢？
我想起来两种方法：
第一种：查看配置文件servername .cfg的所有参数，并找出取值不等于DEFAULT的所有参数。
第二种：利用sp_configure存储过程。
可能还有别的办法，比如用Sybase Central或者其它的第三方工具。我没有在这些工具上寻找查看的方法，您要是知道也可以共享一下方法。
先来看看系统存储过程sp_configure的参数内容。我利用联机的帮助信息来查看sp_configure的参数，详细的使用方法请查看sybase官方文档中的存储过程这一本。
1> sp_syntax "sp_configure"
2> go
Syntax Help
------------------------------------------------------------------------------
System Procedure
sp_configure - Displays or changes configuration parameters.
sp_configure [configname [, configvalue] | group_name
| non_unique_parameter_fragment]
["p|P|k|K|m|M|g|G"]]
Or,
sp_configure "configuration file", 0, {"write" | "read"
| "verify" | "restore" } "file_name"
(return status = 0)
（sybase ASE为15.0.3）
查看非缺省值参数的方法为：
1> sp_configure "display"
2> go
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ------------ ------------- ------------------ ----------- ---------------- -------
SQL batch capture 0 0 1 1 switch dynamic
allow updates to system tables 0 0 1 1 switch dynamic
deadlock pipe active 0 0 1 1 switch dynamic
deadlock pipe max messages 0 915 1024 1024 number dynamic
default character set id 2 0 1 1 id static
default database size 6 0 30 30 megabytes dynamic
enable encrypted columns 0 4138 1 1 switch dynamic
enable file access 0 0 1 1 switch dynamic
enable job scheduler 0 0 1 1 switch dynamic
enable monitoring 0 0 1 1 switch dynamic
enable semantic partitioning 0 0 1 1 switch dynamic
enable stmt cache monitoring 0 0 1 1 switch dynamic
errorlog pipe active 0 0 1 1 switch dynamic
errorlog pipe max messages 0 1087 1024 1024 number dynamic
event buffers per engine 100 #977 5000 5000 number static
max SQL text monitored 0 104 4096 4096 bytes static
max memory 39936 200000 100000 100000 memory pages(2k) dynamic
number of devices 10 #24 20 20 number dynamic
number of engines at startup 1 0 2 2 number static
number of histogram steps 20 0 40 40 number dynamic
object lockwait timing 0 0 1 1 switch dynamic
per object statistics active 0 0 1 1 switch dynamic
plan text pipe active 0 0 1 1 switch dynamic
plan text pipe max messages 0 382 1024 1024 number dynamic
print deadlock information 0 0 1 1 number dynamic
procedure cache size 7000 21170 10000 10000 memory pages(2k) dynamic
process wait events 0 0 1 1 switch dynamic
sql text pipe active 0 0 1 1 switch dynamic
sql text pipe max messages 0 1084 2000 2000 number dynamic
stack size 41984 #2813 43008 43008 bytes static
statement cache size 0 4 2 2 memory pages(2k) dynamic
statement pipe active 0 0 1 1 switch dynamic
statement pipe max messages 0 4182 20000 20000 number dynamic
statement statistics active 0 0 1 1 switch dynamic
wait event timing 0 0 1 1 switch dynamic
xp_cmdshell context 1 0 0 0 switch dynamic
呵呵，这个方法的确很方便啊。
最后补充一点，sp_configure过程的源代码有1400多行，可见这个过程要实现的功能还是挺多的。
通过查看该过程的源代码，找到实现sp_configure "display"这个功能的sql语句为：
select Parameter_Name = convert(char(30), name),
Default_Value = convert(varchar(11), defvalue),
Memory_Used = convert(varchar(11), c.comment),
Config_Value = isnull(b.value2, convert(char(32), b.value)),
Run_Value = isnull(c.value2, convert(char(32), c.value)),
Unit = convert(char(20), c.unit),
Type_ = convert(char(10), c.type)
from master.dbo.sysconfigures b,
master.dbo.syscurconfigs c
where
b.config = c.config
and (c.defvalue != isnull(b.value2, convert(char(32), b.value))
or
c.defvalue != isnull(c.value2, convert(char(32), c.value)))
and c.config != 114 /* Exclude option 'configuration file' */
and c.type != "read-only"
and display_level <= 10 --@user_displaylevel
order by Parameter_Name
go
查询出来的结果是一样的。利用这一堆sql语句查询出来的结果可以用作进一步的分析。
就介绍到这里吧!
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：参数 非缺省 sp_configure
————————————————————————————————-