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

Oh - except for a little bit at the end of each file you maybe can't use if you're not using bigfile. (But that can happen with uniform, too, maybe worse.)

Also, ASSM can be either uniform or not, as can Freelists. Only dictionary managed gives you the opportunity to be truly helter skelter.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA
Sent: Friday, May 03, 2013 3:10 PM
To: ORACLE-L
Subject: ASSM and tablespace fragmentation in a table that adds 140,140 partitions/subpartitions per year

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

--

http://www.freelists.org/webpage/oracle-l Received on Sat May 04 2013 - 00:44:48 CEST