The old question about truncate and undo (“does a truncate generate undo or not”) appeared on the OTN database forum over the week-end and then devolved into “what really happens on a truncate” and then carried on.
The quick answer to the traditional question is essentially this: the actual truncate activity typically generates very little undo (and redo) compared to a full delete of all the data because all it does is tidy up any space management blocks and update the data dictionary; the undo and redo generated is only about the metadata, not about the data itself.
Of course, a reasonable response to the quick answer is: “how do you prove that?” – so I suggested that all you had to do was “switch logfile, truncate a table, dump logfile”. Unfortunately I realised that I had never bothered to do this myself and, despite having far more useful things to do, I couldn’t resist wasting some of my evening doing it. Here’s the little script I wrote to help
create table t2 (v1 varchar2(32)); insert into t2 values (rpad('A',32)); commit; create table t1 nologging as with generator as ( select --+ materialize rownum id from dual connect by level <= 1e4 -- > comment to avoid wordpress format issue ) select rownum id, rpad('x',100) padding from generator v1, generator v2 where rownum <= 1e5 -- > comment to avoid wordpress format issue ; create index t1_i1 on t1(id); alter system flush buffer_cache; execute dbms_lock.sleep(3) alter system switch logfile; insert into t2 values(rpad('X',32)); truncate table t1; insert into t2 values(rpad('Y',32)); commit; execute dump_log
Procedure dump_log simply dumps the current log file. The call to switch logfile keeps the dumped log file as small as possible; and I’ve flushed the buffer cache with a three second sleep to minimise the number of misleading “Block Written Record” entries that might otherwise appear in the log file after the truncate. There were all sorts of interesting little details in the resulting activity when I tested this on 12.1.0.2 – here’s one that’s easy to spot before you even look at the trace file:
SQL> select object_id, data_object_id, object_name from user_objects where object_name like 'T1%'; OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- -------------- -------------------- 108705 108706 T1_I1 108704 108707 T1
Notice how the data_object_id of the index is smaller than that of the table after the truncate ? Oracle truncates (and renumbers) the index before truncating the table.
The truncate activity was pretty much as I had assumed it would be – with one significant variation. The total number of change vectors reported was 272 in 183 redo records (your numbers may vary slightly if you try to reproduce the example), and here’s a summary of the redo OP codes that showed up in those change vectors in order of frequency:
Change operations ================= 1 OP:10.25 Format root block 1 OP:11.11 Insert multiple rows (table) 1 OP:24.1 DDL 1 OP:4.1 Block cleanout record 2 OP:10.4 Delete leaf row 2 OP:13.28 HWM on segment header block 3 OP:10.2 Insert leaf row 3 OP:17.28 standby metadata cache invalidation 4 OP:11.19 Array update (index) 4 OP:11.5 Update row (index) 10 OP:13.24 Bitmap Block state change (Level 2) 11 OP:23.1 Block written record 12 OP:14.1 redo: clear extent control lock 12 OP:22.5 File BitMap Block Redo 14 OP:14.2 redo - lock extent (map) 14 OP:14.4 redo - redo operation on extent map 14 OP:5.4 Commit / Rollback 15 OP:18.3 Reuse record (object or range) 15 OP:22.16 File Property Map Block (FPM) 22 OP:13.22 State on Level 1 bitmap block 24 OP:22.2 File Space Header Redo 29 OP:5.2 Get undo header 58 OP:5.1 Update undo block
The line that surprised me was the 14 commit/rollback OP codes – a single truncate appears to have operated as 14 separate (recursive) transactions. I did start to walk through the trace file to work out the exact order of operation but it’s a really tedious and messy task so I just did a quick scan to get the picture. I may have made a couple of mistakes in the following but I think the steps were:
- Start transaction
- Lock the extent map for the INDEX segment — no undo needed
- Lock each bitmap (space management) block — no undo needed
- Reset each bitmap block — undo needed to preserve space management information
- Reset highwater marks where relevant on bitmap and segment header block — undo needed
- Clear segment header block — undo needed
- Write all the updated space management blocks to disc (local write waits)
- Log file records “Block Written Record”.
- For each space management block in turn
- Update space management blocks with new data object_id — undo needed
- Write the updated block to disc (local write wait)
- Log file records one “Block Written Record” for each block
- Repeat all the above for the TABLE segment.
- Start a recursive transaction
- Insert a row into mon_mod$ — undo needed
- recursive commit
- Set DDL marker in redo log (possibly holding the text of the DDL statement, but it’s not visible in the dump)
- Set object reuse markers in the redo log
- update tab$ — needs undo, it’s just DML
- update ind$ — needs undo, it’s just DML
- update seg$ — needs undo, it’s just DML (twice – once for table once for index)
- update obj$ — needs undo, it’s just DML (twice – ditto)
- COMMIT — at last, with a change vector for a “Standby metadata cache invalidation” marker
The remaining 12 transactions look like things that could be delayed to tidy up things like space management blocks for the files and tablespaces and releasing “block locks”.
This first, long, transaction, is the thing that has to happen as an atomic event to truncate the table – and you can imagine that if the database crashed (or you crashed the session) in the middle of a very slow truncate then there seems to be enough information being recorded in the undo to allow the database to roll forward an incomplete truncate, and then roll back to before the truncate.
It would be possible to test whether or not this would actually work – but I wouldn’t want to do it on a database that anyone else was using.
Hi Jonathan
is the insert into sys.mon_mods$ actually a delete/insert an update or a merge?
{For anyone not aware what this table is for, it holds the source data as revealed in dba_tab_modifications – the automatic stats gathering job uses this data to know when stats on a table need to be re-gathered, as they do after a truncate).
Thanks, Martin
Comment by mwidlake — August 25, 2015 @ 10:00 am BST Aug 25,2015 |
Martin,
Remember that I’m only reading the redo, and it is my particular test case, so I can’t predict exactly what other people may see in different circumstances.
I can tell that the redo shows a row being inserted through the array insert mechanism; this might have been an insert statement, it might have been the insert pass of a merge statement. It wasn’t an update or a delete/insert.
In my test I hadn’t done any work before the truncate that could have produced a row in mon_mod$, but I’d guess that if I’d (say) done a bit of DML then a call to dbms_stats.flush_database_monitoring_info() would probably have created a row in mon_mod$ for me and then the truncate would have shown an update of an existing row rather than an insert of a new one.
Easy enough to test for the actual statement by repeating the experiment with SQL tracing enabled.
Comment by Jonathan Lewis — August 25, 2015 @ 10:27 am BST Aug 25,2015 |
Jonathan,
Illuminating analysis as usual. Just to avoid confusion, the question Actually mentioned undo first.
Best regards, Stew
Comment by stewashton — August 25, 2015 @ 10:01 am BST Aug 25,2015 |
Stew,
Thanks for the comment – I had been concentrating so much on deciphering the redo that I wrote “redo” in the opening lines when I should have been writing “undo”. I’ve now changed a critical couple of appearance of “undo” to “redo”.
Comment by Jonathan Lewis — August 25, 2015 @ 10:20 am BST Aug 25,2015 |
[…] on from my earlier comments about how a truncate works in Oracle, the second oldest question about truncate (and other DDL) appeared on the OTN database […]
Pingback by Truncate – 2 | Oracle Scratchpad — August 25, 2015 @ 6:25 pm BST Aug 25,2015 |