extremely slow sessions

[a128]

Hi,

When querying V$SESSION, there is a column called TADDR. According to the docs, it is for Transaction State Object. I notice some sessions have a value in the TADDR column while the STATUS column is 'INACTIVE'. Does this mean the session is hang?

Doesn't INACTIVE status mean the SQL statement should finish executing already?

Thanks,

[pando]

inactive means it´s not doing anything, TADDR means it´s in a transaction

you can run an update, when update finishes the session becomes inactive but the transaction is NOT finished since you have not committed, in that case you will se what you are seeing

[a128]

But then, when I joined v$session and v$sqlarea, there is an inactive session that looks like it's in a transaction but with a SELECT statement. Does it make sense?

SADDR SID SERIAL# TYPE TADDR LOCKWAIT STATUS LAST_CALL_ET SQL_TEXT

940DB740 16 13704 USER 946EF54C INACTIVE 21270 SELECT ... FROM ... ORDER BY ...


(actual SQL has been relaced with ...)
I doubt a SELECT query is considered a transaction. Is this abnormal?

What if the developer does an UPDATE (without commit) and then a SELECT, will v$sqlarea.sql_text show the SELECT statement, thus give the result above?

Thanks,

[abhaysk]

Originally posted by a128
I doubt a SELECT query is considered a transaction. Is this abnormal?

Select Query ISNT considered a TRAN. I think u asked this Question in ur other post.

Pando has already said you unless you COMMIT or Rollback, the session which performed DML, will have TADDR.

Let me clear ur doubt with a demo.

Session 1:
=========

TEST:ABHAY> Update Test_Rollback set ID1=100;

100000 rows updated.

TEST:ABHAY> select count(1) from Test_Rollback where id1=100;


            COUNT(1)
--------------------
              100000

TEST:ABHAY> commit;

Commit complete.

TEST:ABHAY> select SYSDATE from Dual;


SYSDATE
---------
13-JUN-03

Session 2:
=========

TEST:ABHAY> ed
Wrote file afiedt.buf

  1  Select S.Sid,S.Taddr,S.SQL_ADDRESS,T.Sql_Text from v$session S, v$sqlarea T
  2* Where S.sid=8 and t.ADDRESS=S.SQL_ADDRESS
TEST:ABHAY> /


                 SID TADDR    SQL_ADDR SQL_TEXT
-------------------- -------- -------- --------------------------------------------------------
                   8 79E09830 794D4B10 Update Test_Rollback set ID1=100

TEST:ABHAY> /


                 SID TADDR    SQL_ADDR SQL_TEXT
-------------------- -------- -------- --------------------------------------------------------
                   8 79E09830 794E0D30 select count(1) from Test_Rollback where id1=100

--Note here, i use SELECT query, even TADDR is having value beacuse i didnt 
COMMIT or ROLLBACK after that UPDATE. But this dosent mean Select is TRAN

TEST:ABHAY> /


                 SID TADDR    SQL_ADDR SQL_TEXT
-------------------- -------- -------- --------------------------------------------------------
                   8          794974B8 Commit

TEST:ABHAY> /


                 SID TADDR    SQL_ADDR SQL_TEXT
-------------------- -------- -------- --------------------------------------------------------
                   8          794A28F8 select SYSDATE from Dual

Do you see any TADDR value here, I am using SELECT query, but this is after COMMIT.

Abhay.