Oracle Scratchpad

September 8, 2014

ASSM Truncate.

Filed under: ASSM,Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 11:34 am BST Sep 8,2014

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     Script:         truncate_test.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2014

	tablespace test_8k_auto_assm
	datafile	-- OMF
	SIZE 1030M
	autoextend off
	blocksize 8k
	extent management local
	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;

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 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”).


The same effect appears in

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

Update (May 2020)

Still the same in



  1. 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 | Reply

    • 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 | Reply

  2. 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 | Reply

    • 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 | Reply

  3. […] 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 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: