select segment_type, count(*) segment_count, sum(extents) extent_count
from dba_segments
group by segment_type
order by 2 desc;
SEGMENT_TYPE SEGMENT# EXTENT#
TABLE SUBPARTITION 1506441 34429790 this is a lot, isn't it?
TABLE PARTITION 1000799 41428518 so is this
INDEX PARTITION 104062 1440000
INDEX SUBPARTITION 74342 1499875
TABLE 11160 4885061
INDEX 6249 2023874
TYPE2 UNDO 5647 115525
...
...
As you can see, there are a lot partitions and extents in this 11.1.0.7.0 data warehouse RAC. Running on 64-bit Linux with more than 16 nodes.
To drop or truncate any table which has more than 500 partitions (or subpartitions), ORACLE will take a few hours. Even though the hardware is pretty powerful, this type of performance is really disappointing.
If moving extents from used bucket to free bucket is the biggest pain here, why hasn't any code change been done to 11g yet?
System tablespace is not in bad shape (fragment wise). Do I have to cache some of the system tables in SGA?