Home » RDBMS Server » Performance Tuning » drop partition slow (Oracle 9.2.0.6.0 with Partitioning option - Solaris 9)
icon5.gif  drop partition slow [message #302999] Wed, 27 February 2008 09:41 Go to next message
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

Re: drop partition slow [message #303003 is a reply to message #302999] Wed, 27 February 2008 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: drop partition slow [message #303004 is a reply to message #302999] Wed, 27 February 2008 10:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: drop partition slow [message #303005 is a reply to message #303003] Wed, 27 February 2008 10:02 Go to previous messageGo to next message
jzt9sf
Messages: 3
Registered: January 2007
Location: FRANCE
Junior Member
How I can trace the DDL command ?
Re: drop partition slow [message #303006 is a reply to message #303005] Wed, 27 February 2008 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Like any other session.
alter session set events '10046 trace name context forever, level 12';
your DDL
disconnect;

Regards
Michel
Re: drop partition slow [message #303036 is a reply to message #302999] Wed, 27 February 2008 13:00 Go to previous messageGo to next message
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
Re: drop partition slow [message #303461 is a reply to message #303036] Fri, 29 February 2008 07:44 Go to previous messageGo to next message
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
Re: drop partition slow [message #303465 is a reply to message #303461] Fri, 29 February 2008 07:54 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if you activated the trace at the same time you'd now know the reason.

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


Current Time: Mon Dec 05 07:12:45 CST 2016

Total time taken to generate the page: 0.10110 seconds