App Sessions table - update stats, -244, etc
Hi, I have a table on an informix database (IDS 2000 Version 9.21.HC2) that stores login sessions info for a website. The users session timeout gets updated everytime he access a page and once a day the stale sessions are removed so there is quite a lot of UPDATE/DELETE'ing on the table. The problem i have, is that i ofter get -244 errors when SELECT'ing from the table, which seems to be caused by a SEQUENCIAL SCAN and locked rows. If i run UPDATE STATISTICS HIGH FOR TABLE sessions, and check sqexplain.out, the SELECT uses INDEX PATH and all is fine for a while (about a day). How can i optimize this table so that i don't have to run UPDATE STATS for it so often ? the table isn't big (avg. rows per day: 150, rowsize: 104) Thanks ! Corne' :wq
I suppose you're using page locking. Try using row locking: ALTER TABLE <table name> LOCK MODE ROW; and/or try to end your updating sessions as soon as possible (small work units). Gorazd "Corne' Cornelius" <corne@no-domain-no-spam.com> wrote in message news:CAqdnZCVb6KllxiiXTWJhg@is.co.za... > Hi, > > I have a table on an informix database (IDS 2000 Version 9.21.HC2) that > stores login sessions info for a website. > > The users session timeout gets updated everytime he access a page and > once a day the stale sessions are removed so there is quite a lot of > UPDATE/DELETE'ing on the table. > > The problem i have, is that i ofter get -244 errors when SELECT'ing from > the table, which seems to be caused by a SEQUENCIAL SCAN and locked > rows. If i run UPDATE STATISTICS HIGH FOR TABLE sessions, and check > sqexplain.out, the SELECT uses INDEX PATH and all is fine for a while > (about a day). > > How can i optimize this table so that i don't have to run UPDATE STATS > for it so often ? the table isn't big (avg. rows per day: 150, rowsize: > 104) > > Thanks ! > Corne' > :wq >
The table is allready in Row locking mode. not sure what you mean by "end your updating sessions as soon as possible". it runs the UPDATE query on the timeout field, and then continues with the rest of the app. Gorazd Hribar Rajteric wrote: > I suppose you're using page locking. Try using row locking: > ALTER TABLE <table name> LOCK MODE ROW; > and/or try to end your updating sessions as soon as possible (small work > units). > > Gorazd > > "Corne' Cornelius" <corne@no-domain-no-spam.com> wrote in message > news:CAqdnZCVb6KllxiiXTWJhg@is.co.za... > >>Hi, >> >>I have a table on an informix database (IDS 2000 Version 9.21.HC2) that >>stores login sessions info for a website. >> >>The users session timeout gets updated everytime he access a page and >>once a day the stale sessions are removed so there is quite a lot of >>UPDATE/DELETE'ing on the table. >> >>The problem i have, is that i ofter get -244 errors when SELECT'ing from >>the table, which seems to be caused by a SEQUENCIAL SCAN and locked >>rows. If i run UPDATE STATISTICS HIGH FOR TABLE sessions, and check >>sqexplain.out, the SELECT uses INDEX PATH and all is fine for a while >>(about a day). >> >>How can i optimize this table so that i don't have to run UPDATE STATS >>for it so often ? the table isn't big (avg. rows per day: 150, rowsize: >>104) >> >>Thanks ! >>Corne' >>:wq >> > >
On 09 Okt 2003, Corne' Cornelius <corne@no-domain-no-spam.com> wrote: > The table is allready in Row locking mode. > > not sure what you mean by "end your updating sessions as soon as > possible". it runs the UPDATE query on the timeout field, and > then continues with the rest of the app. maybe a "commit" helps here Christian -- #include <std_disclaimer.h> /* The opinions stated above are my own and not necessarily those of my employer. */
[clip] > not sure what you mean by "end your updating sessions as soon as > possible". it runs the UPDATE query on the timeout field, and then > continues with the rest of the app. [clip] What I ment was that you could try to make updating of session table in separate transaction and then open another transaction for other updates if needed. Gorazd