Maximum Extents in 7.x and 8.0.5
Date: Thu, 22 Jul 1999 22:05:44 +0500
From: S V MANSATA kunal@blr.vsnl.net.in
To: oraapps-dba@cpa.qc.ca
Subject: max number of extents
hello all
is there a limit on the maximum number of extents that one can have in
version 7.x, if so is it 249 and what is the reason for this , also is it
unlimited in version 8.0.5
thanks in advance
promeet s mansata
Date: Thu, 22 Jul 1999 15:09:00 PDT
From: "Pete Beer" pbeer@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: max number of extents
In 7.1 it was limited to 121. This was later lifted - I don't know what the
maximum is. With Oracle 8 it can be unlimited. However, you really should
manage your extents. Sizing should be based on acquiring about 10 extents a
year, and they should be a set size or multiple of that size. The size
should be based on block size and multi-block read count. The I/O buffer on
your system (you will have to do some disovery here) can read in x bytes,
divide that by your database block size. (Database Block size * Multi-block
read count) should be less than I/O Buffer Size. This optimizes Disk I/O
throughput.
You definitely want to avoid creating new extents too often. That incurs a
good deal of overhead creating the new extent.
If the extents for all objects are always a multiple of some base size you
establish, you will help avoid holes in available free space and any holes
that occur will be more likely to be reused.
Allowing only a few extents (10) per table per year helps avoid excessive
table fragmentation. Of course, you will probably want to re-organize the
database periodically too (once a year).
Regards;
Pete Beer
Conley - Canitano & Associates
pbeer@hotmail.com
Date: Thu, 22 Jul 1999 20:58:58 PDT
From: "shankar govindan" shankargovindan@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: max number of extents
That depends on the block_size.
For
2k - 121
4k - 249
8k - 505
Ver 7.3.2 > max extents Unlimited.
If you set Max_extents unlimited at table level then the contol of the table
growth becomes difficult. Normally Oracle for APPS guys install the
tablespace at Unlimited and control the tables with Max_extent size defined
with proper analysis.
Shankar
Date: Thu, 22 Jul 1999 22:09:53 PDT
From: "venkat subramaniam" venkatdvs@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: max number of extents
Hi
I feel there is a formula which will give you the correct picture of max
extents.
Now a basic thumb rule is Initial,Next should be same in RBS.But in the
case of Datafile it is diffrent.Please make initial small and have next
extent bigger some where around 100% to 200% of the initial This should be
able to sustain for a long time.This factor is also depending upon the
status of transactions.
Dont allow the Next extent to grow more than 5 extents this should lead to
performance degration.
cheers
Date: Fri, 23 Jul 1999 21:03:49 PDT
From: "shankar govindan" shankargovindan@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: max number of extents
I disagree with venkat on this. If its is datafile then it is tablespace
extent size that you are talking about. The tablespace size initial and next
should be the same always for uniform growth and avoid tablespace level
fragmentation. There is write up by Bhaskar of Oracle Corp or rather
whitepaper which clearly mentions that the tablespace size should be of
three sizes, something like 160k,5120k or 160M depending on your database
size and transaction volume.This has been set and tested out by a lot DBA's
and found to reduce Fragmentation. On the other hand table level initial and
next should be set correctly solely depending on the transaction volume.I
prefer to set it initial = next at table level to.
Shankar
Date: Sat, 24 Jul 1999 15:07:08 PDT
From: "Pete Beer" pbeer@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: max number of extents
For fragmentation concerns, the extent size is what really matters. You
should choose some standard extent sizes, so that freed up extents are more
likely to be reused without leaving odd size free space holes that a new
extent can't use.
Again, watch you active tables and make their next extents a size that is a
multiple of your standard extent size (if your standard extent size was
128K, then 512K or 1M would be a useful multiple). Size next extent to keep
total new extents a year under 10.
Also remember that I/O will be wasted if the size of your systems disk i/o
buffer is not considered in your database multi-block read count and your
standard next extent sizing.
Regards;
Pete Beer
Date: Sun, 25 Jul 1999 22:00:51 PDT
From: "venkat subramaniam" venkatdvs@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: max number of extents
Hi
From your(Shankar Govindan) mail there is a statement that extent should be evenly sized
,As per your documentation if you have an initial extent of 160k then you
should have an next extent of 512k so that it sustains for a longer period.
Basic principle towards growth of extent is it should not throw more
extents which leads to performance problem.So plan accordingly and
decide.Dont take what Oracle says as a thumb rule.
Main criteria W.R.T DBA is to analyze the DB and plan according to that.
cheers
Date: Mon, 26 Jul 1999 16:02:48 -0400
From: "Mark W. Farnham" mwf@rsiz.com
Subject: RE: max number of extents
You( Pete Beer) are on the right track. Please remember that Oracle "helpfully" rounds
next extent sizes up to a multiple
of 5 database blocks, if the request is for more than 4 blocks. For a
conjunction of 64K physical i/o and 16K
database blocks, I find a base building block of 320K to be very useful.
I also want to underscore your point about extent size, rather than
quantity, being the important performance
determinant. If you have extents smaller than 64K on tables, you are
missing chances for multiblock reads.
Unless you are intensely memory constrained, the number of extents has
almost no effect as long as each
extent is large enough to avoid wasted i/o. You do wish to avoid dynamic
extention causing recursive sql too
frequently, so an extention size that allows extention only every 1 to 3
months is usually best.
The old thumbrule of 5 extents came about a long time ago when disk was
expensive and we were trying to
use the minimum extents sizes we could get away with. A lot of installat
ions were using 2K database blocks,
and many performance problems would go away if you rebuilt your tablespaces
when extent counts rose to 5
or so. But now, disk is cheap, and it is far more effective to use extents
larger than and a multiple of the
multiblock read size, wasting space on some very small segments but
optimizing i/o across the entire database without the should I rebuild
question hanging over your head.
Since the advent of the 'alter index rebuild' to efficiently correct
indexes skewed by monotonically increasing keys, there is very little
reason to rebuild an entire tablespace or database any more.
Perhaps Venkat is referring to the initial development of a custom schema.
If you have no idea what the size
and growth of a new application will be, then using small initial extents
and moderate next extent sizes can
give you data by extent date by object. Then after you have 6 months or a
year of database growth data to use for planning you can rebuild the
database (just that once) or adjust next sizes if things are reasonable so
that you have a manageable and reasonable extent growth over time and can
do real capacity planning.
Mark