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@conce To: Multiple recipients of list
ORACLE-L
ntric.net> <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] 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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
