ASSM and tablespace fragmentation in a table that adds 140,140 partitions/subpartitions per year

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Fri, 3 May 2013 15:10:06 -0400
Message-ID: <CAE-dsOJ9p0F_+w+BEJbH00gJq9WpoPb6ETMWmNQgF0h_gjkPoQ_at_mail.gmail.com>



oracle 11.2.0.3
Since ASSM uses different size extents, are there any issues with fragmentation? There was an Oracle-L thread dating from 2007 that discussed this.

I have multiple tables that need to be hourly partition and will have 16-32 (possibly more ) hash sub-partitions. So I will be constantly dding and dropping partitions. I did some tests with my hash sub-partitioned and they do not appear to be particularly well balanced. The data values I have are skewed. (there are 700+ and more get added, so list is not practical).

With hourly partitions and 16 hash sub-partitions I am adding 140,160 partitions/sub-partitions a year. We really only keep a 4 day window of data. So at any time, we will not have that many partitions. I don't think the data rate will always be real consistent. I also do not know how inconsistent the rate will be on the sub-partitions. I actually won't know until it is deployed to production.

is there any reason for me to use uniform extents? These tables are insert only. I believe freelists settings only matter if the table gets updates. Just want to be safe on fragmentation. Another company runs the production database. They probably won't check for this. We also have very high uptime requirements with at most 1 hour outages for builds/maintenance (and very infrequent).

Also, I cannot use interval partitions. I stumbled on a limitation with intervals. Oracle can only handle a total of 1m partitions/sub-partitions dating from the root partition.

So even though I am only keeping a 4 day window of partitons, the counter starts at the root and keeps going up. So when we hit 1m from the root, we can't handle inserts. This gets turned over to another company and its not a good idea to pass something that would cause an outage. It would almost certainly get forgotten over the years.

I don't have the test case any more, but if you are interested... create an interval partitioned table by hour. subpartition it with 16 hash partitions
set the root to the year 2000.

Insert 1 record. You get an exception stating that oracle can only handle 1m partitions/sub-partitions.

adding this because I am not sure if this increases my chance of fragmentation.
I don't think it matters. So this is probably just background info.

--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 03 2013 - 21:10:06 CEST

Original text of this message