8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Writing to the undo tablespace requires the database to be open in read-write mode, so global temporary tables can't be used in read-only databases and physical standby databases.
- Global temporary tables contain transient data, which is not needed in a recovery scenario, so protecting them with redo represents an unnecessary additional load on the system.
- The undo associated with global temporary tables adds to the total space needed to meet the undo retention period.
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.
- Global Temporary Tables (GTT)
- Session Sequences in Oracle Database 12c Release 1 (12.1)
- Temporary Undo for Global Temporary Tables (GTTs) in Oracle 12c
- Global Temporary Tables (GTT) in Oracle
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.
- The temporary undo functionality is only available if the
COMPATIBLE
parameter is set to 12.0.0 or higher. - Once a session has created some undo for a global temporary table, whether using conventional or temporary undo, any attempt to alter the
TEMP_UNDO_ENABLED
parameter setting will be ignored for the lifetime of the session. No error is produced. - Temporary undo is the default for standby databases, so any alteration to the
TEMP_UNDO_ENABLED
parameter is ignored on standby databases.
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:
- Managing Temporary Undo
- TEMP_UNDO_ENABLED
- V$TEMPUNDOSTAT
- Global Temporary Tables (GTT)
- Session Sequences in Oracle Database 12c Release 1 (12.1)
- Temporary Undo for Global Temporary Tables (GTTs) in Oracle 12c
- Global Temporary Tables (GTT) in Oracle
Hope this helps. Regards Tim...