1 2 Previous Next 20 Replies Latest reply: Feb 15, 2013 2:37 AM by Mihael RSS

    Undo Table Space Issue

    985871
      Hi ,

      I am using Oracle 10.2.0.3.

      Since yesterday i am seeing a session with sid 1160 using undo tablespace but not able to find how much it is using .

      I need to know which session and from which module and how much is the Undo being used by those sessions. I have tried searching but all the queries provide me with some different results each time.

      Also i need the same information for REDO being generated .

      Thanks in advance.
        • 1. Re: Undo Table Space Issue
          Aman....
          A quick search gave this,
          select
             s.sid, 
             s.username,
             r.name       "RBS name", 
             t.start_time,
             t.used_ublk  "Undo blocks",
             t.used_urec  "Undo recs"
           from
             v$session s,
             v$transaction t,
             v$rollname r
           where
             t.addr = s.taddr and
             r.usn  = t.xidusn;
          From http://www.adp-gmbh.ch/ora/concepts/undo/undo_by_session.html

          HTH
          Aman....
          • 2. Re: Undo Table Space Issue
            Mihael
            SQL> select statistic#, name from v$statname where name in ('undo change vector size','redo size');
            STATISTIC# NAME
            ---------- -----------------------
                   175 redo size
                   284 undo change vector size
            join this with v$sesstat
            • 3. Re: Undo Table Space Issue
              985871
              Thanks for the quick reply .

              The output shows 1 undo block and 1 undo rec . But how will i know how much size a particular sid is consuming the undo .


              Also if i can get the same for REDO size will be helpfull

              Thanks in advance .
              • 4. Re: Undo Table Space Issue
                Osama_Mustafa
                982868 wrote:
                Thanks for the quick reply .

                The output shows 1 undo block and 1 undo rec . But how will i know how much size a particular sid is consuming the undo .


                Also if i can get the same for REDO size will be helpfull

                Thanks in advance .
                post the output for the query
                • 5. Re: Undo Table Space Issue
                  985871
                  1198     WEBAPP_SS     _SYSSMU12$     02/14/13 08:18:50     1     1
                  1295     WEBAPP_SS     _SYSSMU13$     02/11/13 16:22:34     1     1
                  1570     WEBAPP_SS     _SYSSMU14$     02/14/13 08:39:23     1     1
                  1303     GBC_CORE     _SYSSMU9$     02/11/13 18:44:41                    1     1
                  1382     GBC_CORE     _SYSSMU15$     02/13/13 18:32:46                     1     1
                  1377     WEBAPP_SS     _SYSSMU16$     02/14/13 08:09:24     1     1
                  1160     GBC_CORE     _SYSSMU2$     02/12/13 18:33:15                      1     1


                  i am more concerned about this last session 1160.

                  Edited by: 982868 on Feb 14, 2013 12:41 AM
                  • 6. Re: Undo Table Space Issue
                    Mihael
                    The output shows 1 undo block and 1 undo rec . But how will i know how much size a particular sid is consuming the undo .

                    Also if i can get the same for REDO size will be helpfull
                    You was already given the ways how to get needed result. Some additional work required from you. Just open some documentation. This will give you experience. But if you want to get ready result, it will be useless for you. :)
                    • 7. Re: Undo Table Space Issue
                      985871
                      I tried using the below query but it is not getting completed so had to cancel it.

                      SELECT
                      s.sid,
                      s.username,
                      s.program,
                      ROUND(t.VALUE/(1024*1024)) AS "Redo Size MB",
                      sa.sql_text
                      FROM V$SESSION s, V$SESSTAT t, V$STATNAME sn, V$SQLAREA sa
                      WHERE s.sid = t.sid
                      AND t.statistic# = sn.statistic#
                      AND sn.name = 'redo size'
                      AND sa.sql_id = s.sql_id
                      AND ROUND(t.VALUE/(1024*1024)) != 0
                      ORDER BY t.VALUE DESC;
                      • 8. Re: Undo Table Space Issue
                        Mihael
                        I tried using the below query but it is not getting completed so had to cancel it.

                        SELECT
                        s.sid,
                        s.username,
                        s.program,
                        ROUND(t.VALUE/(1024*1024)) AS "Redo Size MB",
                        sa.sql_text
                        FROM V$SESSION s, V$SESSTAT t, V$STATNAME sn, V$SQLAREA sa
                        WHERE s.sid = t.sid
                        AND t.statistic# = sn.statistic#
                        AND sn.name = 'redo size'
                        AND sa.sql_id = s.sql_id
                        AND ROUND(t.VALUE/(1024*1024)) != 0
                        ORDER BY t.VALUE DESC;
                        instead of V$SQLAREA use V$SQL and add CHILD_NUMBER for join in addition to sql_id
                        • 9. Re: Undo Table Space Issue
                          985871
                          Finally found the below query for finding how much undo is being used and by which session.


                          SELECT r.NAME "Undo Segment Name", dba_seg.size_mb,
                          DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
                          TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
                          v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
                          v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE, v$session.PROGRAM, v$session.module, action
                          FROM v$lock l, v$process p, v$rollname r, v$session,
                          (SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg
                          WHERE l.SID = p.pid(+) AND
                          v$session.SID = l.SID AND
                          TRUNC (l.id1(+)/65536)=r.usn AND
                          l.TYPE(+) = 'TX' AND
                          l.lmode(+) = 6
                          AND r.NAME = dba_seg.segment_name
                          --AND v$session.username = 'SYSTEM'
                          --AND status = 'INACTIVE'
                          ORDER BY size_mb DESC;
                          • 10. Re: Undo Table Space Issue
                            Mihael
                            Okay, you can use it if you are sure that it is correct. But why not using more easy query with v$sesstat ? Or you are using 9i where there is no such statistic ?
                            • 11. Re: Undo Table Space Issue
                              985871
                              Mihael ,

                              I tried using that query but still didnt get any output. I am not sure even if this is correct but i am able to see some figures :)
                              • 12. Re: Undo Table Space Issue
                                Mihael
                                This is your query. Just select all non-zero values.

                                SELECT
                                s.sid,
                                s.username,
                                s.program,
                                ROUND(t.VALUE/1024) AS "Undo Size Kb",
                                sa.sql_text
                                FROM V$SESSION s, V$SESSTAT t, V$STATNAME sn, V$SQL sa
                                WHERE s.sid = t.sid
                                AND t.statistic# = sn.statistic#
                                AND sn.name = 'undo change vector size'
                                AND sa.sql_id = s.sql_id and sa.child_number=s.sql_child_number
                                AND t.VALUE != 0
                                ORDER BY t.VALUE DESC;
                                • 13. Re: Undo Table Space Issue
                                  985871
                                  not sure why but the query is still running from past 3 mins....
                                  • 14. Re: Undo Table Space Issue
                                    Mihael
                                    982868 wrote:
                                    not sure why but the query is still running from past 3 mins....
                                    Some performance issue. You can omit v$statname and use exact value.
                                    See http://www.database.com.mx/?q=es/node/18
                                    In this article there is also another query that uses v$transaction and can be used in 9i.
                                    1 2 Previous Next