Sessions and transactions

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: sid tty pid in_wait critical locksheld pagreads pagwrites log_span threads 748814 /dev/pts/510 5267 0 no_crit 455 20214 97 1 1 748814 /dev/pts/510 5267 0 no_crit 455 20214 97 14 1 808419 -1 0 in_crit 422 36 193 1 1 455221 20012 0 no_crit 315 222558 314480 1 1 455221 20012 0 no_crit 315 222558 314480 1 1 782678 /dev/pts/2252 25363 0 no_crit 196 220 105 1 1 782678 /dev/pts/2252 25363 0 no_crit 196 220 105 7 1 782678 /dev/pts/2252 25363 0 no_crit 196 220 105 1 1 754343 /dev/pts/460 21731 0 no_crit 180 2059 142 1 1 754343 /dev/pts/460 21731 0 no_crit 180 2059 142 1 1 174440 /dev/pts/3 15224 0 no_crit 180 65446 13258 2 1 174440 /dev/pts/3 15224 0 no_crit 180 65446 13258 1 1 174440 /dev/pts/3 15224 0 no_crit 180 65446 13258 1 1 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! ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

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 threadIf there is 1 row on the last one: select address from sysrstcb where sid=748814Then select tx.indx,tx.address from systxptab where owner= <prev value> # Sid IS NOT unqiue on this tableAlso 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. ------------------------------ David Williams Senior Database Platform Engineer Flutter London ------------------------------

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, ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

Hi, " I discovered that one thread i.e. one rstcb structure - can be associated with more than one transaction structure. " I did wonder about that, how is this happening? Is the application using SAVEPOINTs? Regards, David. ------------------------------ David Williams Senior Database Platform Engineer Flutter London ------------------------------