How-To Efficiently Truncate/Drop A Table With Many Extents

Scenario:

When we drop or truncate a large table, it is very slow sometimes.

 

Why slow?

1. When a truncate is issued, the checkpoint process does a complete scan (till 9204) of the buffer cache.  All of the dirty buffers of the object in the buffer cache are written down to disk.  All of the clean buffers are invalidated.  The extents are de-allocated and the high-water mark is reset.

 

2. Too many extents need to move from used extents (uet$) to free extents (fet$)

 

Suggested action:

1. Upgrade to 10g

 Some architectural changes have been done to improve the concurrent drop and truncate operations in 10g

 

2. Using reuse storage clause in truncate table statement

   1). Truncate the table, specifying the REUSE STORAGE clause:

      SQL> TRUNCATE TABLE BIGTAB REUSE STORAGE;

 

   2). If it takes 3 days (72 hours) to drop the table, spread this out over

      6 nights i.e. drop 1/3 GB per night. This can be achieved in 6 (nightly)

      steps as follows:

      Night 1:

        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1707M; (2Gb*5/6)

      Night 2:

        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1365M; (2Gb*4/6)

      Night 3:

        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1024M; (2Gb*3/6)

      Night 4:

        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 683M; (2Gb*2/6)

      Night 5:

        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 341M; (2Gb*1/6)

      Night 6:

        SQL> DROP TABLE BIGTAB;

    The same method can be applied if LOB segments are involved.

         SQL> ALTER TABLE <table_name> MODIFY LOB (<lob_column> )

             DEALLOCATE UNUSED KEEP <value>M;

 

  3). Further Diagnostic

    Step 1. Trace the truncate and identify where the wait is most of the time.

     SQL Syntax:

       alter session set timed_statistics=TRUE;

       alter session set max_dump_file_size=UNLIMITED;

       alter session set events ‘10046 trace name context forever, level 8’;

       [Run the truncate Script]

    The SQL trace will be generated in user_dump_dest.

 

   Step 2. Run tkprof.

    SQL Syntax:

       tkprof <trace file name>  <output file name>;

  Depending on the wait event you may have to further tune the system.

 

Reference: Metalink 287429.1

 

 

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

4 Responses to How-To Efficiently Truncate/Drop A Table With Many Extents

  1. Paul says:

    Thanks for posting this useful information.

  2. neworacledba says:

    in a datawarehousing environment with million rows your method is a boon

  3. kishore says:

    It was usefull info .
    Keep posting these wonderfull scenarios.

    Thanks
    Kishore

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: