Hi Y'all (with a capital Y 😀)

A few weeks ago I posted a question about transactions and, with the help of Markus, I was able to create the query and wrapping script that I was planning.  My manager liked the idea but now asked for a similar scripts to summarize session, so we can seek out sessions are may be adversely affecting overall performance, like holding a gazzillion locks or a crazy amount of I/O.  I came up with the query below, ordering by the number of locks held but a bit more, like how many threads each session is running and how much log space it is spanning.  Here is the query, plus some explanation below:

select ses.sid,
     --ses.hostname,
     --ses.username,
       ses.tty,
       ses.pid,
       (  ses.is_wlatch
        + ses.is_wlock
        + ses.is_wbuff
        + ses.is_wckpt
        + ses.is_wlogbuf
        + ses.is_wtrans)::integer in_wait,
       (case
         when is_incrit > 0 then "in_crit"
         else                    "no_crit"
       end) critical,
       prof.locksheld::integer locksheld,
       prof.pagreads::integer pagreads,
       prof.pagwrites::integer pagwrites,
       (tx.loguniq - tx.logbeg + 1)::integer log_span,
       (select count(*)
          from sysrstcb tcb
         where tcb.sid = ses.sid)::integer threads --,
     --ses.feprogram
  from syssessions ses,
       syssesprof  prof,
       systxptab   tx,
       sysrstcb    tcb
 where ses.sid     = prof.sid
   and prof.sid    = tcb.sid
   and tcb.address = tx.owner
 order by locksheld desc

I commented out some columns because I want to show sample output and these would reveal internal information.  It works OK without those.  Also, I ordered by locksheld desc because I suspect that someone holding 1.5 million locks may be affecting the overall performance.  Here is some sample output:

Notice the first 2 rows: Identical.  And some rows, (SID 782678 and 177440), are even in triplicate!  Now, I have joined 4 tables with three join conditions.  Yet, I am still getting some rows duplicated.  This means I need another join condition i.e. another column from one table needs another join condition to a table where is has already joined.  Is if the primary key of one table and the foreign key of its correspondent are both composite keys.  But WHERE?

Another key indicator here: If I comment out all lines referencing tx (syspxptab) the duplication does not happen.  This tells me the transaction table is at the root of this problem.  But what other column in there can I join to which other column in one of the other tables?

Yeah, I could SELECT UNIQUE but that just papers over the bad SQL.  I want it done right.

Read this far?  WOW, you get the "Patience of Job" award! 😁

As usual, open to ideas.  Some folks are really into these undocumented internals!

Thanks much! 

Hi Jacob,

Do set explain and get the query plan for your query

NOTE: syssessions is a view on both sysscblst and sysrstcb with the additional filter
bitval(b.flags, '0x80000') = 1;  { primary thread } # b is sysrstcb 

Whilst sid IS a unique key on sysscblst, it is NOT a unique key on sysrstcb!

[ As syssessions is view which already includes sysrstcb I would fold the view into the query and try again. ]

Just for confirmation:

select count(*) from syssessions where sid=748814 # Should be unique as only 1 sysrstcb should be marked primary
select count(*) from syssesprof  where sid=748814 # Also a view on sysrstcb but WITHOUT the filter on primary thread
select count(*) from sysscblst   where sid=748814 # Sid IS a unique key here
select count(*) from sysrstcb    where sid=748814 # Sid IS NOT unique as without the filter on primary thread

If there is 1 row on the last one:

select address from sysrstcb    where sid=748814

Then 

select tx.indx,tx.address from systxptab  where owner= <prev value> # Sid IS NOT unqiue on this table

Also

select tx.indx,tx.address,tcb.indx,tcb.address from systxptab tx,sysrstcb   tcb
where tcb.sid=748814
and tcb.address = tx.owner

with query plan

Regards,
David.

Hi Y'all (with a capital Y 😀)

A few weeks ago I posted a question about transactions and, with the help of Markus, I was able to create the query and wrapping script that I was planning.  My manager liked the idea but now asked for a similar scripts to summarize session, so we can seek out sessions are may be adversely affecting overall performance, like holding a gazzillion locks or a crazy amount of I/O.  I came up with the query below, ordering by the number of locks held but a bit more, like how many threads each session is running and how much log space it is spanning.  Here is the query, plus some explanation below:

