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: Tue, 7 May 2013 16:32:59 -0400
uniform extents is per tablespace with local (as opposed to dictionary)
managed tablespaces.
so if you run the query
select
tablespace_name,initial_extent,next_extent,extent_management,allocation_type
from dba_tablespaces;
(recommend set linesize 140 pagesize 40 null ~ [or something like that])
Then you'll see 8M for initial_extent and next_extent for the tablespace
you're using.
Once set, I don't think you can change that, although it might be possible
if the tablespace is still empty, but then you could simply re-create it.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Dba DBA
Sent: Tuesday, May 07, 2013 1:58 PM
To: ORACLE-L
Subject: Re: ASSM and tablespace fragmentation in a table that adds 140,140
partitions/subpartitions per year
We add and drop 140,140 partitions/year.
I meant let oracle pick the extents or I pick them. I got a little mixed up
on ASSM. I noticed something odd with uniform extents ( i have not used
these in years).
I have 8k block sizes. I set uniform extents to 1mb. I noticed that my
extents are actually 8mb. I can tell these because when I create the table
and get the initial set of partitions, I can see the 1 extent per partition.
so extent size with uniform is the size you set times the block size? This
is in 11.2.0.3
I used 'uniform extent size 1mb' and got 8 mb extent sizes when I look in
the database. This is before any data is in the table.
On Fri, May 3, 2013 at 6:44 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
