This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jan 23, 2013 9:18 PM by J RSS

High "ACTIVE" undo blocks..ORA-30036..

J Newbie
Currently Being Moderated
All,

In my database the most of the undo blocks are ACTIVE.

 show parameters undo

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management                      string                            AUTO
undo_retention                       integer                           36000
undo_tablespace                      string                            UNDOTBS1

*we 've set the retention to 10hrs to enable flashback query..

select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

SUM(BYTES)/1024/1024/1024
-------------------------
               16.1132813


 SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 , COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

STATUS                      SUM(BYTES)/1024/1024   COUNT(*)
--------------------------- -------------------- ----------
ACTIVE                                 15417.625      14620
UNEXPIRED                                    136       1096
EXPIRED                                  55.1875        823


select  sid , serial#, osuser , USED_UBLK *8192/1024/1024 UNDO_USED_IN_MB ,
 logon_time ,sql_id from v$transaction a , v$session b
  where a.addr = b.taddr
  and a.USED_UBLK > 0
order by 6;  2    3    4    5

       SID    SERIAL# OSUSER  UNDO_USED_IN_MB LOGON_TIME      SQL_ID
---------- ---------- ------- --------------- --------------- ---------------------------------------
        10      14535 oracle         .0078125 20-JAN-13       27977dzwm35zq
       888      21467 oracle         7.984375 20-JAN-13       71pjfn7yf83uq
       395      12907 oracle         .0078125 21-JAN-13       8rg2kjzsxw97v
Why is the ACTIVE so high when there sum(UNDO_USED_IN_MB) is very less ?

*The ACTIVE keeps growing slowly. I already hit ORA-30036, before i resized it.

TIA,
Jon

