extremely slow sessions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: extremely slow sessions

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    extremely slow sessions

    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,

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #3
    Join Date
    Apr 2001
    Posts
    257

    Question

    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,

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.

    Code:
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width