Here’s one that started off with a tweet from Kevin Closson, heading towards a finish that shows some interesting effects when you truncate large objects that are using ASSM. To demonstrate the problem I’ve set up a tablespace using system allocation of extents and automatic segment space management (ASSM). It’s the ASSM that displays the problem but it requires a mixture of circumstances to create a little surprise.
rem rem Script: truncate_test.sql rem Author: Jonathan Lewis rem Dated: Sep 2014 rem create tablespace test_8k_auto_assm datafile -- OMF SIZE 1030M autoextend off blocksize 8k extent management local autoallocate segment space management auto ; create table t1 (v1 varchar2(100)) pctfree 99 tablespace test_8k_auto_assm storage(initial 1G); insert into t1 select user from dual; commit; alter system flush buffer_cache; truncate table t1;
I’ve created a table with an initial definition of 1GB which means that (in a clean tablespace) the autoallocate option will jump straight to extents of 64MB with 256 table blocks mapped per bitmap block for a total of 32 bitmap blocks in each 64MB extent. Since I’m running this on 11.2.0.4 and haven’t included “segment creation immediate” in the definition I won’t actually see any extents until I insert the first row.
So here’s the big question – when I truncate this table (using the given command) how much work will Oracle have to do ?
Exchanging notes over twitter (140 char at a time) and working from a model of the initial state it took a little time to get to understand what was (probably) happening before I could produce this silly example – but here’s the output from a snapshot of v$session_event for the session across the truncate:
Event Waits Time_outs Csec Avg Csec Max Csec ----- ----- --------- ---- -------- -------- local write wait 490 0 83.26 .170 13 enq: RO - fast object reuse 2 0 104.90 52.451 105 db file sequential read 47 0 0.05 .001 0 db file parallel read 8 0 0.90 .112 0 SQL*Net message to client 10 0 0.00 .000 0 SQL*Net message from client 10 0 0.67 .067 153 events in waitclass Other 2 0 0.04 .018 109
The statistic I want to highlight is the number recorded against “local write wait”. Truncating a table of one row we wait for 490 blocks to be written! We also have 8 “db file parallel read” waits which, according to a 10046 trace file, were reading hundreds of blocks. (I think the most significant time in this test – the “RO enqueue wait” – may have been waiting for the database writer to complete the work needed for an object checkpoint, but I’m not sure of that.)
The blocks written were the space management bitmap blocks for the extent(s) that remained after the truncate – even the ones that referenced extents above the high water mark for the table. Since we had set the tables initial storage to 1GB, we had a lot of bitmap blocks. At 32 per extent and 16 extents (64MB * 16 = 1GB) we might actually expect something closer to 512 blocks, but actually Oracle had formatted the last extent with only 8 space management blocks. and the first extent had an extra 2 to cater for the level 2 bitmap lock and segment header block giving: 32 * 15 + 8 + 2 = 490.
As you may have seen from the referenced tweet thread, the impact on the test that Kevin was doing was quite dramatic – he had set the initial storage to 128GB (lots of bitmap blocks), partitioned the table (more bitmap blocks) and was running RAC (so the reads were running into waits for global cache grants).
I had assumed that this type of behaviour happened only with the “reuse storage” option of the truncate command: and I hadn’t noticed before that it also appeared even if you didn’t reuse storage – but that’s probably because the effect applies only to the bit you keep, which may typically mean a relatively small first extent. It’s possible, then, that in most cases this is an effect that isn’t going to be particularly visible in production systems – but if it is, can you work around it? Fortunately another tweeter asked the question “What happens if you ‘drop all storage?'” Here’s the result from adding that clause to my test case:
Event Waits Time_outs Csec Avg Csec Max Csec ----- ----- --------- ---- -------- -------- enq: RO - fast object reuse 1 0 0.08 .079 0 log file sync 1 0 0.03 .031 0 db file sequential read 51 0 0.06 .001 0 SQL*Net message to client 10 0 0.00 .000 0 SQL*Net message from client 10 0 0.56 .056 123 events in waitclass Other 3 0 0.87 .289 186
Looking good – if you don’t keep any extents you don’t need to make sure that their bitmaps are clean. (The “db file sequential read” waits are almost all about the data dictionary, following on from my “flush buffer cache”).
Footnote
The same effect appears in 12.1.0.2
Footnote 2
It’s interesting to note that the RO enqueue wait time seems to parallel the local write wait time: perhaps a clue that there’s some double counting going on. (To be investigated, one day).
Update (June 2018)
The same effect appears in 12.2.0.1
Update (May 2020)
Still the same in 19.3.0.0
So “DROP ALL STORAGE” versus “DROP STORAGE” (default) behaviour is different when a table is created upfront with large extents ? What happens if the table started in AutoAllocate with 64KB and grew to 64MB extents (obviously with many rows — but I don’t think the number of rows matters). In this case, too, there would be many space management bitmap blocks ?
Comment by Hemant K Chitale — September 10, 2014 @ 10:20 am BST Sep 10,2014 |
If you have let the object grow in the normal way (and ignoring special cases of parallel direct path load, move table etc.) then the truncate would reduce you to a single 64KB extent, which would only need 3 blocks written (L1, L2, Seg header). That probably wasn’t immediately obvious from my comment about “effect applies only to the bit you keep”.
Comment by Jonathan Lewis — September 10, 2014 @ 10:27 am BST Sep 10,2014 |
Hi Jonathan. In a tweet you asked, “Just wondering why you set 128GB as initial. Avoiding contention on TT locks on loading ?”. The answer is I my primary aim is to test storage and since I know how much data is loaded into each partition I generally make INITIAL large enough to cover the partition’s entire share of the ingest. Is that possibly a factor in this 6 minute TRUNCATE TABLE issue?
Comment by kevinclosson — September 10, 2014 @ 4:52 pm BST Sep 10,2014 |
Kevin,
It will be a contributing factor. When you truncate any space management blocks in the remaining space have to be (read) cleared and written. The writes are local write waits one block at a time (surely some optimisation available there). Even the space management blocks above the HWM are done (another optimisation relevant to your case, though perhaps insignificant in general). If you want to play around to demonstrate a point:
a) You’re using about 76GB of space in each partition, which means 50GB worth of redundant clearing (ca. 200MB of blocks) will be acquired and written per partition. Try 80GB as the initial.
b) Forget the overhead of loading for a moment, and make the initial 64MB, then the truncate will only have to clear 64MB worth of redundant clearing (ca. 5MB of blocks)
c) Stick with 128GB, but try the truncate with “drop all storage” – should take no time.
Comment by Jonathan Lewis — September 10, 2014 @ 6:20 pm BST Sep 10,2014 |
truncate table XYZ drop all storage takes 2 seconds… down from 6 minutes.
Comment by kevinclosson — September 11, 2014 @ 2:15 am BST Sep 11,2014 |
[…] and you can imagine that if the database crashed (or you crashed a 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 […]
Pingback by Truncate | Oracle Scratchpad — August 25, 2015 @ 8:40 am BST Aug 25,2015 |