Edited by: J on 20-Jan-2013 22:24
  • 1. Re: High "ACTIVE" undo blocks..ORA-30036..
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Refer to
    Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace [ID 460481.1]
  • 2. Re: High "ACTIVE" undo blocks..ORA-30036..
    J Newbie
    Currently Being Moderated
    Thx for stopping by..

    I did check that earlier..
    Active: Extents that are currently in user
    Unexpired: This represents the extents which are required to satisfy the time specified by Undo_retention Initialisation parameter.
    Expired: These are extents which are not being used by transaction and have crossed the time specified by Undo_retention .
    
    select BEGIN_TIME,END_TIME,UNDOTSN,UNDOBLKS,ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS from v$undostat order by 1
    
    BEGIN_TIME        END_TIME             UNDOTSN   UNDOBLKS ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS
    ----------------- ----------------- ---------- ---------- ---------- ------------- -----------
    21-01-13 00:18:01 21-01-13 00:28:01          1      20840    2111048           896          32
    21-01-13 00:28:01 21-01-13 00:38:01          1      23162    2155112           600        1672
    21-01-13 00:38:01 21-01-13 00:41:33          1       8289    2175464          2208         768
    
    * my blocks are of 8K size
    
    SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 , COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
    
    STATUS                      SUM(BYTES)/1024/1024   COUNT(*)
    --------------------------- -------------------- ----------
    ACTIVE                                16863.1875      16523
    UNEXPIRED                                71.9375        926
    EXPIRED                                  51.9375        816
    
    
     select  sid , serial#, osuser , USED_UBLK *8192/1024/1024 UNDO_USED_IN_MB ,
     logon_time ,sql_id from v$transaction a , v$session b
      where a.addr = b.taddr
      and a.USED_UBLK > 0
    order by 6;00:44:34   2  00:44:34   3  00:44:34   4  00:44:34   5
    
           SID    SERIAL# OSUSER  UNDO_USED_IN_MB LOGON_TIME        SQL_ID
    ---------- ---------- ------- --------------- ----------------- ---------------------------------------
           772      10091 oracle          8.15625 21-01-13 00:39:27 4upwng87y4vha
           395      12907 oracle         .0078125 21-01-13 00:06:47 6ybz8x809qna9
           888      21467 oracle        8.0234375 20-01-13 23:54:24 71pjfn7yf83uq
           633      25407 oracle         .0078125 21-01-13 00:38:44 73crnktam91dc
           133      64795 oracle         .0078125 20-01-13 23:33:56 7g8175xp12pxj
           889      28281 oracle         .0078125 21-01-13 00:37:47 budrwa8bhp3q8
    
    6 rows selected.
    Any idea on why so much is 'ACTIVE' ?

    On most of my other databases, i see this "ACTIVE" from DBA_UNDO_EXTENTS as very low.

    TIA,
    jon

    Edited by: J on 20-Jan-2013 22:48
  • 3. Re: High "ACTIVE" undo blocks..ORA-30036..
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    you have active transaction

    post output for this query
     
    SELECT username, terminal, osuser,
           t.start_time, r.name, t.used_ublk "ROLLB BLKS",
           DECODE(t.SPACE, 'YES', 'SPACE TX',
              DECODE(t.recursive, 'YES', 'RECURSIVE TX',
                 DECODE(t.noundo, 'YES', 'NO UNDO TX', t.status)
           )) status
    FROM sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
    WHERE t.xidusn = r.usn
      AND t.ses_addr = s.saddr
    /
     
    http://psoug.org/snippet/Show-active-transactions_523.htm
  • 4. Re: High "ACTIVE" undo blocks..ORA-30036..
    J Newbie
    Currently Being Moderated
    Yeah, the active goes high only when we have active transactions.. but i do not see any sessions running high..
     SELECT username, terminal, osuser,
    03:24:15   2         t.start_time, r.name, t.used_ublk "ROLLB BLKS",
    03:24:15   3         DECODE(t.SPACE, 'YES', 'SPACE TX',
    03:24:15   4            DECODE(t.recursive, 'YES', 'RECURSIVE TX',
    03:24:15   5               DECODE(t.noundo, 'YES', 'NO UNDO TX', t.status)
           )) status
    03:24:15   6  03:24:15   7  FROM sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
    03:24:15   8  WHERE t.xidusn = r.usn
    03:24:15   9    AND t.ses_addr = s.saddr
    03:24:15  10  /
    
    USERNAME           TERMINAL   OSUSER  START_TIME                NAME                      ROLLB BLKS STATUS
    --------------     ---------- ------- ------------------------- ------------------------- ---------- ----------
    LABEL               UNKNOWN    oracle  01/21/13 02:09:42         _SYSSMU40_1216190258$           1029 ACTIVE
    FIONA               unknown    oracle  01/21/13 03:23:43         _SYSSMU53_1216190675$              1 ACTIVE
    FIONA               unknown    oracle  01/21/13 03:22:22         _SYSSMU67_1217577694$              1 ACTIVE
    FIONA               unknown    oracle  01/21/13 03:22:10         _SYSSMU95_1220350384$              1 ACTIVE
    FIONA               UNKNOWN    oracle  01/21/13 03:00:10         _SYSSMU138_1220364310$             3 ACTIVE
    FIONA               UNKNOWN    oracle  01/21/13 03:00:10         _SYSSMU154_1225400405$             1 ACTIVE
    
    
    
    
    SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 , COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
    
    STATUS                      SUM(BYTES)/1024/1024   COUNT(*)
    --------------------------- -------------------- ----------
    ACTIVE                                 18777.625      18401
    EXPIRED                                  49.6875        780
    UNEXPIRED                                 76.625       1031
    
    ** The ACTIVE has increase much more than my first post
  • 5. Re: High "ACTIVE" undo blocks..ORA-30036..
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    If I insert a row into a table, without committing, I will acquire an undo block in an undo extent, and my use will ensure that that extent stays ACTIVE until I commit.

    In a typical system with many users, other users will also acquire space in the same undo tablespace, sometimes in the same segement, and over time they will commit their transactions and gradually work their way through other extents in the same undo segment.

    Every subsequent extent that newer transactions have used in the same segment as my original extent will have to stay active - even though there may be no other active transactions at the time. This may be what's happened with your system - a few small transactions happened a relatively long time ago, but have not yet committed.

    Regards
    Jonathan Lewis
  • 6. Re: High "ACTIVE" undo blocks..ORA-30036..
    J Newbie
    Currently Being Moderated
    Jonathan, thx for stopping by !!

    As long as a user has not committed the DML, there should be an entry in the v$transaction, is it not ?

    I do no see any sessions lingering for long time. The "ACTIVE" undo extents are growing every few minutes. I just added another 5gb.
    SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 , COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
    
    STATUS     SUM(BYTES)/1024/1024   COUNT(*)
    ---------- -------------------- ----------
    ACTIVE               19999.0625      19819
    EXPIRED                 48.3125        728
    UNEXPIRED              797.6875       1824
    
    
     select  sid , serial#, osuser , USED_UBLK *8192/1024/1024 UNDO_USED_IN_MB ,
     logon_time ,sql_id from v$transaction a , v$session b
      where a.addr = b.taddr
      and a.USED_UBLK > 0
    order by 6; 04:50:23   2  04:50:23   3  04:50:23   4  04:50:23   5
    
           SID    SERIAL# OSUSER  UNDO_USED_IN_MB LOGON_TIME                SQL_ID
    ---------- ---------- ------- --------------- ------------------------- ---------------
           517      18327 oracle         .0078125 21-01-13 04:11:40         7vsr0akd5g13r
           282         19 oracle           .03125 21-01-13 03:00:00         bj5vcnbtbzcn0
           655      27279 oracle          .015625 21-01-13 04:00:50         cyypqtvmbws0h
           903      65191 oracle         .0078125 21-01-13 03:00:09         guw9p0b09k7bm
    
    
    select sysdate from dual;
    
    SYSDATE
    -----------------
    21-01-13 04:50:41
    If what you said is happening, how do i confirm it and how do i stop it from eating the space ?


    TIA,
    Jon

    Edited by: J on 21-Jan-2013 03:00
  • 7. Re: High "ACTIVE" undo blocks..ORA-30036..
    JohnWatson Guru
    Currently Being Moderated
    You are joining on a.addr = b.taddr, I think you should get the same result if you join on a.ses_addr=b.saddr instead but it wouldn't take a second to check. Perhaps addr or taddr is null for some reason.
  • 8. Re: High "ACTIVE" undo blocks..ORA-30036..
    J Newbie
    Currently Being Moderated
    John, thx for stopping by !

    Here is the o/p with the change in the 'where' clause.
    select  sid , serial#, osuser , USED_UBLK *8192/1024/1024 UNDO_USED_IN_MB ,
     logon_time ,sql_id from v$transaction a , v$session b
      where a.ses_addr=b.saddr
      and a.USED_UBLK > 0
    order by 6;  2    3    4    5
    
           SID    SERIAL# OSUSER  UNDO_USED_IN_MB LOGON_TIME                SQL_ID
    ---------- ---------- ------- --------------- ------------------------- ---------------
           890      33549 oracle         .0078125 21-01-13 05:19:07         49xjr921mgy4w
    
    1 row selected.
    The "ACTIVE" extents are growing at the rate of ~150mb/10min .

    Regards,
    Jon
  • 9. Re: High "ACTIVE" undo blocks..ORA-30036..
    Mihael Pro
    Currently Being Moderated
    The issue is why v$transaction does not reflect DBA_UNDO_EXTENTS. You can try to put away v$session, but this hardly will help :
     select sum(USED_UBLK) *8192/1024/1024 from v$transaction; 
    I have found one more case of such behaviour:
    SQL> SELECT  STATUS, SUM(BYTES) bytes, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
    STATUS    BYTES COUNT(*)
    ------- ------- --------
    ACTIVE  252.4 M       60
    EXPIRED   4.6 G    1,252
    
    
    SQL> select count(*) from v$transaction;
    COUNT(*)
    --------
           0
    But here answer is simple.
  • 10. Re: High "ACTIVE" undo blocks..ORA-30036..
    J Newbie
    Currently Being Moderated
    Mihael, thx for stopping by !

    I don't see the session/process still...
    select sum(USED_UBLK) *8192/1024/1024 from v$transaction;
    
    SUM(USED_UBLK)*8192/1024/1024
    -----------------------------
                           .40625
    
    1 row selected.
    
    SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 , COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
    
    STATUS     SUM(BYTES)/1024/1024   COUNT(*)
    ---------- -------------------- ----------
    ACTIVE                  21056.5      20386
    UNEXPIRED                   949       1988
    EXPIRED                 55.1875        726
    
    3 rows selected.
    Regards,
    Jon
  • 11. Re: High "ACTIVE" undo blocks..ORA-30036..
    Mihael Pro
    Currently Being Moderated
    I don't see the session/process still...
    Check at the operating system level for processes with high disk i/o.
    What is your OS and Oracle version ?
  • 12. Re: High "ACTIVE" undo blocks..ORA-30036..
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    J wrote:

    If what you said is happening, how do i confirm it and how do i stop it from eating the space ?
    I think I'd start by looking at the undo segments - is the growth due to just one or two segments, or is the space spread across a lot of segments.
    select segment_name, count(*) from dba_undo_segments
    where status = 'ACTIVE'
    group by segment_name 
    order by count(*)
    Run the query a couple of times with a few minutes gap to see where the growth is.

    Regards
    Jonathan Lewis
  • 13. Re: High "ACTIVE" undo blocks..ORA-30036..
    JohnWatson Guru
    Currently Being Moderated
    Does a quiery like this give any hints about which session (if it is a session) it is -

    select sid,value from v$sesstat where statistic#=
    (select statistic# from v$statname where name='undo change vector size');
  • 14. Re: High "ACTIVE" undo blocks..ORA-30036..
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    J wrote:

    If what you said is happening, how do i confirm it and how do i stop it from eating the space ?
    It would also be worth checking v$transaction with a simple query to see if there are any transactions that are not showing up in the various joins you've done. In particular you would be interested in transaction start times: select start_time from v$transaction;

    Have you said which version of Oracle you're using ?

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

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