Hi Family.

I've been quiet for too long so I'll make for that with two threads.

• How to tell if there is blocking transaction at the moment.  This will be the subject of my next thread.
• How long has a transaction been running? And how long as a current query been running?  That is the subject of this thread.

I see in the output of onstat -x stuff like this:

address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
16f238028        A---- 16f1f0028        0      -                 -                 COMMIT  -        0
16f238398        A---- 16f1f0918        0      -                 -                 COMMIT  -        0
16f238708        A---- 16f1f1208        0      -                 -                 COMMIT  -        0
1823e1788        A-B-- 7f0b80c98        4      2995248:0x8d71728 2995248:0x8d71760 DIRTY   00:00:00 0
1c6551f58        A-B-- c04336a98        34     2995244:0xc067018 2995248:0x141e5d0 DIRTY   00:03:53 0

I presume, subject to correction by y'all, that only the rows with info under begin_logpos and current_logpos are active transaction; I should be able to ignore al the other lines.  And he estimated rb_time is a guess at how long it would take to roll back the transaction from the current point.  Nothing about the length of time the transaction has been running.  Going back to systrans: nada. AH, but systtrans is a view on systxptab.  and there I find column stamp:

stamp       integer,    { activity time stamp       }

Is that a count of seconds, an encoded datetime or an encoded interval, like days to seconds?

Of course, that would not necessarily lead me to a transaction that is causing a blocking checkpoint.  But it might be a predictor.

Similarly, onstat -g ses <sid> has no such timing information and neither do I see this in onstat -g sql <sid>.  How about view syssessions or better, its underlying tables.  For example, I see .sysscblst:connected integer,        { time that user connected } but again, I can't tell if this is a time stamp or number of seconds or an interval.  The other table underlying the syssessions view, sysrstcb, sure has a lotta columns but non see to be time related.

(What table tells me the same stuff as onstat -g sql?)

So I've done some research but come up almost empty.  Unless someone tells me how interprets those integer columns.

Thanks much.  Now to post the companion question, a lot simpler, I think.

Hi Family.

I've been quiet for too long so I'll make for that with two threads.

• How to tell if there is blocking transaction at the moment.  This will be the subject of my next thread.
• How long has a transaction been running? And how long as a current query been running?  That is the subject of this thread.

I see in the output of onstat -x stuff like this:

address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
16f238028        A---- 16f1f0028        0      -                 -                 COMMIT  -        0
16f238398        A---- 16f1f0918        0      -                 -                 COMMIT  -        0
16f238708        A---- 16f1f1208        0      -                 -                 COMMIT  -        0
1823e1788        A-B-- 7f0b80c98        4      2995248:0x8d71728 2995248:0x8d71760 DIRTY   00:00:00 0
1c6551f58        A-B-- c04336a98        34     2995244:0xc067018 2995248:0x141e5d0 DIRTY   00:03:53 0

I presume, subject to correction by y'all, that only the rows with info under begin_logpos and current_logpos are active transaction; I should be able to ignore al the other lines.  And he estimated rb_time is a guess at how long it would take to roll back the transaction from the current point.  Nothing about the length of time the transaction has been running.  Going back to systrans: nada. AH, but systtrans is a view on systxptab.  and there I find column stamp:

stamp       integer,    { activity time stamp       }

Is that a count of seconds, an encoded datetime or an encoded interval, like days to seconds?

Of course, that would not necessarily lead me to a transaction that is causing a blocking checkpoint.  But it might be a predictor.

Similarly, onstat -g ses <sid> has no such timing information and neither do I see this in onstat -g sql <sid>.  How about view syssessions or better, its underlying tables.  For example, I see .sysscblst:connected integer,        { time that user connected } but again, I can't tell if this is a time stamp or number of seconds or an interval.  The other table underlying the syssessions view, sysrstcb, sure has a lotta columns but non see to be time related.

(What table tells me the same stuff as onstat -g sql?)

So I've done some research but come up almost empty.  Unless someone tells me how interprets those integer columns.

Thanks much.  Now to post the companion question, a lot simpler, I think.

Hi Jacob,

checkpoints being blocked, if that really is what's happening, is a concern, but it certainly is not a transaction per se, much less its duration, that can do this.  You can have as many transactions open as you want, even for extended periods of time, and still get all kinds of checkpoints executed and completed.

So what makes you think a checkpoint is blocked?

