Home » RDBMS Server » Performance Tuning » Slow Query Execuation
Slow Query Execuation [message #227404] Wed, 28 March 2007 03:09 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi All,
If a query like 'Drop user xxx cascade;' take more than 10 min to execute ..!then where must anybody start searching?which parameter in which configuration file must change to solve this slowness..?


Thanks in Advance,

Report message to a moderator

Re: Slow Query Execuation [message #227405 is a reply to message #227404] Wed, 28 March 2007 03:13 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
'Drop user xxx cascade

Actaully it is depand on schema.. how many objects on it.

if schema is very big it will take time to drop.

regards
Taj

Report message to a moderator

Re: Slow Query Execution [message #227407 is a reply to message #227405] Wed, 28 March 2007 03:16 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks for your reply,
well the schema is a test schema and it is small enough to execute the same query within 5 misc..so any suggestion?



thanks in Advance,

Report message to a moderator

Re: Slow Query Execuation [message #227422 is a reply to message #227404] Wed, 28 March 2007 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no parameter, you have to wait.

Regards
Michel

Report message to a moderator

Re: Slow Query Execution [message #227423 is a reply to message #227407] Wed, 28 March 2007 03:50 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
There are other options..

1.If your schema is NOT sharing tablespace with another schema, the fastest way would be

DROP TABLESPACE tablespace_name INCLUDING CONTENTS.


2.Another way i found is faster is
to truncate the tables first then drop the schema.

Report message to a moderator

Re: Slow Query Execution [message #227428 is a reply to message #227423] Wed, 28 March 2007 04:01 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Hi,
I want to add one more option.

Disable all constraints and drop user .


regards
Taj

Report message to a moderator

Re: Slow Query Execution [message #227439 is a reply to message #227422] Wed, 28 March 2007 04:35 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thank you All,
The problem have nothing to do ' i think ' with the tablespace ..because i tried to drop another user which has another default_tablespace and the execution time of the other query is the same almost 22 min...
So any suggestions?


Thanks in Advance,

Report message to a moderator

Re: Slow Query Execuation [message #227444 is a reply to message #227404] Wed, 28 March 2007 04:54 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
truncate all tables in the schema you like to drop.

It has saved me several hours a few times.

Report message to a moderator

Re: Slow Query Execution [message #227470 is a reply to message #227428] Wed, 28 March 2007 05:40 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi All,
after i disabled all foreign keys constraints on all tables + disabled all triggers + truncate all tables..the execution time is still 22 min..

So any suggestions??

thanks in Advance,

Report message to a moderator

Re: Slow Query Execuation [message #227478 is a reply to message #227444] Wed, 28 March 2007 06:14 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi All,
after i disabled all foreign keys constraints on all tables + disabled all triggers + truncate all tables..the execution time is still 22 min..

So any suggestions??

thanks in Advance,

Report message to a moderator

Re: Slow Query Execuation [message #227482 is a reply to message #227478] Wed, 28 March 2007 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Wait!

Report message to a moderator

Re: Slow Query Execution [message #227484 is a reply to message #227482] Wed, 28 March 2007 06:30 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
The same query cost less than 5 misc 2 days ago..! maybe some test by the OEM can solve the problem??


Thanks in Advance,

Report message to a moderator

Re: Slow Query Execution [message #227498 is a reply to message #227439] Wed, 28 March 2007 07:18 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
bahy91 wrote on Wed, 28 March 2007 11:35
Thank you All,
The problem have nothing to do ' i think ' with the tablespace ..because i tried to drop another user which has another default_tablespace and the execution time of the other query is the same almost 22 min...
So any suggestions?


Thanks in Advance,



As I said earlier
Quote:

1.If your schema is NOT sharing tablespace with another schema, the fastest way would be
DROP TABLESPACE tablespace_name INCLUDING CONTENTS.


Also have you monitored the OS while doing this ?

Report message to a moderator

Previous Topic: tuning needed for a query
Next Topic: Training on 10g PT
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 16 08:20:12 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.