Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Hypercharge Oracle truncate table performance

Oracle Database Tips by Donald BurlesonMay 21, 2015

Question (Balasubramanian .J):  I have a very large 50 gig table and I?ve had cases where a truncate table hangs.  I understood that there we no performance issues with a truncate table since it only nulls the high water mark.  What causes poor performance problems with truncate table?  I have tried to truncate table before which took 32 hours to get truncated.

Answer: (from forum):  Truncate performance is directed related to system load, and truncates will always run fast on an idle database, or on a database with low activity. 

Oracle does have to free-up extents within the table, perhaps that is the source of the waiting and during a truncate you may see two wait events in v$session_wait: "rdbms ipc reply" and "local write wait".

There are also slow truncate performance  reports where you should create a smaller cache, dedicated to the specific (partitioned ) object.  This is done by moving the partition into a separate blocksize which is defined with a separate data buffer. See my notes on deploying multiple blocksizes for additional details.

For non-partitioned tables, you can create a db_16k_cache_size of 500 meg and adjust the table to use the tiny buffer.

I gather you have already truncated the table at least once, but it takes too long. If that's true, consider the REUSE STORAGE option. If that isn't feasible, check if the table is in a dictionary-managed tablespace. If it is, try increasing the extent size - releasing lots of extents in a dictionary-managed tablespace is really slow. Better yet, change to a locally-managed tablespace.  As noted you can use the syntax like:


- You may want to check you patch level for BUGS with truncate.

- Limit the activity on the system (preferably none).

- Increase redo log size if you are having frequent log switches (more than one every 15 minutes or so)

- If using Oracle 9i, check out the db_writer_processes (Increase DBWR Throughput Note: 62172.1).  That can improve truncate table speed.

- Check whether you are using a dictionary-managed tablespace:

   tablespace_name in
(select tablespace_name from user_tables where table_name='50GBTABLE');

- If you are, check how many extents in that table.  If it's more than 20,000 extents, I would be concerned that a truncate table would be too slow.

        select extents from user_segments where segment_name='50GBTABLE';

I have seen truncate table or drop table statements take a very long time to complete because the table was very badly fragmented, that is, it had a lot of extents. If you have lots of extents, those extents must be released when you do a drop, or when you do a truncate without "reuse storage". If you have 100,000 or more extents in a dictionary-managed tablespace that can take hours; I've heard of it taking days. If you use "reuse storage", then those extents will not be released.

 If you have plenty of disk space, you could rename this table and then create a new table with the same name and structure as this one. The new table should be in a locally-managed tablespace with uniform extent size of 10 MB or more, so you won't have this truncate table problem again in the future.

See related articles here:

Oracle TRUNCATE tips
Truncate: A One-Way Trip



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.