Home » RDBMS Server » Performance Tuning » Understanding UNDO 11g (Oracle RDBMS 11G, Windows Server 2019)
Understanding UNDO 11g [message #686617] Thu, 27 October 2022 03:32 Go to next message
esirenko
Messages: 1
Registered: October 2022
Junior Member
Hi Guys,

Couple of month ago i faced UNDO issue at my database. Now it is always full and system ask 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, v$parameter p
  4     where 1 = 1 and p.name = 'db_block_size' and u.end_time >= sysdate - 7
  5  group by u.maxquerysqlid, p.value
  6    having round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) >= 95
  7  order by round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) desc;


MAXQUERYSQLID GB_UNEXPIRE
------------- -----------
cj2h00pd9h4uf          96
0rc4km05kgzb9          96
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?
2. How come the internal Oracle`s mechanism got 95GB 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?
Re: Understanding UNDO 11g [message #686618 is a reply to message #686617] Thu, 27 October 2022 05:48 Go to previous message
John Watson
Messages: 8793
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum.

With undo retention set to 50000 seconds, you are going to need an undo tablespace the size of Jupiter. Why so high? Do you really have queries that run for 14 hours? dba_hist_undostat will tell you that. Or if it is an open cursor, then perhaps you need to rethink your application design. For example, if you are committing within a cursor loop then setting the retention to the end of universe my not help.
Previous Topic: gather statistics on partition table
Next Topic: Why *is* my index used?
Goto Forum:
  


Current Time: Mon Jan 30 01:55:26 CST 2023