Drop partition (big tabkes) elapsed time

[orajoe]

Hello DBAs,

I was wondering if you could help me with this free space issue.

Currently we have some big partitioned tables, that have started to being recycled. Each partition has +2GB/+0.5GB of data/index.

When we started to recycle partitions three weeks ago, the elapsed time to drop one was 15 to 20 minutes. But, since we have continued recycling space, now its taken more than 5 hours.

The big problem is, since this is taking that long, it has started to affect other processes because of RBS or TEMP segment.

I have noticed that it's not particular of an specific tablespace, because it's now affecting the whole database. Also, it does seem to affect more to index tablespaces than data's.

Do you have any ideas about what can I do to solve this?

Where do you think we might have the first issue to solve?

Where could be the main problem? tablespaces, smon or dictionary?

Thanks, Joe.

[slimdave]

So drop partition is now taking 5 hours? Do you have any global indexes on the table?

[orajoe]

None. It has 21 bitmaps a 1 unique index, but all of them are local to each partition.

I have also noticed that the delete of the index space is much slower than the needed for the data.

Thanks.

[BV1963]

Unique index mostly like is a global index. Please double check

[orajoe]

Double checked. All of them are local. The table is subpartitioned, and the index is partitioned by time (range) and a dimension (hash).

And none of all index subpartitions have become unusable.

Thanks

[slimdave]

are you using the "alter table ... drop partition ..." syntax?

[slimdave]

also, do you have any foreign keys defined against the pk of that table?

[orajoe]

No foreign keys.

And the complete syntax I'm using is:
"alter table mytable drop partition mypartition1;"

Jus FYI, I'm altering the sesion to the enable parallel ddl, each time I drop (or create) a partition for that table., in order to use more than 1 cpu to exec these statements.

Thanks.

[hrishy]

Hi

well why dont you try

alter table mytable disable constraint my_constraint_fk
--disable all constraints on these tables like foreign key
alter table mytable truncate partition mypartition1
--truncate the partition
alter table mytable enable constraint my_constraint_pk

instead and let us know

regards
Hrishy

[slimdave]

dropping a partition is nothing to do with parallelism.