ASE 12.0 Есть большущий набор хранимых процедур и таблиц купленной системы. Проблемы с быстродействием. Надо проверить попали ли разработчики в индексы.
Есть ли возможность SQL-запросом поймать такие места в sql-кодах процедур, где во from -е от некоторой таблицы стоит (indeх ей несоответствующий?
PS
отдельно отлаживать каждую процедуру и ловить такие warning-и нет возможности, а пользователю системы на этой БД они не отображаются.

авторсервер Sybase у вас кто-нить конфигурил? Вы сверили настройки сервера с рекондациями разработчика?
да, естественно

авторчто за система то?
самим лазить в код нехорошо - это надо призвать разработчиков к ответу
Sorry.
не хочу обижать известных разработчиков лучшей банковской системы "Д******" :)
Это тяжело. Надо ловить места самим.
А потом уж к разработчикам.

Спасибо за дельные советы!
Вообще-то имел ввиду что-то более приземленное типа глобалистического поиска по всем процедурам:

select obj.Name, ttt.text
from dbo.sysobjects obj
join dbo.syscomments ttt on ttt.id = obj.id
where ttt.text like '%from T1%(index %T1%'
order by obj.Name, ttt.colid

но естественно более сложное и с переменной T1 (имя таблицы)

PS
а где где этот клуб? :) "Sybase-Dias" :)

вот...
автор
Cache Name Status Type Config Value Run Value
default data cache Active Default 0.00 Mb 1244.68 Mb
------------ ------------
Total 0.00 Mb 1244.68 Mb
==========================================================================
Cache: default data cache, Status: Active, Type: Default
Config Size: 0.00 Mb, Run Size: 1244.68 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 1, Run Partition: 1

IO Size Wash Size Config Size Run Size APF Percent
2 Kb 61440 Kb 0.00 Mb 1244.68 Mb 10
(return status = 0)


Cache Name Config Size Run Size Overhead
------------------------ ------------- ---------- ----------
default data cache 0.00 Mb 1244.68 Mb 80.93 Mb


Memory Available For Memory Configured
Named Caches To Named Caches
-------------------- ----------------
1277.80 Mb 0.00 Mb

There is 1277.80 Mb of memory left over that will be allocated to the default cache


------------------ Cache Binding Information: ------------------

Cache Name Entity Name Type Index Name Status
---------- ----------- ---- ---------- ------
(return status = 0)



Usage information at date and time: Jan 18 2007 5:56PM.

Name # Free # Active % Active # Max Ever Used Re-used
------------------------------ ---------- ---------- ---------- --------------- -------
number of open databases 3 9 75.00 9 No


Usage information at date and time: Jan 18 2007 5:56PM.

Name # Free # Active % Active # Max Ever Used Re-used
------------------------------ ---------- ---------- ---------- --------------- -------
number of open objects 47330 2670 5.34 2814 No


Usage information at date and time: Jan 18 2007 5:56PM.

Name # Free # Active % Active # Max Ever Used Re-used
------------------------------ ---------- ---------- ---------- --------------- -------
number of open indexes 12 2988 99.60 3000 No


Usage information at date and time: Jan 18 2007 5:56PM.

Name # Free # Active % Active # Max Ever Used Re-used
------------------------------ ---------- ---------- ---------- --------------- -------
number of aux scan descriptors 200 0 0.00 29 NA


Usage information at date and time: Jan 18 2007 5:56PM.

Name # Free # Active % Active # Max Ever Used Re-used
------------------------------ ---------- ---------- ---------- --------------- -------
txn to pss ratio 3605 59 1.61 76 NA


Usage information at date and time: Jan 18 2007 5:56PM.

Name # Free # Active % Active # Max Ever Used Re-used
------------------------------ ---------- ---------- ---------- --------------- -------
number of dtx participants 500 0 0.00 0 NA

(return status = 0)


авторесли вы поддержку купили, то почему бы не заставить разработчиков исправить тормозящие места?
даже проект в д****** открыли "повышение быстродействия", но активность приходится проявлять нашему сопровождению

были на bankir.ru в форуме Диасофт ?

авторбыли на bankir.ru в форуме Диасофт ?
да знаем. регистированы. но там как-то неактивно...

автора чем Вас это не устраивает?
получили результаты и вперед потаблично
автоматизировать надо бы -
и не так просто с запросом - надо искать несовпадение
'%from T1%(index %T2%'

