Re: App Sessions table - update stats, -244, etc

On Thu, 09 Oct 2003 03:15:42 -0400, Corne' Cornelius wrote: Several suggestions: 1) Make sure that the web logon update is running with 'SET LOCK MODE TO WAIT <nsecs>;'. Since these update locks are short lived, lasting a fraction of a second, an 'nsecs' of 5 should eliminate all of the -244 errors. 2) As to the problem of the stats on the table going out-of-date and causing unneccessary sequential scans, the only solution for such a volatile table is to maintain the stats for it without data distributions so that the old OL5 optimizer algorithms are used. These will favor an index on ORDER BY or filter keys if present and selection between competing indexes is based solely on the index width and depth stored in sysindexes and the max/min key values stored in syscolumns. These are maintained by running 'UPDATE STATISTICS LOW FOR TABLE <tablename> DROP DISTRIBUTIONS;'. Art S. Kagel > 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