UNDO MANAGEMENT INTERNALS IN ORACLE

An oracle database records the data twice

  • In datafile or database buffer cache which is new data
  • Redo log files which contains information to recreate the blocks in datafile

Basically oracle maintains data consistency using the following way

  • Write new changes of data blocks to redo record
  • Write the old state of data blocks to undo record (undo block of undo tablespace)
  • Generate redo record for changes in undo blocks
  • Modify the data block

As in oracle, there are three types of isolation levels ,

(Connolly, Thomas & Begg, Carolyn et al., (2010))
  • read committed
  • read only
  • serializable

Undo in the name itself denotes to go back to the previous state like a time travel to past like flashback ability in prince of Persia – sand of time game. It is also used for read consistency purposes.

UNDO MECHANISM

If you know the oracle architecture well, then you should know that oracle build mirror(consistent) images of data blocks for the original data lock and that’s why database cannot fit into the database buffer cache.

This property solves the purpose of isolation property in ACID, if multiple transactions access the same data blocks, then there should not be a transparency between two sessions.

Real time example 1 :

  • You started reading this article at 10:00 AM without closing the browser tab or refreshing the tab till 10:20 AM. The title is “UNDO MANAGEMENT INTERNALS IN ORACLE” when you opened the page.
  • I make changes to this article with title as “NEW VERSION OF UNDO MANAGEMENT INTERNALS IN ORACLE” at 10:05 AM, saved the changes at 10:15 AM and left.
  • You should not see the changes made at 10:05 AM. But once you reopen or refresh the tab after 10:05 AM, you should see updated title. This is called read consistency.

Real time example 2 :

  • You open online website for booking a ticket for movie or bus travel at 10:00:00
  • Another person open the page and look for the same seat number 3 at 10:00:00
  • Here both person can see the empty seat number 3
  • The person who choose seat 3 first and enter payment page will lock the page from further access.
  • But other person still see the seat 3 available because of read consistency.
  • If you booked the ticket which means committed the transaction, then other person cannot book the ticket and he should see updated details in the page that the seat 3 is already booked.
  • If you closed the booking page by changing your mind which means rollback the transaction, then other person should be able to book the ticket.

Purpose of undo:

  • Store old image of data block for rollback and recover
  • Read consistency

Undo can be managed manually or automatically. By default, automatic undo management is enabled in undotbs01. If database is manually created without using dbca ,then oracle automatically store undo records in system tablespace. In this case, we have to explicitly set dedicated tablespace for undo. If auto growth for an undo tablespace is set , oracle manages undo retention as well for you on retaining the older image of records.

There are three states of undo

Active – Online transactions which currently run. Here undo retention is not valid and so undo tablespace should be always ready to accommodate the undo blocks. Example: Undo retention- 15 min and an insert query ran for 20 min. Undo retention threshold is anyway exceeded and so the remaining data should be spilled on undo tablespace for commit or rollback and also read consistency. Note: In automatic undo mode, the undo_retention parameter is not considered for active extents due to auto tuning of retention. Oracle automatically manage to increase undo based on the query requirement and dump the contents to undo tablespace.

Expired – Transaction is either committed or rollback and query duration may be greater than undo retention. These extents can be reused and borrowed for active extents Example: The long or short running DML transaction is completed with commit and these extents are no longer needed for commit or rollback and read consistency. So other transactions can borrow the extents.

Unexpired – Transaction is either committed or rollback and query duration is less than undo retention. The main purpose of this state is to keep the blocks for read consistency. Example: There are two sessions.

  • Session 1 executes a select query at 10:00 AM.
  • Session 2 executes a delete query at 10:15 AM.
  • Session 2 completed with commit at 10:30 AM.
  • Session 1 still run after 10:30 AM
  • Here Session 1 should not be seeing the deleted results as per 10:15 because it should see 10:00AM results

The most common buzz error code 01555 which is heard in most of the Oracle DBA interviews

ORA-01555 – basically means unexpired extents are overwritten or borrowed by active extents and the query which is reading the data can no longer see the old details of the data. Transaction is halted suddenly. There is an option called guaranteed undo retention which guarantees the availability of old undo image unexpired extents for read consistency.

Command to force undo guarantee. This option has some drawback in which multiple DML transactions can fail. The reason for this behavior will be examined in another post.

alter tablespace UNDOTBS1 retention guarantee;
[oracle@x3dbzx36 ~]$ oerr ora 01555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

ORA-30036 – This error occurs if there is no space to store active undo records in undo tablespace. Transaction is rollback due to lack of space.

[oracle@x3dbzx36 ~]$ oerr ora 30036
30036, 00000, "unable to extend segment by %s in undo tablespace '%s'"
// *Cause:   the specified undo tablespace has no more space available.
// *Action:  Add more space to the undo tablespace before retrying
//           the operation. An alternative is to wait until active
//           transactions to commit.

Calculating optimal undo:

Optimal undo retention = Total size of undo tablespace / (db_block_size * undo_block_per_sec)

SQL to calculate optimal undo retention

with UNDOSZE as 
(select sum(bytes) UNDOMB 
        from dba_data_files 
where FILE_NAME like '%undo%'),
UNDOBPS as 
(select round(max(undoblks/((end_time-begin_time)*3600*24)),3) UNDOBPS 
from v$undostat),
DBBS as 
(select value DBBS 
        from v$parameter 
where name='db_block_size')
select round(UNDOMB / (UNDOBPS*DBBS)) as OPTIUNDORET 
from 
undosze,
undobps,
dbbs;

