Temporary undo stores the undo for changes on temporary objects in a temporary tablespace. For a workload that stages and discards many temporary rows, this can reduce demand on regular undo and its associated redo logging. Test the application's actual workload, including temporary-space capacity and transaction correctness, before choosing the setting.
Oracle DBA Lesson 65 Part 1 — Reduce Undo Pressure from Temporary Work
What changes
Rows in a global temporary table are private to the session that inserts them. Oracle still needs undo to reverse uncommitted work and to provide transaction behavior.
| Object changed | TEMP_UNDO_ENABLED = FALSE | TEMP_UNDO_ENABLED = TRUE |
|---|---|---|
| Temporary object | Undo in the regular undo tablespace. Changes to this undo generate redo. | Undo in temporary space. This temporary undo has no redo logging. |
| Permanent object | Regular undo and normal redo protection | Regular undo and normal redo protection |
One transaction that changes both kinds of objects can have two undo streams. Enabling temporary undo places new undo for temporary operations in temporary space. Undo already written for the current transaction stays in the tablespace where it was created.
Regular undo tablespaces have physical data files on disk. Temporary tablespaces are backed by physical tempfiles on disk. These are logical storage and allocation paths. A particular record's physical write can occur later through Oracle's normal caching and I/O.
The temporary tablespace must hold temporary undo together with temporary table data, sorts, and other temporary work. Space saved in regular undo can show up as higher demand on temporary storage. A logging reduction, by itself, is not a capacity decision.
Use this on an Oracle Database 19c primary with one instance, automatic undo management, and COMPATIBLE at least 12.0.0, in the application PDB. TEMP_UNDO_ENABLED defaults to FALSE and can be set at session scope. A session value applies to that session. The system default, the undo mode, and permanent-table logging stay as they are. On a physical standby with Active Data Guard, Oracle documents temporary undo as enabled by default, and this parameter has no effect.
See managing undo for temporary undo, compatibility, and enablement, and TEMP_UNDO_ENABLED for scope, the default, and session restrictions.
Create the temporary table once
Create the global temporary table once, before any measured run. CREATE TABLE is DDL and can commit existing work, so keep it outside the interval you measure. The creating schema needs CREATE TABLE.
CREATE GLOBAL TEMPORARY TABLE stage_rows (id NUMBER)
ON COMMIT DELETE ROWS;Later sessions can use the persistent definition. Its rows stay private to the session that inserts them. ON COMMIT DELETE ROWS makes those rows transaction-specific and clears them at commit. Each comparison ends with an explicit ROLLBACK.
See CREATE TABLE for global temporary tables and ON COMMIT.
Set the choice before first temporary use
In a new session in the same application PDB, set the parameter before any temporary object is used:
ALTER SESSION SET temp_undo_enabled = TRUE;
INSERT INTO stage_rows VALUES (101);
ROLLBACK;TRUE enables temporary undo for the session. The insert creates a temporary row, and ROLLBACK reverses it. The row is absent afterward.
Oracle fixes the temporary-object undo choice at the first use of temporary objects, for the rest of that session.
| What the session already used | Later parameter change |
|---|---|
| Temporary objects using regular undo | Setting TRUE has no effect |
| Temporary objects using temporary undo | Temporary undo cannot be disabled for that session |
Rollback, commit, or clearing the table leaves the session on the choice it already bound. A connection pool or session-initialization routine can reuse a physical session that has already touched temporary objects. Use a separate new database session for each choice, and set the value before any application or setup operation can bind it.
Measure the same workload
Hold the precreated table, row count, SQL, transaction boundary, schema, database or PDB, storage conditions, and surrounding load constant.
| Run | New session's first setting | Workload |
|---|---|---|
| A | ALTER SESSION SET temp_undo_enabled = FALSE; | Same bounded insert and rollback |
| B | ALTER SESSION SET temp_undo_enabled = TRUE; | Same bounded insert and rollback |
In each new session, set the choice first, then read the session redo baseline:
SELECT m.value AS redo_bytes
FROM v$mystat m
JOIN v$statname n ON n.statistic# = m.statistic#
WHERE n.name = 'redo size';V$MYSTAT describes the current session. redo size is the total redo generated, in bytes. Resolve the statistic by name, because statistic numbers can change across releases. Confirm that the query returns this one statistic before you measure. The video shows the subtraction. This query supplies the two readings.
See V$MYSTAT for current-session statistics and V$STATNAME for identifying a statistic by name.
After the starting counter, run this bounded workload:
INSERT INTO stage_rows (id)
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 1000;
ROLLBACK;Record elapsed time for that same insert-and-rollback scope. Read redo size again immediately afterward.
Redo generated in bytes = ending redo size − starting redo size.
The delta covers the chosen session interval, including the work inside those boundaries. Keep unrelated statements out of the interval, and use the same observation method in both sessions. Compare each session's own delta. A raw cumulative total from another session, or the instance-wide redo total, counts different work. The insert and rollback interval includes any redo generated during rollback.
Confirm the same transaction behavior, and confirm that the rows are absent after rollback. Record available capacity and concurrent usage in the temporary tablespace. Repeat comparable runs in new sessions with the same warm-up policy, and record whether the interval includes first allocation, parsing, or caching. Keep preparation, DDL, and the connection itself outside the measurement boundary. The comparison carries no promised speedup, reduction percentage, or zero-redo result.
Read temporary undo statistics
SELECT begin_time, end_time, txncount,
undoblkcnt, nospaceerrcnt
FROM v$tempundostat
ORDER BY begin_time DESC
FETCH FIRST 6 ROWS ONLY;| Column | Meaning |
|---|---|
| BEGIN_TIME / END_TIME | Statistics interval boundaries. Intervals are normally ten minutes, and the current interval can be partial. |
| TXNCOUNT | Transactions that bound to the temporary undo segments during the interval |
| UNDOBLKCNT | Temporary undo blocks consumed. The unit is blocks, not bytes. |
| NOSPACEERRCNT | Occurrences of the no-space-left-for-temporary-undo error |
V$TEMPUNDOSTAT holds instance statistics, reports CON_ID, and normally keeps 576 rows spanning four days. Match the database, instance, container, and time to the workload. In RAC, read the instance that did the work. A row can include several sessions. UNDOBLKCNT is a consumption statistic: blocks used in the interval, separate from one statement's redo bytes and from a direct peak-storage gauge. In the 19c reference, MAXQUERYLEN and MAXQUERYID in this view are reserved for future use.
See V$TEMPUNDOSTAT for the interval statistics and units.
Interpret residual redo
Temporary undo itself is not redo-logged. The session can still generate redo from metadata, internal SQL, and permanent-object changes. Treat a smaller redo delta as meaningful when the workload matches, the session was fresh, and the transaction behavior is correct. When the result is unexpected, check the actual scope and workload before changing a default.
See statistics descriptions for redo size and recursive work.
Recap
Temporary undo stores undo for temporary-object changes in a temporary tablespace, and that temporary undo is not redo-logged. Permanent-object changes keep regular undo and normal redo. A transaction that changes both can have two undo streams. Enabling TEMP_UNDO_ENABLED places new temporary-operation undo in temporary space. Undo already written stays where it was created. Regular undo lives in data files. Temporary undo lives in tempfiles, alongside temporary table data, sorts, and other temporary work, so space saved in regular undo can raise demand on temporary storage. On a physical standby with Active Data Guard, temporary undo is enabled by default and the parameter has no effect.
On an Oracle Database 19c primary with automatic undo and COMPATIBLE at least 12.0.0, the parameter defaults to FALSE and can be set for the session. The first use of temporary objects fixes that choice for the rest of the session. Compare two new sessions, each set before any temporary use, with the same bounded insert and rollback. Redo for the interval is that session's ending redo size minus its starting redo size. V$TEMPUNDOSTAT reports interval transactions, temporary undo blocks, and no-space errors. A lower redo delta can still include redo from metadata, internal SQL, and permanent-object changes. Check transaction behavior and temporary capacity before changing a default.
No comments:
Post a Comment