分类： Sybase
2008-05-08 08:17:06
| 来源：赛迪网 作者：雅诗 |
|
这篇文章用一个实例讲解了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 200 200
caps per ccb 50 0 50 50
dump on conditions 0 0 0 0
maximum dump conditions 10 0 10 10
number of ccbs 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
allow sql server async i/o 1 0 1 1
disable disk mirroring 0 0 0 0
disk i/o structures 256 31 256 256
number of devices 10 #5 10 10
page utilization percent 95 0 95 95
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
event log computer name LocalSystem 0 LocalSystem LocalSystem
event logging 1 0 1 1
log audit logon failure 0 0 0 0
log audit logon success 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
esp execution priority 8 0 8 8
esp execution stacksize 77824 0 77824 77824
esp unload dll 0 0 0 0
start mail session 0 0 0 0
xp_cmdshell context 1 0 1 1
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
configuration file 0 0 0 /sybase/hgd
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
enable java 0 0 0 0
size of global fixed heap 300 0 300 300
size of process object heap 300 0 300 300
size of shared class heap 3072 0 3072 3072
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
default character set id 1 0 1 1
default language id 0 0 0 0
default sortorder id 50 0 50 50
disable character set conversi 0 0 0 0
enable unicode conversions 0 0 1 1
number of languages in cache 3 4 3 3
size of unilib cache 0 140 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
deadlock checking period 500 0 500 500
freelock transfer block size 30 0 30 30
lock address spinlock ratio 100 0 100 100
lock hashtable size 2048 48 2048 2048
lock scheme allpages 0 allpages allpages
lock spinlock ratio 85 0 85 85
lock table spinlock ratio 20 0 20 20
lock wait period 2147483647 0 2147483647 2147483647
max engine freelocks 10 0 10 10
number of locks 5000 2344 10000 10000
print deadlock information 0 0 1 1
read committed with lock 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
additional network memory 0 0 0 0
allow resource limits 0 0 0 0
audit queue size 100 42 100 100
average cap size 200 0 200 200
caps per ccb 50 0 50 50
deadlock pipe max messages 0 0 0 0
default network packet size 512 #505 512 512
disk i/o structures 256 31 256 256
enable rep agent threads 0 0 0 0
errorlog pipe max messages 0 0 0 0
event buffers per engine 100 #11 100 100
executable codesize + overhead 0 20261 0 20261
lock hashtable size 2048 48 2048 2048
lock spinlock ratio 85 0 85 85
max cis remote servers 25 19 25 25
max number network listeners 5 868 5 5
max online engines 1 216 1 1
max roles enabled per user 20 #22 20 20
memory per worker process 1024 0 1024 1024
number of alarms 40 3 40 40
number of aux scan descriptors 200 #258 200 200
number of ccbs 0 0 0 0
number of devices 10 #5 10 10
number of languages in cache 3 4 3 3
number of large i/o buffers 6 97 6 6
number of locks 5000 2344 10000 10000
number of mailboxes 30 1 30 30
number of messages 64 3 64 64
number of open databases 12 1239 12 12
number of open indexes 500 512 500 500
number of open objects 500 561 500 500
number of remote connections 20 86 50 50
number of remote logins 20 23 20 20
number of remote sites 10 1729 10 10
number of user connections 25 43141 250 250
number of worker processes 0 0 0 0
partition groups 1024 904 1024 1024
permission cache entries 15 #227 15 15
plan text pipe max messages 0 0 0 0
procedure cache percent 20 22426 20 20
process wait events 0 0 0 0
remote server pre-read packets 3 #83 3 3
size of global fixed heap 300 0 300 300
size of process object heap 300 0 300 300
size of shared class heap 3072 0 3072 3072
size of unilib cache 0 140 0 0
sql text pipe max messages 0 0 0 0
stack guard size 4096 #1108 4096 4096
stack size 86016 #23269 86016 86016
statement pipe max messages 0 0 0 0
total data cache size 0 89698 0 89698
total memory 47104 196608 98304 98304
txn to pss ratio 16 3692 16 16
wait event timing 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
number of open databases 12 1239 12 12
number of open indexes 500 512 500 500
number of open objects 500 561 500 500
open index hash spinlock ratio 100 0 100 100
open index spinlock ratio 100 0 100 100
open object spinlock ratio 100 0 100 100
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
Q diagnostics active 0 0 0 0
SQL batch capture 0 0 0 0
deadlock pipe active 0 0 0 0
deadlock pipe max messages 0 0 0 0
errorlog pipe active 0 0 0 0
errorlog pipe max messages 0 0 0 0
object lockwait timing 0 0 0 0
per object statistics active 0 0 0 0
plan text pipe active 0 0 0 0
plan text pipe max messages 0 0 0 0
process wait events 0 0 0 0
sql text pipe active 0 0 0 0
sql text pipe max messages 0 0 0 0
statement pipe active 0 0 0 0
statement pipe max messages 0 0 0 0
statement statistics active 0 0 0 0
wait event timing 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
additional network memory 0 0 0 0
allow remote access 1 0 1 1
allow sendmsg 0 0 0 0
default network packet size 512 #505 512 512
max network packet size 512 0 512 512
max number network listeners 5 868 5 5
number of remote connections 20 86 50 50
number of remote logins 20 23 20 20
number of remote sites 10 1729 10 10
remote server pre-read packets 3 #83 3 3
syb_sendmsg port number 0 0 0 0
tcp no delay 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
max async i/os per engine 2147483647 0 2147483647 2147483647
max async i/os per server 2147483647 0 2147483647 2147483647
o/s file descriptors 0 0 0 1024
tcp no delay 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
max parallel degree 1 0 1 1
max scan parallel degree 1 0 1 1
memory per worker process 1024 0 1024 1024
number of worker processes 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
additional network memory 0 0 0 0
lock shared memory 0 0 0 0
max SQL text monitored 0 7 0 0
shared memory starting address 0 0 0 0
total memory 47104 196608 98304 98304
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
max online engines 1 216 1 1
min online engines 1 0 1 1
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
enable rep agent threads 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
SQL Perfmon Integration 1 0 1 1
abstract plan cache 0 0 0 0
abstract plan dump 0 0 0 0
abstract plan load 0 0 0 0
abstract plan replace 0 0 0 0
allow backward scans 1 0 1 1
allow nested triggers 1 0 1 1
allow resource limits 0 0 0 0
allow updates to system tables 0 0 1 1
audit queue size 100 42 100 100
cpu accounting flush interval 200 0 200 200
cpu grace time 500 0 500 500
deadlock retries 5 0 5 5
default database size 2 0 2 2
default exp_row_size percent 5 0 5 5
default fill factor percent 0 0 0 0
enable DTM 0 0 0 0
enable HA 0 0 0 0
enable housekeeper GC 1 0 1 1
enable sort-merge join and JTC 0 0 0 0
event buffers per engine 100 #11 100 100
housekeeper free write percent 1 0 1 1
i/o accounting flush interval 1000 0 1000 1000
i/o polling process count 10 0 10 10
identity burning set factor 5000 0 5000 5000
identity grab size 1 0 1 1
license information 25 0 25 25
number of alarms 40 3 40 40
number of aux scan descriptors 200 #258 200 200
number of large i/o buffers 6 97 6 6
number of mailboxes 30 1 30 30
number of messages 64 3 64 64
number of open databases 12 1239 12 12
number of open indexes 500 512 500 500
number of open objects 500 561 500 500
number of pre-allocated extent 2 0 2 2
number of sort buffers 500 0 500 500
page lock promotion HWM 200 0 200 200
page lock promotion LWM 200 0 200 200
page lock promotion PCT 100 0 100 100
partition groups 1024 904 1024 1024
partition spinlock ratio 10 0 10 10
print deadlock information 0 0 1 1
row lock promotion HWM 200 0 200 200
row lock promotion LWM 200 0 200 200
row lock promotion PCT 100 0 100 100
runnable process search count 2000 0 2000 2000
size of auto identity column 10 0 10 10
sql server clock tick length 100000 0 100000 100000
text prefetch size 16 0 16 16
time slice 100 0 100 100
upgrade version 1100 0 12000 12000
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
allow procedure grouping 1 0 1 1
auditing 0 0 0 0
check password for digit 0 0 0 0
curread change w/ open cursors 1 0 1 1
current audit table 1 0 1 1
max roles enabled per user 20 #22 20 20
maximum failed logins 0 0 0 0
minimum password length 6 0 6 6
msg confidentiality reqd 0 0 0 0
msg integrity reqd 0 0 0 0
secure default login guest 0 guest guest
select on syscomments.text 1 0 1 1
suspend audit when device full 1 0 1 1
unified login required 0 0 0 0
use security services 0 0 0 0
Parameter Name Default Memory Used Config Value Run Value
-------------- ------- ----------- ------------ ---------
default network packet size 512 #505 512 512
number of pre-allocated extent 2 0 2 2
number of user connections 25 43141 250 250
permission cache entries 15 #227 15 15
stack guard size 4096 #1108 4096 4096
stack size 86016 #23269 86016 86016
systemwide password expiration 0 0 0 0
user log cache size 2048 0 2048 2048
user log cache spinlock ratio 20 0 20 20 |
