This discussion is archived
11 Replies Latest reply: May 6, 2010 12:08 AM by Hemant K Chitale RSS

undo usage by session

685460 Newbie
Currently Being Moderated
Hi

I want to query which sesssion using more undo t ?
ur reply highlly appreciated

Regards

Kmp
  • 1. Re: undo usage by session
    sb92075 Guru
    Currently Being Moderated
    I want to query which sesssion using more undo t ?
    why?


    ALWAYS
    Post Operating System (OS) name & version for DB server system.(uname -a)
    Post results of
    SELECT * from v$version
  • 2. Re: undo usage by session
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Query V$SESSION, V$SESSTAT and V$STATNAME for V$STATNAME.name = 'undo change vector size'

    NOTE : Once a session logs out, it's details are lost from these views. However, the same SID is reassigned to another session. Therefore, be very careful when comparing snapshots of these statistics taken at different times. The same SID in two snapshots might actually be two different sessions !

    You could write a LOGOUT trigger to have the session write it's statistics to a logging table. That would add overheads to every logout !


    Hemant K Chitale
    http://hemantoracledba.blogspot.com
  • 3. Re: undo usage by session
    asifkabirdba Guru
    Currently Being Moderated
    SELECT s.username, s.SID, s.serial#, s.logon_time, t.xidusn, t.ubafil,
    t.ubablk, t.used_ublk, t.start_date, t.status
    FROM v$session s, v$transaction t
    WHERE s.saddr = t.ses_addr;


    Regards
    Asif Kabir
  • 4. Re: undo usage by session
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Querying for used_ublk from V$TRANSACTION will work for a Transaction. However, a session can have many Transactions. Therefore joining V$SESSION to V$TRANSACTION won't help to determin "undo usage by session".


    Hemant K Chitale
  • 5. Re: undo usage by session
    Chinar Guru
    Currently Being Moderated
    You can find this using

    select sess.USERNAME, tr.XIDUSN "Undo segment number",tr.USED_UBLK "Number of undo blocks used",
    tr.USED_UREC "     Number of undo records used" from v$transaction tr,v$session sess
    where tr.ADDR = sess.TADDR
  • 6. Re: undo usage by session
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    "admin"
    You are making the same mistaken assumption(s) as "asifkabirdba"

    A session can span transactions.
    V$TRANSACTION hold information only about a current transaction -- and only till the transaction remains uncommited.

    Hemant K Chitale
  • 7. Re: undo usage by session
    Chinar Guru
    Currently Being Moderated
    I had not seen"asifkabirdba" `s post when i post this message
  • 8. Re: undo usage by session
    Girish Sharma Guru
    Currently Being Moderated
    Hi Hemant,
    I run two transactions:
    Session 1 Tran 1:
    update emp set ename='yy';

    Then i see in sys session:

    set line 200;
    SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
    NVL(s.username, 'None') orauser,
    s.program,
    r.name undoseg,
    t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
    FROM sys.v_$rollname r,
    sys.v_$session s,
    sys.v_$transaction t,
    sys.v_$parameter x
    WHERE s.taddr = t.addr
    AND r.usn = t.xidusn(+)
    AND x.name = 'db_block_size'
    /
    SID_SERIAL ORAUSER    PROGRAM                        UNDOSEG         Undo
    ---------- ---------- ------------------------------ --------------- -------
    149,4      SCOTT      sqlplus.exe                    _SYSSMU3$       16K
    Then:
    Session 1 Tran 2:
    update chem set subname='chemistry';
    1753 rows updated.

    Again in sys's session:
    /
    SID_SERIAL ORAUSER    PROGRAM                        UNDOSEG         Undo
    ---------- ---------- ------------------------------ --------------- -------
    149,4      SCOTT      sqlplus.exe                    _SYSSMU3$       288K
    Means, at this moment Scott user is using 288K sized undo segment named _SYSSMU3$.

    Am i right or is right to say OP's question answer?

    Regards
    Girish Sharma
  • 9. Re: undo usage by session
    685460 Newbie
    Currently Being Moderated
    According to ur query u filtered "'db_block_size" is it related to undo ?

    Regards
    kmp
  • 10. Re: undo usage by session
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Since you did not COMMIT between the two UPDATEs, they are one single transaction.


    You have reported data for one single transaction. Once Sesssion 1 COMMITS, V$TRANSACTION is cleared. If you query V$TRANSACTION thereafter, you would report "no undo being used" because you would not find anything in V$TRANSACTION.

    Therefore, you have reported incorrect information by using such a query.


    Hemant K Chitale
  • 11. Re: undo usage by session
    Girish Sharma Guru
    Currently Being Moderated
    Thank you for your prompt reply. Then what is the query by which OP can know, which sesssion is using more undo? Or i think he need to provide us the more details regarding question.

    @OP
    Please do'nt use my query; since it is wrong/incorrect information; concluded by Hemant.

    Regards
    Girish Sharma

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points