In Oracle Database, Can a Select statement generate some Redo vector?
Alireza Kamrani
21/March/2025
No🙄, a pure SELECT statement does not generate redo vectors because it does not modify data. However, there are some exceptions where a SELECT can indirectly cause redo generation.
You may face some situations that a SELECT in Oracle DB has some wait redo log in some classification. And on the first you ask question why a SELECT statement should generate Redo.
The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER or DROP operations. Redo entries can be used for database recovery if necessary.
Why a Regular SELECT Doesn't Generate Redo?
• Redo is generated only for changes to database blocks (INSERT, UPDATE, DELETE, etc.).
• SELECT is a read-only operation, so it does not alter data or create redo records.
When Can a SELECT Generate Redo?
There are some special cases where redo is generated due to a SELECT:
1. Selecting from a Temporary Table (GLOBAL TEMPORARY TABLE - GTT)
• If a SELECT on a GTT causes a temporary UNDO change (e.g., sorting in PGA spills to TEMP tablespace), it may indirectly generate redo for undo segments.
• Although the data in a GTT is written to the temporary tablespace, the associated undo is still written to the normal undo tablespace, which is itself protected by redo, so using a GTT does not reduce undo and the redo associated with protecting the undo tablespace.
• The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior to Oracle 12c, all undo associated with DML against a GTT is written to the normal undo tablespace, which is itself protected by redo. As a result, using a GTT reduces the amount of redo generation, but does not eliminate it. Another why of describing this is, using a GTT removes direct redo generation, but not indirect redo generation cause by undo.
2. Execution Plans that Modify Memory Structures
• A SELECT with operations like sorting (ORDER BY, GROUP BY), hash joins, or parallel execution may trigger temporary segment usage, generating redo for undo blocks.
3. Function-Based Indexes and Materialized Views
• If a SELECT triggers an on-demand refresh of a materialized view or involves function-based indexes, it could cause redo due to internal updates.
4. Read Consistency Mechanism and Multi-Version (Undo Changes)
• Oracle uses UNDO to maintain read consistency, and UNDO changes generate redo.
• If your SELECT accesses data modified by another transaction, Oracle may generate redo for undo blocks to reconstruct a consistent snapshot.
• SELECT for UPDATE- these queries generate redo to maintain the locks
• if Auditing is enabled – auditing the select query could create redo
• delayed block cleanout (Do not neglect this)
When Oracle Database commits a transaction, all modified blocks are not necessarily updated with the commit SCN immediately. In this case, it is done later on demand when the block is read or updated. This is called delayed block cleanout.
Delayed Block Cleanout is the process where Oracle completes the cleanout processes started by the DML operation that modified the affected blocks.
There is a limit on how many blocks Oracle will automatically clean out at commit time and that is 10% of the buffer cache. Any blocks exceeding that threshold must wait for a transaction or SELECT to finish the job.
Use this way to view redo generation for a SQL:
SQL> set autotrace traceonly statistics
Or
SQL> SET AUTOTRACE ON
Select * From Table whre ...;
See output on Redo Size section.
Statistics
----------------------------------------------------------
2274 recursive calls
46503 db block gets
12033 consistent gets
7 physical reads
66050648 redo size
821 bytes sent via SQL*Net to client
822 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10000 rows processed
Why is this Beneficial?
Even though a simple SELECT doesn't generate redo, Oracle’s redo mechanisms ensure:
• Crash Recovery: If undo changes were made, redo for undo ensures rollback after a crash.
• Read Consistency: Redo logging helps manage undo, ensuring a stable snapshot for long-running queries.
• Better Performance with Parallel Query: Parallel queries that involve sorting, temp space usage, or function-based indexes may need redo to maintain integrity.
Conclusion
A SELECT itself does not create redo vectors, but certain indirect operations (undo for read consistency, temp tables, sorting) might cause redo generation. If you're seeing redo during selects, checking V$LOGMNR_CONTENTS or V$UNDOSTAT can help identify the cause.
Diagnosing a specific case where you're seeing redo from a SELECT
To diagnose why a SELECT statement is generating redo, follow these steps:
1. Check if Redo is Generated for the Session
Before running the SELECT, capture the current redo log usage:
SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'redo size';
Then, execute the SELECT query:
SELECT * FROM your_table WHERE some_condition;
Afterward, check the redo size again:
SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'redo size';
SELECT name , sid , value
from v$sesstat s, v$statname n
where name in ('redo size','redo log space requests')
and n.statistic# = s.statistic# and value > 0
order by 1,2
select * from ( select s.sid, ss.value, s.username, s.program, s.type, s.module from v$sesstat ss, v$statname sn, v$session s where ss.statistic# = ss.statistic# and s.sid = ss.sid and name ='redo size' and ss.value > 0 order by ss.value desc) where rownum<20;
If the value increases, the SELECT is causing redo generation.
Also you can see output of V$SESSTAT.
2. Check for Undo Activity (Read Consistency)
Redo can be generated for undo records, especially in long-running queries. Run:
SELECT BEGIN_TIME, END_TIME, UNDOBLKS, TXNCOUNT FROM V$UNDOSTAT WHERE BEGIN_TIME > SYSDATE - INTERVAL '10' MINUTE;
If undo blocks (UNDOBLKS) are increasing during the SELECT, it indicates read consistency is causing redo generation.
3. Verify If a Temporary Table (GTT) is Used
If your query accesses a Global Temporary Table (GTT), redo can be generated for UNDO segments. Check:
SELECT DISTINCT SEGMENT_NAME, SEGMENT_TYPE FROM USER_SEGMENTS WHERE SEGMENT_TYPE LIKE '%TEMP%';
If a GTT is used, and it’s set with ON COMMIT PRESERVE ROWS, undo for GTT modifications can generate redo.
4. Check Sorting, Hash Joins, and Parallel Query Effects
• If sorting occurs, redo might be generated due to PGA spills into the TEMP tablespace.
• Check for sort operations that might trigger redo:
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('workarea executions - multipass', 'workarea executions - onepass');
If multipass executions are increasing, PGA is spilling data to TEMP, which can generate redo for undo tracking.
• Check if the query is running in parallel:
SELECT * FROM V$PX_SESSION;
Parallel execution may trigger redo if it involves temporary table modifications.
5. Analyze Redo Log Contents Using LogMiner
If you want to see exactly what redo is being generated, use LogMiner:
BEGIN DBMS_LOGMNR.START_LOGMNR(
STARTTIME => SYSDATE - INTERVAL '10' MINUTE,
ENDTIME => SYSDATE,
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG );
END;
/
SELECT OPERATION, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE OPERATION != 'SELECT';
This will show what redo entries (if any) were triggered by the SELECT statement.
Next Steps
• If redo is caused by undo for read consistency, consider reducing long-running queries.
• If redo is from sorting, try increasing PGA memory (pga_max_size, pga_aggregate_limit).
• If a GTT is involved, verify whether redo suppression is enabled (TEMP_UNDO_ENABLED=TRUE).
No comments:
Post a Comment