To check active, unexpired and expired undo history

kIsH@x3z6zx9<^>def ucol="status,round(sum(bytes/1048576)) sizeMB,count(*) ucnt"
kIsH@x3z6zx9<^>def uview="dba_undo_extents"
kIsH@x3z6zx9<^>def ugrp="group by status"
kIsH@x3z6zx9<^>select &ucol
  2  from &uview
  3  &ugrp;
old   1: select &ucol
new   1: select status,round(sum(bytes/1048576)) sizeMB,count(*) ucnt
old   2: from &uview
new   2: from dba_undo_extents
old   3: &ugrp
new   3: group by status

STATUS        SIZEMB       UCNT
--------- ---------- ----------
UNEXPIRED         15         37
EXPIRED         9720      10238

This query help to identify the undo bound queries and undo errors

col SQL_TEXT for a20
col NOSPACEERRCNT for 999
col UNSC for 999
col ORA1555 for 999
select to_char(dhu.BEGIN_TIME,'DD-MM-YY HH24:MI') BT,
  2         to_char(dhu.END_TIME,'DD-MM-YY HH24:MI') ET,
           dhu.MAXQUERYLEN as MQL,
           dhu.MAXQUERYSQLID,
           dhsq.SQL_TEXT,
           dhu.TUNED_UNDORETENTION as TR,
           dhu.ACTIVEBLKS as ABLK,     --Retention not valid but space is valid
           dhu.UNEXPIREDBLKS as UXBLK,
           dhu.EXPIREDBLKS as XBLK,
           dhu.NOSPACEERRCNT as ORA30036,
           dhu.SSOLDERRCNT as ORA1555,
           dhu.UNXPSTEALCNT as UNSC
from dba_hist_undostat dhu
inner join dba_hist_sqltext dhsq on (dhu.MAXQUERYSQLID = dhsq.sql_id)
order by dhu.MAXQUERYLEN desc;  7    8    9   10   11   12   13   14   15

BT             ET                    MQL MAXQUERYSQLID SQL_TEXT                     TR       ABLK      UXBLK       XBLK   ORA30036 ORA1555 UNSC
-------------- -------------- ---------- ------------- -------------------- ---------- ---------- ---------- ---------- ---------- ------- ----
03-04-22 15:17 03-04-22 15:27       4945 6a7chgvffupxz  delete from "KISH".       5786     563168       2832         16          0       0  344
                                                       "MVSAL"

03-04-22 15:07 03-04-22 15:17       4344 6a7chgvffupxz  delete from "KISH".       5185     563168       2832         16          0       0    0
                                                       "MVSAL"

To simulate the error, reduce the undo_retention to less value and also reduce the undo tablespace size

kish@exdbx<>show parameter undo_retention

NAME                                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900
kish@exdbx<>alter system set undo_retention=200;

System altered.

Elapsed: 00:00:00.28

Perform DML transaction in a million rows table with less undo tablespace size

SQL> update test.tabx set order_priority='L' where order_id > 10 and order_id < 20000;
update test.tabx set order_priority='L' where order_id > 10 and order_id < 20000
            *
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

Use select query with multiple joins on million row tables with less undo retention

SQL> select a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
  2  from test.SALES_TAB_COPY a
  3  inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
  4  inner join test.sales_tab c on b.order_id=c.order_id;

COUNTRY
--------------------------------------------------------------------------------
ITEM_TYPE                              SALES_CHANNEL
-------------------------------------- --------------------------------------
ORDER_DAT TOTAL_COST TOTAL_PROFIT
--------- ---------- ------------
01-FEB-41  190060628

"Chad
Clothes                                Offline
01-APR-41    1958656

ERROR:
ORA-01555: snapshot too old: rollback segment number 2 with name
"_SYSSMU2_2697001022$" too small

64875 rows selected.

One can check the max query length to have an idea on setting undo retention. Here the value that needs to be set is undo_retention >= 938

kIsH@x3z6zx9<^>show parameter undo_rete

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900
kIsH@x3z6zx9<^>select max(maxquerylen) as maxql from v$undostat;

    maxql
----------------
             938

Elapsed: 00:00:00.01

If auto growth for undo tablespace is not enabled, then oracle ignore the undo_retention(value=0 will be considered by oracle) parameter and automatically increase the undo retention time in an elastic manner until the space exhaust in undo tablespace. This parameter adjustment can be viewed in tuned_undoretention column of v$undostat.

kIsH@x3z6zx9<^>select max(tuned_undoretention) from v$undostat;

MAX(TUNED_UNDORETENTION)
------------------------
                    2078

Solution:

Calculate the maximum query length which consumed undo in the past history. This value can be set for retention as a reactive approach. But there is no guarantee that this value cannot be exceeded in the future.

kIsH@Xhydra<>select max(maxquerylen) from dba_hist_undostat;

MAX(MAXQUERYLEN)
----------------
            3408

To estimate the size of undo, below link can be used

http://alphaoragroup.com/2022/07/09/calculate-and-estimate-optimal-undo-for-database-oracle/

Summary:

High Undo consumers:

MVIEW refresh, Long DML queries, Index maintenance, Partition maintenance, Flashback logging, Expdp / Impdp.

Active transactions require high undo space

Unexpired transactions require high undo retention

Expired transactions are available undo extents for reuse

Leave a Reply