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
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
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
- 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 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
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
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:
Truncate: A One-Way Trip
TRUNCATE TABLE Command