Drop Partition performance

[wakedba]

I have a partitioned fact table that is partitioned by range and subpartitioned by 8 way hash. I have 1083 partitions and need to drop 645 of them. I have a dynamic sql script to loop through and drop the partitions via "execute immediate 'alter table fact1 drop partition p'||partition_name;".
This is a brand new box and no one else is using it's SAN or system resources. Performance seems pretty slow, Is there a large cost with dropping partitions? Each partition has anywhere from 500 records to 100,000 records.


System: Oracle 9.2.x just upgraded to 10.2.x on Solaris 10 box, SAN RAID 10 with 4 way 16GB sparc.

[PAVB]

It's not the number of rows in a partition, it's the number of extents allocated to the partition.

If partition has just one extent it will be dropped instantly.
If partition has thousands of extents it will take forever.

[wakedba]

The extent sizes are a bit small, so there are about 1,000 extents. Even so, it looks like disk IO and CPU are hardly being used. I'm just wondering if it's solely extents or something else?

[PAVB]

trace it. I bet you are gonna see the process moving extents from used to free list most of the time.

Still not sure? you can test it.
Create a test partitioned table with large extent size, populate it then truncate partition.

[pando]

Pavb, freelists are for blocks....

[PAVB]

Pavb, freelists are for blocks....

freelists? who's talking about freelists?

When you drop a table or a table-partition like happens in this case all extents have to be moved from used_extents (sys.uet$) to free_extents (sys.fet$).

I'm sorry but I'll keep calling them "used extent list" and "free extent list"... oops, Oracle calls them the same way!!!

[abhaysk]

do you have a clause to update global indexes?

[pando]

freelists? who's talking about freelists?

When you drop a table or a table-partition like happens in this case all extents have to be moved from used_extents (sys.uet$) to free_extents (sys.fet$).

I'm sorry but I'll keep calling them "used extent list" and "free extent list"... oops, Oracle calls them the same way!!!

Not if you use LMT

[PAVB]

Not if you use LMT

That's correct but, as you can see poster is having performance issues while dropping a table partition comprised of 1,000s of extents; that's why my educated guess is that we are talking about a DMT.

[wakedba]

The tablespaces are all LMT. We don't have any global indexes, only local indexes since we use exchange partitioning and create the indexes before the partitions are plugged into the FACT table, hence no "update global indexes" clause on the drop partition statement. I've yet to trace it, but will do that sometime today, and I would likely test many extents vs. few extents in LMT with partitioned data and see what the performance difference is.