select ses.sid,
     --ses.hostname,
     --ses.username,
       ses.tty,
       ses.pid,
       (  ses.is_wlatch
        + ses.is_wlock
        + ses.is_wbuff
        + ses.is_wckpt
        + ses.is_wlogbuf
        + ses.is_wtrans)::integer in_wait,
       (case
         when is_incrit > 0 then "in_crit"
         else                    "no_crit"
       end) critical,
       prof.locksheld::integer locksheld,
       prof.pagreads::integer pagreads,
       prof.pagwrites::integer pagwrites,
       (tx.loguniq - tx.logbeg + 1)::integer log_span,
       (select count(*)
          from sysrstcb tcb
         where tcb.sid = ses.sid)::integer threads --,
     --ses.feprogram
  from syssessions ses,
       syssesprof  prof,
       systxptab   tx,
       sysrstcb    tcb
 where ses.sid     = prof.sid
   and prof.sid    = tcb.sid
   and tcb.address = tx.owner
 order by locksheld desc

I commented out some columns because I want to show sample output and these would reveal internal information.  It works OK without those.  Also, I ordered by locksheld desc because I suspect that someone holding 1.5 million locks may be affecting the overall performance.  Here is some sample output:

Notice the first 2 rows: Identical.  And some rows, (SID 782678 and 177440), are even in triplicate!  Now, I have joined 4 tables with three join conditions.  Yet, I am still getting some rows duplicated.  This means I need another join condition i.e. another column from one table needs another join condition to a table where is has already joined.  Is if the primary key of one table and the foreign key of its correspondent are both composite keys.  But WHERE?

Another key indicator here: If I comment out all lines referencing tx (syspxptab) the duplication does not happen.  This tells me the transaction table is at the root of this problem.  But what other column in there can I join to which other column in one of the other tables?

Yeah, I could SELECT UNIQUE but that just papers over the bad SQL.  I want it done right.

Read this far?  WOW, you get the "Patience of Job" award! 😁

As usual, open to ideas.  Some folks are really into these undocumented internals!

Thanks much! 

Thanks, Dave.

The sqexplain output was bizarre and I will spare the family the details.  As to your explanation of duplicates:

I was beginning to suspect that my issue might have something to do with the fact that one session is often associated with multiple threads.  (Try combining a parallel sort with PDQPRIORITY set high!)  I don't believe this was the issue.  By adding some tx-releated columns to the query, I discovered that one thread i.e. one rstcb structure - can be associated with more than one transaction structure.

What made me look down that path?  I finally gave up on finding that mystery other column and added the "unique" to the query.  I still got what seemed to be duplicate rows.  Almost, that is.  They were identical in every column but "log_span".  In one case, for example, the log span was 1 but in the next record it was 5.  I don't want to see the lower number.

Thus: Here's how I think I have the query right:

drop table if exists temp_sessions;

--unload to /tmp/sessions.pip
select unique
       ses.sid,
       ses.hostname,
       ses.username,
       ses.tty,
       ses.pid,
       (  ses.is_wlatch
        + ses.is_wlock
        + ses.is_wbuff
        + ses.is_wckpt
        + ses.is_wlogbuf
        + ses.is_wtrans)::integer in_wait,
       (case
         when is_incrit > 0 then "in_crit"
         else                    "no_crit"
       end) critical,
       prof.locksheld::integer locksheld,
       prof.pagreads::integer pagreads,
       prof.pagwrites::integer pagwrites,
       (tx.loguniq - tx.logbeg + 1)::integer log_span,
       (select count(*)
          from sysrstcb tcb
         where tcb.sid = ses.sid)::integer threads,
       ses.feprogram
  from syssessions ses,
       syssesprof  prof,
       systxptab   tx,
       sysrstcb    tcb
 where ses.sid     = prof.sid
   and prof.sid    = tcb.sid
   and tcb.address = tx.owner
   and hostname is not null
--order by locksheld desc
  into temp temp_sessions
;
select * from temp_sessions t1
 where log_span = (select max(log_span)
                     from temp_sessions t2
                    where t2.sid = t1.sid)
 order by locksheld desc