Спасибо! - читаю пока...

to komran

автор
select @@version
sp_configure
sp_configure 'memory'

----
Adaptive Server Enterprise/12.0.0.4/P/SWR 9846 ROLLUP/NT (IX86)/OS 4.0/1777/32bit/OPT/Fri Nov 23 02:27:07 2001

Parameter Name Default Memory Used Config Value Run Value
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
additional network memory 0 0 0 0
allow backward scans 1 0 1 1
allow nested triggers 1 0 1 1
allow procedure grouping 1 0 1 1
allow remote access 1 0 1 1
allow resource limits 0 0 0 0
allow sendmsg 0 0 0 0
allow sql server async i/o 1 0 1 1
allow updates to system tables 0 0 0 0
audit queue size 100 42 100 100
auditing 0 0 0 0
average cap size 200 0 200 200
Backup/Recovery 0 0 0 0
Cache Manager 0 0 0 0
caps per ccb 50 0 50 50
check password for digit 0 0 0 0
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
Component Integration Services 0 0 0 0
configuration file 0 0 0 D:\SYBASE\C
Configuration Options 0 0 0 0
cpu accounting flush interval 200 0 200 200
cpu grace time 500 0 500 500
curread change w/ open cursors 1 0 1 1
current audit table 1 0 1 1
deadlock checking period 500 0 5000 5000
deadlock pipe active 0 0 0 0
deadlock pipe max messages 0 0 0 0
deadlock retries 5 0 5 5
default character set id 1 0 53 53
default database size 2 0 2 2
default exp_row_size percent 5 0 5 5
default fill factor percent 0 0 0 0
default language id 0 0 0 0
default network packet size 512 #388 512 512
default sortorder id 50 0 59 59
Diagnostics 0 0 0 0
disable character set conversi 0 0 0 0
disable disk mirroring 0 0 0 0
Disk I/O 0 0 0 0
disk i/o structures 256 33 256 256
DTM Administration 0 0 0 0
dtm detach timeout period 0 0 0 0
dtm lock timeout period 300 0 300 300
dump on conditions 0 0 0 0
enable cis 1 0 1 1
enable DTM 0 0 0 0
enable HA 0 0 0 0
enable housekeeper GC 1 0 1 1
enable java 0 0 0 0
enable rep agent threads 0 0 0 0
enable sort-merge join and JTC 0 0 0 0
enable unicode conversions 0 0 0 0
enable xact coordination 1 0 1 1
Error Log 0 0 0 0
errorlog pipe active 0 0 0 0
errorlog pipe max messages 0 0 0 0
esp execution priority 8 0 8 8
esp execution stacksize 34816 0 34816 34816
esp unload dll 0 0 0 0
event buffers per engine 100 #40 100 100
event log computer name LocalSystem 0 LocalSystem LocalSystem
event logging 1 0 1 1
executable codesize + overhead 0 6159 0 6159
Extended Stored Procedure 0 0 0 0
freelock transfer block size 30 0 30 30
General Information 0 0 0 0
global async prefetch limit 10 0 10 10
global cache partition number 1 0 1 1
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
Java Services 0 0 0 0
Languages 0 0 0 0
license information 25 0 25 25
lock address spinlock ratio 100 0 100 100
lock hashtable size 2048 24 2048 2048
Lock Manager 0 0 0 0
lock scheme allpages 0 datapages datapages
lock shared memory 0 0 0 0
lock spinlock ratio 85 0 85 85
lock table spinlock ratio 20 0 20 20
lock wait period 2147483647 0 2147483647 2147483647
log audit logon failure 0 0 0 0
log audit logon success 0 0 0 0
max async i/os per engine 2147483647 0 2147483647 2147483647
max async i/os per server 2147483647 0 2147483647 2147483647
max cis remote connections 0 0 0 0
max cis remote servers 25 19 25 25
max engine freelocks 10 0 10 10
max network packet size 512 0 512 512
max number network listeners 5 329 5 5
max online engines 1 117 4 4
max parallel degree 1 0 1 1
max roles enabled per user 20 #18 20 20
max scan parallel degree 1 0 1 1
max SQL text monitored 0 222 1024 1024
maximum dump conditions 10 0 10 10
maximum failed logins 0 0 0 0
memory alignment boundary 2048 0 2048 2048
memory per worker process 1024 0 1024 1024
Memory Use 0 0 0 0
Meta-Data Caches 0 0 0 0
min online engines 1 0 1 1
minimum password length 6 0 6 6
Monitoring 0 0 0 0
msg confidentiality reqd 0 0 0 0
msg integrity reqd 0 0 0 0
Network Communication 0 0 0 0
number of alarms 40 2 40 40
number of aux scan descriptors 200 #197 200 200
number of ccbs 0 0 0 0
number of devices 10 #14 32 32
number of dtx participants 500 129 500 500
number of index trips 0 0 0 0
number of languages in cache 3 4 3 3
number of large i/o buffers 6 97 6 6
number of locks 5000 14063 100000 100000
number of mailboxes 30 1 30 30
number of messages 64 2 64 64
number of oam trips 0 0 0 0
number of open databases 12 1037 12 12
number of open indexes 500 2625 3000 3000
number of open objects 500 48192 50000 50000
number of pre-allocated extent 2 0 2 2
number of remote connections 20 33 20 20
number of remote logins 20 22 20 20
number of remote sites 10 653 10 10
number of sort buffers 500 0 500 500
number of user connections 25 13017 200 200
number of worker processes 0 0 0 0
o/s file descriptors 0 0 0 100000
O/S Resources 0 0 0 0
object lockwait timing 0 0 0 0
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
page lock promotion HWM 200 0 20000 20000
page lock promotion LWM 200 0 200 200
page lock promotion PCT 100 0 100 100
page utilization percent 95 0 95 95
Parallel Query 0 0 0 0
partition groups 1024 836 1024 1024
partition spinlock ratio 10 0 10 10
per object statistics active 0 0 0 0
permission cache entries 15 #121 15 15
Physical Memory 0 0 0 0
Physical Resources 0 0 0 0
plan text pipe active 0 0 0 0
plan text pipe max messages 0 0 0 0
print deadlock information 0 0 1 1
print recovery information 0 0 0 0
procedure cache percent 20 339390 20 20
process wait events 0 0 0 0
Processors 0 0 0 0
Q diagnostics active 0 0 0 0
read committed with lock 0 0 0 0
recovery interval in minutes 5 0 5 5
remote server pre-read packets 3 #32 3 3
Rep Agent Thread Administratio 0 0 0 0
row lock promotion HWM 200 0 20000 20000
row lock promotion LWM 200 0 200 200
row lock promotion PCT 100 0 100 100
runnable process search count 2000 0 2000 2000
secure default login guest 0 guest guest
Security Related 0 0 0 0
select on syscomments.text 1 0 1 1
shared memory starting address 0 0 23662592 23662592
size of auto identity column 10 0 10 10
size of global fixed heap 150 0 150 150
size of process object heap 150 0 150 150
size of shared class heap 1536 0 1536 1536
size of unilib cache 0 135 0 0
SQL batch capture 0 0 0 0
SQL Perfmon Integration 1 0 1 1
SQL Server Administration 0 0 0 0
sql server clock tick length 100000 0 100000 100000
sql text pipe active 0 0 0 0
sql text pipe max messages 0 0 0 0
stack guard size 16384 0 16384 16384
stack size 41984 0 41984 41984
start mail session 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
strict dtm enforcement 0 0 0 0
suspend audit when device full 1 0 1 1
syb_sendmsg port number 0 0 0 0
systemwide password expiration 0 0 0 0
tape retention in days 0 0 0 0
tcp no delay 0 0 0 0
text prefetch size 16 0 16 16
time slice 100 0 100 100
total data cache size 0 1357554 0 1357554
total memory 21504 1835008 917504 917504
txn to pss ratio 16 2434 16 16
unified login required 0 0 0 0
upgrade version 1100 0 12000 12000
use security services 0 0 0 0
User Environment 0 0 0 0
user log cache size 2048 0 4096 4096
user log cache spinlock ratio 20 0 20 20
wait event timing 0 0 0 0
xact coordination interval 60 0 60 60
xp_cmdshell context 1 0 0 0
(return status = 0)


Configuration option is not unique.


Parameter Name Default Memory Used Config Value Run Value
additional network memory 0 0 0 0
lock shared memory 0 0 0 0
memory alignment boundary 2048 0 2048 2048
memory per worker process 1024 0 1024 1024
Memory Use 0 0 0 0
Physical Memory 0 0 0 0
shared memory starting address 0 0 23662592 23662592
total memory 21504 1835008 917504 917504
(return status = 1)