Re: Ways to improve speediness of truncate, drop, coalesce

Set the tablespace to some reasonable INITIAL and NEXT that accommodates all the objects with less than 100 extents per object. Let all the objects in the tablespace inherit their storage parameters from the tablespace. You will never have any fragmentation of the tablespace and will not need to coalesce the tablespace. Actually, I've heard that you can go well over 100 extents with no performance problems.

In choosing our INITIAL and NEXT sizes, I tried to make them integral divisors of the shared_buffer_pool, figuring that this would decrease the fragmentation of the SGA.

This only works for Oracle 8 and above.

                                                                                       
      
                    Jay Weinshenker                                                    
      
                    <jweinshe_at_conce        To:     Multiple recipients of list 
ORACLE-L      
                    ntric.net>             <ORACLE-L_at_fatcity.com>                      
      
                    Sent by:               cc:                                         
      
                    root_at_fatcity.co        Subject:     Ways to improve speediness of  
      
                    m                      truncate, drop, coalesce                    
      
                                                                                       
      
                                                                                       
      
                    08/16/2001                                                         
      
                    02:22 PM                                                           
      
                    Please respond                                                     
      
                    to ORACLE-L                                                        
      
                                                                                       
      
                                                                                       
      

Sun Sparc Solaris 2.6
Oracle 8.0.6.2.0

42 G tablespace made up of 21 2G files, called T1 for our example 44 G tablespace made up of 22 2G files, called T2 for our example

Scenario:

We have two tablespaces where we wish to export/import all the data. We wish to do this because of the excessive fragmentation in the tablespaces. The sizes/make up of the tablespaces are above.

First I export all the data. After this, I then truncate all the tables
(to avoid redo generation). Some of these truncates fail due to
parent/child key issues. Fine, no big deal. I then go and drop all the tables (with cascade option).

The timings for these items are currently

Truncate 42 minutes
Drop 149 minutes

QUESTION: Anyone know of a way to speed either of these up? I don't want to drop the schema.

Finally, I want to coalesce the tablespaces before I do the import.

What is the fastest way of doing this? I've tried alter tablespace coalesce t1
alter tablespace coalesce t2

These took a combined time of 150 minutes.

Other ideas which I have yet to explore: a) change the pctincrease on the tablespaces from 0 to 1 back to 0. This should force SMON to coalesce.
b) Modify the init.ora (forgot exact parameter) to dedicate more time to smon coalescing
c) shutdown/startup the database (which should force smon to coalesce)

QUESTION: What of these (or other alternatives, I'm open...) would be the fastest? I cannot believe that 2.5 hours to coalesce 86G of tablespace is the best I can do.

Thoughts?

J

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Weinshenker
  INET: jweinshe_at_concentric.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: tday6_at_csc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).