;

That is: Select all the info I want into a temp table, which comes with some excelsior. Then filter it so that I see only the row with the largest log-span for that SID.  This takes a few seconds to start spitting out data.  But you need a REALLY wide terminal window with a tiny font.  As a practical matter you may want to see only the top 50 or so lock-holders.

Thanks for the lead, Dave,

Hi Jacob,

Do set explain and get the query plan for your query

NOTE: syssessions is a view on both sysscblst and sysrstcb with the additional filter
bitval(b.flags, '0x80000') = 1;  { primary thread } # b is sysrstcb 

Whilst sid IS a unique key on sysscblst, it is NOT a unique key on sysrstcb!

[ As syssessions is view which already includes sysrstcb I would fold the view into the query and try again. ]

Just for confirmation:

select count(*) from syssessions where sid=748814 # Should be unique as only 1 sysrstcb should be marked primary
select count(*) from syssesprof  where sid=748814 # Also a view on sysrstcb but WITHOUT the filter on primary thread
select count(*) from sysscblst   where sid=748814 # Sid IS a unique key here
select count(*) from sysrstcb    where sid=748814 # Sid IS NOT unique as without the filter on primary thread

If there is 1 row on the last one:

select address from sysrstcb    where sid=748814

Then 

select tx.indx,tx.address from systxptab  where owner= <prev value> # Sid IS NOT unqiue on this table

Also

select tx.indx,tx.address,tcb.indx,tcb.address from systxptab tx,sysrstcb   tcb
where tcb.sid=748814
and tcb.address = tx.owner

with query plan

Regards,
David.

Hi Y'all (with a capital Y 😀)

A few weeks ago I posted a question about transactions and, with the help of Markus, I was able to create the query and wrapping script that I was planning.  My manager liked the idea but now asked for a similar scripts to summarize session, so we can seek out sessions are may be adversely affecting overall performance, like holding a gazzillion locks or a crazy amount of I/O.  I came up with the query below, ordering by the number of locks held but a bit more, like how many threads each session is running and how much log space it is spanning.  Here is the query, plus some explanation below:

select ses.sid,
     --ses.hostname,
     --ses.username,
       ses.tty,
       ses.pid,
       (  ses.is_wlatch
        + ses.is_wlock
        + ses.is_wbuff
        + ses.is_wckpt
        + ses.is_wlogbuf
        + ses.is_wtrans)::integer in_wait,
       (case
         when is_incrit > 0 then "in_crit"
         else                    "no_crit"
       end) critical,
       prof.locksheld::integer locksheld,
       prof.pagreads::integer pagreads,
       prof.pagwrites::integer pagwrites,
       (tx.loguniq - tx.logbeg + 1)::integer log_span,
       (select count(*)
          from sysrstcb tcb
         where tcb.sid = ses.sid)::integer threads --,
     --ses.feprogram
  from syssessions ses,
       syssesprof  prof,
       systxptab   tx,
       sysrstcb    tcb
 where ses.sid     = prof.sid
   and prof.sid    = tcb.sid
   and tcb.address = tx.owner
 order by locksheld desc

I commented out some columns because I want to show sample output and these would reveal internal information.  It works OK without those.  Also, I ordered by locksheld desc because I suspect that someone holding 1.5 million locks may be affecting the overall performance.  Here is some sample output:

Notice the first 2 rows: Identical.  And some rows, (SID 782678 and 177440), are even in triplicate!  Now, I have joined 4 tables with three join conditions.  Yet, I am still getting some rows duplicated.  This means I need another join condition i.e. another column from one table needs another join condition to a table where is has already joined.  Is if the primary key of one table and the foreign key of its correspondent are both composite keys.  But WHERE?

Another key indicator here: If I comment out all lines referencing tx (syspxptab) the duplication does not happen.  This tells me the transaction table is at the root of this problem.  But what other column in there can I join to which other column in one of the other tables?

Yeah, I could SELECT UNIQUE but that just papers over the bad SQL.  I want it done right.

Read this far?  WOW, you get the "Patience of Job" award! 😁

As usual, open to ideas.  Some folks are really into these undocumented internals!

Thanks much!