ASSM and tablespace fragmentation in a table that adds 140,140 partitions/subpartitions per year
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 May 2013 15:10:06 -0400
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
