OraFAQ Forum: Performance Tuning » drop partition slow

Home » RDBMS Server » Performance Tuning » drop partition slow (Oracle 9.2.0.6.0 with Partitioning option - Solaris 9)

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

drop partition slow [message #302999]

Wed, 27 February 2008 09:41

jzt9sf
Messages: 3
Registered: January 2007
Location: FRANCE

Junior Member

Hi

I have a range-partitioned table with 43200 partitions (3600 partitions per month) in my database. There is no global index in this table. This table contains 200 000 000 rows per month. All the partitions of one month are stored in one tablespace. 3 fields of the table are used for the partitioning.

When I drop all the partitions of a month (3600), it's very slow (37 sec/partition * 3600 => 37 hours).

To drop one partition, I use the 'ALTER TABLE x DROP PARTITION y' command.

I have done some tests, and it's clear that the time to drop a partition is always the same (00:00:37), is independent of the partition size, and it's not shorter if the partition is empty.

Is it normal that it's take a long time ?

How can I reduce this time ?

Thanks you

Configuration :
----------------------
SunOS dose 5.9 sun4us sparc FJSV,GPUZC-M
Oracle 9.2.0.6.0 with Partitioning option
Memory size: 8192 Megabytes
2 CPU SPARC64V 1.32Ghz

Report message to a moderator

Re: drop partition slow [message #303003 is a reply to message #302999]

Wed, 27 February 2008 09:59

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no way to optimize DDL.
Try to run several in parallel but there will be a limit on concurrent access to the dictionary.
You can trace one drop partition and see where Oracle spends its time.
Above all you have to do it when there is no activity on your database.

Regards
Michel

Report message to a moderator

Re: drop partition slow [message #303004 is a reply to message #302999]

Wed, 27 February 2008 10:01

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

Senior Member
Account Moderator

>>Is it normal that it's take a long time ?
IMHO,
strictly depends on configuration, available resources, number of updates need to be made in dictionary and a lot more.

Report message to a moderator

Re: drop partition slow [message #303005 is a reply to message #303003]

Wed, 27 February 2008 10:02

jzt9sf
Messages: 3
Registered: January 2007
Location: FRANCE

Junior Member

How I can trace the DDL command ?

Report message to a moderator

Re: drop partition slow [message #303006 is a reply to message #303005]

Wed, 27 February 2008 10:03

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Like any other session.

alter session set events '10046 trace name context forever, level 12';
your DDL
disconnect;

Regards
Michel

Report message to a moderator

Re: drop partition slow [message #303036 is a reply to message #302999]

Wed, 27 February 2008 13:00

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

Check this link. It clearly explains why you could be experiencing unrealistic delays.

http://www.jlcomp.demon.co.uk/partprob.doc It's a word document and written by Jonathan Lewis. Have a read.

Google always helps.

Cheers

Raj

Report message to a moderator

Re: drop partition slow [message #303461 is a reply to message #303036]

Fri, 29 February 2008 07:44

jzt9sf
Messages: 3
Registered: January 2007
Location: FRANCE

Junior Member

> Try to run several in parallel but there will be a limit on concurrent access to the dictionary.

I have tried to drop 2 partitions in parallel. One partition is dropped in 37 sec, and the second is dropped in 72 sec. It is not conclusive.

Regards

Report message to a moderator

Re: drop partition slow [message #303465 is a reply to message #303461]

Fri, 29 February 2008 07:54

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And if you activated the trace at the same time you'd now know the reason.

Regards
Michel

Report message to a moderator

Previous Topic:	:x ORA-01920: user name 'PERFSTAT' conflicts with another user or role name :x
Next Topic:	Oracle Database Stop working

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 03 13:33:50 CDT 2025