OraFAQ Forum: Server Administration » DDL are very slow on Oracle9i

Home » RDBMS Server » Server Administration » DDL are very slow on Oracle9i

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DDL are very slow on Oracle9i [message #133262]

Thu, 18 August 2005 05:07

Hamlet
Messages: 4
Registered: August 2005

Junior Member

A little history:

there was database crash due hardware problems.
Using old scripts was created database, similar to old one, and using imp FULL=Y DESTROY=Y full database dump was imported.
For this moment database are up and running, all sql queries are ok, but all DDL command are extreamly slow !!! droping empty table are takes about 3-5 minutes
mostly during this operation CPU a disk subsistem are not overloaded (about 10%).
for this moment drop user are running, and current statement for this command (I can see it using TOAD) is UPDATE seg$ set ....

Any ideas ?

Report message to a moderator

Re: DDL are very slow on Oracle9i [message #133284 is a reply to message #133262]

Thu, 18 August 2005 07:02

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

First, truncate table with reuse storage.
then
drop the table.

scott@9i > truncate table emp reuse storage;

Table truncated.

scott@9i > drop table emp;

Table dropped.

Report message to a moderator

Re: DDL are very slow on Oracle9i [message #133338 is a reply to message #133284]

Thu, 18 August 2005 09:48

Hamlet
Messages: 4
Registered: August 2005

Junior Member

truncating takes 5 sec, dropping about 5 minutes....

Report message to a moderator

Re: DDL are very slow on Oracle9i [message #133341 is a reply to message #133338]

Thu, 18 August 2005 09:59

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

happen to be using Dictionary Managed tablespaces?
then the behaviour is expected.
Else
Open a tar.

Report message to a moderator

Re: DDL are very slow on Oracle9i [message #133343 is a reply to message #133338]

Thu, 18 August 2005 10:05

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

and did you try the good old standard approach?

HOw big is the table?
if it is say 10m it and took 5mins to drop it , try

truncate table emp reuse storage;
alter table emp keep 2m;##see how long it takes.do it again.
drop table emp;

Report message to a moderator

Re: DDL are very slow on Oracle9i [message #133349 is a reply to message #133343]

Thu, 18 August 2005 10:28

Hamlet
Messages: 4
Registered: August 2005

Junior Member

Hmmm, looks like that the problem disappears without any tuning and manipulating ...
All table can be dropped now in seconds...

I cant imagine, what happens....

Report message to a moderator

Re: DDL are very slow on Oracle9i [message #133350 is a reply to message #133341]

Thu, 18 August 2005 10:31

Hamlet
Messages: 4
Registered: August 2005

Junior Member

Mahesh Rajendran wrote on Thu, 18 August 2005 09:59

happen to be using Dictionary Managed tablespaces?
then the behaviour is expected.
Else
Open a tar.

in Dictionary Managed tablespaces case - what need to do, if it happens again ?

Report message to a moderator

Re: DDL are very slow on Oracle9i [message #133354 is a reply to message #133350]

Thu, 18 August 2005 10:45

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

It is becuase of extent managemnet.
switch to LMT
or
use same methods as discussed above.

Report message to a moderator

Previous Topic:	Dynamic Initializiattion Parameters
Next Topic:	Problem in copying production

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 10:25:57 CST 2025