RE: ASSM and tablespace fragmentation in a table that adds 140,140 partitions/subpartitions per year
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracledbaquestions@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 May 2013 18:44:48 -0400
As far as tablespace fragmentation goes, you can only get that if you drop
things.
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
