Why Do /*+ materialize */ Hints Generate Redo?
What modifications are materialized subquery factoring clauses making that is needing to be protected by redo?
Session A executes a SELECT statement six times.
The first three executions are without the /*+ materialize */ hint and no redo is generated.
The next three executions are with the hint and redo is generated.
Session A was monitored using an admittedly complicated Session B query as I wanted to track session A''s v$sesstat 'temp space allocated (bytes)', v$transaction.used_ublk, and v$transaction.used_urec and AUTOTRACE does not show all of this.
When executed with Session A only and using AUTOTRACE, then, surprisingly, even the first execution with the /*+ materialize */ hint did not generate redo.
Note: You have shown in
https://asktom.oracle.com/ords/asktom.search?tag=temp-undo-enabled-and-with-clause that TEMP_UNDO_ENABLE reduces redo slightly with the /*+ materialize */ hint.
If no undo is being generated then how does this parameter reduce redo - what is needing to be protected with redo?
Thank you for all your Oracle related podcasts, blogs, office hours, online answers, and talks. Very much appreciated.
Using Oracle 19c 19.23 Enterprise Edition on Red Hat.
Session A
SQL>alter session set temp_undo_enabled = false
2 /
Session altered.
SQL>select S.sid
2 , S.serial#
3 , S.saddr
4 , S.audsid
5 from v$session "S"
6 where s.audsid = sys_context('userenv', 'sessionid')
7 /
SID SERIAL# SADDR AUDSID
---------- ---------- ---------------- ----------
924 14019 000000009F00CE68 4203252318
SQL>with "D" as ( select level lvl, rpad('x', 200) from dual connect by level <= 1000000)
2 select max(lvl) from "D"
3 /
MAX(LVL)
----------
1000000
SQL>/
MAX(LVL)
----------
1000000
SQL>/
MAX(LVL)
----------
1000000
SQL>with "D" as ( select /*+ materialize */ level lvl, rpad('x', 200) from dual connect by level <= 1000000)
2 select max(lvl) from "D"
3 /
MAX(LVL)
----------
1000000
SQL>/
MAX(LVL)
----------
1000000
SQL>/
MAX(LVL)
----------
1000000
SQL>
Session B (used to monitor Session A undo, redo, and temp space changes)
SQL>set verify off
SQL>set feedback off
SQL>set sqlterm off
SQL>define undo_count_block = 0
SQL>define undo_count_record = 0
SQL>define redo_entries = 0
SQL>define redo_size = 0
SQL>define temp_space = 0
SQL>column undo_count_block new_value undo_count_block
SQL>column undo_count_record new_value undo_count_record
SQL>column redo_entries new_value redo_entries
SQL>column redo_size new_value redo_size
SQL>column temp_space new_value temp_space
SQL>
SQL>with "S" as
2 ( select S.sid
3 , S.serial#
4 , S.saddr
5 , S.audsid
6 from v$session "S"
7 where s.audsid = 4203252318 -- from Session A's v$session.audsid
8 )
9 -- select * from "S";
10 , "U0" as
11 ( select S.sid
12 , nvl(T.used_ublk, 0) used_ublk
13 , nvl(T.used_urec, 0) used_urec
14 from "S"
15 left join v$transaction "T"
16 on S.saddr = T.ses_addr
17 )
18 , "U" as
19 ( select sid
20 , value
21 , name
22 from "U0"
23 unpivot ( value for name in (used_ublk as 'undo block count', used_urec as 'undo record count') )
24 )
25 -- select * from "U";
26 , "D" as
27 ( select SS.sid
28 , SS.value
29 , N.name
30 from v$sesstat "SS"
31 join v$statname "N"
32 on SS.statistic# = N.statistic#
33 and N.name in('redo size', 'redo entries', 'temp space allocated (bytes)')
34 join "S"
35 on S.sid = SS.sid
36 union all
37 select SS.sid
38 , SS.value
39 - decode( N.name, 'redo entries' , nvl( to_number(trim('&redo_entries')), 0 )
40 , 'redo size' , nvl( to_number(trim('&redo_size ')), 0 )
41 , 'temp space allocated (bytes)', nvl( to_number(trim('&temp_space ')), 0 )
42 , 0
43 ) delta
44 , N.name || ' delta' name
45 from v$sesstat "SS"
46 join v$statname "N"
47 on SS.statistic# = N.statistic#
48 and N.name in('redo size', 'redo entries', 'temp space allocated (bytes)')
49 join "S"
50 on S.sid = SS.sid
51 union all
52 select sid
53 , value
54 , name
55 from "U"
56 union all
57 select sid
58 , value
59 - decode( name, 'undo block count' , nvl( to_number(trim('&undo_count_block ')), 0 )
60 , 'undo record count' , nvl( to_number(trim('&undo_count_record')), 0 )
61 , 0
62 ) delta
63 , name || ' delta' name
64 from "U"
65 )
66 -- select * from "D";
67 select *
68 from "D"
69 pivot ( max(value) for (name) in ( 'undo block count' undo_count_block
70 , 'undo record count' undo_count_record
71 , 'redo entries' redo_entries
72 , 'redo size' redo_size
73 , 'temp space allocated (bytes)' temp_space
74 , 'undo block count delta' undo_count_block_delta
75 , 'undo record count delta' undo_count_record_delta
76 , 'redo entries delta' redo_entries_delta
77 , 'redo size delta' redo_size_delta
78 , 'temp space allocated (bytes) delta' temp_space_delta
79 )
80 )
81 /
SID UNDO_COUNT_BLOCK UNDO_COUNT_RECORD REDO_ENTRIES REDO_SIZE TEMP_SPACE UNDO_COUNT_BLOCK_DELTA UNDO_COUNT_RECORD_DELTA REDO_ENTRIES_DELTA REDO_SIZE_DELTA TEMP_SPACE_DELTA
---------- ---------------- ----------------- ------------ ---------- ---------- ---------------------- ----------------------- ------------------ --------------- ----------------
924 0 0 24 10432 0 0 0 24 10432 0
SQL>-- BEFORE 1
SQL>/
SID UNDO_COUNT_BLOCK UNDO_COUNT_RECORD REDO_ENTRIES REDO_SIZE TEMP_SPACE UNDO_COUNT_BLOCK_DELTA UNDO_COUNT_RECORD_DELTA REDO_ENTRIES_DELTA REDO_SIZE_DELTA TEMP_SPACE_DELTA
---------- ---------------- ----------------- ------------ ---------- ---------- ---------------------- ----------------------- ------------------ --------------- ----------------
924 0 0 24 10432 0 0 0 0 0 0
SQL>-- AFTER 1
SQL>/
SID UNDO_COUNT_BLOCK UNDO_COUNT_RECORD REDO_ENTRIES REDO_SIZE TEMP_SPACE UNDO_COUNT_BLOCK_DELTA UNDO_COUNT_RECORD_DELTA REDO_ENTRIES_DELTA REDO_SIZE_DELTA TEMP_SPACE_DELTA
---------- ---------------- ----------------- ------------ ---------- ---------- ---------------------- ----------------------- ------------------ --------------- ----------------
924 0 0 24 10432 0 0 0 0 0 0
SQL>-- AFTER 2
SQL>/
SID UNDO_COUNT_BLOCK UNDO_COUNT_RECORD REDO_ENTRIES REDO_SIZE TEMP_SPACE UNDO_COUNT_BLOCK_DELTA UNDO_COUNT_RECORD_DELTA REDO_ENTRIES_DELTA REDO_SIZE_DELTA TEMP_SPACE_DELTA
---------- ---------------- ----------------- ------------ ---------- ---------- ---------------------- ----------------------- ------------------ --------------- ----------------
924 0 0 24 10432 0 0 0 0 0 0
SQL>-- AFTER 3
SQL>/
SID UNDO_COUNT_BLOCK UNDO_COUNT_RECORD REDO_ENTRIES REDO_SIZE TEMP_SPACE UNDO_COUNT_BLOCK_DELTA UNDO_COUNT_RECORD_DELTA REDO_ENTRIES_DELTA REDO_SIZE_DELTA TEMP_SPACE_DELTA
---------- ---------------- ----------------- ------------ ---------- ---------- ---------------------- ----------------------- ------------------ --------------- ----------------
924 0 0 24 10432 0 0 0 0 0 0
SQL>-- AFTER 4
SQL>/
SID UNDO_COUNT_BLOCK UNDO_COUNT_RECORD REDO_ENTRIES REDO_SIZE TEMP_SPACE UNDO_COUNT_BLOCK_DELTA UNDO_COUNT_RECORD_DELTA REDO_ENTRIES_DELTA REDO_SIZE_DELTA TEMP_SPACE_DELTA
---------- ---------------- ----------------- ------------ ---------- ---------- ---------------------- ----------------------- ------------------ --------------- ----------------
924 0 0 26 10852 0 0 0 2 420 0
SQL>-- AFTER 5
SQL>/
SID UNDO_COUNT_BLOCK UNDO_COUNT_RECORD REDO_ENTRIES REDO_SIZE TEMP_SPACE UNDO_COUNT_BLOCK_DELTA UNDO_COUNT_RECORD_DELTA REDO_ENTRIES_DELTA REDO_SIZE_DELTA TEMP_SPACE_DELTA
---------- ---------------- ----------------- ------------ ---------- ---------- ---------------------- ----------------------- ------------------ --------------- ----------------
924 0 0 31 11800 0 0 0 5 948 0
SQL>-- AFTER 6
SQL>/
SID UNDO_COUNT_BLOCK UNDO_COUNT_RECORD REDO_ENTRIES REDO_SIZE TEMP_SPACE UNDO_COUNT_BLOCK_DELTA UNDO_COUNT_RECORD_DELTA REDO_ENTRIES_DELTA REDO_SIZE_DELTA TEMP_SPACE_DELTA
---------- ---------------- ----------------- ------------ ---------- ---------- ---------------------- ----------------------- ------------------ --------------- ----------------
924 0 0 36 12704 0 0 0 5 904 0
SQL>
Session A only and AUTOTRACE
SQL>set autotrace on statistic explain
SQL>with "D" as ( select level lvl, rpad('x', 200) from dual connect by level <= 1000000)
2 select max(lvl) from "D"
3 /
MAX(LVL)
----------
1000000
Execution Plan
----------------------------------------------------------
Plan hash value: 2919102380
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(LEVEL<=1000000)
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
359 bytes sent via SQL*Net to client
441 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>/
MAX(LVL)
----------
1000000
Execution Plan
----------------------------------------------------------
Plan hash value: 2919102380
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(LEVEL<=1000000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
359 bytes sent via SQL*Net to client
437 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>with "D" as ( select /*+ materialize */ level lvl, rpad('x', 200) from dual connect by level <= 1000000)
2 select max(lvl) from "D"
3 /
MAX(LVL)
----------
1000000
Execution Plan
----------------------------------------------------------
Plan hash value: 209889042
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6FF6_5147FD35 | | | | |
|* 3 | CONNECT BY WITHOUT FILTERING | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6FF6_5147FD35 | 1 | 115 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(LEVEL<=1000000)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
359 bytes sent via SQL*Net to client
456 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>/
MAX(LVL)
----------
1000000
Execution Plan
----------------------------------------------------------
Plan hash value: 209889042
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6FF7_5147FD35 | | | | |
|* 3 | CONNECT BY WITHOUT FILTERING | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6FF7_5147FD35 | 1 | 115 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(LEVEL<=1000000)
Statistics
----------------------------------------------------------
231 recursive calls
29877 db block gets
29438 consistent gets
29412 physical reads
600 redo size
359 bytes sent via SQL*Net to client
456 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
The secret there is the "TEMP TABLE TRANSFORMATION".
If you throw a trace on such a query, you'll typically see a (internal version of) "create global temporary table ..."
We are using are own temporary table technology to implement the requirements of the "materialize" hint. That means we're
- creating a table
- populating a table
- tearing down the table
all in the process of running your query. That is going to necessitate some undo/redo, because if (say) your instance crashed half way through this query, we need to be able to clean up the mess on restart.