8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Temporary Undo in Oracle Database 12c Release 1 (12.1)

A rather interesting feature of global temporary tables is their undo segments are stored in the regular undo tablespace, which is in turn protected by redo. This presents a number of problems.

Oracle database 12c Release 1 (12.1) introduced the concept of temporary undo, allowing the undo segments for global temporary tables to be stored in the temporary tablespace. This allows global temporary tables to be used in physical standby databases and read-only databases, as well as removing the need to create redo.

Related articles.

Enable/Disable Temporary Undo

In Oracle 12c, undo segments for global temporary tables are still managed using the conventional undo tablespace by default. Temporary undo is enabled/disabled using the TEMP_UNDO_ENABLED parameter at session or system level.

-- Session level
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;

-- System level
CONN sys@pdb1 AS SYSDBA

ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;

There are some caveats associated with this.

Monitor Temporary Undo

Regular undo activity is monitored using the V$UNDOSTAT view. In Oracle database 12c, the V$TEMPUNDOSTAT view has been added to perform a similar function, but for temporary undo.

Create a global temporary table to test against.

CONN test/test@pdb1

DROP TABLE my_temp_table PURGE;

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

Make sure the test user can access the V$TEMPUNDOSTAT view.

CONN sys@pdb1 AS SYSDBA

GRANT SELECT ON v_$tempundostat TO test;

The following test creates a new session, loads some data into the GTT, then checks the redo and undo usage. This example is not using temporary undo.

CONN test/test@pdb1
SET AUTOTRACE ON STATISTICS;

-- Populate GTT.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         46  recursive calls
      15346  db block gets
       2379  consistent gets
         16  physical reads
    2944564  redo size
        855  bytes sent via SQL*Net to client
        986  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

SET AUTOTRACE OFF

-- Check undo used by transaction.
SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

 USED_UBLK  USED_UREC
---------- ----------
       302       6238

SQL>

We can see the insert produces 302 blocks of undo, which is why the statistics show a redo size of 2.9 million bytes of redo, generated to protect the undo.

The following test creates a new session, enables temporary undo, loads some data into the GTT, checks the redo and undo usage, then checks the temporary undo usage using the V$TEMPUNDOSTAT view.

CONN test/test@pdb1

-- Enable temporary undo.
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

SET AUTOTRACE ON STATISTICS;

-- Populate GTT.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         25  recursive calls
      15369  db block gets
       2348  consistent gets
         16  physical reads
       1004  redo size
        853  bytes sent via SQL*Net to client
        986  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

SET AUTOTRACE OFF

-- Check undo used by transaction.
SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

 USED_UBLK  USED_UREC
---------- ----------
         1          1

SQL>

-- Check temporary undo usage.
SET LINESIZE 200
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SELECT *
FROM   v$tempundostat
WHERE  end_time >= SYSDATE - INTERVAL '1' MINUTE;

BEGIN_TIME           END_TIME                UNDOTSN   TXNCOUNT MAXCONCURRENCY MAXQUERYLEN MAXQUERYID    UNDOBLKCNT     EXTCNT    USCOUNT SSOLDERRCNT NOSPACEERRCNT    CON_ID
-------------------- -------------------- ---------- ---------- -------------- ----------- ------------- ---------- ---------- ---------- ----------- ------------- ----------
24-NOV-2014 15:11:09 23-NOV-2014 15:17:30          3          2              0           0                      321          4          1           0           0           0

SQL>

Here we can see only a single byte of conventional undo is generated, which results in approximately 1000 bytes of redo. The UNDOBLKCNT of the V$TEMPUNDOSTAT view shows us 321 blocks of temporary undo have been used.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.