Hi, I made a different test and found a new issue:
This time worker2 starts 1.5 seconds after worker1.
Please see:

08:49:44: Worker1: Start (table t1)

Snapshot: 08:49:45
attach=76, tran=679, stmt=04, text=select count(*) from t1

08:49:45: Worker 2: Start (table t2)

Snapshot: 08:49:46
attach=77, tran=682, stmt=04, text=select count(*) from t2
attach=76, tran=679, stmt=14, text=select count(*) from t1

08:49:49: Worker 2: End

Snapshot: 08:49:49
attach=76, tran=679, stmt=11, text=select count(*) from t1

08:49:51: Worker 1: End


Worker1 starts and the statement gets id 4.
When worker2 starts, the statement that worker1 issued is changed from
id 4 to 14 and id 4 is reassigned to the statement that worker2 just
issued.
When worker2 ends (the table it is querying is much smaller), the
statement that worker1 issues is again reassigned, this time to id 11.

I´m looking for a way to uniquely identify a statement across snapshots.
First I thought of using attachment_id + text but that won´t work. If
the same attachment issues the same text repeatedly (it will probably
get the same attachment id), I may conclude its only one statement.

What do you think?
Any other way to truly uniquely identify a statement across monitoring
snapshots?

Thanks again,
Douglas Tosi

On Tue, Jun 17, 2008 at 5:38 PM, Douglas Tosi <douglasht@...> wrote:
> Hello!
>
> Please allow me try again and explain the problems I´m having with
> mon$statement_id.
> Today I created a reproducible test case.
> I can send you if needed. It´s a small c# program but you can also
> reproduce using isql (and fast fingers).
> Here´s what happens:
>
> I open two connections to the database. Connections A and B.
> Now each of these connections will start working at the same time and
> do the following:
>
> open a read-commited transaction
> select count(*) from a different table
> commit
>
> As each connection will query a different table, the execution times
> will be different and that´s important for the test. One of them must
> finish first.
> I´m using tables T1 with 2M record and T2 with 200K records.
>
> The following select is used to poll the monitoring table every second:
>
> select
> m.mon$attachment_id aid, m.mon$transaction_id tid,
> m.mon$statement_id sid, m.mon$state state,
> m.mon$sql_text sql_text, m.mon$stat_id stat_id
> from mon$statements m
> where m.mon$attachment_id <> current_connection and
> m.mon$sql_text is not null
>
> Here are the results:
>
> 16:54:32: Worker 1: Start (table t1)
> 16:54:32: Worker 2: Start (table t2)
>
> Snapshot: 16:54:33
> stmt=04, text=select count(*) from t2
> stmt=14, text=select count(*) from t1
>
> Snapshot: 16:54:36
> stmt=04, text=select count(*) from t2
> stmt=14, text=select count(*) from t1
>
> 16:54:36: Worker 2: End
>
> Snapshot: 16:54:37
> stmt=11, text=select count(*) from t1
>
> Snapshot: 16:54:38
> stmt=11, text=select count(*) from t1
>
> 16:54:39: Worker 1: End
>
> Both worker threads start and the snapshots are ok.
> But when worker 2 ends, the statement that worker 1 issued (that is
> still running) appears as id 11 instead of 14 as before.
> Worker 1 did nothing to cause this change.
>
> If this is as designed, please let´s discuss if it can be reviewed and
> perhaps changed for a future version.
>
> Let me tell you why I believe this is important
>
> I´m gladly taking snapshots of the monitoring tables, looking for
> something wrong.
> Then I see there is a certain statement id 15 that is causing trouble.
> What do I do? Try to gather more detail about statement 15.
> Maybe I´ll find where its coming from and can go talk to the person responsible.
> Maybe I´ll wait a little while and check back to see if it´s still
> there causing trouble.
> But if its id keeps changing every snapshot, how can I do that? How
> can I "zoom in" that statement once I found it?
>
> Thanks,
> []s
> Douglas Tosi
>