Ahh, or is it about "blocking checkpoints"?  That's even more independent from transactions

 -> look at onstat -g ckp (or sysadmin:mon_checkpoint) -> trigger/caller of checkpoints marked as blocking.

Or is it, once again, about the blocking phase that every checkpoint, even a non-blocking one, has at its start?

BR,
 Andreas

Hi Family.

I've been quiet for too long so I'll make for that with two threads.

• How to tell if there is blocking transaction at the moment.  This will be the subject of my next thread.
• How long has a transaction been running? And how long as a current query been running?  That is the subject of this thread.

I see in the output of onstat -x stuff like this:

address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
16f238028        A---- 16f1f0028        0      -                 -                 COMMIT  -        0
16f238398        A---- 16f1f0918        0      -                 -                 COMMIT  -        0
16f238708        A---- 16f1f1208        0      -                 -                 COMMIT  -        0
1823e1788        A-B-- 7f0b80c98        4      2995248:0x8d71728 2995248:0x8d71760 DIRTY   00:00:00 0
1c6551f58        A-B-- c04336a98        34     2995244:0xc067018 2995248:0x141e5d0 DIRTY   00:03:53 0

I presume, subject to correction by y'all, that only the rows with info under begin_logpos and current_logpos are active transaction; I should be able to ignore al the other lines.  And he estimated rb_time is a guess at how long it would take to roll back the transaction from the current point.  Nothing about the length of time the transaction has been running.  Going back to systrans: nada. AH, but systtrans is a view on systxptab.  and there I find column stamp:

stamp       integer,    { activity time stamp       }

Is that a count of seconds, an encoded datetime or an encoded interval, like days to seconds?

Of course, that would not necessarily lead me to a transaction that is causing a blocking checkpoint.  But it might be a predictor.

Similarly, onstat -g ses <sid> has no such timing information and neither do I see this in onstat -g sql <sid>.  How about view syssessions or better, its underlying tables.  For example, I see .sysscblst:connected integer,        { time that user connected } but again, I can't tell if this is a time stamp or number of seconds or an interval.  The other table underlying the syssessions view, sysrstcb, sure has a lotta columns but non see to be time related.

(What table tells me the same stuff as onstat -g sql?)

So I've done some research but come up almost empty.  Unless someone tells me how interprets those integer columns.

Thanks much.  Now to post the companion question, a lot simpler, I think.

Hi Family.

I've been quiet for too long so I'll make for that with two threads.

• How to tell if there is blocking transaction at the moment.  This will be the subject of my next thread.
• How long has a transaction been running? And how long as a current query been running?  That is the subject of this thread.

I see in the output of onstat -x stuff like this:

address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
16f238028        A---- 16f1f0028        0      -                 -                 COMMIT  -        0
16f238398        A---- 16f1f0918        0      -                 -                 COMMIT  -        0
16f238708        A---- 16f1f1208        0      -                 -                 COMMIT  -        0
1823e1788        A-B-- 7f0b80c98        4      2995248:0x8d71728 2995248:0x8d71760 DIRTY   00:00:00 0
1c6551f58        A-B-- c04336a98        34     2995244:0xc067018 2995248:0x141e5d0 DIRTY   00:03:53 0

I presume, subject to correction by y'all, that only the rows with info under begin_logpos and current_logpos are active transaction; I should be able to ignore al the other lines.  And he estimated rb_time is a guess at how long it would take to roll back the transaction from the current point.  Nothing about the length of time the transaction has been running.  Going back to systrans: nada. AH, but systtrans is a view on systxptab.  and there I find column stamp:

stamp       integer,    { activity time stamp       }

Is that a count of seconds, an encoded datetime or an encoded interval, like days to seconds?

Of course, that would not necessarily lead me to a transaction that is causing a blocking checkpoint.  But it might be a predictor.

Similarly, onstat -g ses <sid> has no such timing information and neither do I see this in onstat -g sql <sid>.  How about view syssessions or better, its underlying tables.  For example, I see .sysscblst:connected integer,        { time that user connected } but again, I can't tell if this is a time stamp or number of seconds or an interval.  The other table underlying the syssessions view, sysrstcb, sure has a lotta columns but non see to be time related.

(What table tells me the same stuff as onstat -g sql?)

So I've done some research but come up almost empty.  Unless someone tells me how interprets those integer columns.

Thanks much.  Now to post the companion question, a lot simpler, I think.