v$session和v$transaction的关系

一个tom论坛的话题：

以备以后研究：

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4226409800346774588

You Asked Hi Tom -  Just happened to notice this weird behavior at my client place.. Part of my streams (capture) troubleshooting, I was tracing back the duration of active transactions.  I just had a simple script polling v$transaction polling info and spooling the duration into a text file (duration = sysdate - start_date) for active transactions. Then I added v$session to the query to get session info (v$session.taddr = v$transaction.addr used as join).. I doubted a few culprit rows were missing and I made it at outer join and now I am getting few more transactions without a session.  Question is.  a) have you seen this behavior of transaction being active in v$transaction without any matching row in v$session.. Any clues on what could be the root cause would be much appreciated.  ( Environment: 3 tier archiceture with transaction manager sitting somewhere in middle tier (Weblogic). All are XA transactions from application and database calls are mostly distributed transactions )  Thanks in advance & Happy holidays.  PS: I just happened to see "Submit your question" and am sending this.. I will get access to server on Monday and can upload the actual monitoring script as is, if needed.  and we said... if a session has more than one transaction going - and I believe you'll see that a lot in XA as they are an external resource manager and some fairly odd things take place due to that - then there isn't a single transaction associated with a session - but many and the simple data model of a one to one relation doesn't work  ops$tkyte%ORA11GR2> create table t ( x int ); Table created. ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> create or replace procedure p( p_count in number ) 2 as 3 pragma autonomous_transaction; 4 begin 5 if ( p_count = 0 ) 6 then 7 dbms_output.put_line( 'at the end: ' ); 8 for x in ( select to_char(rownum,'09') || ') transaction ' || rawtohex(vt.addr) || 9 ' associated with session (' || vs.sid || ', ' || vs.serial# || ')' data 10 from v$transaction vt, v$session vs 11 where rawtohex(vt.addr) = vs.taddr (+) ) 12 loop 13 dbms_output.put_line( x.data ); 14 end loop; 15 for x in ( select username || ' - ' || taddr data 16 from v$session 17 where username = user ) 18 loop 19 dbms_output.put_line( x.data ); 20 end loop; 21 else 22 insert into t values ( p_count ); 23 p( p_count-1 ); 24 end if; 25 26 commit; 27 end; 28 / Procedure created. ops$tkyte%ORA11GR2> insert into t values ( 0 ); 1 row created. ops$tkyte%ORA11GR2> select 'transaction ' || rawtohex(vt.addr) || ' associated with session (' || vs.sid || ', ' || vs.serial# || ')' data 2 from v$transaction vt, v$session vs 3 where rawtohex(vt.addr) = vs.taddr (+); DATA ------------------------------------------------------------------------------- transaction 26C64010 associated with session (74, 381) ops$tkyte%ORA11GR2> select username || ' - ' || taddr data 2 from v$session 3 where username = user; DATA ----------------------------------------- OPS$TKYTE - 26C64010 ops$tkyte%ORA11GR2> exec p(10); at the end: 01) transaction 26C64010 associated with session (, ) 02) transaction 26C5C11C associated with session (, ) 03) transaction 26C5D408 associated with session (, ) 04) transaction 26C64664 associated with session (, ) 05) transaction 26C5B484 associated with session (, ) 06) transaction 26C5CDB4 associated with session (, ) 07) transaction 26C5A1A8 associated with session (, ) 08) transaction 26C5BAD8 associated with session (, ) 09) transaction 26C5C770 associated with session (, ) 10) transaction 26C5AE40 associated with session (, ) 11) transaction 26C5DA4C associated with session (, ) OPS$TKYTE - PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select 'transaction ' || rawtohex(vt.addr) || ' associated with session (' || vs.sid || ', ' || vs.serial# || ')' data 2 from v$transaction vt, v$session vs 3 where rawtohex(vt.addr) = vs.taddr (+); DATA ------------------------------------------------------------------------------- transaction 26C64010 associated with session (74, 381) ops$tkyte%ORA11GR2> select username || ' - ' || taddr data 2 from v$session 3 where username = user; DATA ----------------------------------------- OPS$TKYTE - 26C64010 ops$tkyte%ORA11GR2> commit; Commit complete. Reviews   Excellent explanation   December 19, 2011 - 9am UTC Bookmark |  Bottom |  Top Reviewer:  Oraboy  Thanks Tom. Couldn't have asked for a better explanation. I clearly missed the point, a session could have multiple active transactions. But I am still unclear on how the application code could do this. To my limited understanding, the application code should grab a connection/session from connection pool and use that pass on its db work & after its done, it releases back to connection pool. Since each unit of work would be capsuled within its transaction boundary, shouldn't the relation between transaction and session be always 1:1? I can understand "pragma transaction" in plsql..but this application does not use any DB procedure. Your response actually made me more curious , now I want to dig through application code and its transaction management to understand more. Thanks once again for this invaluable site. Followup   December 19, 2011 - 4pm UTC: XA is magic, it does strange things - really odd things. I don't have the ability to test it - but I'm pretty sure it would be the cause.  .. shouldn't the relation  between transaction and session be always 1:1? ..  not with XA. It in general is many to many.    Autonomous transactions?   December 19, 2011 - 9pm UTC Bookmark |  Bottom |  Top Reviewer:  Nathan Marston from Adelaide AU Wouldn't this cause the relationship between transactions and sessions to be many to one also? Followup   December 20, 2011 - 8am UTC: no, it would be one to many. An autonomous transaction belongs to exactly one session. A session can have many autonomous transactions.