Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle 7.3.4 problem... SLOW Drop user

RE: Oracle 7.3.4 problem... SLOW Drop user

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 4 Aug 2004 12:16:18 -0500
Message-ID: <003e01c47a46$c1aaeb80$6701a8c0@CVMLAP02>


The one thing that is slow when a dictionary-managed segment has lots of extents is DROP. To enable recoverability, DROP works this way:

	while exists(extent) {
		seek to the end of the extent list
		drop the final extent
	}

The problem is that this algorithm is O(n^2). A 100-extent table will = take
100^2 =3D 10,000x longer to drop than a 1-extent table. A 1,000-extent = table
will take 100x longer to drop than a 100-extent table (or a million = times
longer than a 1-extent table).

With locally-managed segments, this is no longer a problem. So, part 2 = of
your solution is that when you rebuild your schema, use locally-managed tablespaces if you can.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 8/10 Boston, 9/14 San Francisco, 10/5 = Charlotte
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 = Hartford
- Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details...

-----Original Message-----

From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of GUPTA, DEEPENDER
Sent: Wednesday, August 04, 2004 11:59 AM To: 'oracle-l_at_freelists.org'
Subject: RE: Oracle 7.3.4 problem... SLOW Drop user

Truncate all the tables and then try it..

Deep

-----Original Message-----

From: Denham Eva [mailto:EVAD_at_TFMC.co.za]=20 Sent: Wednesday, August 04, 2004 11:02 AM To: oracle-l_at_freelists.org
Subject: Oracle 7.3.4 problem... SLOW Drop user

Hello Folks,
=20

We have a legacy system on Oracle 7.3.4 on NT.=20

I am dropping the user with cascade, but it is extremely slow???

About 2GB in 5hrs? and there are still 10GB to go.

=20

Can anyone tell me why? Can I stop it and restart it for beter performance?

=20

I am doing the drop from a client machine, could this be the problem?

=20

Any help would be appreciated!

Denham



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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Aug 04 2004 - 12:12:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US