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,
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
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,
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: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no parameter, you have to wait.

Regards
Michel
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.



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
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,
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: 283
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.
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,
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,
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: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Wait!
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,
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 ?
Previous Topic: tuning needed for a query
Next Topic: Training on 10g PT
Goto Forum:
  


Current Time: Wed Dec 07 18:55:51 CST 2016

Total time taken to generate the page: 0.10970 seconds