Oracle 11g Understanding UNDO behaviour

Question

Couple of weeks ago i faced UNDO issue at my database. Now it is always full and system asks to add some space. I have added another 32GB autoextensible file now it is 32+32+32, but in a couple days, situation repeated. Previously i just ajusted RETENTION in order to get rid of "ORA-01555: SNAPSHOT TOO OLD"

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     50000
undo_tablespace                      string      UNDO_NEW0



SQL> select RETENTION from dba_tablespaces where TABLESPACE_NAME='UNDO_NEW0';

RETENTION
-----------
NOGUARANTEE




SQL> select sum(bytes)/1024/1024/1024  "size_in_gb" from dba_data_files where tablespace_name='UNDO_NEW0';

size_in_gb
----------
95.9999542




SQL>   select u.maxquerysqlid,
  2           round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2)    as gb_unexpire
  3      from dba_hist_undostat u
  4         , v$parameter p
  5     where 1 = 1 
  6       and p.name = 'db_block_size' 
  7       and u.end_time >= sysdate - 7
  8  group by u.maxquerysqlid, p.value
  9    having round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) >= 95
 10  order by round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) desc;


MAXQUERYSQLID GB_UNEXPIRE
------------- -----------
cj2h00pd9h4uf          96
0rc4km05kgzb9          96 <--  internal mechanism
bcczhd6zmr29y          96
0hf365k4cf8dm       95.99
c3grdz4gpkyp2       95.99
93bq2869k9k42       95.99
8tmj8s99y09zm       95.99
424h0nf7bhqzd       95.99
5xbbf036g054c       95.99
ajb6c0shjbb5v       95.97
3wyz3c99jnm6q       95.93
9dzjush42kmfs       95.86
19jayuy078n04       95.79
0422fz4tpgvvj       95.66
8f1sjvfxuup9w       95.62




SQL>   SELECT tablespace_name                                 tablespace,
  2           status,
  3           ROUND (SUM (bytes) / 1024 / 1024 / 1024, 2)     sum_in_gb,
  4           COUNT (*)                                       counts
  5      FROM dba_undo_extents
  6  GROUP BY tablespace_name, status
  7  ORDER BY 1, 2;

TABLESPACE                     STATUS     SUM_IN_GB     COUNTS
------------------------------ --------- ---------- ----------
UNDO_NEW0                      ACTIVE             0          1
UNDO_NEW0                      EXPIRED          .01          8
UNDO_NEW0                      UNEXPIRED      95.99      25312




SQL> SELECT ses.sid,
  2         ses.username,
  3         ses.status,
  4         ses.program     command,
  5         round(tra.used_ublk / 8192 / 1024 ,1 ) as MB
  6    FROM v$SESSION ses INNER JOIN v$TRANSACTION tra ON ses.saddr = tra.ses_addr;

no rows selected

So my questions are:

1. What may be the reason that despite I have add 32 GB for UNDO it still asking for more? For last couple of years 64GB was completely enough, now 96GB - almost everyday it asks to expand UNDO. Where i must to dig in order to get the solution? Of course data amount is growing, but linear and slowly. There were not any new features and calculations implemented.

2. How come the internal Oracle`s mechanism got 96GB of undo? I am talking about "0rc4km05kgzb9" - "select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'" ? I have seen that Oracle have a doc about it, but i do not have access there.

3. Is it OK that so many SQLs consumes almost full size of UNDO?

I understand that decreasing of RETENTION may help here, but cannot figure out why i have such a problems now, after it worked well for a so long time?

Answer 1

cannot figure out why i have such a problems now, after it worked well for a so long time?

Something changed. You've got new (probably badly written) SQL or PL/SQL in the mix, or new data, or new user behavior of some kind causing increased transaction rates.

The UNDO segments are being used to keep a logically consistent picture of your data available for a long-running query as more and more transactions are changing the data. Eventually there will be too many changes to keep track of in the available UNDO space: Oracle will overwrite some needed piece of rollback data or run out of space (if the UNDO retention is guaranteed) and the long-running SQL will fail with one of the errors you mentioned.

Check the following:

• Schedule long-running queries for times when there are fewer DML transactions. This will reduce the changes that consistent gets will need to rollback transactions (i.e. need UNDO) and should improve query performance as well.
• In PL/SQL code, don't fetch across commits: in other words, don't fetch on a cursor that was opened prior to the last commit.
• Commit less often in operations that will run at the same time as your long-running query.
• Tune long-running queries to complete more quickly.