Jay,
The size of the buffer cache and the number of
DB_WRITER_PROCESSES has a big impact on the time it
takes to truncate a table.
Each DBWR process has to scan its portion of the
buffer cache to flush any blocks belonging to the
truncated table or its indexes. This is a sequential
process as only one DBWR process can do this at a
time.
Obviously it's not always feasible to bounce the db,
but if you have to truncate a large # of tables and
can afford to bounce the db, starting it up with a
very small buffer cache and only one DBWR process can
greatly reduce the time it takes to truncate.
HTH,
-- Anita
--- Jay Weinshenker <[EMAIL PROTECTED]> wrote:
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
