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

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