11 Replies Latest reply: May 6, 2010 2:08 AM by Hemant K Chitale RSS

    undo usage by session

    685460
      Hi

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

      Regards

      Kmp
        • 1. Re: undo usage by session
          sb92075
          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
            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
              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
                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
                  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
                    "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
                      I had not seen"asifkabirdba" `s post when i post this message
                      • 8. Re: undo usage by session
                        Girish Sharma
                        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
                          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
                            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
                              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