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:

TRUNCATE TABLE BIG_TABLE REUSE STORAGE;

- 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:

select
   extent_management
from
   user_tablespaces
where
   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.

��