个人经验总结：Sybase数据库性能优化

     这篇论坛文章（赛迪网技术社区）用一个实例讲解了Sybase数据库性能优化的具体过程，具体内容请参考下文：

共享锁

sp_getapplock 锁定应用程序资源

sp_releaseapplock 为应用程序资源解锁

SET LOCK_TIMEOUT 1800 锁超时期限设置

sp_configure 'deadlock checking period',5000 设置锁检测周期

sp_configure 'lock wait period',5000 设置锁的等待时间

sp_setrowlockpromote 设置基本个表的最大行锁升级数(锁数)

sp_setrowlockpromote 'TABLE',TREECODE,500,500,100

sp_setrowlockpromote 'TABLE',LCD05,500,500,100

[Lock Manager]

number of locks = 50000 #锁数

deadlock checking period = DEFAULT

freelock transfer block size = DEFAULT

max engine freelocks = DEFAULT

lock spinlock ratio = DEFAULT

lock hashtable size = DEFAULT

lock scheme = DEFAULT

lock wait period = DEFAULT

read committed with lock = DEFAULT

当很多事务同时访问同一个数据库时，会加剧锁资源争夺，严重时事务之间会发生死锁。可用sp_object_stats查明死锁位置。该过程报告资源争夺最激烈的10张表、一个数据库中资源争夺的表和单个表的争夺情况。语法为sp_object_stats interval [, top_n [, dbname [, objname [, rpt_option ]]]]，查看锁争夺情况只需设置interval为“hh:mm:ss”。如果显示每种锁的争夺程度超过15%，应该改变加锁方式，比如表的全页锁改成数据页锁，数据页锁改成数据行锁等。

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

allow remote access 1 0 1 1

print recovery information 0 0 0 0

recovery interval in minutes 5 0 5 5

tape retention in days 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

global async prefetch limit 10 0 10 10

global cache partition number 1 0 1 1

memory alignment boundary 2048 0 2048 2048

number of index trips 0 0 0 0

number of oam trips 0 0 0 0

procedure cache percent 20 22426 20 20

total data cache size 0 89698 0 89698

total memory 47104 196608 98304 98304

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

cis bulk insert batch size 0 0 0 0

cis connect timeout 0 0 0 0

cis cursor rows 50 0 50 50

cis packet size 512 0 512 512

cis rpc handling 0 0 0 0

enable cis 1 0 1 1

max cis remote connections 0 0 0 0

max cis remote servers 25 19 25 25

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

dtm detach timeout period 0 0 0 0

dtm lock timeout period 300 0 300 300

enable xact coordination 1 0 1 1

number of dtx participants 500 149 500 500

strict dtm enforcement 0 0 0 0

txn to pss ratio 16 3692 16 16

xact coordination interval 60 0 60 60

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

average cap size 200 0 20