which sql statement causes sequential scans?

Hi! I've an application that stores a lot of data in my database-tables (Informix DS 9.30). As there are some problems with the performance, I analyze, among other things, the parameter seqscans (given by onstat -p) and which tables causes them (script: "select tabname, seqscan, nrows from sysptprof a, sysptnhdr b where seqscans > 100 and dbsname = 'DB' and a.partnum=b.partnum order by seqscans desc"). How can I retrieve information, which specific SQL-Statement cause the sequential scan on a table? Any help and suggestions would be appreciated. Thanks in advance! Thomas

Thomas wrote: > Hi! > > I've an application that stores a lot of data in my database-tables > (Informix DS 9.30). As there are some problems with the performance, I > analyze, among other things, the parameter seqscans (given by onstat -p) and > which tables causes them (script: "select tabname, seqscan, nrows from > sysptprof a, sysptnhdr b where seqscans > 100 and dbsname = 'DB' and > a.partnum=b.partnum order by seqscans desc"). > > How can I retrieve information, which specific SQL-Statement cause the > sequential scan on a table? > > Any help and suggestions would be appreciated. > Thanks in advance! > Thomas > > > You should start by identifying the tables which get sequential scans. The only way to identify the query would be to set explain on for all sessions... It's not easy. Don't forget that sequential scans are perfectly normal in small tables. onstat -g ppf can be used to see which tables are being sequential scan. You'll have to match the partnum with systables. Don't forget that onstat will give you the partnum in hexadecimal form. Regards.

Thomas schrieb: > Hi! > > I've an application that stores a lot of data in my database-tables > (Informix DS 9.30). As there are some problems with the performance, I > analyze, among other things, the parameter seqscans (given by onstat -p) and > which tables causes them (script: "select tabname, seqscan, nrows from > sysptprof a, sysptnhdr b where seqscans > 100 and dbsname = 'DB' and > a.partnum=b.partnum order by seqscans desc"). > > How can I retrieve information, which specific SQL-Statement cause the > sequential scan on a table? > > Any help and suggestions would be appreciated. > Thanks in advance! > Thomas > > > Maybe this is a little bit off topic but yesterday I found out that when using nvl() in a query which runs in a foreach loop causes excessive allocation of memory in my 4GL programs (RDS 7.30UC6 with IDS 7.30UC10 on Unixware 7.1.1). For each loop new memory is allocated which is freed only at the end of the program. After eleminating all nvl() functions a program which had run for more than 24 hours now runs in about half an hour. -- Roland Wintgen (Systemadministrator) EVG Elektro-Vertriebs-Gesellschaft Martens GmbH & Co KG Trompeterallee 244-246, D-41189 Moenchengladbach Tel. +49 21 66 / 55 08 23, Fax +49 21 66 / 55 08 90 www.evg.de rw@evg.de